How to map Java and SQL arrays with JPA and Hibernate

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Hypersistence Optimizer is that tool!

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 Hibernate Types 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 a String and int Java arrays, respectively. So, we are going to use the Hibernate Types project to achieve this goal.

Maven dependency

The first thing you need to do is to set up the following Maven dependency in your project pom.xml configuration file:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-55</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

JPA entity mapping

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

@Entity(name = "Event")
@Table(name = "event")
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 string-array and int-array are custom types which can be defined in the BaseEntity superclass:

@TypeDefs({
    @TypeDef(
        name = "string-array", 
        typeClass = StringArrayType.class
    ),
    @TypeDef(
        name = "int-array", 
        typeClass = IntArrayType.class
    )
})
@MappedSuperclass
public class BaseEntity {

    @Id
    private Long id;

    @Version
    private Integer version;

    //Getters and setters omitted for brevity
}

The StringArrayType and IntArrayType are classes offered by the Hibernate Types 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!

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Seize the deal! 50% discount.

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 Hibernate Types project uses the Hibernate API to provide additional column type mappings.

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.

High-Performance Java Persistence 📚 - 50% OFF