iBatis SQL Mapper Date Argument

Question

I'm just new to Spring and I'm a little bit confuse with iBatis. I was given a task to edit a select query condition (pretty simple) but things get tricky. I only pass java.sql.Date object (with setter and getter) to the SQLMapper to provide the condition parameter. This is what my WHERE clause looks like

<sql id="dateWhere">
    <where>
       <if test="arg.sqlStartDate != null and arg.sqlEndDate != null">
          table.date BETWEEN #{arg.sqlStartDate} AND #{arg.sqlEndDate}
       </if>
    </where>
</sql>

But I get an error

org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='arg.sqlStartDate', mode=IN, javaType=class java.lang.Object, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException

And I researched that I need to specify the jdbcType of the argument. SOlink. Also added a DATE() function to further specify that the argument is a DATE variable.

 <sql id="dateWhere">
        <where>
           <if test="arg.sqlStartDate != null and arg.sqlEndDate != null">
              table.date BETWEEN DATE(#{arg.sqlStartDate,jdbcType=DATE}) AND DATE(#{arg.sqlEndDate,jdbcType=DATE})
           </if>
        </where>
  </sql>

And I got it working. But I notice with other Mapper they didn't specify the jdbcType of each argument. So I got confuse on what really is this problem and what cause it, how this happens, what I have done wrong. Just give me information to further understand this matter. Thanks

Answer 1

You don't need to use DATE() function. Use java.util.Date instead of java.sql.Date and table.date BETWEEN #{arg.sqlStartDate} AND #{arg.sqlEndDate} should work.

Apparently iBatis can better handle java.util.Date than java.sql.Date