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

Hibernate custom Types allow you to map all sorts of database specific column types, like IP address, JSON columns, bit sets or SQL arrays.

There are two ways to define a custom Hibernate Type:

The latter option is preferred since it allows you to better split the Java-to-JDBC and the JDBC-to-SQL type handling. In this article, we are going to see how you can map SQL arrays to their Java counterpart.

You don't have to create all these types manually. You can simply get them via Maven Central using the following dependency:

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

For more info, check out the hibernate-types open-source project.

SQL to JPA mapping

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.

The JPA mapping for this database table looks 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
}

StringArrayType

The StringArrayType extends AbstractSingleColumnStandardBasicType and defines the SQL and Java Type descriptors:

public class StringArrayType
        extends AbstractSingleColumnStandardBasicType<String[]>
        implements DynamicParameterizedType {

    public StringArrayType() {
        super( 
            ArraySqlTypeDescriptor.INSTANCE, 
            StringArrayTypeDescriptor.INSTANCE 
        );
    }

    public String getName() {
        return "string-array";
    }

    @Override
    protected boolean registerUnderJavaType() {
        return true;
    }

    @Override
    public void setParameterValues(Properties parameters) {
        ((StringArrayTypeDescriptor) 
            getJavaTypeDescriptor())
            .setParameterValues(parameters);
    }
}

IntArrayType

The IntArrayType also extends AbstractSingleColumnStandardBasicType and defines the SQL and Java Type descriptors:

public class IntArrayType
        extends AbstractSingleColumnStandardBasicType<int[]>
        implements DynamicParameterizedType {

    public IntArrayType() {
        super( 
            ArraySqlTypeDescriptor.INSTANCE, 
            IntArrayTypeDescriptor.INSTANCE 
        );
    }

    public String getName() {
        return "int-array";
    }

    @Override
    protected boolean registerUnderJavaType() {
        return true;
    }

    @Override
    public void setParameterValues(Properties parameters) {
        ((IntArrayTypeDescriptor) 
            getJavaTypeDescriptor())
            .setParameterValues(parameters);
    }
}

Array Types class diagram

Before going into details about how each component works internally, it’s worth to see the classes involved when mapping a custom Hibernate Type using Java and SQL descriptors:

ArrayType Diagram

ArraySqlTypeDescriptor

As you can see from the aforementioned Hibernate Types, both share the same SqlTypeDescriptor which is used for handling the JDBC-to-SQL type mapping:

public class ArraySqlTypeDescriptor 
    implements SqlTypeDescriptor {

    public static final ArraySqlTypeDescriptor INSTANCE = 
        new ArraySqlTypeDescriptor();

    @Override
    public int getSqlType() {
        return Types.ARRAY;
    }

    @Override
    public boolean canBeRemapped() {
        return true;
    }

    @Override
    public <X> ValueBinder<X> getBinder(
        JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicBinder<X>( javaTypeDescriptor, this) {
            @Override
            protected void doBind(
                    PreparedStatement st, 
                    X value, 
                    int index, 
                    WrapperOptions options
                ) throws SQLException {

                AbstractArrayTypeDescriptor<Object> abstractArrayTypeDescriptor = 
                    (AbstractArrayTypeDescriptor<Object>) 
                        javaTypeDescriptor;

                st.setArray( 
                    index, 
                    st.getConnection().createArrayOf(
                        abstractArrayTypeDescriptor.getSqlArrayType(),
                        abstractArrayTypeDescriptor.unwrap( 
                            value, 
                            Object[].class, 
                            options 
                        )
                    )
                );
            }

            @Override
            protected void doBind(
                    CallableStatement st, 
                    X value, 
                    String name, 
                    WrapperOptions options
                ) throws SQLException {
                throw new UnsupportedOperationException( 
                    "Binding by name is not supported!" 
                );
            }
        };
    }

    @Override
    public <X> ValueExtractor<X> getExtractor(
        final JavaTypeDescriptor<X> javaTypeDescriptor) {
        return new BasicExtractor<X>(javaTypeDescriptor, this) {
            @Override
            protected X doExtract(
                    ResultSet rs, 
                    String name, 
                    WrapperOptions options
                ) throws SQLException {
                return javaTypeDescriptor.wrap(
                    rs.getArray(name), 
                    options
                );
            }

            @Override
            protected X doExtract(
                    CallableStatement statement, 
                    int index, 
                    WrapperOptions options
                ) throws SQLException {
                return javaTypeDescriptor.wrap(
                    statement.getArray(index), 
                    options
                );
            }

            @Override
            protected X doExtract(
                    CallableStatement statement, 
                    String name, 
                    WrapperOptions options
                ) throws SQLException {
                return javaTypeDescriptor.wrap(
                    statement.getArray(name), 
                    options
                );
            }
        };
    }
}

