ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: Custom Type Handler for Oracle DATE
Date Tue, 26 Sep 2006 12:20:36 GMT
Here are a couple of thoughts:

 1. Oracle's DATE type can be DATE, TIME. or TIMESTAMP depending on how it's
configured.  But I think their JDBC driver always reports it as TIMESTAMP -
so that's why Abator generated it as TIMESTAMP.  If it really is a DATE then
use a <columnOverride> to force the field to jdbcType DATE.

2. Upgrade to Abator 1.0 and regenerate your objects.  I made a change in
the select by example clauses regarding DATE/TIME/TIMESTAMP that also may
fix the probem.

I don't think you need to resort to a custom type handler for this.

Jeff Butler


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;
>    }
>
> }
>
>
>
>

Mime
View raw message