How to map Java and SQL arrays 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, 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-52</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!

I'm running an online workshopk on the 14th of May about The Best Way to Fetch Data with Java Persistence and Hibernate.

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

Transactions and Concurrency Control eBook

22 Comments on “How to map Java and SQL arrays with JPA and Hibernate

  1. Vlad thank you very much for doing everything possible to make programming a great universe of possibilities, all your tutorials have helped me.

  2. Does this support other database as well? Aside from PostgresSql (oracle, mysql, etc.)?

    • MySQL and Oracle do not support ARRAY column types. Only PostgreSQL has support for ARRAY columns.

  3. Many thanks for this.

    I’ve been wrestling with plugging into JPA with pure XML persistence_2_1.xsd configuration.

    What I found is that I can make it work if I declare:

    <property name="hibernate.metadata_builder_contributor" value="myjpa.MBcontrib" />
    

    inside persistence.xml, and then declare myjpa.MBcontrib as:

    public class MBcontrib implements MetadataBuilderContributor {
    
        @Override
        public void contribute(MetadataBuilder metadataBuilder) {
            metadataBuilder.applyBasicType(StringArrayType.INSTANCE)
            .applyBasicType(IntArrayType.INSTANCE);
        }
    }
    

    Putting out there in case it is helpful to somebody else.

      • Perhaps we should provide a basic implementation for MetadataBuilderContributor in hibernate-types? Although some of the types may not be able to load due to missing classes on the classpath. We’d have to use some reflection for that. On the other hand, pointing people to the MetadataBuilderContributor may make hibernate-types easier to use. The typedefs grow quite old after a while…

        We could perhaps even register the MetadataBuilderContributor using the service provider API, although this might have too many side effects.

      • Yes, I think this is a good idea. There could be a pre-defined MetadataBuilderContributor containing all supported types. I’ll add an issue to it.

  4. Hi Vlad, thanks for your great Library. I am new to Jpa. I am using Java 11 with Postgres.
    I am trying to use a JPQL where clause with a int[] field like “where a in :variable” where a is declared as a int[] table field, exactly as your example.

    I am getting the following error message:

    java.lang.ClassCastException: class beans.IntArrayTypeDescriptor cannot be cast to .beans.AbstractArrayTypeDescriptor .beans.IntArrayTypeDescriptor and .AbstractArrayTypeDescriptor are in unnamed module of loader org.springframework.boot.devtools.restart.classloader.RestartClassLoader @9566fe3)

    If I remove this JPQK where clause argument, I have my resultset as it should. So, I am only struggling with the int[] in where clause. Any hint?

    • Looks like a class loading issue. I’m not very familiar with the Java module system, so I don’t know what the problem is.

  5. But a Java list is resizeable and an array is not. A Postgres array is also resizeable; feels like a List would be a better fit than arrays for mapping Postgres arrays.

    • It’s not difficult to add a new type that maps a List to a PostgreSQL ARRAY. If you are interested in this feature, send me a Pull Request with a proposal and I’ll review it when it’s done.

  6. Hi Vlad, StringArrayTypeDescriptor class is missing
    public static final StringArrayTypeDescriptor INSTANCE = new StringArrayTypeDescriptor();
    in latest version

    • You only need to use StringArrayType. The descriptor is used implicitly, so you don’t need it on your code, although you can find it there as well.

  7. Hi,
    When I add :
    @Column(columnDefinition = “integer[]”)
    private int[] days;

    hmb2ddl creates:
    alter table if exists “access_control_permissions”
    add column “days” “integer[]”

    And coulmn cannot be created because the “integer[]” should not be in double quotations.
    I am using Hibernate 5.4.3, Postgrsql 9.6 and set dialect to: “org.hibernate.dialect.PostgreSQL95Dialect”
    Could you please help me

    • If you run the tests in the hibernate-types GitHub repository, you will see that the table is generated without any quotes:

      create table event (id int8 not null, version int4, sensor_ids uuid[], sensor_long_values bigint[], sensor_names text[], sensor_states sensor_state[], sensor_values integer[], primary key (id))
      

      Try to compare why my test works fine and yours does not.

  8. I am newbee to JPA. Could you please describe, how to persist int[][] (two-dimesional array of int-type)
    Thanks in advice!

    • Check out the associated GitHub repository and inspect the tests. I haven’t tested this use case, but you can try it and see how it works. If it doesn’t, send me a Pull Request with a fix. Cheers.

      • I am going to pass to EntityManager already assembled/populated two-dimensional array.
        Therefore, I believe, that mentioned code in the article above should be rewritten slightly for my particular case, since it’s like Object.

        Please, don’t get me wrong, due to my lack of expirience and knowledge.

      • When I pass to hibernate two-dimensional Long[][] and configured classes as in upper article, Logger prints:

        19:05:21 PM [main] TRACE org.hibernate.type.descriptor.sql.BasicBinder – binding parameter [1] as [ARRAY] – [[[Ljava.lang.Long;@3eabe84a]
        19:05:21 PM [main] WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper – SQL Error: 0, SQLState: 42602
        19:05:21 PM [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper – Unable to find server array type for provided name object.

      • Try to debug it and see what causes the problem. Once you figure it out, you need to send a Pull Request with a fix. Looking forward to it.

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.