How to map a PostgreSQL array with Hibernate

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" colum开发者_如何学Pythonn="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.

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
)

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;

@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 };
}
}

This has been tested against string arrays. Maybe some modifications in the converter is required for numeric arrays. This works with Spring JPA.

1) add PostgreSQLTextArray to your project

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Map;

/**
 * This is class provides {@link java.sql.Array} interface for PostgreSQL <code>text</code> array.
 *
 * @author Valentine Gogichashvili
 *
 */

public class PostgreSQLTextArray implements java.sql.Array {

    private final String[] stringArray;
    private final String stringValue;

    /**
     * Initializing constructor
     * @param stringArray
     */
    public PostgreSQLTextArray(String[] stringArray) {
        this.stringArray = stringArray;
        this.stringValue = stringArrayToPostgreSQLTextArray(this.stringArray);

    }

    @Override
    public String toString() {
        return stringValue;
    }

    private static final String NULL = "NULL";

    /**
     * This static method can be used to convert an string array to string representation of PostgreSQL text array.
     * @param a source String array
     * @return string representation of a given text array
     */
    public static String stringArrayToPostgreSQLTextArray(String[] stringArray) {
        final int arrayLength;
        if ( stringArray == null ) {
            return NULL;
        } else if ( ( arrayLength = stringArray.length ) == 0 ) {
            return "{}";
        }
        // count the string length and if need to quote
        int neededBufferLentgh = 2; // count the beginning '{' and the ending '}' brackets
        boolean[] shouldQuoteArray = new boolean[stringArray.length];
        for (int si = 0; si < arrayLength; si++) {
            // count the comma after the first element
            if ( si > 0 )  neededBufferLentgh++;

            boolean shouldQuote;
            final String s = stringArray[si];
            if ( s == null ) {
                neededBufferLentgh += 4;
                shouldQuote = false;
            } else {
                final int l = s.length();
                neededBufferLentgh += l;
                if ( l == 0 || s.equalsIgnoreCase(NULL) ) {
                    shouldQuote = true;
                } else {
                    shouldQuote = false;
                    // scan for commas and quotes
                    for (int i = 0; i < l; i++) {
                        final char ch = s.charAt(i);
                        switch(ch) {
                            case '"':
                            case '\\':
                                shouldQuote = true;
                                // we will escape these characters
                                neededBufferLentgh++;
                                break;
                            case ',':
                            case '\'':
                            case '{':
                            case '}':
                                shouldQuote = true;
                                break;
                            default:
                                if ( Character.isWhitespace(ch) ) {
                                    shouldQuote = true;
                                }
                                break;
                        }
                    }
                }
                // count the quotes
                if ( shouldQuote ) neededBufferLentgh += 2;
            }
            shouldQuoteArray[si] = shouldQuote;
        }

        // construct the String
        final StringBuilder sb = new StringBuilder(neededBufferLentgh);
        sb.append('{');
        for (int si = 0; si < arrayLength; si++) {
            final String s = stringArray[si];
            if ( si > 0 ) sb.append(',');
            if ( s == null ) {
                sb.append(NULL);
            } else {
                final boolean shouldQuote = shouldQuoteArray[si];
                if ( shouldQuote ) sb.append('"');
                for (int i = 0, l = s.length(); i < l; i++) {
                    final char ch = s.charAt(i);
                    if ( ch == '"' || ch == '\\' ) sb.append('\\');
                    sb.append(ch);
                }
                if ( shouldQuote ) sb.append('"');
            }
        }
        sb.append('}');
        assert sb.length() == neededBufferLentgh;
        return sb.toString();
    }


    @Override
    public Object getArray() throws SQLException {
        return stringArray == null ? null : Arrays.copyOf(stringArray, stringArray.length);
    }

    @Override
    public Object getArray(Map<String, Class<?>> map) throws SQLException {
        return getArray();
    }

    @Override
    public Object getArray(long index, int count) throws SQLException {
        return stringArray == null ? null : Arrays.copyOfRange(stringArray, (int) index, (int) index + count);
    }

    @Override
    public Object getArray(long index, int count, Map<String, Class<?>> map) throws SQLException {
        return getArray(index, count);
    }

    @Override
    public int getBaseType() throws SQLException {
        return java.sql.Types.VARCHAR;
    }

    @Override
    public String getBaseTypeName() throws SQLException {
        return "text";
    }

    @Override
    public ResultSet getResultSet() throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public ResultSet getResultSet(Map<String, Class<?>> map) throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public ResultSet getResultSet(long index, int count) throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public ResultSet getResultSet(long index, int count, Map<String, Class<?>> map) throws SQLException {
        throw new UnsupportedOperationException();
    }

    @Override
    public void free() throws SQLException {
    }

}

2) Add ListToArrayConverter to your code

import org.postgresql.jdbc4.Jdbc4Array;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Converter(autoApply = true)
public class ListToArrayConveter implements AttributeConverter<List<String>, Object> {
    @Override
    public PostgreSQLTextArray convertToDatabaseColumn(List<String> attribute) {
        if (attribute == null || attribute.isEmpty()) {
            return null;
        }
        String[] rst = new String[attribute.size()];
        return new PostgreSQLTextArray(attribute.toArray(rst));
    }

    @Override
    public List<String> convertToEntityAttribute(Object dbData) {

        List<String> rst = new ArrayList<>();
        try {
            String[] elements = (String[]) ((Jdbc4Array) dbData).getArray();
            for (String element : elements) {
                rst.add(element);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return rst;
    }
}

3) Use it!

@Entity
@Table(name = "emails")
public class Email {

    [...]

    @SuppressWarnings("JpaAttributeTypeInspection")
    @Column(name = "subject", columnDefinition = "text[]")
    @Convert(converter = ListToArrayConveter.class)
    private List<String> subject;

    [...]

I was able to save a String[] to PostgreSQL 9.4 and EclipseLink 2.6.2 via the JPA Converter approach posted here

which seems to be the source for the answer of

Tk421 of 1st July 2016.

Loading an Array from DB also works well.

Additionally added to persistence.xml my path to ListToArrayConverter:

<class> com.foo1.foo2.foo3.backend.jpa.convert.ListToArrayConverter </class>

Please mention that Jdbc4Array is not present in Postgre JDBC driver anymore, please instead use:

org.postgresql.jdbc.PgArray

See here: Package org.postgresql.jdbc4 is missing since 9.4-1207

Here is the int[] UserType I used to do what you're after which also includes the null checks for nullSafeGet() and nullSafeSet():

import org.apache.commons.lang.ArrayUtils;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;

import java.io.Serializable;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class IntegerArrayUserType implements UserType {
    protected static final int  SQLTYPE = java.sql.Types.ARRAY;

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
        Array array = rs.getArray(names[0]);
        if (array == null) {
            return null;
        }
        Integer[] javaArray = (Integer[]) array.getArray();
        return ArrayUtils.toPrimitive(javaArray);
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
        Connection connection = st.getConnection();

        if (value == null) {
            st.setNull( index, sqlTypes()[0] );
        } else {
            int[] castObject = (int[]) value;
            Integer[] integers = ArrayUtils.toObject(castObject);
            Array array = connection.createArrayOf("integer", integers);

            st.setArray(index, 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 };
    }
}