Inserting int[] into PostgreSql with iBatis

Question

... is there an easy way to insert a Java int[] into PostgreSql with the help of iBatis? (the older one, not the new MyBatis)

Not sure if I DO need a custom type handler or not, but I'm having a difficult time finding a code sample that would illustrate what's going on.

Thanks in advance.

ps:

since the original posting, I'm able to read the array from DB and populate the int[] in the domain object. But can't write to the db yet :-(

so in the domain model there's:

int[] crap = null;

with getters and setters, cusom property handler looks like this:

public class ArrayTypeHandler implements TypeHandlerCallback {
public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {

    if( parameter == null){
        setter.setNull( Types.ARRAY);
    } else {
        setter.setArray( (Array) Arrays.asList(parameter ) );
    }

}

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 string) {
    throw new UnsupportedOperationException("Not supported yet.");
}

}

sqlMapConfig.xml:

<typeHandler javaType="java.sql.Array" jdbcType="ARRAY" callback="project.persistance.sqlmapdao.ArrayTypeHandler"  />

When trying to update i get the following error:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];   

--- The error occurred in project/persistance/sql_xml/Article.xml.
--- The error occurred while applying a parameter map.
--- Check the updateArticle-InlineParameterMap.
--- Check the parameter mapping for the 'crap' property.
--- Cause: java.lang.NullPointerException; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in project/persistance/sql_xml/Article.xml.
--- The error occurred while applying a parameter map.
--- Check the updateArticle-InlineParameterMap.
--- Check the parameter mapping for the 'crap' property.
--- Cause: java.lang.NullPointerException

... any hints as to what I'm missing? thanks

===

... worked my way up to ClassCastExceptiong :-)

trying to set the propery:

    public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
    int[] c = (int[]) parameter;

    setter.setArray( (java.sql.Array) c  );
}

... and the ensuing exception:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [null]; error code [0];   

--- The error occurred in project/persistance/sql_xml/Article.xml.
--- The error occurred while applying a parameter map.
--- Check the updateArticle-InlineParameterMap.
--- Check the parameter mapping for the 'crap' property.
--- Cause: java.lang.ClassCastException: java.util.ArrayList; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in project/persistance/sql_xml/Article.xml.
--- The error occurred while applying a parameter map.
--- Check the updateArticle-InlineParameterMap.
--- Check the parameter mapping for the 'crap' property.
--- Cause: java.lang.ClassCastException: java.util.ArrayList

... I've had it today though. Thanks

Answer 1

This looks promising:

http://beerholder.blogspot.com/2007/10/mapping-postgresql-arrays-with-ibatis-i.html

Answer 2

Based on the page cited by Jeremy's answer, I had coded my own handler (with some hacks) a while ago. In case you find it useful:

public class ArrayIntsTypeHandlerCallback implements TypeHandlerCallback {

     /**
      * to write an integer array in db. Object should be Integer[]
      */    
      public void setParameter(ParameterSetter setter, Object parameter) throws SQLException  {
          Connection con = setter.getPreparedStatement().getConnection();
          // hack: if using poolable connection from dbcp must get inside true connection! 
          if(con instanceof org.apache.commons.dbcp.PoolableConnection ) {
              con =     ((org.apache.commons.dbcp.PoolableConnection)con).getInnermostDelegate();
          }
          Array array = con.createArrayOf("integer", (Object[])parameter);
          setter.setArray(array);
        }

      /**
       * read integer array from db. returns Integer[]
       */
      public Object getResult(ResultGetter getter) throws SQLException {
        Array array = getter.getArray();
        if (!getter.getResultSet().wasNull()) {
          return array.getArray();
        } else {
          return null;
        }
      }

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


    }

Answer 3

... got it finally. Here's how it went from the beginning:

... first: reading the int[]

... second: second, while searching and stumbling around found an implementation of the java.sql.Array interface (jdk 1.6 only though ) and posting on a mailing list from 2005.

The final implementation of the setParameter method in TypeHandlerCallbac iterface:

    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<String, Class<?>> map) {
            return null;
        }

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

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

        public ResultSet getResultSet() {
            return null;
        }

        public ResultSet getResultSet(Map<String, Class<?>> map) {
            return null;
        }

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

        public ResultSet getResultSet(long index, int count,
            Map<String, Class<?>> 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;
}

In the end thanks to all and hope this will save someone else some time :-)

PS: Just as a final FYI, when I posted the question to the MyBatis mailing list, here's what I got back:

Several problems with this...

1. Arrays.asList() does not work for primitive arrays. See here:

   http://code.google.com/p/mybatis/source/detail?r=3467

   For the change we made in MyBatis 3 to deal with primitive arrays.

2. Then, you probably cannot cast a java.util.List to java.sql.Array. To make the java.sql.Array, you will need to use some utility from your JDBC driver, or switch to JDK6 and use the Connection.createArrayOf(...) method.

Support for the ARRAY type in JDBC is a total mess, mixing it with primitive arrays adds a whole other layer of messiness :)