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

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.