Spring boot JPA- How To Handle or insert ARRAY and JSON Data in PostgreSQL

---

In this article, we'll explore how to work with arrays and JSON data in PostgreSQL using Spring Boot and JPA (Java Persistence API).

Dependencies

we will use the hibernate-types library developed by a renowned Hibernate expert, Vlad Mihalcea. This library provides us with a few types that are not native in the core Hibernate ORM.

<dependency>
  <groupId>com.vladmihalcea</groupId>
  <artifactId>hibernate-types-52</artifactId>
  <version>2.20.0</version>
</dependency>

Defining columns

Here's an example of creating a report table that stores its request parameters in a JSONB column called "request" and senderList(array of emailIds) in "mail_list".

create table generate_report_detail(
  id varchar(255) not null PRIMARY key,
  mail_list varchar[],
  request jsonb,
  created_at int8
);

Data Model

The data model for this tutorial will allow us to store information about reportDetail. A reportDetail has list of receviers'email. A request JSON and created_at timestamp in millseconds.

  @Data
  @Entity(name = "report_detail")
  @Table(name = "report_detail")
  @TypeDefs({
  	@TypeDef(name = "string-array", typeClass = StringArrayType.class),
  	@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
  })
  public class ReportDetail
  {
  	@Id
  	@GenericGenerator(name = "uuid2", strategy = "uuid2")
  	@GeneratedValue(strategy = GenerationType.IDENTITY, generator = "uuid2")
  	@Column(name = "id")
  	String id;

  	@Type(type = "string-array")
  	@Column(name = "mail_list", columnDefinition = "varchar[]")
  	String[] mailList;

  	@Type(type = "jsonb")
  	@Column(name = "request", columnDefinition = "jsonb")
  	ReportParameter request;

  	@Column(name = "created_at")
  	Long createdAt;
  }

The @Type for StringArrayType and JsonBinaryType makes the types string array and jsonb available. for json type, use JsonStringType. Similarly, we can find a few other handy mappers like DateArrayType, EnumArrayType, and DoubleArrayType in the library.

PostgreSQL's JSON data type stores data as text, following JSON rules, with processing happening during each execution. In contrast, JSONB stores data in binary format, initially slower for input but offering faster processing, resulting in superior efficiency for JSONB.

NOTE: the @TypeDef is no longer available in Hibernate 6, so you have to use the @Type annotation instead.

ReportParameter class and DAO method

@Data
public class ReportParameter
{
	@ApiModelProperty(example = "SUMMARY_REPORT", allowableValues = ReportTypeEnum.ALLOWABLE_VALUES)
	@CustomEnumValidator(enumClass = ReportTypeEnum.class, fieldName = "reportType")
	String reportType;
	@NotEmpty(message = "sendReportTo list cannot be empty.")
	List sendReportTo;
}

// method to save data just for reference
public ReportDetail insertReportDetail(ReportParameter reportRequest)
{

	ReportDetail reportDetail = new ReportDetail();
	reportDetail.setParameters(reportRequest);
	reportDetail.setSenderList(reportRequest.getSendReportTo().toArray(new String[0]));
	reportDetail.setCreatedAt(currentMillis);

	return reportDetailRepository.save(reportDetail);
}

Conclusion

In this article, we explored mapping the PostgreSQL array and JSON with Hibernate.we used the hibernate-types library to map the PostgreSQL array and JSON.

Top