使用iBATIS通过Java JDBC的Oracle SQL DATE转换问题 [英] Oracle SQL DATE conversion problem using iBATIS via Java JDBC
问题描述
我正在使用Java中的iBATIS来摔倒Oracle SQL DATE转换问题。
使用Oracle JDBC瘦驱动程序ojdbc14版本10.2.0.4.0。 iBATIS版本2.3.2。 Java 1.6.0_10-rc2-b32。
该问题围绕着这段SQL代码返回的DATE类型列：
'pre>
SELECT *
从TABLE（pk_invoice_qry.get_contract_rate（？，？，？，？，？，？，？，？，？，？） ）order by from_date
包过程调用返回正在包装在TABLE中的ref游标到然后很容易读取结果集，就像是对表的select查询。
在PL / SQL Developer中，SQL_DATE中返回的列之一FROM_DATE类型，具有精确到一天的时间：
Tue Dec 16 23:59:00 PST 2008
Tue Dec 16 12:00:00 AM PST 2008
当这样显示时，这是更清楚的：
应该是：
code> 1229500740000 milliseconds si nce epoch
2008年12月16日星期二11:59:00 PM PST
但是得到这代替：
从历元起$ 1229414400000毫秒
2008年12月16日星期二12:00:00 AM PST
（作为类java.sql.Date的实例）
无论我尝试什么，我无法透露通过Java JDBC和iBATIS返回的DATE列的完整精度。
iBATIS的映射方式是：
FROM_DATE：2008-12-03：class java.sql.Date
当前iBATIS的映射是这样的：
<导致财产= FROM_DATE jdbcType为DATEjavaType =java.sql.Date/>
我也试过：
< result property =from_datejdbcType =DATETIMEjavaType =java.sql.Date/>
或
< result property =from_datejdbcType =TIMESTAMPjavaType =java.sql.Timestamp/>
但所有尝试的映射都产生相同的截断日期值。在iBATIS甚至触及之前，JDBC已经损失了丢失数据精度的损失。
显然，我通过JDBC和iBATIS失去了一些数据精度当我在PL / SQL Developer中运行与测试脚本相同的SQL代码片段时，并没有发生这种情况。根本不可接受的，非常令人沮丧，最终很可怕的。
在充分信息（比这里描述它更复杂，可能取决于哪个特定版本的Oracle驱动程序正在使用）在Richard Yee的答案 - [现在过期链接到Nabble]
在从nabble过期之前快速抓取...
Roger，
请参阅： http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281 .html＃08_01
具体来说：
简单数据类型
DATE和TIMESTAMP发生了什么？
本节介绍简单的数据类型。 ： - ）
在9.2之前，Oracle JDBC驱动程序将DATE SQL类型映射到java.sql.Timestamp。这样做有一定的意义，因为Oracle DATE SQL类型包含日期和时间信息，与java.sql.Timestamp一样。对java.sql.Date的更明显的映射有些问题，因为java.sql.Date不包含时间信息。它也是该RDBMS不支持的时间戳SQL型的情况下，所以不存在与映射DATE到时间戳没有问题。
在9.2 TIMESTAMP支持加入该RDBMS。 DATE和TIMESTAMP之间的区别是TIMESTAMP包括纳秒，DATE不包括。所以，从9.2开始，DATE映射到Date，TIMESTAMP被映射到Timestamp。不幸的是，如果您依赖于DATE值来包含时间信息，则会出现问题。
有几种方法可以解决这个问题：
更改表以使用TIMESTAMP而不是DATE。这很可能很少，但它是最好的解决方案。
更改您的应用程序以使用defineColumnType将列定义为TIMESTAMP而不是DATE。有这样的问题，因为你真的不想使用defineColumnType，除非你必须（见什么是defineColumnType，什么时候应该使用它）。
改变你应用程序使用getTimestamp而不是getObject。这是一个很好的解决方案，尽管许多应用程序包含依赖于getObject的通用代码，所以并不总是可能的。
设置V8Compatible连接属性。这告诉JDBC驱动程序使用旧的映射，而不是新映射。您可以将此标志设置为连接属性或系统属性。通过将连接属性添加到传递给DriverManager.getConnection或OracleDataSource.setConnectionProperties的java.util.Properties对象中来设置连接属性。您可以在java命令行中包含-D选项来设置系统属性。
java -Doracle.jdbc.V8Compatible =trueMyApp
Oracle JDBC 11.1修复了这个问题。从此版本开始，驱动程序默认将SQL DATE列映射到java.sql.Timestamp。没有必要设置V8Compatible来获取正确的映射。 V8Compatible被强烈弃用。你根本不应该使用它。如果你把它设置为true，它不会伤害任何东西，但你应该停止使用它。
尽管很少这样使用，V8Compatible存在没有修复日期到日期问题，但是支持与8i数据库的兼容性。 8i（及更旧版本）数据库不支持TIMESTAMP类型。设置V8Compatible不仅导致SQL DATE从数据库读取时映射到Timestamp，还会导致所有Timestamps在写入数据库时转换为SQL DATE。由于不支持8i，因此11.1 JDBC驱动程序不支持此兼容性模式。由于这个原因V8Compatible被支持。
如上所述，11.1驱动程序默认情况下将SQL DATE转换为从数据库读取时的时间戳记。这一切都是正确的，9i的变化是一个错误。 11.1驱动程序已经恢复正确的行为。即使您没有在应用程序中设置V8Compatible，在大多数情况下，您不应该看到行为方面的差异。如果您使用getObject读取DATE列，您可能会注意到差异。结果将是时间戳而不是日期。由于Timestamp是Date的子类，这通常不是问题。您可能会注意到，如果您依赖于从DATE到Date的转换来截断时间组件，或者如果您对该值进行了操作。否则更改应该是透明的。
如果由于某种原因，您的应用程序对此更改非常敏感，您只需要具有9i-10g行为，就会有一个连接属性你可以设置将mapDateToTimestamp设置为false，驱动程序将恢复为默认的9i-10g行为，并将DATE映射到Date。
如果可能，您应该将列类型更改为TIMESTAMP而不是日期。
-Richard
Roger Voss写道：
我在stackoverflow上发布了以下问题/问题，所以如果有人知道一个决议，那么很高兴看到它在那里回答：
使用iBATIS的Oracle SQL DATE转换问题通过Java JDBC
以下是问题描述：
我目前正在摔倒Oracle sql DATE转换问题使用Java中的iBATIS。
使用Oracle JDBC瘦驱动程序ojdbc14版本10.2.0.4.0。 iBATIS版本2.3.2。 Java 1.6.0_10-rc2-b32。
该问题围绕着这段SQL代码返回的DATE类型列：
SELECT *
FROM TABLE（pk_invoice_qry.get_contract_rate（？，？，？，？，？，？，？，？，？，？））order by from_date
包过程调用返回一个正在包装在TABLE中的ref游标，然后很容易地将结果集读取为对表的select查询。
在PL / SQL Developer中，SQL_DATE类型返回FROM_DATE的列之一具有精确到一天的时间：
Tue Dec 16 23:59:00 PST 2008
但是当我通过iBATIS和JDBC访问这个值时，该值仅保持精确到当天：
Tue Dec 16 12:00:00 AM PST 2008
显示如下所示更清楚：
应该是：
从历元以来的1229500740000毫秒
2008年12月16日，星期二11：太平洋标准时间下午59:00
但是取而代之的是：
从历元起，1229414400000毫秒
2008年12月16日，星期二12:00:00 PST
（作为类java.sql.Date的实例）
无论我尝试什么，我无法将此DATE列的完整精度公开为通过Java JDBC和iBATIS返回。
iBATIS的映射方式是这样的：
FROM_DATE：2008-12 -03：class java.sql.Date
目前的iBATIS映射是这样的：
我也试过：
或
但所有尝试的映射都产生相同的截断日期值。就像JDBC已经在iBATIS甚至触及之前已经损失了数据精度的损失。
显然，我通过JDBC和iBATIS失去了我的一些数据精度当我在PL / SQL Developer中运行与测试脚本相同的SQL代码片段时，并没有发生这种情况。不可接受，非常令人沮丧，最终非常可怕。
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 losing data precision before iBATIS even touches it.
Clearly I'm losing 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.
The full info (and it's more complex than described here and might depend upon which particular version of the Oracle drivers are in use) is in Richard Yee's answer here - [now expired link to Nabble]
Quick grab before it expires from nabble...
Roger, See: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01
Specifically: Simple Data Types What is going on with DATE and TIMESTAMP? This section is on simple data types. :-)
Prior to 9.2, the Oracle JDBC drivers mapped the DATE SQL type to java.sql.Timestamp. This made a certain amount of sense because the Oracle DATE SQL type contains both date and time information as does java.sql.Timestamp. The more obvious mapping to java.sql.Date was somewhat problematic as java.sql.Date does not include time information. It was also the case that the RDBMS did not support the TIMESTAMP SQL type, so there was no problem with mapping DATE to Timestamp.
In 9.2 TIMESTAMP support was added to the RDBMS. The difference between DATE and TIMESTAMP is that TIMESTAMP includes nanoseconds and DATE does not. So, beginning in 9.2, DATE is mapped to Date and TIMESTAMP is mapped to Timestamp. Unfortunately if you were relying on DATE values to contain time information, there is a problem.
There are several ways to address this problem:
Alter your tables to use TIMESTAMP instead of DATE. This is probably rarely possible, but it is the best solution when it is.
Alter your application to use defineColumnType to define the columns as TIMESTAMP rather than DATE. There are problems with this because you really don't want to use defineColumnType unless you have to (see What is defineColumnType and when should I use it?).
Alter you application to use getTimestamp rather than getObject. This is a good solution when possible, however many applications contain generic code that relies on getObject, so it isn't always possible.
Set the V8Compatible connection property. This tells the JDBC drivers to use the old mapping rather than the new one. You can set this flag either as a connection property or a system property. You set the connection property by adding it to the java.util.Properties object passed to DriverManager.getConnection or to OracleDataSource.setConnectionProperties. You set the system property by including a -D option in your java command line.
java -Doracle.jdbc.V8Compatible="true" MyApp Oracle JDBC 11.1 fixes this problem. Beginning with this release the driver maps SQL DATE columns to java.sql.Timestamp by default. There is no need to set V8Compatible to get the correct mapping. V8Compatible is strongly deprecated. You should not use it at all. If you do set it to true it won't hurt anything, but you should stop using it.
Although it was rarely used that way, V8Compatible existed not to fix the DATE to Date issue but to support compatibility with 8i databases. 8i (and older) databases did not support the TIMESTAMP type. Setting V8Compatible not only caused SQL DATE to be mapped to Timestamp when read from the database, it also caused all Timestamps to be converted to SQL DATE when written to the database. Since 8i is desupported, the 11.1 JDBC drivers do not support this compatibility mode. For this reason V8Compatible is desupported.
As mentioned above, the 11.1 drivers by default convert SQL DATE to Timestamp when reading from the database. This always was the right thing to do and the change in 9i was a mistake. The 11.1 drivers have reverted to the correct behavior. Even if you didn't set V8Compatible in your application you shouldn't see any difference in behavior in most cases. You may notice a difference if you use getObject to read a DATE column. The result will be a Timestamp rather than a Date. Since Timestamp is a subclass of Date this generally isn't a problem. Where you might notice a difference is if you relied on the conversion from DATE to Date to truncate the time component or if you do toString on the value. Otherwise the change should be transparent.
If for some reason your app is very sensitive to this change and you simply must have the 9i-10g behavior, there is a connection property you can set. Set mapDateToTimestamp to false and the driver will revert to the default 9i-10g behavior and map DATE to Date.
If possible, you should change your column type to TIMESTAMP instead of DATE.
-Richard
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
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:
I've also tried:
or
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.
这篇关于使用iBATIS通过Java JDBC的Oracle SQL DATE转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
