How to map a PostgreSQL array with Hibernate

Question

has anyone successfully mapped a numeric array in PostgreSQL to a numeric array in java via Hibernate?

sql:

CREATE TABLE sal_emp (name text, pay_by_quarter integer[]);
INSERT INTO sal_emp VALUES ('one', '{1,2,3}');
INSERT INTO sal_emp VALUES ('two', '{4,5,6}');
INSERT INTO sal_emp VALUES ('three', '{2,4,6}');

mapping:

<hibernate-mapping>
    <class name="SalEmp" table="sal_emp">
        <id name="name" />
        <property name="payByQuarter" column="pay_by_quarter" />
    </class>
</hibernate-mapping>

class:

public class SalEmp implements Serializable{
    private String name;
    private Integer[] payByQuarter;
    ...// getters & setters
}

i get an exception when querying the table.

Answer 1

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     ) }) @MappedSuperclass public 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 ) 

Answer 2

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.

Answer 3

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;

@Override
public 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);
}

@Override
public 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);
}

@Override
public Object assemble(final Serializable cached, final Object owner) throws HibernateException {
    return cached;
}

@Override
public Object deepCopy(final Object o) throws HibernateException {
    return o == null ? null : ((int[]) o).clone();
}

@Override
public Serializable disassemble(final Object o) throws HibernateException {
    return (Serializable) o;
}

@Override
public boolean equals(final Object x, final Object y) throws HibernateException {
    return x == null ? y == null : x.equals(y);
}

@Override
public int hashCode(final Object o) throws HibernateException {
    return o == null ? 0 : o.hashCode();
}

@Override
public boolean isMutable() {
    return false;
}

@Override
public Object replace(final Object original, final Object target, final Object owner) throws HibernateException {
    return original;
}

@Override
public Class<int[]> returnedClass() {
    return int[].class;
}

@Override
public int[] sqlTypes() {
    return new int[] { SQLTYPE };
}
}