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.

Insert details about how the information is going to be processed

DOWNLOAD NOW