Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: error (idunn.apache.osuosl.org: domain agilemovement.it from
 62.241.4.129 cause and error)
Message-ID: <451A89DC.3020703@agilemovement.it>
Date: Wed, 27 Sep 2006 16:25:32 +0200
From: aris <aris@agilemovement.it>
User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923)
MIME-Version: 1.0
To: user-java@ibatis.apache.org
Subject: Re: Custom Type Handler for Oracle DATE
References: <4518E1CA.8090405@agilemovement.it>
 <c593b8750609260520o178c94bn2e95418b2883ae5@mail.gmail.com>
In-Reply-To: <c593b8750609260520o178c94bn2e95418b2883ae5@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Thank you Jeff for your reply, I'll try Abator 1.0 but probably I 
haven't well explained the problem.

I like the way Abator generates xml mappings and domain objects and I 
think it's right to set the oracle Date as (jdbc) TIMESTAMP (if set to 
(jdbc) DATE then hours, minutes and seconds are lost).

The problem is related to this specific context:
- Oracle 9
- Oracle jdbc driver 9 or superior
- Field of type DATE (oracle) with an index on it

Oracle 9 introduces the new data type (oracle) TIMESTAMP.

I'm using Oracle 9 and I've made this test. I've created following table 
and indexes and I've fill in the table with 500.000 records:
================================================================
create table TABLE_TEST (
     COL_DATE       	DATE DEFAULT sysdate NULL,
     COL_TIMESTAMP  	TIMESTAMP(6) DEFAULT systimestamp NULL,
     COL_WASTE_SPACE	VARCHAR2(1000) NULL
     );

create index IX_TABLE_TEST_COL_DATE
     on TABLE_TEST(COL_DATE);

create index IX_TABLE_TEST_COL_TIMESTAMP
     on TABLE_TEST(COL_TIMESTAMP);
================================================================

Then I test queries as follow (sql statement logged with 
net.rkbloom.logdriver):

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH.mm");
String str_date = "2006-09-11 13.00";
java.util.Date date = sdf.parse(sDate);

======================= TEST 1 =======================
- sql statement:
   "select count(*) as CNT from TABLE_TEST where COL_DATE >= ?" with 
bind parameters: {1=oracle.sql.DATE@1865b28}

- prepared statement parameters:
   pst.setObject(1, new oracle.sql.DATE(new Timestamp(date.getTime())));

- result:
   CNT: 88072 in 31 msec (optimizer uses index on COL_DATE)

======================= TEST 2 =======================
- sql statement:
   "select count(*) as CNT from TABLE_TEST where COL_DATE >= to_date(?, 
'yyyy-mm-dd hh24.mi')" with bind parameters: {1=2006-09-11 13.00}

- prepared statement parameters:
    pst.setString(1, str_date);

- result:
   CNT: 88072 in 31 msec (optimizer uses index on COL_DATE)

======================= TEST 3 =======================
- sql statement:
   "select count(*) as CNT from TABLE_TEST where COL_DATE >= ?" with 
bind parameters: {1=2006-09-11 13:00:00.0}

- prepared statement parameters:
   pst.setTimestamp(1, new Timestamp(date.getTime()));

- result:
   CNT: 88072 in 234 msec (optimizer doesn't use index on COL_DATE so a 
full table scan is performed)

======================= TEST 4 =======================
- sql statement:
"select count(*) as CNT from TABLE_TEST where COL_TIMESTAMP >= ?" with 
bind parameters: {1=2006-09-11 13:00:00.0}

- prepared statement parameters:
   pst.setTimestamp(1, new Timestamp(date.getTime()));

- result:
   CNT: 88072 in 31 msec (optimizer uses index on COL_TIMESTAMP)

======================= TEST 5 =======================
- sql statement:
   "select count(*) as CNT from TABLE_TEST where COL_TIMESTAMP >= 
to_timestamp(?, 'yyyy-mm-dd hh24.mi')" with bind parameters: 
{1=2006-09-11 13.00}

- prepared statement parameters:
   pst.setString(1, str_date);

- result:
   CNT: 88072 in 31 msec (optimizer uses index on COL_TIMESTAMP)

My conclusion:
TIMESTAMP has no problem with prepared statement but for backward 
compatibility with Oracle 8 my db schema has to use DATE.
If you don't want to lose hours, minute and seconds then you cannot use 
the method setDate() of a prepared statement but you have to use the 
setTimestamp().
With Oracle jdbc driver version 9 or superior using setTimestamp() 
"confuses" the optimizer that generates a query that can't use index.
TEST1 and TEST2 are the only ways I've found to force the use of the 
index (a third choice can be Oracle hints but I don't like them).

This is the query in my SqlMapConfig:
<select
     id="selectCountByExample"
     resultClass="java.math.BigDecimal"
     parameterClass="java.util.Map">

   select count(*) from TABLE_TEST
	<include refid="abatorgenerated_Example_Where_Clause"/>
</select>

<sql id="abatorgenerated_Example_Where_Clause">
  <dynamic prepend="where">
   <isPropertyAvailable prepend="and" property="AND_COL_DATE_GE">
     COL_DATE <![CDATA[ >= ]]> #colDate#
   </isPropertyAvailable>
   ....
</sql>

Ibatis uses the type of each parameters in the parameterClass to handle 
it. A parameter of type java.util.Date is added to the prepared 
statement with a setTimestamp(). In a context like the one stated above 
this produce a very slow query. So I've thought that use a custom type 
handler (to implement a solution like in TEST 1) was the best compromise 
between portability and maintanability.

Sorry for this very long post but I hope the context is more clear now.

Best regards,
aris


Jeff Butler wrote:
> 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;
>>    }
>>
>> }
>>
>>
>>
>>
>