Hibernate HSQLDB ARRAY Type

Last modified:

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

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.

How to implement a custom #Hibernate ArrayType. @vlad_mihalcea https://t.co/HJGv5uz7zY pic.twitter.com/xTVPbING1C
— Java (@java) January 22, 2018

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:

@Entity(name = "Event")
@Table(name = "event")
@TypeDefs({
    @TypeDef(
        name = "string-array", 
        typeClass = VarCharStringArrayType.class
    ),
    @TypeDef(
        name = "int-array", 
        typeClass = IntArrayType.class
    ),
})
public class Event {

    @Id
    private Long id;

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

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

    //Getters and setters omitted for brevity
}

The code for StringArrayType and IntArrayType has been described before in this article so we are not going to repeat it here.

The only difference is that we are using the VarCharStringArrayType now since HSQLDB does not support the TEXT database column type:

public class VarCharStringArrayType
        extends StringArrayType {

    public static final VarCharStringArrayType INSTANCE = 
        new VarCharStringArrayType();

    public VarCharStringArrayType() {
        super(
            VarCharStringArrayTypeDescriptor.INSTANCE
        );
    }
}

Also the VarCharStringArrayTypeDescriptor is rather trivial too:

public class VarCharStringArrayTypeDescriptor
        extends StringArrayTypeDescriptor {

    public static final VarCharStringArrayTypeDescriptor INSTANCE = 
            new VarCharStringArrayTypeDescriptor();

    @Override
    public String getSqlArrayType() {
        return "varchar";
    }
}

And, that’s it!

You don't have to create all these types manually. You can simply get them via Maven Central using the following dependency:
    com.vladmihalcea
    hibernate-types-55
    ${hibernate-types.version}
For more info, check out the hibernate-types open-source project.

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();