Tuesday, March 24, 2009

iBATIS Parameter Maps (a.k.a my iBATIS brain f*rt)

Problem:
On a project using iBATIS, I was receiving the following stacktrace:

stacktrace
org.springframework.dao.TransientDataAccessResourceException: SqlMapClient operation; SQL [];  
--- The error occurred in {myproject}/PlanningProject.xml. 
--- The error occurred while applying a parameter map. 
--- Check the {myparametermap}  
--- Check the parameter mapping for the 'startDate' property.  

--- Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in com/kpe/resourcePlanning/domain/dao/ibatis/PlanningProject.xml. 
--- The error occurred while applying a parameter map. 
--- Check the {myparametermap}  
--- Check the parameter mapping for the 'startDate' property.  
--- Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).


And this is the snippet of the parameter map that is significant:

PlanningProject.xml
  <parameterMap id="updatePmap" class="PlanningProject">
    <parameter property="startDate" jdbcType="TIMESTAMP" javaType="org.joda.time.LocalDate" typeHandler="com.kpe.resourcePlanning.domain.dao.ibatis.TypeHandlerCallbackLocalDate" />
    <parameter property="endDateConstraint" jdbcType="TIMESTAMP" javaType="org.joda.time.LocalDate" typeHandler="com.kpe.resourcePlanning.domain.dao.ibatis.TypeHandlerCallbackLocalDate" />     
    <parameter property="modifiedBy" jdbcType="VARCHAR" />
    <parameter property="id" jdbcType="NUMBER" />
  </parameterMap>

  <update id="update" parameterMap="updatePmap">
    UPDATE resourcePlanning.planningproject
    SET 
        startDate = #startDate#,
        endDateConstraint = #endDateConstraint#,
        modifiedBy = #modifiedBy#,
        modifiedDate = now() 
    WHERE 
        projectId = #id#
  </update>



Well, my project hardly ever used explicit parameter maps. Usually, statements used the parameterClass="" syntax. So, as you might already know, my syntax was terribly wrong. The correct syntax follows. The thing to note (at least for me) is that the order of the individual SET parameters must match the order given in the parameter map that you use.



  <parameterMap id="updatePmap" class="PlanningProject">
    <parameter property="startDate" jdbcType="TIMESTAMP" javaType="org.joda.time.LocalDate" typeHandler="com.kpe.resourcePlanning.domain.dao.ibatis.TypeHandlerCallbackLocalDate" />
    <parameter property="endDateConstraint" jdbcType="TIMESTAMP" javaType="org.joda.time.LocalDate" typeHandler="com.kpe.resourcePlanning.domain.dao.ibatis.TypeHandlerCallbackLocalDate" />     
    <parameter property="modifiedBy" jdbcType="VARCHAR" />
    <parameter property="id" jdbcType="NUMBER" />
  </parameterMap>

  <update id="update" parameterMap="updatePmap">
    UPDATE resourcePlanning.planningproject
    SET 
        startDate = ?,
        endDateConstraint = ?,
        modifiedBy = ?,
        modifiedDate = now() 
    WHERE 
        projectId = ?
  </update>

3 comments:

Ricochet_New said...

He i resolve the same error changing the field type in my data base to timestamp and in my persistent class the parameters are date

Richard L Wellman said...

Hmm, not sure exactly what your point is as my post was not trying to resolve "typing issues". I am *purposely* not using java.util.date in my class, in favor of Joda classes (hence the need for the typehandler and parameter map). But thanks for the comment.

nartrab said...

Thanks! I have exactly the same problem: "my project hardly ever used explicit parameter maps".

Nice tip!