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 Alex Savitsky <AS...@cihi.ca> on 2008/12/09 21:29:34 UTC
Trying to use HashMap as a param object for an UPDATE - getting an
exception
Hi,
I'm trying to execute an update, with a HashMap for parameter (there's no business object to pass). The mapping file is as follows (iBATIS v.2.3.0.677)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Sector">
<resultMap class="java.util.HashMap" id="sector">
<result property="id" column="SECTOR_ID" javaType="java.lang.Integer" />
<result property="code" column="SECTOR_CD" />
<result property="description" column="SECTOR_DESC" />
<result property="fiscalYear" column="FISCAL_YR" javaType="java.lang.Integer" />
</resultMap>
<parameterMap class="java.util.HashMap" id="sector">
<parameter property="id" jdbcType="NUMERIC" />
<parameter property="code" jdbcType="VARCHAR" />
<parameter property="description" jdbcType="VARCHAR" />
<parameter property="fiscalYear" jdbcType="NUMERIC" />
</parameterMap>
<select id="getSectorList" resultMap="sector" parameterMap="sector">
SELECT * FROM SECTOR WHERE FISCAL_YR = #fiscalYear#
<isNotEmpty property="code">AND SECTOR_CD LIKE #code#||'%'</isNotEmpty>
<isNotEmpty property="description">AND SECTOR_DESC LIKE #description#||'%'</isNotEmpty>
</select>
<update id="updateSector" parameterMap="sector">
UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
</update>
</sqlMap>
Both parameters and results are Maps, and the select works when I pass it a parameter map, whether it has all the properties, or not. Update, however, fails:
Map s1 = new HashMap();
s1.put("code", "001");
s1.put("description", "Region Transaction upd");
s1.put("fiscalYear", "2005");
s1.put("id", "1501");
client.update("updateSector", s1);
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in ca/cihi/cmdb/model/Sector.xml.
--- The error occurred while applying a parameter map.
--- Check the Sector.sector.
--- Check the parameter mapping for the 'id' property.
--- Cause: java.sql.SQLException: Invalid column index
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)
at ca.cihi.cmdb.service.MaintenanceService.update(MaintenanceService.java:52)
... 29 more
Caused by: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4754)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4717)
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 $Proxy2.setString(Unknown Source)
at com.ibatis.sqlmap.engine.type.StringTypeHandler.setParameter(StringTypeHandler.java:30)
at com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69)
at com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter(BasicParameterMap.java:165)
at com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters(BasicParameterMap.java:125)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:79)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
... 33 more
Am I doing something wrong?
Thanks,
Alex
Re: Trying to use HashMap as a param object for an UPDATE - getting an exception
Posted by Larry Meadors <la...@gmail.com>.
No, I meant I almost never have a parameterClass or parameterMap in my
mapped statements.
I use Map objects a lot for reads, but not often for writes.
Larry
On Mon, Dec 15, 2008 at 7:38 AM, Rick <ri...@gmail.com> wrote:
> On Sun, Dec 14, 2008 at 11:02 AM, Larry Meadors <la...@gmail.com> wrote:
>> On Sat, Dec 13, 2008 at 8:09 PM, Clinton Begin <cl...@gmail.com> wrote:
>>> That said, (to further argue with myself), I actually prefer the flexibility
>>> of not typing the statement. It's a sort of duck typing for iBATIS.
>>>
>>
>> I almost never use a parameter type or map. :-/
>>
>> I'm just lazy that way.
>>
>
> Even for retrieves? I like using map as my parameter object for
> retrieves. I always seem to run into cases where the POJO itself
> doesn't really fit for retrieves because you often need extra
> parameters that I don't feel like sticking in as properties on my
> pojo. Maps seem to work nice for this. ( Sometimes I'll create a
> FooBarSearchCriteria object, if it's parameters chosen from a
> front-end, but often a Map is just fine.)
>
> Don't fear the Map! </vic>
>
Re: Trying to use HashMap as a param object for an UPDATE - getting an exception
Posted by Rick <ri...@gmail.com>.
On Sun, Dec 14, 2008 at 11:02 AM, Larry Meadors <la...@gmail.com> wrote:
> On Sat, Dec 13, 2008 at 8:09 PM, Clinton Begin <cl...@gmail.com> wrote:
>> That said, (to further argue with myself), I actually prefer the flexibility
>> of not typing the statement. It's a sort of duck typing for iBATIS.
>>
>
> I almost never use a parameter type or map. :-/
>
> I'm just lazy that way.
>
Even for retrieves? I like using map as my parameter object for
retrieves. I always seem to run into cases where the POJO itself
doesn't really fit for retrieves because you often need extra
parameters that I don't feel like sticking in as properties on my
pojo. Maps seem to work nice for this. ( Sometimes I'll create a
FooBarSearchCriteria object, if it's parameters chosen from a
front-end, but often a Map is just fine.)
Don't fear the Map! </vic>
Re: Trying to use HashMap as a param object for an UPDATE - getting an exception
Posted by Larry Meadors <la...@gmail.com>.
On Sat, Dec 13, 2008 at 8:09 PM, Clinton Begin <cl...@gmail.com> wrote:
> That said, (to further argue with myself), I actually prefer the flexibility
> of not typing the statement. It's a sort of duck typing for iBATIS.
>
I almost never use a parameter type or map. :-/
I'm just lazy that way.
Larry
Re: Trying to use HashMap as a param object for an UPDATE - getting an exception
Posted by Clinton Begin <cl...@gmail.com>.
Now, now... :-)
Maps are useful for more than just ad hoc non-types... they're good for
passing multiple (typed) params as well.
But I believe the typing Nathan was talking about was the parameter type
itself... without specifying the Map as the expected parameter, you could
happily pass someObjectOfAnyClass to the statement and it would work.
That said, (to further argue with myself), I actually prefer the flexibility
of not typing the statement. It's a sort of duck typing for iBATIS.
Clinton
On Sat, Dec 13, 2008 at 5:48 PM, Larry Meadors <la...@gmail.com>wrote:
> If you're passing a Map, you've already lost it...in more ways than one.
> ;-)
>
> Larry
>
>
> On Sat, Dec 13, 2008 at 3:56 PM, Nathan Maves <na...@gmail.com>
> wrote:
> > sure you could do that put you lose a bit of type safety
> >
> > On Fri, Dec 12, 2008 at 4:08 PM, Larry Meadors <la...@gmail.com>
> wrote:
> >> Actually...it's even easier.
> >>
> >> <update id="updateSector">
> >>
> >> No parameter class or map required.
> >>
> >> Larry
> >>
> >>
> >> On Fri, Dec 12, 2008 at 12:12 PM, joeweder <jo...@gmail.com> wrote:
> >>>
> >>> You do not have to map a Map as a parameter. Just tell iBatis that the
> >>> parameter is a map and use the key-names you used to populate it in
> your
> >>> update (note: use parameterClass instead of parameterMap).
> >>>
> >>> Map s1 = new HashMap();
> >>> s1.put("code", "001");
> >>> s1.put("description", "Region Transaction upd");
> >>> s1.put("fiscalYear", "2005");
> >>> s1.put("id", "1501");
> >>> client.update("updateSector", s1);
> >>>
> >>> <update id="updateSector" parameterClass="map">
> >>> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID =
> #id#
> >>> </update>
> >>>
> >>> That's all there is to it.
> >>>
> >>>
> >>> Alex Savitsky-2 wrote:
> >>>>
> >>>> Hi,
> >>>>
> >>>> I'm trying to execute an update, with a HashMap for parameter (there's
> no
> >>>> business object to pass). The mapping file is as follows (iBATIS
> >>>> v.2.3.0.677)
> >>>>
> >>>> <?xml version="1.0" encoding="UTF-8" ?>
> >>>> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> >>>> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> >>>> <sqlMap namespace="Sector">
> >>>> <resultMap class="java.util.HashMap" id="sector">
> >>>> <result property="id" column="SECTOR_ID"
> javaType="java.lang.Integer" />
> >>>> <result property="code" column="SECTOR_CD" />
> >>>> <result property="description" column="SECTOR_DESC" />
> >>>> <result property="fiscalYear" column="FISCAL_YR"
> >>>> javaType="java.lang.Integer" />
> >>>> </resultMap>
> >>>> <parameterMap class="java.util.HashMap" id="sector">
> >>>> <parameter property="id" jdbcType="NUMERIC" />
> >>>> <parameter property="code" jdbcType="VARCHAR" />
> >>>> <parameter property="description" jdbcType="VARCHAR" />
> >>>> <parameter property="fiscalYear" jdbcType="NUMERIC" />
> >>>> </parameterMap>
> >>>> <select id="getSectorList" resultMap="sector" parameterMap="sector">
> >>>> SELECT * FROM SECTOR WHERE FISCAL_YR = #fiscalYear#
> >>>> <isNotEmpty property="code">AND SECTOR_CD LIKE
> #code#||'%'</isNotEmpty>
> >>>> <isNotEmpty property="description">AND SECTOR_DESC LIKE
> >>>> #description#||'%'</isNotEmpty>
> >>>> </select>
> >>>> <update id="updateSector" parameterMap="sector">
> >>>> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID =
> #id#
> >>>> </update>
> >>>> </sqlMap>
> >>>>
> >>>> Both parameters and results are Maps, and the select works when I pass
> it
> >>>> a parameter map, whether it has all the properties, or not. Update,
> >>>> however, fails:
> >>>>
> >>>> Map s1 = new HashMap();
> >>>> s1.put("code", "001");
> >>>> s1.put("description", "Region Transaction upd");
> >>>> s1.put("fiscalYear", "2005");
> >>>> s1.put("id", "1501");
> >>>> client.update("updateSector", s1);
> >>>>
> >>>> com.ibatis.common.jdbc.exception.NestedSQLException:
> >>>> --- The error occurred in ca/cihi/cmdb/model/Sector.xml.
> >>>> --- The error occurred while applying a parameter map.
> >>>> --- Check the Sector.sector.
> >>>> --- Check the parameter mapping for the 'id' property.
> >>>> --- Cause: java.sql.SQLException: Invalid column index
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)
> >>>> at
> >>>>
> ca.cihi.cmdb.service.MaintenanceService.update(MaintenanceService.java:52)
> >>>> ... 29 more
> >>>> Caused by: java.sql.SQLException: Invalid column index
> >>>> at
> >>>>
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
> >>>> at
> >>>>
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
> >>>> at
> >>>>
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
> >>>> at
> >>>>
> oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4754)
> >>>> at
> >>>>
> oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4717)
> >>>> 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 $Proxy2.setString(Unknown Source)
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.type.StringTypeHandler.setParameter(StringTypeHandler.java:30)
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69)
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter(BasicParameterMap.java:165)
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters(BasicParameterMap.java:125)
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:79)
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
> >>>> at
> >>>>
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
> >>>> ... 33 more
> >>>>
> >>>> Am I doing something wrong?
> >>>>
> >>>> Thanks,
> >>>>
> >>>> Alex
> >>>>
> >>>
> >>> --
> >>> View this message in context:
> http://www.nabble.com/Trying-to-use-HashMap-as-a-param-object-for-an-UPDATE---getting-an-exception-tp20922966p20981745.html
> >>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> >>>
> >>>
> >>
> >
>
Re: Trying to use HashMap as a param object for an UPDATE - getting an exception
Posted by Larry Meadors <la...@gmail.com>.
If you're passing a Map, you've already lost it...in more ways than one. ;-)
Larry
On Sat, Dec 13, 2008 at 3:56 PM, Nathan Maves <na...@gmail.com> wrote:
> sure you could do that put you lose a bit of type safety
>
> On Fri, Dec 12, 2008 at 4:08 PM, Larry Meadors <la...@gmail.com> wrote:
>> Actually...it's even easier.
>>
>> <update id="updateSector">
>>
>> No parameter class or map required.
>>
>> Larry
>>
>>
>> On Fri, Dec 12, 2008 at 12:12 PM, joeweder <jo...@gmail.com> wrote:
>>>
>>> You do not have to map a Map as a parameter. Just tell iBatis that the
>>> parameter is a map and use the key-names you used to populate it in your
>>> update (note: use parameterClass instead of parameterMap).
>>>
>>> Map s1 = new HashMap();
>>> s1.put("code", "001");
>>> s1.put("description", "Region Transaction upd");
>>> s1.put("fiscalYear", "2005");
>>> s1.put("id", "1501");
>>> client.update("updateSector", s1);
>>>
>>> <update id="updateSector" parameterClass="map">
>>> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
>>> </update>
>>>
>>> That's all there is to it.
>>>
>>>
>>> Alex Savitsky-2 wrote:
>>>>
>>>> Hi,
>>>>
>>>> I'm trying to execute an update, with a HashMap for parameter (there's no
>>>> business object to pass). The mapping file is as follows (iBATIS
>>>> v.2.3.0.677)
>>>>
>>>> <?xml version="1.0" encoding="UTF-8" ?>
>>>> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
>>>> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
>>>> <sqlMap namespace="Sector">
>>>> <resultMap class="java.util.HashMap" id="sector">
>>>> <result property="id" column="SECTOR_ID" javaType="java.lang.Integer" />
>>>> <result property="code" column="SECTOR_CD" />
>>>> <result property="description" column="SECTOR_DESC" />
>>>> <result property="fiscalYear" column="FISCAL_YR"
>>>> javaType="java.lang.Integer" />
>>>> </resultMap>
>>>> <parameterMap class="java.util.HashMap" id="sector">
>>>> <parameter property="id" jdbcType="NUMERIC" />
>>>> <parameter property="code" jdbcType="VARCHAR" />
>>>> <parameter property="description" jdbcType="VARCHAR" />
>>>> <parameter property="fiscalYear" jdbcType="NUMERIC" />
>>>> </parameterMap>
>>>> <select id="getSectorList" resultMap="sector" parameterMap="sector">
>>>> SELECT * FROM SECTOR WHERE FISCAL_YR = #fiscalYear#
>>>> <isNotEmpty property="code">AND SECTOR_CD LIKE #code#||'%'</isNotEmpty>
>>>> <isNotEmpty property="description">AND SECTOR_DESC LIKE
>>>> #description#||'%'</isNotEmpty>
>>>> </select>
>>>> <update id="updateSector" parameterMap="sector">
>>>> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
>>>> </update>
>>>> </sqlMap>
>>>>
>>>> Both parameters and results are Maps, and the select works when I pass it
>>>> a parameter map, whether it has all the properties, or not. Update,
>>>> however, fails:
>>>>
>>>> Map s1 = new HashMap();
>>>> s1.put("code", "001");
>>>> s1.put("description", "Region Transaction upd");
>>>> s1.put("fiscalYear", "2005");
>>>> s1.put("id", "1501");
>>>> client.update("updateSector", s1);
>>>>
>>>> com.ibatis.common.jdbc.exception.NestedSQLException:
>>>> --- The error occurred in ca/cihi/cmdb/model/Sector.xml.
>>>> --- The error occurred while applying a parameter map.
>>>> --- Check the Sector.sector.
>>>> --- Check the parameter mapping for the 'id' property.
>>>> --- Cause: java.sql.SQLException: Invalid column index
>>>> at
>>>> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
>>>> at
>>>> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
>>>> at
>>>> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
>>>> at
>>>> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)
>>>> at
>>>> ca.cihi.cmdb.service.MaintenanceService.update(MaintenanceService.java:52)
>>>> ... 29 more
>>>> Caused by: java.sql.SQLException: Invalid column index
>>>> at
>>>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
>>>> at
>>>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
>>>> at
>>>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
>>>> at
>>>> oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4754)
>>>> at
>>>> oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4717)
>>>> 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 $Proxy2.setString(Unknown Source)
>>>> at
>>>> com.ibatis.sqlmap.engine.type.StringTypeHandler.setParameter(StringTypeHandler.java:30)
>>>> at
>>>> com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69)
>>>> at
>>>> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter(BasicParameterMap.java:165)
>>>> at
>>>> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters(BasicParameterMap.java:125)
>>>> at
>>>> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:79)
>>>> at
>>>> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
>>>> at
>>>> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
>>>> ... 33 more
>>>>
>>>> Am I doing something wrong?
>>>>
>>>> Thanks,
>>>>
>>>> Alex
>>>>
>>>
>>> --
>>> View this message in context: http://www.nabble.com/Trying-to-use-HashMap-as-a-param-object-for-an-UPDATE---getting-an-exception-tp20922966p20981745.html
>>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>>
>>>
>>
>
Re: Trying to use HashMap as a param object for an UPDATE - getting an exception
Posted by Nathan Maves <na...@gmail.com>.
sure you could do that put you lose a bit of type safety
On Fri, Dec 12, 2008 at 4:08 PM, Larry Meadors <la...@gmail.com> wrote:
> Actually...it's even easier.
>
> <update id="updateSector">
>
> No parameter class or map required.
>
> Larry
>
>
> On Fri, Dec 12, 2008 at 12:12 PM, joeweder <jo...@gmail.com> wrote:
>>
>> You do not have to map a Map as a parameter. Just tell iBatis that the
>> parameter is a map and use the key-names you used to populate it in your
>> update (note: use parameterClass instead of parameterMap).
>>
>> Map s1 = new HashMap();
>> s1.put("code", "001");
>> s1.put("description", "Region Transaction upd");
>> s1.put("fiscalYear", "2005");
>> s1.put("id", "1501");
>> client.update("updateSector", s1);
>>
>> <update id="updateSector" parameterClass="map">
>> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
>> </update>
>>
>> That's all there is to it.
>>
>>
>> Alex Savitsky-2 wrote:
>>>
>>> Hi,
>>>
>>> I'm trying to execute an update, with a HashMap for parameter (there's no
>>> business object to pass). The mapping file is as follows (iBATIS
>>> v.2.3.0.677)
>>>
>>> <?xml version="1.0" encoding="UTF-8" ?>
>>> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
>>> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
>>> <sqlMap namespace="Sector">
>>> <resultMap class="java.util.HashMap" id="sector">
>>> <result property="id" column="SECTOR_ID" javaType="java.lang.Integer" />
>>> <result property="code" column="SECTOR_CD" />
>>> <result property="description" column="SECTOR_DESC" />
>>> <result property="fiscalYear" column="FISCAL_YR"
>>> javaType="java.lang.Integer" />
>>> </resultMap>
>>> <parameterMap class="java.util.HashMap" id="sector">
>>> <parameter property="id" jdbcType="NUMERIC" />
>>> <parameter property="code" jdbcType="VARCHAR" />
>>> <parameter property="description" jdbcType="VARCHAR" />
>>> <parameter property="fiscalYear" jdbcType="NUMERIC" />
>>> </parameterMap>
>>> <select id="getSectorList" resultMap="sector" parameterMap="sector">
>>> SELECT * FROM SECTOR WHERE FISCAL_YR = #fiscalYear#
>>> <isNotEmpty property="code">AND SECTOR_CD LIKE #code#||'%'</isNotEmpty>
>>> <isNotEmpty property="description">AND SECTOR_DESC LIKE
>>> #description#||'%'</isNotEmpty>
>>> </select>
>>> <update id="updateSector" parameterMap="sector">
>>> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
>>> </update>
>>> </sqlMap>
>>>
>>> Both parameters and results are Maps, and the select works when I pass it
>>> a parameter map, whether it has all the properties, or not. Update,
>>> however, fails:
>>>
>>> Map s1 = new HashMap();
>>> s1.put("code", "001");
>>> s1.put("description", "Region Transaction upd");
>>> s1.put("fiscalYear", "2005");
>>> s1.put("id", "1501");
>>> client.update("updateSector", s1);
>>>
>>> com.ibatis.common.jdbc.exception.NestedSQLException:
>>> --- The error occurred in ca/cihi/cmdb/model/Sector.xml.
>>> --- The error occurred while applying a parameter map.
>>> --- Check the Sector.sector.
>>> --- Check the parameter mapping for the 'id' property.
>>> --- Cause: java.sql.SQLException: Invalid column index
>>> at
>>> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
>>> at
>>> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
>>> at
>>> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
>>> at
>>> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)
>>> at
>>> ca.cihi.cmdb.service.MaintenanceService.update(MaintenanceService.java:52)
>>> ... 29 more
>>> Caused by: java.sql.SQLException: Invalid column index
>>> at
>>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
>>> at
>>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
>>> at
>>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
>>> at
>>> oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4754)
>>> at
>>> oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4717)
>>> 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 $Proxy2.setString(Unknown Source)
>>> at
>>> com.ibatis.sqlmap.engine.type.StringTypeHandler.setParameter(StringTypeHandler.java:30)
>>> at
>>> com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69)
>>> at
>>> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter(BasicParameterMap.java:165)
>>> at
>>> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters(BasicParameterMap.java:125)
>>> at
>>> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:79)
>>> at
>>> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
>>> at
>>> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
>>> ... 33 more
>>>
>>> Am I doing something wrong?
>>>
>>> Thanks,
>>>
>>> Alex
>>>
>>
>> --
>> View this message in context: http://www.nabble.com/Trying-to-use-HashMap-as-a-param-object-for-an-UPDATE---getting-an-exception-tp20922966p20981745.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>
Re: Trying to use HashMap as a param object for an UPDATE - getting an exception
Posted by Larry Meadors <la...@gmail.com>.
Actually...it's even easier.
<update id="updateSector">
No parameter class or map required.
Larry
On Fri, Dec 12, 2008 at 12:12 PM, joeweder <jo...@gmail.com> wrote:
>
> You do not have to map a Map as a parameter. Just tell iBatis that the
> parameter is a map and use the key-names you used to populate it in your
> update (note: use parameterClass instead of parameterMap).
>
> Map s1 = new HashMap();
> s1.put("code", "001");
> s1.put("description", "Region Transaction upd");
> s1.put("fiscalYear", "2005");
> s1.put("id", "1501");
> client.update("updateSector", s1);
>
> <update id="updateSector" parameterClass="map">
> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
> </update>
>
> That's all there is to it.
>
>
> Alex Savitsky-2 wrote:
>>
>> Hi,
>>
>> I'm trying to execute an update, with a HashMap for parameter (there's no
>> business object to pass). The mapping file is as follows (iBATIS
>> v.2.3.0.677)
>>
>> <?xml version="1.0" encoding="UTF-8" ?>
>> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
>> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
>> <sqlMap namespace="Sector">
>> <resultMap class="java.util.HashMap" id="sector">
>> <result property="id" column="SECTOR_ID" javaType="java.lang.Integer" />
>> <result property="code" column="SECTOR_CD" />
>> <result property="description" column="SECTOR_DESC" />
>> <result property="fiscalYear" column="FISCAL_YR"
>> javaType="java.lang.Integer" />
>> </resultMap>
>> <parameterMap class="java.util.HashMap" id="sector">
>> <parameter property="id" jdbcType="NUMERIC" />
>> <parameter property="code" jdbcType="VARCHAR" />
>> <parameter property="description" jdbcType="VARCHAR" />
>> <parameter property="fiscalYear" jdbcType="NUMERIC" />
>> </parameterMap>
>> <select id="getSectorList" resultMap="sector" parameterMap="sector">
>> SELECT * FROM SECTOR WHERE FISCAL_YR = #fiscalYear#
>> <isNotEmpty property="code">AND SECTOR_CD LIKE #code#||'%'</isNotEmpty>
>> <isNotEmpty property="description">AND SECTOR_DESC LIKE
>> #description#||'%'</isNotEmpty>
>> </select>
>> <update id="updateSector" parameterMap="sector">
>> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
>> </update>
>> </sqlMap>
>>
>> Both parameters and results are Maps, and the select works when I pass it
>> a parameter map, whether it has all the properties, or not. Update,
>> however, fails:
>>
>> Map s1 = new HashMap();
>> s1.put("code", "001");
>> s1.put("description", "Region Transaction upd");
>> s1.put("fiscalYear", "2005");
>> s1.put("id", "1501");
>> client.update("updateSector", s1);
>>
>> com.ibatis.common.jdbc.exception.NestedSQLException:
>> --- The error occurred in ca/cihi/cmdb/model/Sector.xml.
>> --- The error occurred while applying a parameter map.
>> --- Check the Sector.sector.
>> --- Check the parameter mapping for the 'id' property.
>> --- Cause: java.sql.SQLException: Invalid column index
>> at
>> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
>> at
>> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
>> at
>> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
>> at
>> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)
>> at
>> ca.cihi.cmdb.service.MaintenanceService.update(MaintenanceService.java:52)
>> ... 29 more
>> Caused by: java.sql.SQLException: Invalid column index
>> at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
>> at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
>> at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
>> at
>> oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4754)
>> at
>> oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4717)
>> 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 $Proxy2.setString(Unknown Source)
>> at
>> com.ibatis.sqlmap.engine.type.StringTypeHandler.setParameter(StringTypeHandler.java:30)
>> at
>> com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69)
>> at
>> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter(BasicParameterMap.java:165)
>> at
>> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters(BasicParameterMap.java:125)
>> at
>> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:79)
>> at
>> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
>> at
>> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
>> ... 33 more
>>
>> Am I doing something wrong?
>>
>> Thanks,
>>
>> Alex
>>
>
> --
> View this message in context: http://www.nabble.com/Trying-to-use-HashMap-as-a-param-object-for-an-UPDATE---getting-an-exception-tp20922966p20981745.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Re: Trying to use HashMap as a param object for an UPDATE - getting
an exception
Posted by joeweder <jo...@gmail.com>.
You do not have to map a Map as a parameter. Just tell iBatis that the
parameter is a map and use the key-names you used to populate it in your
update (note: use parameterClass instead of parameterMap).
Map s1 = new HashMap();
s1.put("code", "001");
s1.put("description", "Region Transaction upd");
s1.put("fiscalYear", "2005");
s1.put("id", "1501");
client.update("updateSector", s1);
<update id="updateSector" parameterClass="map">
UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
</update>
That's all there is to it.
Alex Savitsky-2 wrote:
>
> Hi,
>
> I'm trying to execute an update, with a HashMap for parameter (there's no
> business object to pass). The mapping file is as follows (iBATIS
> v.2.3.0.677)
>
> <?xml version="1.0" encoding="UTF-8" ?>
> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> <sqlMap namespace="Sector">
> <resultMap class="java.util.HashMap" id="sector">
> <result property="id" column="SECTOR_ID" javaType="java.lang.Integer" />
> <result property="code" column="SECTOR_CD" />
> <result property="description" column="SECTOR_DESC" />
> <result property="fiscalYear" column="FISCAL_YR"
> javaType="java.lang.Integer" />
> </resultMap>
> <parameterMap class="java.util.HashMap" id="sector">
> <parameter property="id" jdbcType="NUMERIC" />
> <parameter property="code" jdbcType="VARCHAR" />
> <parameter property="description" jdbcType="VARCHAR" />
> <parameter property="fiscalYear" jdbcType="NUMERIC" />
> </parameterMap>
> <select id="getSectorList" resultMap="sector" parameterMap="sector">
> SELECT * FROM SECTOR WHERE FISCAL_YR = #fiscalYear#
> <isNotEmpty property="code">AND SECTOR_CD LIKE #code#||'%'</isNotEmpty>
> <isNotEmpty property="description">AND SECTOR_DESC LIKE
> #description#||'%'</isNotEmpty>
> </select>
> <update id="updateSector" parameterMap="sector">
> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
> </update>
> </sqlMap>
>
> Both parameters and results are Maps, and the select works when I pass it
> a parameter map, whether it has all the properties, or not. Update,
> however, fails:
>
> Map s1 = new HashMap();
> s1.put("code", "001");
> s1.put("description", "Region Transaction upd");
> s1.put("fiscalYear", "2005");
> s1.put("id", "1501");
> client.update("updateSector", s1);
>
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in ca/cihi/cmdb/model/Sector.xml.
> --- The error occurred while applying a parameter map.
> --- Check the Sector.sector.
> --- Check the parameter mapping for the 'id' property.
> --- Cause: java.sql.SQLException: Invalid column index
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)
> at
> ca.cihi.cmdb.service.MaintenanceService.update(MaintenanceService.java:52)
> ... 29 more
> Caused by: java.sql.SQLException: Invalid column index
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
> at
> oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4754)
> at
> oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4717)
> 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 $Proxy2.setString(Unknown Source)
> at
> com.ibatis.sqlmap.engine.type.StringTypeHandler.setParameter(StringTypeHandler.java:30)
> at
> com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69)
> at
> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter(BasicParameterMap.java:165)
> at
> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters(BasicParameterMap.java:125)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:79)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
> ... 33 more
>
> Am I doing something wrong?
>
> Thanks,
>
> Alex
>
--
View this message in context: http://www.nabble.com/Trying-to-use-HashMap-as-a-param-object-for-an-UPDATE---getting-an-exception-tp20922966p20981745.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
RE: Trying to use HashMap as a param object for an UPDATE - getting
an exception
Posted by Alex Savitsky <AS...@cihi.ca>.
Thanks, it worked now, with the implicit maps.
-----Original Message-----
From: Michael He [mailto:hyxw5890@yahoo.com.cn]
Sent: Wednesday, December 10, 2008 9:54 AM
To: user-java@ibatis.apache.org
Subject: Re: Trying to use HashMap as a param object for an UPDATE - getting an exception
When using parameter maps, you must specify question marks(?) in the SQL instead of property names.
Recommand to pass in a map and use inline syntax instead
Alex Savitsky-2 wrote:
>
> Hi,
>
> I'm trying to execute an update, with a HashMap for parameter (there's
> no business object to pass). The mapping file is as follows (iBATIS
> v.2.3.0.677)
>
> <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC
> "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> <sqlMap namespace="Sector">
> <resultMap class="java.util.HashMap" id="sector">
> <result property="id" column="SECTOR_ID" javaType="java.lang.Integer" />
> <result property="code" column="SECTOR_CD" />
> <result property="description" column="SECTOR_DESC" />
> <result property="fiscalYear" column="FISCAL_YR"
> javaType="java.lang.Integer" />
> </resultMap>
> <parameterMap class="java.util.HashMap" id="sector">
> <parameter property="id" jdbcType="NUMERIC" />
> <parameter property="code" jdbcType="VARCHAR" />
> <parameter property="description" jdbcType="VARCHAR" />
> <parameter property="fiscalYear" jdbcType="NUMERIC" />
> </parameterMap> <select id="getSectorList" resultMap="sector"
> parameterMap="sector">
> SELECT * FROM SECTOR WHERE FISCAL_YR = #fiscalYear#
> <isNotEmpty property="code">AND SECTOR_CD LIKE #code#||'%'</isNotEmpty>
> <isNotEmpty property="description">AND SECTOR_DESC LIKE
> #description#||'%'</isNotEmpty> </select> <update id="updateSector"
> parameterMap="sector">
> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
> </update> </sqlMap>
>
> Both parameters and results are Maps, and the select works when I pass
> it a parameter map, whether it has all the properties, or not. Update,
> however, fails:
>
> Map s1 = new HashMap();
> s1.put("code", "001");
> s1.put("description", "Region Transaction upd"); s1.put("fiscalYear",
> "2005"); s1.put("id", "1501"); client.update("updateSector", s1);
>
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in ca/cihi/cmdb/model/Sector.xml.
> --- The error occurred while applying a parameter map.
> --- Check the Sector.sector.
> --- Check the parameter mapping for the 'id' property.
> --- Cause: java.sql.SQLException: Invalid column index
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)
> at
> ca.cihi.cmdb.service.MaintenanceService.update(MaintenanceService.java:52)
> ... 29 more
> Caused by: java.sql.SQLException: Invalid column index
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
> at
> oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4754)
> at
> oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4717)
> 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 $Proxy2.setString(Unknown Source)
> at
> com.ibatis.sqlmap.engine.type.StringTypeHandler.setParameter(StringTypeHandler.java:30)
> at
> com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69)
> at
> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter(BasicParameterMap.java:165)
> at
> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters(BasicParameterMap.java:125)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:79)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
> ... 33 more
>
> Am I doing something wrong?
>
> Thanks,
>
> Alex
>
--
View this message in context: http://www.nabble.com/Trying-to-use-HashMap-as-a-param-object-for-an-UPDATE---getting-an-exception-tp20922966p20936872.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Trying to use HashMap as a param object for an UPDATE - getting
an exception
Posted by Michael He <hy...@yahoo.com.cn>.
When using parameter maps, you must specify question marks(?) in the SQL
instead of property names.
Recommand to pass in a map and use inline syntax instead
Alex Savitsky-2 wrote:
>
> Hi,
>
> I'm trying to execute an update, with a HashMap for parameter (there's no
> business object to pass). The mapping file is as follows (iBATIS
> v.2.3.0.677)
>
> <?xml version="1.0" encoding="UTF-8" ?>
> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
> "http://ibatis.apache.org/dtd/sql-map-2.dtd">
> <sqlMap namespace="Sector">
> <resultMap class="java.util.HashMap" id="sector">
> <result property="id" column="SECTOR_ID" javaType="java.lang.Integer" />
> <result property="code" column="SECTOR_CD" />
> <result property="description" column="SECTOR_DESC" />
> <result property="fiscalYear" column="FISCAL_YR"
> javaType="java.lang.Integer" />
> </resultMap>
> <parameterMap class="java.util.HashMap" id="sector">
> <parameter property="id" jdbcType="NUMERIC" />
> <parameter property="code" jdbcType="VARCHAR" />
> <parameter property="description" jdbcType="VARCHAR" />
> <parameter property="fiscalYear" jdbcType="NUMERIC" />
> </parameterMap>
> <select id="getSectorList" resultMap="sector" parameterMap="sector">
> SELECT * FROM SECTOR WHERE FISCAL_YR = #fiscalYear#
> <isNotEmpty property="code">AND SECTOR_CD LIKE #code#||'%'</isNotEmpty>
> <isNotEmpty property="description">AND SECTOR_DESC LIKE
> #description#||'%'</isNotEmpty>
> </select>
> <update id="updateSector" parameterMap="sector">
> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
> </update>
> </sqlMap>
>
> Both parameters and results are Maps, and the select works when I pass it
> a parameter map, whether it has all the properties, or not. Update,
> however, fails:
>
> Map s1 = new HashMap();
> s1.put("code", "001");
> s1.put("description", "Region Transaction upd");
> s1.put("fiscalYear", "2005");
> s1.put("id", "1501");
> client.update("updateSector", s1);
>
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in ca/cihi/cmdb/model/Sector.xml.
> --- The error occurred while applying a parameter map.
> --- Check the Sector.sector.
> --- Check the parameter mapping for the 'id' property.
> --- Cause: java.sql.SQLException: Invalid column index
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)
> at
> ca.cihi.cmdb.service.MaintenanceService.update(MaintenanceService.java:52)
> ... 29 more
> Caused by: java.sql.SQLException: Invalid column index
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
> at
> oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4754)
> at
> oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4717)
> 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 $Proxy2.setString(Unknown Source)
> at
> com.ibatis.sqlmap.engine.type.StringTypeHandler.setParameter(StringTypeHandler.java:30)
> at
> com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69)
> at
> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter(BasicParameterMap.java:165)
> at
> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters(BasicParameterMap.java:125)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:79)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
> ... 33 more
>
> Am I doing something wrong?
>
> Thanks,
>
> Alex
>
--
View this message in context: http://www.nabble.com/Trying-to-use-HashMap-as-a-param-object-for-an-UPDATE---getting-an-exception-tp20922966p20936872.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
RE: Trying to use HashMap as a param object for an UPDATE - getting
an exception
Posted by Alex Savitsky <AS...@cihi.ca>.
No, it wasn't this, when I removed the extra parameters, the result was the same. I even went into the iBATIS initialization code with a debugger (something I always end up doing for such poorly documented frameworks), and discovered that, when there's a parameter map specified, the SQL is not even parsed for the ## parameters (the parsing is supposed to replace them with '?'s), and sent to DB as is - most likely a bug. Ended up using the implicit maps, like this:
<update id="updateSector" parameterClass="java.util.HashMap">
UPDATE SECTOR SET SECTOR_DESC = #description:VARCHAR# WHERE SECTOR_ID = #id:NUMERIC#
</update>
This way, the SQL gets parsed, and all ##s are properly replaced with '?'s
________________________________
From: luc [mailto:luc.mdk@gmail.com]
Sent: Tuesday, December 09, 2008 8:57 PM
To: user-java@ibatis.apache.org
Subject: Re: Trying to use HashMap as a param object for an UPDATE - getting an exception
I think your update sql statement has 2 questionmarks,but it try to set 4 parameters to fill those.
You can set the log level to DEBUG for javax.sql,then may be find more informations.
2008/12/10 Alex Savitsky <AS...@cihi.ca>
Hi,
I'm trying to execute an update, with a HashMap for parameter (there's no business object to pass). The mapping file is as follows (iBATIS v.2.3.0.677)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Sector">
<resultMap class="java.util.HashMap" id="sector">
<result property="id" column="SECTOR_ID" javaType="java.lang.Integer" />
<result property="code" column="SECTOR_CD" />
<result property="description" column="SECTOR_DESC" />
<result property="fiscalYear" column="FISCAL_YR" javaType="java.lang.Integer" />
</resultMap>
<parameterMap class="java.util.HashMap" id="sector">
<parameter property="id" jdbcType="NUMERIC" />
<parameter property="code" jdbcType="VARCHAR" />
<parameter property="description" jdbcType="VARCHAR" />
<parameter property="fiscalYear" jdbcType="NUMERIC" />
</parameterMap>
<select id="getSectorList" resultMap="sector" parameterMap="sector">
SELECT * FROM SECTOR WHERE FISCAL_YR = #fiscalYear#
<isNotEmpty property="code">AND SECTOR_CD LIKE #code#||'%'</isNotEmpty>
<isNotEmpty property="description">AND SECTOR_DESC LIKE #description#||'%'</isNotEmpty>
</select>
<update id="updateSector" parameterMap="sector">
UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
</update>
</sqlMap>
Both parameters and results are Maps, and the select works when I pass it a parameter map, whether it has all the properties, or not. Update, however, fails:
Map s1 = new HashMap();
s1.put("code", "001");
s1.put("description", "Region Transaction upd");
s1.put("fiscalYear", "2005");
s1.put("id", "1501");
client.update("updateSector", s1);
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in ca/cihi/cmdb/model/Sector.xml.
--- The error occurred while applying a parameter map.
--- Check the Sector.sector.
--- Check the parameter mapping for the 'id' property.
--- Cause: java.sql.SQLException: Invalid column index
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)
at ca.cihi.cmdb.service.MaintenanceService.update(MaintenanceService.java:52)
... 29 more
Caused by: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4754)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4717)
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 $Proxy2.setString(Unknown Source)
at com.ibatis.sqlmap.engine.type.StringTypeHandler.setParameter(StringTypeHandler.java:30)
at com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69)
at com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter(BasicParameterMap.java:165)
at com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters(BasicParameterMap.java:125)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:79)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
... 33 more
Am I doing something wrong?
Thanks,
Alex
Re: Trying to use HashMap as a param object for an UPDATE - getting an exception
Posted by luc <lu...@gmail.com>.
I think your update sql statement has 2 questionmarks,but it try to set 4
parameters to fill those.
You can set the log level to DEBUG for javax.sql,then may be find more
informations.
2008/12/10 Alex Savitsky <AS...@cihi.ca>
> Hi,
>
> I'm trying to execute an update, with a HashMap for parameter (there's no
> business object to pass). The mapping file is as follows (iBATIS
> v.2.3.0.677)
>
> <?xml version="1.0" encoding="UTF-8" ?>
> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "
> http://ibatis.apache.org/dtd/sql-map-2.dtd">
> <sqlMap namespace="Sector">
> <resultMap class="java.util.HashMap" id="sector">
> <result property="id" column="SECTOR_ID"
> javaType="java.lang.Integer" />
> <result property="code" column="SECTOR_CD" />
> <result property="description" column="SECTOR_DESC" />
> <result property="fiscalYear" column="FISCAL_YR"
> javaType="java.lang.Integer" />
> </resultMap>
> <parameterMap class="java.util.HashMap" id="sector">
> <parameter property="id" jdbcType="NUMERIC" />
> <parameter property="code" jdbcType="VARCHAR" />
> <parameter property="description" jdbcType="VARCHAR" />
> <parameter property="fiscalYear" jdbcType="NUMERIC" />
> </parameterMap>
> <select id="getSectorList" resultMap="sector" parameterMap="sector">
> SELECT * FROM SECTOR WHERE FISCAL_YR = #fiscalYear#
> <isNotEmpty property="code">AND SECTOR_CD LIKE
> #code#||'%'</isNotEmpty>
> <isNotEmpty property="description">AND SECTOR_DESC LIKE
> #description#||'%'</isNotEmpty>
> </select>
> <update id="updateSector" parameterMap="sector">
> UPDATE SECTOR SET SECTOR_DESC = #description# WHERE SECTOR_ID = #id#
> </update>
> </sqlMap>
>
> Both parameters and results are Maps, and the select works when I pass it a
> parameter map, whether it has all the properties, or not. Update, however,
> fails:
>
> Map s1 = new HashMap();
> s1.put("code", "001");
> s1.put("description", "Region Transaction upd");
> s1.put("fiscalYear", "2005");
> s1.put("id", "1501");
> client.update("updateSector", s1);
>
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in ca/cihi/cmdb/model/Sector.xml.
> --- The error occurred while applying a parameter map.
> --- Check the Sector.sector.
> --- Check the parameter mapping for the 'id' property.
> --- Cause: java.sql.SQLException: Invalid column index
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:91)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:505)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)
> at
> com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:67)
> at
> ca.cihi.cmdb.service.MaintenanceService.update(MaintenanceService.java:52)
> ... 29 more
> Caused by: java.sql.SQLException: Invalid column index
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
> at
> oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:4754)
> at
> oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:4717)
> 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 $Proxy2.setString(Unknown Source)
> at
> com.ibatis.sqlmap.engine.type.StringTypeHandler.setParameter(StringTypeHandler.java:30)
> at
> com.ibatis.sqlmap.engine.type.UnknownTypeHandler.setParameter(UnknownTypeHandler.java:69)
> at
> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameter(BasicParameterMap.java:165)
> at
> com.ibatis.sqlmap.engine.mapping.parameter.BasicParameterMap.setParameters(BasicParameterMap.java:125)
> at
> com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:79)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteUpdate(GeneralStatement.java:200)
> at
> com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeUpdate(GeneralStatement.java:78)
> ... 33 more
>
> Am I doing something wrong?
>
> Thanks,
>
> Alex