You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by beppes <su...@gmail.com> on 2009/04/07 10:52:25 UTC
Update Postgres Array with IBatis
Hi to everybody. I'm new to this forum.
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
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>
The method realized
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);
}
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?
--
View this message in context: http://www.nabble.com/Update-Postgres-Array-with-IBatis-tp22924852p22924852.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
[SOLVED] Update Postgres Array with IBatis
Posted by beppes <su...@gmail.com>.
It works!!!!
Thank you so much!!!
Giuseppe
--
View this message in context: http://www.nabble.com/Update-Postgres-Array-with-IBatis-tp22924852p22930040.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Update Postgres Array with IBatis
Posted by Ingmar Lötzsch <il...@asci-systemhaus.de>.
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