Use an implicit TypeHandler based on resultType for select in MyBatis

Question

I am trying to select a timestamp in MyBatis and return it as a LocalDateTime (from joda-time).

My configuration works fine if I try to return the result as a java.sql.Timestamp. I proved my type handler works fine: if I use a wrapping class with a LocalDateTime as only field and a resultMap in the MyBatis mapper file, I get the correct results.

However, when I try specifying the org.joda.time.LocalDateTime as resultType for this select, I always get null, as if the type handler is ignored.

It is my understanding that MyBatis uses a default typeHandler in the case I have resultType="java.sql.Timestamp". As a consequence, I expected it to use one of the typeHandlers I configured when meeting resultType="org.joda.time.LocalDateTime".

Did I miss something? Is there a way to make use of my typeHandler or am I forced to make a wrapper class and resultMap? This is my fallback-to solution but I would like to avoid it if possible.

Any help appreciated. Thank you.

mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <typeHandlers>
        <typeHandler javaType="org.joda.time.LocalDate" jdbcType="DATE" handler="...LocalDateTypeHandler"/>
        <typeHandler javaType="org.joda.time.LocalDateTime" jdbcType="TIMESTAMP" handler="...LocalDateTimeTypeHandler"/>
    </typeHandlers>
</configuration>

NotifMailDao.java

import org.joda.time.LocalDateTime;

public interface NotifMailDao {

    LocalDateTime getLastNotifTime(String userId);
}

NotifMailDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="lu.bgl.notif.mail.dao.NotifMailDao">

    <select id="getLastNotifTime" resultType="org.joda.time.LocalDateTime">
        SELECT current_timestamp
        AS last_time
        FROM DUAL
    </select>
</mapper>

Answer 1

To use the TypeHandler configuration, MyBatis needs to know both the Java type of the resulting object and the SQL type of the source column.

Here we use a resultType in the <select /> so MyBatis knows the Java type, but it cannot know the SQL type if we do not set it. The only way is to use a <resultMap />.

The solution

You need to create a Bean with a single field containing the object you want to return (let us call this field time) and use a <resultMap />:

<select id="getLastNotifTime" resultMap="notifMailResultMap">

---

<resultMap id="mapLastTime" type="MyWrapperBean">
    <result property="time" column="my_sql_timestamp" javaType="org.joda.time.LocalDateTime"
        jdbcType="TIMESTAMP" />
</resultMap>

If you wish to spare the creation of dedicated bean, you can also use the attribute type=hashmap on your <resultMap /> as suggested by Shobit.

Variant: set the property on the LocalDateTime

A solution has been proposed on Google Groups, which sets directly the information on the LocalDateTime.

My understanding of it (please comment if I am wrong) is that it sets a property of the LocalDateTime. I will not vouch for it as I do not find the corresponding in the API doc (and I have not tested it) but feel free to use it if you deem it better.

<resultMap id="mapLastTime" type="org.joda.time.LocalDateTime">
    <result property="lastTime" column="my_sql_timestamp" javaType="org.joda.time.LocalDateTime"
        jdbcType="TIMESTAMP" />
</resultMap>

Why it works with java.sql.Timestamp

Timestamp is a standard Java type for SQL, with a default JDBC implementation (ResultSet.getTimestamp(int/String)). The default handler for MyBatis uses this getter¹ and therefore does not need any TypeHandler mapping. I expect this occurs every time you use one of the default handlers.

---

_{1: This is a hunch. Citation required!}

This answer only awaits to be replaced with something better. Please contribute!