How to map a PostgreSQL ARRAY to a Java List with JPA and Hibernate

Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

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.

While the Hibernate Types project has been supporting PostgreSQL ARRAY column types since the very first version, prior to version 2.9, you could only map database ARRAY columns to Java Array entity attributes, as illustrated by this article.

Mapping database ARRAY columns to java.util.List entity attributes is a much better approach than using the previous Java Array option since, most often, it’s more convenient to use Java Collections than Arrays.

Domain Model

Let’s consider we have an event database table containing several array column types:

The event database table with 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:

Event entity using List attributes to map DB 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 Hibernate Types open-source project to be able to map PostgreSQL ARRAY columns to Java List entity attributes.

After adding the Hibernate Types dependency, you can use the ListArrayType as illustrated by the following JPA entity mapping:

@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 = "com.vladmihalcea.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
}

Notice that the @TypeDef mapping at the Event entity level defines the list-array Hibernate Types alias that is referenced by the @Type Hibernate annotation for the sensorIds, sensorNames, sensorValues, sensorLongValues, dateValues, and timestampValues entity attributes.

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?

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 Hibernate Types 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.

Transactions and Concurrency Control eBook

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.