PostgreSQL array data type with Spring and Hibernate

User defined datatype in hibernate

Posted on November 15, 2014
In my previous blog, I have explain how to create PostgreSQL array data type and PostgreSQL JSON data type with hibernate, but the problem with the previous code is that it does not work well with spring framework. The problem is that some time we use apache common dbcp or c3p0, they provide connection to database under the hibernate and thus lose the direct connection with the database and give dialect errors. For resolving this issue we need to an delegating connection with the database under the hibernate. So we cast the default database connection to the DelegatingConnection and fix the issue. Below is the class which implements hibernate UserType interface and and remaining implementations are same as from previous post.
package com.vivekpatidar.postgres.demo;

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.sql.Types;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;

public class IntegerArrayType implements UserType {

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

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return value;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Integer[]) this.deepCopy(value);
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {

        if (x == null) {
            return y == null;
        }
        return x.equals(y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x.hashCode();
    }

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

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

    @Override
    public void nullSafeSet(PreparedStatement statement, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {      
        Connection connection = statement.getConnection();
        Connection con = ((DelegatingConnection) connection).getInnermostDelegate();
        Integer[] castObject = (Integer[]) value;
        Array array = con.createArrayOf("integer", castObject);
        statement.setArray(index, array);
    }

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

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

    @Override
    public int[] sqlTypes() {
        return new int[] { Types.ARRAY };
    }
}