Introduction

In this article, we are going to see how you can map SQL arrays to JPA entity attributes when using Hibernate.

Hibernate custom Types allow you to map all sorts of database-specific column types, like IP address, JSON columns, bit sets, or SQL arrays.

However, while you can create your own custom Hibernate Types for mapping PostgreSQL arrays, you don’t need to implement your own Hibernate Type. All you need to do is use the Hypersistence Utils open-source project.

Database table

Considering we have the following table in our PostgreSQL database schema:

create table event (
    id int8 not null, 
    version int4, 
    sensor_names text[], 
    sensor_values integer[], 
    primary key (id)
)

We want to map this table using JPA and Hibernate. However, neither JPA nor Hibernate support SQL arrays by default, and we want to map these arrays to String and int Java arrays, respectively. So, we are going to use the Hypersistence Utils project to achieve this goal.

Maven dependency

The first thing you need to do is to set up the Hypersistence Utils Maven dependency in your project pom.xml configuration file. Based on the Hibernate version you’re using, you will have to add the proper Hypersistence Utils Maven dependency.

For instance, if you’re using Hibernate 6.1 or 6.0, you can add this dependency:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-60</artifactId>
    <version>${hypersistence-utils.version}</version>
</dependency>

For Hibernate 5.5 and 5.6:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-55</artifactId>
    <version>${hypersistence-utils.version}</version>
</dependency>

And, for Hibernate 5.4 and 5.3, and 5.2:

<dependency>
    <groupId>io.hypersistence</groupId>
    <artifactId>hypersistence-utils-hibernate-52</artifactId>
    <version>${hypersistence-utils.version}</version>
</dependency>

Check out the Hypersistence Utils for more details about the proper dependency you should be using.

JPA entity mapping

The JPA mapping for the event database table will look as follows.

For Hibernate 6, the mapping will look as follows:

@Entity(name = "Event")
@Table(name = "event")
public class Event extends BaseEntity {

    @Type(StringArrayType.class)
    @Column(
        name = "sensor_names", 
        columnDefinition = "text[]"
    )
    private String[] sensorNames;

    @Type(IntArrayType.class)
    @Column(
        name = "sensor_values", 
        columnDefinition = "integer[]"
    )
    private int[] sensorValues;

    //Getters and setters omitted for brevity
}

And for Hibernate 5, like this:

@Entity(name = "Event")
@Table(name = "event")
@TypeDef(name = "string-array", typeClass = StringArrayType.class)
@TypeDef(name = "int-array", typeClass = IntArrayType.class)
public class Event extends BaseEntity {

    @Type(type = "string-array")
    @Column(
        name = "sensor_names", 
        columnDefinition = "text[]"
    )
    private String[] sensorNames;

    @Type(type = "int-array")
    @Column(
        name = "sensor_values", 
        columnDefinition = "integer[]"
    )
    private int[] sensorValues;

    //Getters and setters omitted for brevity
}

The StringArrayType and IntArrayType are classes offered by the Hypersistence Utils project.

That’s it!

Testing time

When persisting these two Event entities:

Event nullEvent = new Event();
nullEvent.setId(0L);
entityManager.persist(nullEvent);

Event event = new Event();
event.setId(1L);
event.setSensorNames(
    new String[] {
        "Temperature", 
        "Pressure"
    }
);
event.setSensorValues( 
    new int[] {
        12, 
        756
    } 
);
entityManager.persist(event);

Hibernate is going to generate the following SQL statement:

INSERT INTO event (
    version, 
    sensor_names, 
    sensor_values, 
    id
) 
VALUES (
    0, 
    NULL(ARRAY), 
    NULL(ARRAY), 
    0
)
    
INSERT INTO event (
    version, 
    sensor_names, 
    sensor_values, 
    id
) 
VALUES ( 
    0, 
    {"Temperature","Pressure"}, 
    {"12","756"}, 
    1
)

Great! Now, we can also update the Event entities too:

Event event = entityManager.find(Event.class, 1L);

assertArrayEquals( 
    new String[] {
        "Temperature", 
        "Pressure"
    }, 
    event.getSensorNames() 
);
assertArrayEquals( 
    new int[] {
        12, 
        756
    }, 
    event.getSensorValues() 
);

event.setSensorNames(
    new String[] {
        "Temperature",
        "Pressure",
        "Volume"
    } );
event.setSensorValues(
    new int[] {
        12,
        756,
        76
    }
);

And Hibernate is going to generate the following SQL UPDATE statement:

UPDATE 
    event 
SET 
    version = 1, 
    sensor_names = {"Temperature","Pressure","Volume"}, 
    sensor_values = {"12","756","76"} 
WHERE 
    id = 1 
AND 
    version = 0

Since the Hibernate array types are mutable, we can even update them without overriding the array object:

Event event = entityManager.find(Event.class, 1L);

event.getSensorValues()[2] = 84;

Hibernate generating the appropriate UPDATE SQL statement:

UPDATE 
    event 
SET 
    version = 2, 
    sensor_names = {"Temperature","Pressure","Volume"}, 
    sensor_values = {"12","756","84"} 
WHERE 
    id = 1 
AND 
    version = 1

That’s it!

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

While JPA defines the AttributeConverter interface, the standard converters are way too limited when it comes to mapping arrays or JSON types.

That’s why the Hypersistence Utils project uses the Hibernate API to provide additional column type mappings.

Insert details about how the information is going to be processed

DOWNLOAD NOW