Basically, the ArraySqlTypeDescriptor instructs Hibernate how to handle SQL arrays using the JDBC Statement.setArray and ResultSet.

StringArrayTypeDescriptor

StringArrayTypeDescriptor is rather straightforward since the bulk of logic is encapsulated in AbstractArrayTypeDescriptor. StringArrayTypeDescriptor defines the expected Java type (e.g. String[]) and dthe underlying database array type (e.g. text).

public class StringArrayTypeDescriptor
        extends AbstractArrayTypeDescriptor<String[]> {

    public static final StringArrayTypeDescriptor INSTANCE = 
        new StringArrayTypeDescriptor();

    public StringArrayTypeDescriptor() {
        super( String[].class );
    }

    @Override
    protected String getSqlArrayType() {
        return "text";
    }
}

IntArrayTypeDescriptor

The same goes for IntArrayTypeDescriptor since it defines the expected Java type (e.g. int[]) and dthe underlying database array type (e.g. integer).

public class IntArrayTypeDescriptor
        extends AbstractArrayTypeDescriptor<int[]> {

    public static final IntArrayTypeDescriptor INSTANCE = 
        new IntArrayTypeDescriptor();

    public IntArrayTypeDescriptor() {
        super( int[].class );
    }

    @Override
    protected String getSqlArrayType() {
        return "integer";
    }
}

AbstractArrayTypeDescriptor

The bulk of Java-to-JDBC type handling is included in the AbstractArrayTypeDescriptor base class:

public abstract class AbstractArrayTypeDescriptor<T>
        extends AbstractTypeDescriptor<T> 
        implements DynamicParameterizedType {

    private Class<T> arrayObjectClass;

    @Override
    public void setParameterValues(Properties parameters) {
        arrayObjectClass = ( (ParameterType) parameters
            .get( PARAMETER_TYPE ) )
            .getReturnedClass();

    }

    public AbstractArrayTypeDescriptor(Class<T> arrayObjectClass) {
        super( 
            arrayObjectClass, 
            (MutabilityPlan<T>) new MutableMutabilityPlan<Object>() {
                @Override
                protected T deepCopyNotNull(Object value) {
                    return ArrayUtil.deepCopy( value );
                }
            } 
        );
        this.arrayObjectClass = arrayObjectClass;
    }

    @Override
    public boolean areEqual(Object one, Object another) {
        if ( one == another ) {
            return true;
        }
        if ( one == null || another == null ) {
            return false;
        }
        return ArrayUtil.isEquals( one, another );
    }

    @Override
    public String toString(Object value) {
        return Arrays.deepToString((Object[]) value);
    }

    @Override
    public T fromString(String string) {
        return ArrayUtil.fromString(
            string, 
            arrayObjectClass
        );
    }

    @SuppressWarnings({ "unchecked" })
    @Override
    public <X> X unwrap(
            T value, 
            Class<X> type, 
            WrapperOptions options
        ) {
        return (X) ArrayUtil.wrapArray( value );
    }

    @Override
    public <X> T wrap(
            X value, 
            WrapperOptions options
        ) {
        if( value instanceof Array ) {
            Array array = (Array) value;
            try {
                return ArrayUtil.unwrapArray( 
                    (Object[]) array.getArray(), 
                    arrayObjectClass 
                );
            }
            catch (SQLException e) {
                throw new IllegalArgumentException( e );
            }
        }
        return (T) value;
    }

    protected abstract String getSqlArrayType();
}

AbstractArrayTypeDescriptor relies on ArrayUtil to handle the Java array deep copying, wrapping and unwrapping logic.

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. 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.

Because Hibernate types allow you to control the underlying JDBC type handling, it’s possible to map SQL array types to Java arrays.

Code available on GitHub.

