Could not set parameters for mapping ParameterMapping{property, modeIN, javaTypeclass java.util.Date

Question

error meassage:

nested exception is org.apache.ibatis.type.TypeException: Could not set >parameters for mapping: ParameterMapping{property='signIn', mode=IN, >javaType=class java.util.Date, jdbcType=null, numericScale=null, >resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #4 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (4 > number of parameters, which is 3).

xml

    <select id="selectSignRecordList" parameterType="SignRecord" resultMap="SignRecordResult">
        <include refid="selectSignRecordVo"/>
        <where>  
            <if test="id != null  and id != '' "> and id_ = #{id}</if>
             <if test="promiseId != null  and promiseId != '' "> and promise_id = #{promiseId}</if>
             <if test="userId != null  and userId != '' "> and user_id = #{userId}</if>
             <if test="signIn != null">
              and sign_in =#{signIn,jdbcType=DATE}
                 --and sign_in  = date_format(  #{signIn}  , '%Y-%m-%d' )
--                  AND date_format(sign_in,'%y%m%d') = date_format(#{signIn},'%y%m%d')
--                  <![CDATA[   and DATE_FORMAT(sign_in, '%Y-%m-%d')=  DATE_FORMAT(#{signIn}, '%Y-%m-%d')   ]]>
--                  AND date(sign_in) = date(#{signIn,jdbcType=DATE})
              </if>
             <if test="signStatus != null  and signStatus != '' "> and sign_status = #{signStatus}</if>
             <if test="createBy != null  and createBy != '' "> and create_by = #{createBy}</if>
             <if test="createTime != null "> and create_time = #{createTime}</if>
             <if test="updateBy != null  and updateBy != '' "> and update_by = #{updateBy}</if>
             <if test="updateTime != null "> and update_time = #{updateTime}</if>
             <if test="remark != null  and remark != '' "> and remark = #{remark}</if>
         </where>
    </select>

signIn is java.util.Date

When I pass in the parameter error

Roman Konoval Roman Konoval · Accepted Answer · 2018-12-26T10:09:05

Most probably the problem is that -- is a comment start in SQL but mybatis is not aware of that. This (and all similar) line:

--           AND date(sign_in) = date(#{signIn,jdbcType=DATE})

causes a problem because mybatis tries to set the parameter in the prepared statement here but your RDBMS ignores this piece of the query so this parameter does not exist for it. Hence the error message that actual number of the parameters (known to DB) is smaller than the client (mybatis) tries to set.

To fix the problem use xml comments instead of SQL comments like this:

    <select id="selectSignRecordList" parameterType="SignRecord" resultMap="SignRecordResult">
    <include refid="selectSignRecordVo"/>
    <where>  
        <if test="id != null  and id != '' "> and id_ = #{id}</if>
         <if test="promiseId != null  and promiseId != '' "> and promise_id = #{promiseId}</if>
         <if test="userId != null  and userId != '' "> and user_id = #{userId}</if>
         <if test="signIn != null">
          and sign_in =#{signIn,jdbcType=DATE}
             <!-- and sign_in  = date_format(  #{signIn}  , '%Y-%m-%d' )
              AND date_format(sign_in,'%y%m%d') = date_format(#{signIn},'%y%m%d')
              <![CDATA[   and DATE_FORMAT(sign_in, '%Y-%m-%d')=  DATE_FORMAT(#{signIn}, '%Y-%m-%d')   ]]>
              AND date(sign_in) = date(#{signIn,jdbcType=DATE})
             --> 
          </if>
         <if test="signStatus != null  and signStatus != '' "> and sign_status = #{signStatus}</if>
         <if test="createBy != null  and createBy != '' "> and create_by = #{createBy}</if>
         <if test="createTime != null "> and create_time = #{createTime}</if>
         <if test="updateBy != null  and updateBy != '' "> and update_by = #{updateBy}</if>
         <if test="updateTime != null "> and update_time = #{updateTime}</if>
         <if test="remark != null  and remark != '' "> and remark = #{remark}</if>
     </where>
</select>

In this case mybatis will not include the commented piece into the SQL query at all and will not try to set the parameters.

Could not set parameters for mapping ParameterMapping{property, modeIN, javaTypeclass java.util.Date

1 Answers