How to work with Postgresql array data types using Hibernate

Question

How to perform object recovery and saving using type columns array using the Hibernate? In my case I want to save String. How I define in model the object? I found it on the net some examples but they didn’t work.

My class userType extend of other, so does not implement all methods.

public class ArrayStringType extends TypeHibernate {

    public static final String TYPE = "arrayStringType";

    public Class<String[]> returnedClass() {
        return String[].class;
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor sessionImpl, Object obj) throws HibernateException, SQLException {

        Array array = rs.getArray(names[0]);
        return NullUtil.isNull(array) ? null : (String[]) array.getArray(); 
   }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor sessionImpl) throws HibernateException, SQLException {

        Connection connection = st.getConnection();
        String[] stringArray = (String[]) value;

        if (NullUtil.isNull(stringArray)) {
            st.setNull(index, CharArrayType.INSTANCE.sqlType());            
        } else {            
            Array array = connection.createArrayOf("char", stringArray);
            st.setArray(index, array);      
        }

    }

}

Column type and bank name

ufsinss character(2)[]

Version of the JDBC

postgresql-9.3-1101.jdbc41

Has submitted an error in the part of nullSafeSet.

EDIT 1:

After Googlear a little bit, I found the following. The guy on this line was wrong, it was moved to, Array array = connection.createArrayOf("bpchar", stringArray);. I found out by looking at the SO.com. By searching for the array on nullSafeGet using that line array.getBaseTypeName();. My problem right now is nullSet, when the vector comes null.

EDIT 2:

Finally what was missing was to put this code for when the array voidable. st.setNull(index, Types.ARRAY);

Below I will post the reply with the description of the idea. Please we need and be helped give an up vote on my reply.

Answer 1

I believe that this English OS response is just what you need.

A translation would be:

---

I tried some versions based on Type Array intruded by JDBC4: How can I set a String[] Parameter to a Native query?. The problem is that Hibernate ( at least in version 4.3.1.final) does not work with this new feature and returned me an error message.

Could not determine a type for class: org.postgresql.jdbc4.Jdbc4Array

Then I had to make a specific Usertype (based on several stackoverflow articles, and other sources)

My Model

@Type(type = "fr.mycompany.dao.hibernate.types.ArrayUserType")
private String[] values;

My Usertype

public class ArrayUserType implements UserType {

    /** Constante contenant le type SQL "Array".
     */
    protected static final int[] SQL_TYPES = { Types.ARRAY };

    /**
     * Return the SQL type codes for the columns mapped by this type. The
     * codes are defined on <tt>java.sql.Types</tt>.
     * 
     * @return int[] the typecodes
     * @see java.sql.Types
     */
    public final int[] sqlTypes() {
        return SQL_TYPES;
    }

    /**
     * The class returned by <tt>nullSafeGet()</tt>.
     * 
     * @return Class
     */
    public final Class returnedClass() {
        return String[].class;
    }

    /**
     * Retrieve an instance of the mapped class from a JDBC resultset. Implementors
     * should handle possibility of null values.
     * 
     * @param resultSet a JDBC result set.
     * @param names the column names.
     * @param session SQL en cours.
     * @param owner the containing entity 
     * @return Object
     * @throws org.hibernate.HibernateException exception levée par Hibernate
     * lors de la récupération des données.
     * @throws java.sql.SQLException exception SQL 
     * levées lors de la récupération des données.
     */
    @Override
    public final Object nullSafeGet(
            final ResultSet resultSet, 
            final String[] names, 
            final SessionImplementor session, 
            final Object owner) throws HibernateException, SQLException {
        if (resultSet.wasNull()) {
            return null;
        }

        String[] array = (String[]) resultSet.getArray(names[0]).getArray();
        return array;
    }

    /**
     * Write an instance of the mapped class to a prepared statement. Implementors
     * should handle possibility of null values. A multi-column type should be written
     * to parameters starting from <tt>index</tt>.
     * 
     * @param statement a JDBC prepared statement.
     * @param value the object to write
     * @param index statement parameter index
     * @param session sql en cours
     * @throws org.hibernate.HibernateException exception levée par Hibernate
     * lors de la récupération des données.
     * @throws java.sql.SQLException exception SQL 
     * levées lors de la récupération des données.
     */
    @Override
    public final void nullSafeSet(final PreparedStatement statement, final Object value, 
            final int index, final SessionImplementor session) throws HibernateException, SQLException {

        if (value == null) {
            statement.setNull(index, SQL_TYPES[0]);
        } else {
            String[] castObject = (String[]) value;
            Array array = session.connection().createArrayOf("text", castObject);
            statement.setArray(index, array);
        }
    }

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

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

    @Override
    public final Object assemble(final Serializable arg0, final Object arg1)
            throws HibernateException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public final Serializable disassemble(final Object arg0) throws HibernateException {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public final boolean equals(final Object x, final Object y) throws HibernateException {
        if (x == y) {
            return true;
        } else if (x == null || y == null) {
            return false;
        } else {
            return x.equals(y);
        }
    }

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

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

And at the end, but not least, when I need to run SQL Native Queries, I have to type the parameter with the following syntax:

String[] values = ...
Type arrayType = new CustomType(new ArrayUserType());
query.setParameter("value", values, arrayType);

---

Answer 2

First, I would like to thank the above user who posted me the answer, as I had a few days working on this solution for the company where I work. First I was using my database case postgres, and one of the tables was marked with the type of data char[2]. That’s right, a vector of char with size 2. The first thing was to look for something related and found that the Hibernate does not have native support, so it would be necessary to make a converter. In this case using the class I’m posting already makes implementation of UserType, where it was only necessary to implement the methods nullSafeGet and nullSafeSet hereafter returnedClass.

So first we implemented the nullSafeGet searching the object of the database.

public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor sessionImpl, Object obj) throws HibernateException, SQLException {

        Array array = rs.getArray(names[0]);        
        return NullUtil.isNull(array) ? null : (String[]) array.getArray(); 
   }

The first line takes the object and assigns it to a array class java.sql.array. I draw attention to the possibility of this way of finding out what kind of data the Hibernate works from your column to be used in nullSafeSet. This way the user will have a return of the type in the String, only for test, remembering that the column must have some value filled because, if it does not have it will generate a nullPointerException.

String tipoDaColuna = array.getBaseTypeName();

The type returned was bpchar, then we set out to implement the method nullSafeSet.

public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor sessionImpl) throws HibernateException, SQLException {    

    Connection connection = st.getConnection();
    String[] stringArray = (String[]) value;

    if (NullUtil.isNull(stringArray)) {
        st.setNull(index, Types.ARRAY);
    } else {            
        Array array = connection.createArrayOf("bpchar", stringArray);
        st.setArray(index, array);      
    }
}

This method above searches the vector, if it is null, I select the column as null of the kind array, otherwise Seto on the object the vector as you can see above.

The data type in the model is as String[] ufs.

The library calls were.

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;

That’s it, thank you!