How to map a PostgreSQL array with Hibernate

Maven dependency

The first thing you need to do is to set up the following Hibernate Types 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>

Assuming you have this table in your database:

create table event (    id int8 not null,     version int4,     sensor_names text[],     sensor_values integer[],     primary key (id))

And you want to map it like this:

@Entity(name = "Event")@Table(name = "event")@TypeDefs({    @TypeDef(        name = "string-array",         typeClass = StringArrayType.class    ),    @TypeDef(        name = "int-array",         typeClass = IntArrayType.class    )})public static 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    )})@MappedSuperclasspublic 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.

Testing time

Now, when you insert a couple of 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 statements:

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)

Hibernate does not support database arrays (e.g. ones mapped to java.sql.Array) out of the box.

array and primitive-array types provided by Hibernate are for mapping Java arrays into backing table - they're basically a variation of one-to-many / collection-of-elements mappings, so that's not what you want.

Latest PostgreSQL JDBC driver (8.4.whatever) supports JDBC4 Connection.createArrayOf() method as well as ResultSet.getArray() and PreparedStatement.setArray() methods, though, so you can write your own UserType to provide array support.

Here is a UserType implementation dealing with Oracle array that provides a good starting point, it's reasonably straightforward to adapt it to handle java.sql.Array instead.

Perhaps this is useful for someone else: I found that in my case it performs poorly and could not be used with c3p0. (Only explored these issues briefly, is they can be resolved please correct me!)

Hibernate 3.6:

import java.io.Serializable;import java.sql.Array;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Arrays;import org.apache.commons.lang.ArrayUtils;import org.hibernate.HibernateException;import org.hibernate.usertype.UserType;public class IntArrayUserType implements UserType {protected static final int  SQLTYPE = java.sql.Types.ARRAY;@Overridepublic Object nullSafeGet(final ResultSet rs, final String[] names, final Object owner) throws HibernateException, SQLException {    Array array = rs.getArray(names[0]);    Integer[] javaArray = (Integer[]) array.getArray();    return ArrayUtils.toPrimitive(javaArray);}@Overridepublic void nullSafeSet(final PreparedStatement statement, final Object object, final int i) throws HibernateException, SQLException {    Connection connection = statement.getConnection();    int[] castObject = (int[]) object;    Integer[] integers = ArrayUtils.toObject(castObject);    Array array = connection.createArrayOf("integer", integers);    statement.setArray(i, array);}@Overridepublic Object assemble(final Serializable cached, final Object owner) throws HibernateException {    return cached;}@Overridepublic Object deepCopy(final Object o) throws HibernateException {    return o == null ? null : ((int[]) o).clone();}@Overridepublic Serializable disassemble(final Object o) throws HibernateException {    return (Serializable) o;}@Overridepublic boolean equals(final Object x, final Object y) throws HibernateException {    return x == null ? y == null : x.equals(y);}@Overridepublic int hashCode(final Object o) throws HibernateException {    return o == null ? 0 : o.hashCode();}@Overridepublic boolean isMutable() {    return false;}@Overridepublic Object replace(final Object original, final Object target, final Object owner) throws HibernateException {    return original;}@Overridepublic Class<int[]> returnedClass() {    return int[].class;}@Overridepublic int[] sqlTypes() {    return new int[] { SQLTYPE };}}