FREE EBOOK

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

  1. 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.

  2. 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.

  3. 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.

  4. Thanks for the awesome tutorial, but my case is little bit different.
    I have a column in my table with definition numeric(10,3)[]
    eg:
    create table test(name text, measurements numeric(10,3) [])
    @Entity
    @Table(name=”test”)
    public class test {
    private String text;

    private float[] measurements;

    }

    But fetching data fails with deserialization error.
    Help me here ?

    • Send me a Pull Request with a replicating test case in the project GitHub repository

  5. Hi Vlad,

    I am using Hibernate 5.2.10 and Postgresql.
    I read your post but it seems you are doing lots of things to map Postgresql Array column with Hibernate entity.

    I am using this code for mapping :

    public class StringArrayUserType implements UserType {
    
        private final int[] arrayTypes = new int[] {Types.ARRAY};
    
    
        @Override
        public int[] sqlTypes() {
            return arrayTypes;
        }
    
    
        @Override
        public Class returnedClass() {
            return List.class;
        }
    
    
        @Override
        public boolean equals(Object a, Object b) throws HibernateException {
            return a == null ? b == null : a.equals(b);
        }
    
    
        @Override
        public int hashCode(Object o) throws HibernateException {
            return o == null ? 0 : o.hashCode();
        }
    
    
        @Override
        public Object nullSafeGet(ResultSet resultSet, String[] strings,
                SharedSessionContractImplementor sharedSessionContractImplementor, Object o)
                throws HibernateException, SQLException {
            if (strings != null && strings.length > 0 && resultSet != null && resultSet.getArray(strings[0]) != null) {
                Object array = resultSet.getArray(strings[0]).getArray();
                if (array instanceof String[]) { return Arrays.asList((String[]) array); }
            }
            return null;
        }
    
    
        @SuppressWarnings("unchecked")
        @Override
        public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
                throws HibernateException, SQLException {
            if (value != null && st != null) {
                //noinspection unchecked
                List<String> list = (List<String>) value;
                String[] castObject = list.toArray(new String[list.size()]);
                Array array = session.connection().createArrayOf("varchar", castObject);
                st.setArray(index, array);
            } else {
                if (st != null) {
                    st.setNull(index, arrayTypes[0]);
                }
            }
        }
    
    
        @Override
        public Object deepCopy(Object value) throws HibernateException {
            if (value == null) { return null; }
    
            @SuppressWarnings("unchecked") List<String> list = (List<String>) value;
            ArrayList<String> clone = new ArrayList<>();
            for (Object intOn : list) { clone.add((String) intOn); }
            return clone;
        }
    
    
        @Override
        public boolean isMutable() {
            return false;
        }
    
    
        @Override
        public Serializable disassemble(Object o) throws HibernateException {
            return (Serializable) o;
        }
    
    
        @Override
        public Object assemble(Serializable serializable, Object o) throws HibernateException {
            return serializable;
        }
    
    
        @Override
        public Object replace(Object o, Object o1, Object o2) throws HibernateException {
            return o;
        }
    }
    

    which is working fine for me. Isn’t it good alternative to your code ?

    • Why would you create a custom Array Type when you can just use the one provided by the hibernate-types project?

      Basically, this is everything you need to add to your code:

      @TypeDef(
      name = "string-array",
      typeClass = StringArrayType.class
      )

      So, my implementation supports arrays on the Java side while yours supports List. Now, I can easily make it support any collection type on the Java side, while your example will require a reimplementation to support arrays.

      Again, just use hibernate-types, and you will get more than just an ARRAY Type.

  6. I’m having some difficulties using this with Kotlin. When I try to declare:

    @TypeDefs(value = [
    TypeDef(
    name = “string-array”,
    typeClass = StringArrayType.class
    )
    ])

    I get the error: “Name expected” for the TypeDef. Any idea what’s going on there?

  7. Hi Vlad,

    Great library. Is there a way to map entity field of List type to PostgreSQL array column using your library? Using List is more convenient than array.

    • You can use the array as the field, and the List for the getter and setter.

      • 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.

  8. Hello Vlad,

    First thank you, this was very informative. I just tired one of your class on a simple hibernate native query i.e.

    String sql_query = “SELECT ARRAY[]::INTEGER[] AS testTable”;
    Query<Object[]> query = session.createNativeQuery(sql_query)
    .addScalar(“ancestors”, com.vladmihalcea.hibernate.type.array.IntArrayType.INSTANCE)
    ;

    List<Object[]> objectCollection = (List<Object[]>) query.getResultList();

    but i get the error ,

    javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet

    Do you have any idea why is that

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.