Sunday, October 28, 2007

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 getterthrows 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.

8 comments:

Unknown said...

Hi
I have a postgresql double array. Can I use your method to map this array with ibatis ??

[BOFH]Basilisk said...

It should work out of the box for doubles as well, as the array type mapper isn't aware of what type of array that is being retrieved.

Unknown said...

Hi
Does not work, it trows an exception
Cause: net.sf.cglib.beans.BulkBeanException: [Ljava.lang.Double; cannot be cast to [D; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:

Yo can see the trace from apache tomcat in link

I use the java debugger and find that the field string contains the array of doubles, but the array list is null.

You have any idea whats is wrong ??

Unknown said...

Hi,
I've found the error. It try to asign
to a double[] variable a Double[] value.
Converting de Double to double solution
the problem.
Thanks anyway

[BOFH]Basilisk said...

Glad to hear it works! :-)

Marco Pancotti said...

What about the setting of the array in an insert/update statement?

You left the setParameter method not implemented, but did you try to use it? If yes, can you share your experience, please?

Ijon Tichy the Nth said...

... here's a question on StackOverflow dealing with the insert problem and below the 'short version' of it:

public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
setter.setArray( this.convertIntegerToPgSqlArray( (int[]) parameter ) );
}

...

private java.sql.Array convertIntegerToPgSqlArray(final int[] p) {
if (p == null || p.length < 1) {
return null;
}
Array a = new Array() {

public String getBaseTypeName() {
return "int4";
}

public int getBaseType() {
return 0;
}

public Object getArray() {
return null;
}

public Object getArray(Map> map) {
return null;
}

public Object getArray(long index, int count) {
return null;
}

public Object getArray(long index, int count, Map> map) {
return null;
}

public ResultSet getResultSet() {
return null;
}

public ResultSet getResultSet(Map> map) {
return null;
}

public ResultSet getResultSet(long index, int count) {
return null;
}

public ResultSet getResultSet(long index, int count,
Map> map) {
return null;
}

public String toString() {
String fp = "{";
if (p.length == 0) {
} else {
for (int i = 0; i < p.length - 1; i++) {
fp += p[i] + ",";
}
fp += p[p.length - 1];
}
fp += "}";
return fp;
}
};
return a;
}

Ijon Tichy the Nth said...

... forgot to mention, but that's a jdk1.5 compatible implementation of the java.sql.Array, - that's what I needed unfortunately.