Perhaps you should use TIMESTAMP instead of DATE.
-R
Roger Voss wrote:
I posted following question/problem on stackoverflow, so if anyone
knows a resolution, would be good to see it answered there:
Oracle SQL DATE conversion problem using iBATIS via Java JDBC
<http://stackoverflow.com/questions/383783/oracle-sql-date-conversion-problem-using-ibatis-via-java-jdbc>
Here's the problem description:
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.
