On 9/26/06, aris <aris@agilemovement.it> wrote:

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,
aris.

package com.yourpackage;

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

import oracle.sql.DATE;

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

public class OracleDateTypeHandler implements TypeHandlerCallback {

   public void setParameter(ParameterSetter setter, Object parameter) throws SQLException {
       if (parameter == null) {
           setter.setNull (oracle.jdbc.OracleTypes.DATE);
       } else {
           Date date = (Date) parameter;
           DATE oracleDate = new DATE(new Timestamp(date.getTime()));
           setter.setObject(oracleDate);
       }
   }

   public Object getResult(ResultGetter getter) throws SQLException {
       Timestamp value = getter.getTimestamp();
       if (getter.wasNull()) {
           return null;
       }
       return new Date( value.getTime());

   }

   public Object valueOf(String s) {
       return s;
   }

}