Hibernate HSQLDB ARRAY Type

Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.

So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!

You can earn a significant passive income stream from promoting my book, courses, tools, training, or coaching subscriptions.

If you're interested in supplementing your income, then join my affiliate program.

Introduction

As previously explained, although Hibernate does not support database ARRAY column types by default, you can easily implement a custom Hibernate ArrayType. While the previous solution worked on PostgreSQL, in this article, you are going to see that it is fairly easy to adapt it to HSQLDB.

Maven Dependency

First, you need to add the following dependency to your project:

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

For more info about which dependency to use based on the Hibernate version you are using, check out the Hypersistence Utils GitHub repository.

Domain Model

Assuming we have the following Event entity in our system:

We need a way to persist the String[] and Integer[] entity attributes since Hibernate, by default, does not support database-specific ARRAY types.

For that, we are going to use two custom Hibernate types.

For Hibernate 6, the mapping will look as follows:

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

    @Id
    private Long id;

    @Type(
        value = StringArrayType.class,
        parameters = @Parameter(
            name = AbstractArrayType.SQL_ARRAY_TYPE, 
            value = "varchar"
        )
    )
    @Column(name = "sensor_names", columnDefinition = "VARCHAR(20) ARRAY[10]")
    private String[] sensorNames;

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

    //Getters and setters omitted for brevity
}

And for Hibernate 5, like this:

@Entity(name = "Event")
@Table(name = "event")
@TypeDef(
    name = "hsqldb-string-array",
    typeClass = StringArrayType.class,
    parameters = {
        @Parameter(name = AbstractArrayType.SQL_ARRAY_TYPE, value = "varchar")
    }
)
public class Event {

    @Id
    private Long id;

    @Type(type = "hsqldb-string-array")
    @Column(name = "sensor_names", columnDefinition = "VARCHAR(20) ARRAY[10]")
    private String[] sensorNames;

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

    //Getters and setters omitted for brevity
}

And that’s it!

Testing time

When persisting 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 Integer[]{12, 756}
);

entityManager.persist(event);

Hibernate is going to execute the following SQL INSERT statements:

INSERT INTO event (
    sensor_names, 
    sensor_values, 
    id
) 
VALUES (
    NULL(ARRAY), 
    NULL(ARRAY), 
    0
)

INSERT INTO event (
    sensor_names, 
    sensor_values, 
    id
) 
VALUES (
    ARRAY['Temperature','Pressure'], 
    ARRAY[12,756], 
    1
)

When fetching the Event entity:

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

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

Hibernate can properly map the underlying ARRAY column type to the String[] and Integer[] Java arrays.

You can also use JPQL to filter the results based on a given Java array:

Event event = entityManager.createQuery("""
    select e
    from Event e
    where e.sensorNames = :sensorNames
    """, Event.class)
.setParameter
    "sensorNames", 
    new String[]{"Temperature", "Pressure"}
)
.getSingleResult();

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

And there is more!

You can earn a significant passive income stream from promoting all these amazing products that I have been creating.

If you're interested in supplementing your income, then join my affiliate program.

Conclusion

As demonstrated in this article, mapping ARRAY column types to Java String[] or Integer arrays is fairly easy when using Hibernate.

Code available on GitHub.

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.