Hello, > I need to update an array of integers in Postgres with Ibatis 2.3.4 (and > Spring). I want to execute the following query: > UPDATE data SET='{1,2}' WHERE dataid=10
I prefer the use of java.sql.Array and a custom TypeHandler. > The query is > > <update id="updateQuery" parameterClass="java.util.Map"> > UPDATE data > <dynamic prepend="SET arrayData =" > > <iterate property="arrayData" open="'{" close="}'" > conjunction=","> > #arrayData[]# > </iterate> > </dynamic> > WHERE dataid=#dataId# > </update> <update id="updateQuery" parameterClass="java.util.Map"> UPDATE data SET arrayData = #arrayData,handler=IntArrayTypeHandler# WHERE dataid=#dataId:INTEGER# </update> > public void updateData(int dataId, MyData mydata) { > Map<String, Object> params = new HashMap<String, Object>(); > List<Integer> arrayData = mydata.getIntegerArray(); > params.put("dataId", dataId); > params.put("arrayData", arrayData); > this.getSqlMapClientTemplate().update("updateQuery", params); > } This should work unchanged. I prefer to use the id of the statement ("updateQuery" in this case) for the name of the method. That makes easier the maintenance. > > Executing the query I get the following response (arrayData contains 2 > values): > > Caused by: com.ibatis.common.jdbc.exception.NestedSQLException: > --- The error occurred while applying a parameter map. > --- Check the updateData-InlineParameterMap. > --- Check the parameter mapping for the 'arrayData[1]' property. > --- Cause: org.postgresql.util.PSQLException: The column index is out of > range: 2, number of columns: 1 at > com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.jav2009-04-07 > 10:27:26,727 DEBUG [java.sql.Connection] - {conn-100043} Connection > 2009-04-07 10:27:26,729 DEBUG [java.sql.Connection] - {conn-100043} > Preparing Statement: UPDATE data SET arrayData = > '{ ? , ? }' WHERE dataId=? > a:107) > at > com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457) > at > com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90) > at > org.springframework.orm.ibatis.SqlMapClientTemplate$10.doInSqlMapClient(SqlMapClientTemplate.java:413) > at > org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209) > ... 43 more > Caused by: org.postgresql.util.PSQLException: Indice di colonna, 2, è > maggiore del numero di colonne 1. > at > org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:52) > at > org.postgresql.core.v3.SimpleParameterList.setLiteralParameter(SimpleParameterList.java:113) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.bindLiteral(AbstractJdbc2Statement.java:2108) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.setInt(AbstractJdbc2Statement.java:1151) > at > org.apache.commons.dbcp.DelegatingPreparedStatement.setInt(DelegatingPreparedStatement.java:117) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) > at java.lang.reflect.Method.invoke(Method.java:585) > at > com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:70) > at $Proxy9.setInt(Unknown Source) > at > com.ibatis.sqlmap.engine.type.IntegerTypeHandler.setParameter(IntegerTypeHandler.java:30) > at > com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69) > at > com.ibatis.sqlmap.engine.mapping.parameter.ParameterMap.setParameter(ParameterMap.java:166) > at > com.ibatis.sqlmap.engine.mapping.parameter.ParameterMap.setParameters(ParameterMap.java:126) > at > com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:78) > at > com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216) > at > com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94) > ... 47 more > > I've also tried the same query with this variant > > public void updateData(int dataId, MyData mydata) { > Map<String, Object> params = new HashMap<String, Object>(); > int[] arrayData = mydata.getIntArray(); > params.put("dataId", dataId); > params.put("arrayData", arrayData); > this.getSqlMapClientTemplate().update("updateQuery", params); > } > > With the same result. Could someone suggest me a solution? Create the IntArrayTypeHandler class and register is in your config. <typeAlias alias="IntArrayTypeHandler" type="com.asci.common.ibatis.IntArrayTypeHandler" /> class IntArrayTypehandler: package com.asci.common.ibatis; import java.sql.Array; import java.sql.SQLException; import java.util.Collection; import com.asci.common.jdbc.IntArray; import com.ibatis.sqlmap.client.extensions.ParameterSetter; import com.ibatis.sqlmap.client.extensions.ResultGetter; import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback; public class IntArrayTypeHandler implements TypeHandlerCallback { public void setParameter(ParameterSetter setter, Object parameter) throws SQLException { Collection<Integer> keys = (Collection<Integer>) parameter; IntArray intArray = new IntArray(keys); setter.setArray(intArray); } public Object getResult(ResultGetter getter) throws SQLException { Array array = getter.getArray(); return array; } public Object valueOf(String string) { return string; } } Implementation of java.sql.Array with an adapter class for the not used methods: package com.asci.common.jdbc; import java.sql.SQLException; import java.sql.Types; import java.util.Collection; public class IntArray extends SqlArrayAdapter { private static final Integer[] emptyArray = new Integer[0]; private int[] array; public IntArray(int[] array) { if (array == null) { throw new IllegalArgumentException("parameter array should not be null"); } this.array = array; } public IntArray(Collection<Integer> set) { if (set == null) { throw new IllegalArgumentException("parameter set should not be null"); } Integer[] keys = set.toArray(emptyArray); this.array = new int[keys.length]; for (int i = 0; i < keys.length; ++i) { Integer key = keys[i]; this.array[i] = key.intValue(); } } @Override public int getBaseType() throws SQLException { return Types.INTEGER; } /** * This method is called by driver ver. 8 but not by ver. 7. */ @Override public String getBaseTypeName() throws SQLException { return "int4"; } /** * This method is called by both drivers ver. 8 and 7. */ @Override public String toString() { String result = "{"; for (int i = 0; i < this.array.length; ++i) { if (i > 0) { result += ","; } result += this.array[i]; } result += "}"; return result; } } In the overridden method toString() I should have used StringBuilder. Sorry. package com.asci.common.jdbc; import java.sql.Array; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Map; public abstract class SqlArrayAdapter implements Array { public Object getArray() throws SQLException { return null; } public Object getArray(long index, int count) throws SQLException { return null; } public Object getArray(long index, int count, Map<String, Class< ? >> map) throws SQLException { return null; } public Object getArray(Map<String, Class< ? >> map) throws SQLException { return null; } public int getBaseType() throws SQLException { return 0; } public String getBaseTypeName() throws SQLException { return null; } public ResultSet getResultSet() throws SQLException { return null; } public ResultSet getResultSet(long index, int count) throws SQLException { return null; } public ResultSet getResultSet(long index, int count, Map<String, Class< ? >> map) throws SQLException { return null; } public ResultSet getResultSet(Map<String, Class< ? >> map) throws SQLException { return null; } } In Java 1.6 you have to implement one more method. Ingmar