How to map a PostgreSQL ARRAY to a Java List with JPA and Hibernate
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, I’m going to show you how to map PostgreSQL ARRAY column types (e.g., text
, int
, double
, enum
, date
, timestamp
, UUID
) to Java List entity attributes with JPA and Hibernate.
As illustrated by this article, the Hypersistence Utils project provides support for mapping PostgreSQL ARRAY column types to Java Array entity attributes/
However, you can also map database ARRAY columns to java.util.List
entity attributes, as it’s much more convenient to use Java Collections than Arrays.
How to map PostgreSQL ARRAY column types to #Java List entity attributes with JPA and Hibernate.https://t.co/iZOOj8WgPI
— Java (@java) February 9, 2020
Domain Model
Let’s consider we have an event
database table containing several array column types:
We would like to map the event
table to the following Event
JPA entity that uses Java List
attributes to represent the associated PostgreSQL ARRAY columns:
The SensorState
Java Enum looks as follows:
public enum SensorState { ONLINE, OFFLINE, UNKNOWN; }
PostgreSQL ARRAY to Java List Hibernate Type
Because Hibernate ORM does not support ARRAY column types, we need to use the Hypersistence Utils open-source project to be able to map PostgreSQL ARRAY columns to Java List entity attributes.
After adding the Hypersistence Utils dependency, you can use the ListArrayType
as illustrated by the following JPA entity mapping.
For Hibernate 6, the mapping will look as follows:
@Entity(name = "Event") @Table(name = "event") public class Event { @Id private Long id; @Type(ListArrayType.class) @Column( name = "sensor_ids", columnDefinition = "uuid[]" ) private List<UUID> sensorIds; @Type(ListArrayType.class) @Column( name = "sensor_names", columnDefinition = "text[]" ) private List<String> sensorNames; @Type(ListArrayType.class) @Column( name = "sensor_values", columnDefinition = "integer[]" ) private List<Integer> sensorValues; @Type(ListArrayType.class) @Column( name = "sensor_long_values", columnDefinition = "bigint[]" ) private List<Long> sensorLongValues; @Type( value = ListArrayType.class, parameters = { @Parameter( name = ListArrayType.SQL_ARRAY_TYPE, value = "sensor_state" ) } ) @Column( name = "sensor_states", columnDefinition = "sensor_state[]" ) private List<SensorState> sensorStates; @Type(ListArrayType.class) @Column( name = "date_values", columnDefinition = "date[]" ) private List<Date> dateValues; @Type(ListArrayType.class) @Column( name = "timestamp_values", columnDefinition = "timestamp[]" ) private List<Date> timestampValues; //Getters and setters omitted for brevity }
And for Hibernate 5, like this:
@Entity(name = "Event") @Table(name = "event") @TypeDef(name = "list-array", typeClass = ListArrayType.class) public class Event { @Id private Long id; @Type(type = "list-array") @Column( name = "sensor_ids", columnDefinition = "uuid[]" ) private List<UUID> sensorIds; @Type(type = "list-array") @Column( name = "sensor_names", columnDefinition = "text[]" ) private List<String> sensorNames; @Type(type = "list-array") @Column( name = "sensor_values", columnDefinition = "integer[]" ) private List<Integer> sensorValues; @Type(type = "list-array") @Column( name = "sensor_long_values", columnDefinition = "bigint[]" ) private List<Long> sensorLongValues; @Type( type = "io.hypersistence.utils.hibernate.type.array.ListArrayType", parameters = { @Parameter( name = ListArrayType.SQL_ARRAY_TYPE, value = "sensor_state" ) } ) @Column( name = "sensor_states", columnDefinition = "sensor_state[]" ) private List<SensorState> sensorStates; @Type(type = "list-array") @Column( name = "date_values", columnDefinition = "date[]" ) private List<Date> dateValues; @Type(type = "list-array") @Column( name = "timestamp_values", columnDefinition = "timestamp[]" ) private List<Date> timestampValues; //Getters and setters omitted for brevity }
When using Hibernate 5, the @TypeDef
mapping at the Event
entity level defines the list-array
Hypersistence Utils alias that is referenced by the @Type
Hibernate annotation for the sensorIds
, sensorNames
, sensorValues
, sensorLongValues
, dateValues
, and timestampValues
entity attributes.
Note that the
@TypeDef
is no longer available in Hibernate 6, so you have to use the@Type
annotation instead.
On the other hand, the sensorStates
entity attribute defines a specific Hibernate @Type
annotation as it needs to pass the database column type used by the PostgreSQL ARRAY.
Testing time
When persisting the following Event
entity:
entityManager.persist( new Event() .setId(1L) .setSensorIds( Arrays.asList( UUID.fromString( "c65a3bcb-8b36-46d4-bddb-ae96ad016eb1" ), UUID.fromString( "72e95717-5294-4c15-aa64-a3631cf9a800" ) ) ) .setSensorNames(Arrays.asList("Temperature", "Pressure")) .setSensorValues(Arrays.asList(12, 756)) .setSensorLongValues(Arrays.asList(42L, 9223372036854775800L)) .setSensorStates( Arrays.asList( SensorState.ONLINE, SensorState.OFFLINE, SensorState.ONLINE, SensorState.UNKNOWN ) ) .setDateValues( Arrays.asList( java.sql.Date.valueOf(LocalDate.of(1991, 12, 31)), java.sql.Date.valueOf(LocalDate.of(1990, 1, 1)) ) ) .setTimestampValues( Arrays.asList( Date.from( LocalDate.of(1991, 12, 31) .atStartOfDay() .atZone(ZoneId.systemDefault()) .toInstant() ), Date.from( LocalDate.of(1990, 1, 1) .atStartOfDay() .atZone(ZoneId.systemDefault()) .toInstant() ) ) ) );
Hibernate generates the proper SQL INSERT statement:
INSERT INTO event ( date_values, sensor_ids, sensor_long_values, sensor_names, sensor_states, sensor_values, timestamp_values, id ) VALUES ( { "1991-12-31", "1990-01-01" }, { "c65a3bcb-8b36-46d4-bddb-ae96ad016eb1", "72e95717-5294-4c15-aa64-a3631cf9a800" }, { "42", "9223372036854775800" }, { "Temperature", "Pressure" }, { "ONLINE", "OFFLINE", "ONLINE", "UNKNOWN" }, { "12", "756" }, { "Tue Dec 31 00:00:00 EET 1991", "Mon Jan 01 00:00:00 EET 1990" }, 1 )
And, when fetching the Event
entity, we can see that all attributes have been properly read from the database:
Event event = entityManager.find(Event.class, 1L); assertEquals( Arrays.asList( UUID.fromString( "c65a3bcb-8b36-46d4-bddb-ae96ad016eb1" ), UUID.fromString( "72e95717-5294-4c15-aa64-a3631cf9a800" ) ), event.getSensorIds() ); assertEquals( Arrays.asList("Temperature", "Pressure"), event.getSensorNames() ); assertEquals( Arrays.asList(12, 756), event.getSensorValues() ); assertEquals( Arrays.asList(42L, 9223372036854775800L), event.getSensorLongValues() ); assertEquals( Arrays.asList( SensorState.ONLINE, SensorState.OFFLINE, SensorState.ONLINE, SensorState.UNKNOWN ), event.getSensorStates() ); assertEquals( Arrays.asList( java.sql.Date.valueOf(LocalDate.of(1991, 12, 31)), java.sql.Date.valueOf(LocalDate.of(1990, 1, 1)) ), event.getDateValues() ); assertEquals( Arrays.asList( Date.from( LocalDate.of(1991, 12, 31) .atStartOfDay() .atZone(ZoneId.systemDefault()) .toInstant() ), Date.from( LocalDate.of(1990, 1, 1) .atStartOfDay() .atZone(ZoneId.systemDefault()) .toInstant() ) ), event.getTimestampValues() );
Cool, right?
I'm running an online workshop on the 11th of October about High-Performance SQL.If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
If you are using PostgreSQL, then you can take advantage of many database-specific column types, such as ARRAY, Range, HStore, Inet, or JSON. Even if Hibernate ORM does not support these column type mappings by default, you can still use all these database column types as long as you use the Hypersistence Utils project.
Mapping PostgreSQL ARRAY column types to Java List
entity attributes is the most convenient option since it’s much easier to operate a Java Collection than an Array entity attribute.

Really useful article and library.
Thanks and stay tuned for more.