Using Ibatis Typehandlers to fix Oracle Date “missing time”

Friday, June 25th, 2010

The Problem

When using Ibatis to retrieve Date type values, on Oracle 10g (some driver versions), the time portion of the value returned from the database is not mapped onto the Java object.

i.e.

In DB: 02-JAN-10 13:30:00
In Java Object after mapping: 02-JAN-10 00:00:00

The Ibatis ResultMapper, snippet, looks like this:

....
<result property="savedDate" column="SAVED_DATE" javaType="java.util.Date" jdbcType="DATE"/>
....

The Solution

1. Implement our old friend the Ibatis TypeHandlerCallback.

package foo.bar;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;

public class CustomDateHandler implements TypeHandlerCallback {
    
    public Object getResult(ResultGetter getter) throws SQLException {
        final Object obj = getter.getTimestamp();
        return obj != null ? (Date) obj : null;
    }

    public void setParameter(ParameterSetter setter,Object value) throws SQLException {
        setter.setTimestamp(value != null ? new Timestamp(((Date)value).getTime()) : null);
    }

    public Object valueOf(String datetime) {
        return Timestamp.valueOf(datetime);
    }
}

2. Add Typehandlers and TypeAliases to Ibatis config

<typeAlias alias="OracleDateHandler" type="foo.bar.CustomDateHandler"/>
<typeHandler callback="OracleDateHandler" jdbcType="DATETIME" javaType="date"/>

3. Update resultMap to use new “type”

....
<result property="savedDate" column="SAVED_DATE" javaType="java.util.Date" jdbcType="DATETIME"/>
....

Important Note!

This solution deals with issues caused by certain versions of the Oracle JDBC drivers. I believe that the 11g drivers fix this issue. As ever YMMV…..

You can leave a response, or trackback from your own site.

Tags: , ,
Posted in: Examples



2 Responses to “Using Ibatis Typehandlers to fix Oracle Date “missing time””

  1. Jeff Says:

    November 25th, 2010 at 3:36 pm

    Dont’t need to be so complex. You just need to remove the jdbcType=”DATE”, or to change to jdbcType=”date”. Then everything goes well.

    I have done a test here:
    https://github.com/Jeffliu/misc/tree/master/orcl_date_test/

  2. lawrence Says:

    November 25th, 2010 at 5:04 pm

    Thanks for the feedback – Ive not had time to test your solution but I trust you 🙂

    Like I said I think the issue I was addressing was related to the version of the driver but I couldnt nail that down

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>