Mapping PostgreSQL Arrays with iBATIS

I needed to map a PostgreSQL integer array column to a Java integer array using iBATIS. The following is what seems to do the trick. Suppose you have a table with an integer array:

create my_table (
  id integer primary key,
  array_column integer[]
);

And some data was inserted using e.g. the following insert statement:

insert into my_table (id, array_column) values (1, array[1, 2, 3]);

Now lets have a Java class that can map to the above table, as follows:

public class MyTable {

  private int id = 0;
  private int[] arrayColumn = null;
  
  public int getId() { return id; }
  public void setId(int id) { this.id = id; }
  public int[] getArrayColumn() { return arrayColumn; }
  public void setArrayColumn(int[] arrayColumn)
    { this.arrayColumn = arrayColumn; }
  
}

We will now specify a select map, and we pass a result map to the select which is going to contain the mappings from the various columns to the properties in the Java bean.

<select id="getMyTable" parameterClass="int" resultClass="MyTable"
        resultMap="myTableResult">
    select id, array_column from my_table where id = #id#
</select>

The result map that we refer to in the resultMap attribute in the above select map is specified below:

<resultMap id="myTableResult" class="MyTable">
    <result property="id" column="id" />
    <result property="arrayColumn" column="array_column" jdbcType="ARRAY"
            javaType="java.sql.Array" typeHandler="ArrayTypeMapper" />
</resultMap>

In this result map, we map the id column to the id property and the array_column column to the arrayColumn property. There is however additional configuration needed for this second mapping. We specify that the JDBC type is ARRAY (as in java.sql.Types.ARRAY). We also specify that the Java type is java.sql.Array, but more importantly we specify the typeHandler attribute, which refers to the handler that is going to handle this array, and it is called ArrayTypeMapper. It implements TypeHandlerCallback, and its contents are as follows:

import java.sql.Array;
import java.sql.SQLException;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;

public class ArrayTypeMapper implements TypeHandlerCallback {

  public void setParameter(ParameterSetter setter, Object parameter) 
        throws SQLException {
    throw new UnsupportedOperationException("Not implemented");
  }

  public Object getResult(ResultGetter getter) throws SQLException {
    Array array = getter.getResultSet().getArray(getter.getColumnName());
    if (!getter.getResultSet().wasNull()) {
      return array.getArray();
    } else {
      return null;
    }
  }

  public Object valueOf(String s) {
    throw new UnsupportedOperationException("Not implemented");
  }
}

It is very convenient that the getter object has the column name available (so we can avoid having to explicitely name the column or use indexes). Do not get the array from the array object immediately, you should first check whether is was null. If that was the case, return null (causing the MyTable object's array to be set to null), otherwise return array.getArray(). This will automatically cause the int[] in MyTable to be filled with the contents of the array column of my_table.