Using HSQL with Array columns

How to use HSQL with array columns in Hibernate and PostgreSQL.

April 10, 2019, updated April 10, 2019

When using Hibernate (HQL/HSQL) with PostgreSQL array columns, you need to bridge the gap between Java arrays and the database’s native array type. Hibernate doesn’t map arrays out of the box, so you need a custom type or a library.

The Problem

PostgreSQL supports array columns like TEXT[] and INTEGER[], but JPA/Hibernate doesn’t have a built-in mapping for them. If you try to use a List<String> with a standard column definition, you’ll get a serialization error or a bytea column instead of a proper PostgreSQL array.

Using the hibernate-types Library

The hibernate-types library by Vlad Mihalcea provides array type mappings that work with PostgreSQL and H2.

Add the dependency

Maven:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.21.1</version>
</dependency>

Gradle:

implementation 'com.vladmihalcea:hibernate-types-52:2.21.1'

Map the array column

import com.vladmihalcea.hibernate.type.array.StringArrayType;
import com.vladmihalcea.hibernate.type.array.IntArrayType;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;

@Entity
@Table(name = "products")
@TypeDef(name = "string_array", typeClass = StringArrayType.class)
@TypeDef(name = "int_array", typeClass = IntArrayType.class)
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @Type(type = "string_array")
    @Column(columnDefinition = "text[]")
    private String[] tags;

    @Type(type = "int_array")
    @Column(columnDefinition = "integer[]")
    private int[] ratings;

    // getters and setters
}

Using a Custom UserType

If you don’t want an external dependency, you can write your own UserType:

import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;
import java.sql.*;
import java.util.Arrays;

public class StringArrayType implements UserType {

    @Override
    public int[] sqlTypes() {
        return new int[]{Types.ARRAY};
    }

    @Override
    public Class returnedClass() {
        return String[].class;
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names,
            SharedSessionContractImplementor session, Object owner) throws SQLException {
        Array array = rs.getArray(names[0]);
        return array != null ? (String[]) array.getArray() : null;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index,
            SharedSessionContractImplementor session) throws SQLException {
        if (value != null) {
            Array array = session.connection().createArrayOf("text", (String[]) value);
            st.setArray(index, array);
        } else {
            st.setNull(index, Types.ARRAY);
        }
    }

    // ... implement equals, hashCode, deepCopy, etc.
}

H2 Database Compatibility

If you’re running tests against H2 instead of PostgreSQL, you need to handle the fact that H2 represents arrays differently. H2 uses the ARRAY data type:

-- H2
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name TEXT NOT NULL,
    tags ARRAY
);

The hibernate-types library handles this mapping automatically. If you’re rolling your own UserType, check the database dialect at runtime:

if (dialect instanceof PostgreSQLDialect) {
    array = connection.createArrayOf("text", (String[]) value);
} else {
    // H2 and others
    array = connection.createArrayOf("VARCHAR", (String[]) value);
}

Querying Arrays in HQL

Once mapped, you can query array columns using native queries since HQL doesn’t support array operators:

List<Product> results = entityManager.createNativeQuery(
    "SELECT * FROM products WHERE :tag = ANY(tags)", Product.class)
    .setParameter("tag", "sale")
    .getResultList();