Getting Hibernate to Work With PostgreSQL Int Array Types

Recently while working on a new website that was using PostgreSQL for the DB I decided to use a small int array type column for a preference field because hey cool new thing you don’t normally get to use. If you use Hibernate you will unfortunately soon discover that PostgreSQL’s neat additional data types like array and JSON types do not work out of the box with Hibernate.

Luckily there is a fairly simple workaround for you! All you need to do is add a custom Hibernate UserType for the array data types and you are good to go. If you look around on the net there’s only examples of string array UserTypes. I needed a usertype for smallint arrays which turned out to have a couple extra wiggles so I’m sharing that code with you below. Those extra wiggles included weirdness with the result set type getting returned and the extra requirement of columnDefinition being set in the column annotation.

First in your model class you’ll want to make your smallint array type column look like the below. Note that I found the ‘columnDefinition=”smallint[]”‘ part to be necessary for this smallint array case even though examples for string array types on the net did not include it. Also obviously change the type package to whatever your package ends up being for the below ShortArrayType UserType class code.

    @Column(name = "my_array", columnDefinition="smallint[]")
    @Type(type = "com.othoslabs.model.customtypes.ShortArrayType")
    private List<Integer> myArray;



Then you’re going to want to copy the below code into your own class file and you should be all set to go!

package com.othoslabs.model.customtypes;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;
import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

// hibernate does not support postgres array types OOTB, note columnDefinition="smallint[]" must
// be included in the annotation or it will not work.
public class ShortArrayType implements UserType {
    private final int[] arrayTypes = new int[]{Types.ARRAY};

    public int[] sqlTypes() {
        return arrayTypes;
    }

    public Class<List> returnedClass() {
        return List.class;
    }

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

    public int hashCode(Object x) throws HibernateException {
        return x == null ? 0 : x.hashCode();
    }

    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner)
            throws HibernateException, SQLException {
        if (names != null && names.length > 0 && rs != null && rs.getArray(names[0]) != null) {
            // weirdness causing either hibernate or postgres jdbc driver to cause both short and
            // integer types to return.. no idea. Even odder after changing a smallint array from
            // {0,1,2} to {0,1,2,4,5} it switch from Integer to Short.
            Object array = rs.getArray(names[0]).getArray();
            if (array instanceof Integer[])
                return Arrays.asList((Integer[]) array);
            else
                return Arrays.asList(convertShortArrayToInt((Short[]) array));
        }

        return null;
    }

    private Integer[] convertShortArrayToInt(Short[] array) {
        Integer[] intArray = new Integer[array.length];
        for (int i = 0; i < array.length; i++)
            intArray[i] = Integer.valueOf(array[i]);

        return intArray;
    }

    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session)
            throws HibernateException, SQLException {
        if (value != null && st != null) {
            List<Integer> list = (List<Integer>) value;
            Integer[] castObject = list.toArray(new Integer[list.size()]);
            Array array = session.connection().createArrayOf("smallint", castObject);
            st.setArray(index, array);
        } else {
            st.setNull(index, arrayTypes[0]);
        }
    }

    public Object deepCopy(Object value) throws HibernateException {
        if (value == null)
            return null;

        List<Integer> list = (List<Integer>) value;
        ArrayList<Integer> clone = new ArrayList<Integer>();
        for (Object intOn : list)
            clone.add((Integer) intOn);

        return clone;
    }

    public boolean isMutable() {
        return false;
    }

    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) value;
    }

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

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



Thank you for reading!!

Only cool people share. You do wanna be cool right?Share on Reddit0Share on Facebook0Share on StumbleUpon0Tweet about this on Twitter0

Leave a Reply

Time limit is exhausted. Please reload the CAPTCHA.