ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ingmar Lötzsch <iloetz...@asci-systemhaus.de>
Subject Re: Update Postgres Array with IBatis
Date Tue, 07 Apr 2009 09:25:27 GMT
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

Mime
View raw message