ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From aris <a...@agilemovement.it>
Subject Custom Type Handler for Oracle DATE
Date Tue, 26 Sep 2006 08:16:10 GMT
Hi all,
I'm new to this mailing list and to Ibatis too.

Today I wrote my first type handler callback class to solve a problem 
with the Oracle jdbc driver (from version 9).

I've a table with a column of type DATE and an index is defined on this 
column. The table is mapped from a domain object with the related  field 
of type java.util.Date.

The portion of resultMap for the field is:
<result column="COLUMN_DATE" property="columnDate" jdbcType="TIMESTAMP"/>

The domain object and the resultMap have been generated with Abator for 
Eclipse 0.6.5.

I search on that table using a selectByExample. Using the field 
columnDate as parameter in the where clause the execution of the query 
became slower beacause the index wasn't used.

This is the problem: IBatis adds a parameter of type a java.util.Date on 
a prepared statement with a setTimestamp(...). This is correct because a 
setDate(...) truncates information about hours, minutes and seconds. But 
when a setTimestamp is used the query optimizer generates a query that 
doesn't use the index and perform a full table scan.

Solution 1: write a query using the to_date function and change the type 
of the field to String;

Solution 2: wrap the value of the field in a oracle.sql.DATE and prepare 
the statement using the method setObject(...).

Rewrite the query with the to_date implied to change the Abator 
generated code (the dynamic sql for the where clause) so I tried to 
implement the 2nd solution.

I wrote a custom type handler to convert a java.util.Date in an 
oracle.sql.DATE and back. I've tested it and now the query uses the 
index but I'd like a hint about my implementation from other users with 
more experience.

I appreciate any comments.

Best regards,

View raw message