Dealing with nulls when selecting in MyBatis

Question

I have a query which at first launch returns zero rows:

<select id="getParams" parameterType="map" resultMap="ParamsResultMap">
    SELECT ID, GROUPS, TEMPLATES, DATE_FROM, DATE_TO
    FROM MY_FILTERS 
    WHERE ID = #{id}
</select>

<resultMap id="ParamsResultMap" type="my.domain.ParamsVO">
    <result column="ID" property="id"/>
    <result column="GROUPS" property="groups"/>
    <result column="TEMPLATES" property="templates"/>
    <result column="DATE_FROM" property="dateFrom" jdbcType="TIMESTAMP"/>
    <result column="DATE_TO" property="dateTo" jdbcType="TIMESTAMP"/>
</resultMap>

It is ok as I want to manage it in java:

ParamsVO params = myRepository.getParams(id);
if (params == null) {
    ...
}

But I am getting exception:

org.springframework.jdbc.UncategorizedSQLException: Error setting null for parameter #4 with JdbcType OTHER . 
Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. 
Cause: java.sql.SQLException: Invalid column type ; uncategorized SQLException for SQL []; 
SQL state [null]; error code [17004]; 

I've tried with jdbcType = "DATE" and without jdbcType with similar results.

My question is which is correct way to deal with queries which returns zero rows at MyBatis?

Answer 1

Try this way,

  <result column="DATE_TO" property="dateTo" jdbcType="DATE" javaType="java.util.Date"/>

And also need to check and update your JDBC jar file.Check the compatibility of ojdbc.jar here (I think you should need at least ojdbc6.jar) . For additional checking, If you have declared in your mybatis configuration xml file like this

<settings>
     <setting name="jdbcTypeForNull" value="OTHER" />
</settings>

then change like that

<settings>
    <setting name="jdbcTypeForNull" value="NULL" />
</settings>