Oracle SQL DATE conversion problem using iBATIS via Java JDBC

Question

I'm currently wrestling with an Oracle sql DATE conversion problem using iBATIS from Java.

Am using the Oracle JDBC thin driver ojdbc14 version 10.2.0.4.0. iBATIS version 2.3.2. Java 1.6.0_10-rc2-b32.

The problem revolves around a column of DATE type that is being returned by this snippet of SQL:

SELECT * FROM TABLE(pk_invoice_qry.get_contract_rate(?,?,?,?,?,?,?,?,?,?)) order by from_date

The package procedure call returns a ref cursor that is being wrapped in a TABLE to where is then easy to read the result set as though were a select query against a table.

In PL/SQL Developer, one of the columns returned, FROM_DATE, of SQL DATE type, has precision to time of day:

Tue Dec 16 23:59:00 PST 2008

But when I access this via iBATIS and JDBC, the value only retains precision to day:

Tue Dec 16 12:00:00 AM PST 2008

This is clearer when displayed like so:

Should have been:

1229500740000 milliseconds since epoch
Tuesday, December 16, 2008 11:59:00 PM PST

But getting this instead:

1229414400000 milliseconds since epoch
Tuesday, December 16, 2008 12:00:00 AM PST
(as instance of class java.sql.Date)

No matter what I try, I am unable to expose the full precision of this DATE column to be returned via Java JDBC and iBATIS.

What iBATIS is mapping from is this:

FROM_DATE : 2008-12-03 : class java.sql.Date

The current iBATIS mapping is this:

<result property="from_date" jdbcType="DATE" javaType="java.sql.Date"/>

I've also tried:

<result property="from_date" jdbcType="DATETIME" javaType="java.sql.Date"/>

or

<result property="from_date" jdbcType="TIMESTAMP" javaType="java.sql.Timestamp"/>

But all attempted mappings yield the same truncated Date value. It's as though JDBC has already done the damage of loosing data precision before iBATIS even touches it.

Clearly I'm loosing some of my data precision by going through JDBC and iBATIS that is not happening when I stay in PL/SQL Developer running the same SQL snippet as a test script. Not acceptable at all, very frustrating, and ultimately very scary.

Answer 1

The problem is the use of java.sql.Date. According to the Javadoc, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated, to conform with the definition of SQL DATE.

Answer 2

Yes, I see - the plain SQL DATE standard must be to only store to day resolution. Indeed, here is a snippet on Oracle's DATE type:

Oracle supports both date and time, albeit differently from the SQL2 standard. Rather than using two separate entities, date and time, Oracle only uses one, DATE. The DATE type is stored in a special internal format that includes not just the month, day, and year, but also the hour, minute, and second.

Which makes the point that Oracle's DATE exceeds standard SQL DATE.

Hmm, Oracle PL/SQL folks use DATE extensively to hold values where they depend on the resolution being to the second. Looks like iBATIS needs something like the Hibernate sql dialect concept where instead of interpreting DATE via java.sql.Date, could override and instead interpret via java.util.Date, which Javadocs defines as permitting millisecond resolution.

Unfortunately when I've changed the mapping to something like:

<result property="from_date" jdbcType="DATE" javaType="java.util.Date"/>

or

<result property="from_date" jdbcType="DATETIME" javaType="java.util.Date"/>

It's still seemingly first translated the SQL DATE to a java.sql.Date and lost the time of day precision.

Answer 3

I found out how to solve this problem. iBATIS permits custom type handlers to be registered. So in my sqlmap-config.xml file I added this:

<typeAlias alias="OracleDateHandler" type="com.tideworks.ms.CustomDateHandler"/>
<typeHandler callback="OracleDateHandler" jdbcType="DATETIME" javaType="date"/>

And then added this class which implements the iBATIS TypeHandlerCallback interface:

// corrected getResult()/setParameter() to correctly deal with when value is null
public class CustomDateHandler implements TypeHandlerCallback {
    @Override
    public Object getResult(ResultGetter getter) throws SQLException {
        final Object obj = getter.getTimestamp();
        return obj != null ? (Date) obj : null;
    }

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

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

Whennever I need to map an Oracle DATE I now describe it like so:

<result property="from_date" jdbcType="DATETIME" javaType="date"/>

Answer 4

The full info (and it's more complex than described here and might depend upon which particular version of the Oracle drivers are in use) is in Richard Yee's answer here - http://www.nabble.com/Re%3A-Oracle-SQL-DATE-conversion-problem-using-iBATIS-via-Java-JDBC-p21112960.html

Answer 5

I have solved my problem using jdbcType="TIMESTAMP" instead of jdbcType="DATE"

• PROBLEM:

•SOLVED:

Regards.

Pedro

Answer 6

The problem is with the Oracle Driver.

The best solution I found was to change all jdbcType="DATE" to jdbcType="TIMESTAMP" and all #column_name:DATE# to #column_name:TIMESTAMP#

So change:

<result property="from_date" jdbcType="DATE" javaType="java.sql.Date"/>

to

<result property="from_date" jdbcType="TIMESTAMP" javaType="java.sql.Date"/>

Answer 7

Richard Yee mentions that Oracle's latest drivers fix the problem. I can confirm that. Had the same problem here with 10.2 drivers, upgraded today to ojdbc5.jar (11.2.0.1.0), and the problem is gone now.