通过 Java JDBC 使用 iBATIS 的 Oracle SQL DATE 转换问题

Oracle SQL DATE conversion problem using iBATIS via Java JDBC

提问by RogerV

I'm currently wrestling with an Oracle SQL DATE conversion problem using iBATIS from Java.

我目前正在努力解决使用 Java 中的 iBATIS 的 Oracle SQL DATE 转换问题。

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.

我正在使用 Oracle JDBC 瘦驱动程序 ojdbc14 版本 10.2.0.4.0。iBATIS 版本 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:

问题围绕着此 SQL 片段返回的 DATE 类型的列：

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.

包过程调用返回一个被包装在 TABLE 中的引用游标，然后很容易读取结果集，就像对表的选择查询一样。

In PL/SQL Developer, one of the columns returned, FROM_DATE, of SQL DATE type, has precision to time of day:

在 PL/SQL Developer 中，返回的列之一，FROM_DATE，属于 SQL DATE 类型，具有一天中的时间精度：

Tue Dec 16 23:59:00 PST 2008

But when I access this via iBATIS and JDBC, the value only retains precision to day:

但是当我通过 iBATIS 和 JDBC 访问它时，该值只保留到今天的精度：

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.

无论我尝试什么，我都无法公开通过 Java JDBC 和 iBATIS 返回的此 DATE 列的完整精度。

What iBATIS is mapping from is this:

iBATIS 的映射是这样的：

FROM_DATE : 2008-12-03 : class java.sql.Date

The current iBATIS mapping is this:

当前的 iBATIS 映射是这样的：

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

但是所有尝试的映射都会产生相同的截断日期值。就好像 JDBC 在 iBATIS 还没接触到它之前就已经造成了丢失数据精度的损害。

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.

显然，我通过 JDBC 和 iBATIS 丢失了一些数据精度，当我留在 PL/SQL Developer 中运行与测试脚本相同的 SQL 代码段时不会发生这种情况。完全不可接受，非常令人沮丧，最终非常可怕。

采纳答案by Gwyn Evans

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]

完整信息（它比此处描述的更复杂，可能取决于所使用的 Oracle 驱动程序的特定版本）在 Richard Yee 的回答中 - [现在指向 Nabble 的链接已过期]

---

---

Quick grab before it expires from nabble...

在从 nabble 过期之前快速抓取...

Roger, See: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_01

罗杰，见：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. :-)

具体来说： 简单数据类型 DATE 和 TIMESTAMP 发生了什么？本节是关于简单数据类型的。:-)

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.

在 9.2 之前，Oracle JDBC 驱动程序将 DATE SQL 类型映射到 java.sql.Timestamp。这在一定程度上是有意义的，因为 Oracle DATE SQL 类型与 java.sql.Timestamp 一样包含日期和时间信息。到 java.sql.Date 的更明显的映射有些问题，因为 java.sql.Date 不包括时间信息。也是RDBMS不支持TIMESTAMP SQL类型的情况，所以把DATE映射到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.

在 9.2 TIMESTAMP 支持被添加到 RDBMS。DATE 和 TIMESTAMP 之间的区别在于 TIMESTAMP 包括纳秒而 DATE 不包括纳秒。因此，从 9.2 开始，DATE 映射到 Date，TIMESTAMP 映射到 Timestamp。不幸的是，如果您依赖 DATE 值来包含时间信息，就会出现问题。

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.

更改您的表以使用 TIMESTAMP 而不是 DATE。这可能很少可能，但它是最好的解决方案。

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?).

更改您的应用程序以使用defineColumnType 将列定义为TIMESTAMP 而不是DATE。这有问题，因为除非必须，否则您真的不想使用defineColumnType（请参阅什么是defineColumnType 以及何时应该使用它？）。

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.

更改您的应用程序以使用 getTimestamp 而不是 getObject。如果可能，这是一个很好的解决方案，但是许多应用程序包含依赖于 getObject 的通用代码，因此它并不总是可行的。

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.

设置 V8Compatible 连接属性。这告诉 JDBC 驱动程序使用旧映射而不是新映射。您可以将此标志设置为连接属性或系统属性。您可以通过将连接属性添加到传递给 DriverManager.getConnection 或 OracleDataSource.setConnectionProperties 的 java.util.Properties 对象来设置它。您可以通过在 java 命令行中包含 -D 选项来设置系统属性。

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.

java -Doracle.jdbc.V8Compatible="true" MyApp Oracle JDBC 11.1 修复了这个问题。从此版本开始，驱动程序默认将 SQL DATE 列映射到 java.sql.Timestamp。无需设置 V8Compatible 即可获得正确的映射。V8Compatible 已被强烈弃用。你根本不应该使用它。如果您确实将其设置为 true，则不会有任何伤害，但您应该停止使用它。

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.

尽管很少以这种方式使用，但 V8Compatible 的存在并不是为了修复 DATE to Date 问题，而是为了支持与 8i 数据库的兼容性。8i（及更早版本）数据库不支持 TIMESTAMP 类型。设置 V8Compatible 不仅会导致 SQL DATE 从数据库读取时映射到 Timestamp，还会导致所有 Timestamp 在写入数据库时​​都转换为 SQL DATE。由于不支持 8i，因此 11.1 JDBC 驱动程序不支持此兼容模式。因此，不支持 V8Compatible。

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.

如上所述，11.1 驱动程序在从数据库读取时默认将 SQL DATE 转换为 Timestamp。这始终是正确的做法，而 9i 中的更改是一个错误。11.1 驱动程序已恢复到正确的行为。即使您没有在应用程序中设置 V8Compatible，在大多数情况下您也不应该看到任何行为差异。如果您使用 getObject 读取 DATE 列，您可能会注意到不同。结果将是时间戳而不是日期。由于 Timestamp 是 Date 的子类，因此这通常不是问题。您可能会注意到不同之处在于，您是否依赖从 DATE 到 Date 的转换来截断时间组件，或者您是否对值执行 toString 操作。否则，更改应该是透明的。

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.

如果出于某种原因，您的应用程序对这种更改非常敏感，并且您必须具有 9i-10g 行为，那么您可以设置一个连接属性。将 mapDateToTimestamp 设置为 false，驱动程序将恢复到默认的 9i-10g 行为并将 DATE 映射到 Date。

If possible, you should change your column type to TIMESTAMP instead of DATE.

如果可能，您应该将列类型更改为 TIMESTAMP 而不是 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:

Roger Voss 写道：我在 stackoverflow 上发布了以下问题/问题，所以如果有人知道一个解决方案，很高兴看到它在那里得到回答：

Oracle SQL DATE conversion problem using iBATIS via Java JDBC

通过 Java JDBC 使用 iBATIS 的 Oracle SQL DATE 转换问题

Here's the problem description:

这是问题描述：

I'm currently wrestling with an Oracle sql DATE conversion problem using iBATIS from Java.

我目前正在努力解决使用 Java 中的 iBATIS 的 Oracle sql DATE 转换问题。

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.

我正在使用 Oracle JDBC 瘦驱动程序 ojdbc14 版本 10.2.0.4.0。iBATIS 版本 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:

问题围绕着此 SQL 片段返回的 DATE 类型的列：

SELECT * FROM TABLE(pk_invoice_qry.get_contract_rate(?,?,?,?,?,?,?,?,?,?)) order by from_date

SELECT * FROM TABLE(pk_invoice_qry.get_contract_rate(?,?,?,?,?,?,?,?,?,?)) 按 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.

包过程调用返回一个被包装在 TABLE 中的引用游标，然后很容易读取结果集，就像对表的选择查询一样。

In PL/SQL Developer, one of the columns returned, FROM_DATE, of SQL DATE type, has precision to time of day:

在 PL/SQL Developer 中，返回的列之一，FROM_DATE，属于 SQL DATE 类型，具有一天中的时间精度：

Tue Dec 16 23:59:00 PST 2008

But when I access this via iBATIS and JDBC, the value only retains precision to day:

但是当我通过 iBATIS 和 JDBC 访问它时，该值只保留到今天的精度：

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

应该是：自纪元 2008 年 12 月 16 日星期二晚上 11:59:00 PST 以来的 1229500740000 毫秒

But getting this instead: 1229414400000 milliseconds since epoch Tuesday, December 16, 2008 12:00:00 AM PST (as instance of class java.sql.Date)

但是得到这个：1229414400000 毫秒自纪元 2008 年 12 月 16 日星期二 12:00:00 AM PST（作为类 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.

无论我尝试什么，我都无法公开通过 Java JDBC 和 iBATIS 返回的此 DATE 列的完整精度。

What iBATIS is mapping from is this:

iBATIS 的映射是这样的：

FROM_DATE : 2008-12-03 : class java.sql.Date

FROM_DATE : 2008-12-03 : 类 java.sql.Date

The current iBATIS mapping is this:

当前的 iBATIS 映射是这样的：

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.

但是所有尝试的映射都会产生相同的截断日期值。就好像 JDBC 在 iBATIS 接触它之前就已经造成了失去数据精度的损害。

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.

显然，我通过 JDBC 和 iBATIS 丢失了一些数据精度，这在我留在 PL/SQL Developer 中运行与测试脚本相同的 SQL 片段时不会发生。完全不可接受，非常令人沮丧，最终非常可怕。

回答by ninesided

The problem is the use of java.sql.Date. According to the Javadoc, the millisecond values wrapped by a java.sql.Dateinstance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated, to conform with the definition of SQL DATE.

问题是使用java.sql.Date. 根据Javadoc，java.sql.Date实例包装的毫秒值必须通过在实例关联的特定时区将小时、分钟、秒和毫秒设置为零来“规范化”，以符合 SQL 的定义DATE。

回答by RogerV

Yes, I see - the plain SQL DATE standard must be to only store to day resolution. Indeed, here is a snippet on Oracle's DATE type:

是的，我明白了 - 简单的 SQL DATE 标准必须是只存储到一天的分辨率。事实上，这里有一个关于 Oracle 的 DATE 类型的片段：

Oracle supports both date and time, albeit differently from the SQL2 standard. Rather than using two separate entities, date and time, Oracle only uses one, DATE. The DATE type is stored in a special internal format that includes not just the month, day, and year, but also the hour, minute, and second.

Oracle 支持日期和时间，尽管与 SQL2 标准不同。Oracle 没有使用两个独立的实体日期和时间，而是只使用一个，即 DATE。DATE 类型以特殊的内部格式存储，不仅包括月、日和年，还包括小时、分钟和秒。

Which makes the point that Oracle's DATE exceeds standard SQL DATE.

这说明 Oracle 的 DATE 超过了标准 SQL DATE。

Hmm, Oracle PL/SQL folks use DATE extensively to hold values where they depend on the resolution being to the second. Looks like iBATIS needs something like the Hibernate sql dialect concept where instead of interpreting DATE via java.sql.Date, could override and instead interpret via java.util.Date, which Javadocs defines as permitting millisecond resolution.

嗯，Oracle PL/SQL 人员广泛使用 DATE 来保存值，他们依赖于秒的分辨率。看起来 iBATIS 需要类似 Hibernate sql 方言概念的东西，其中不是通过 java.sql.Date 解释 DATE，而是可以覆盖并通过 java.util.Date 解释，Javadocs 将其定义为允许毫秒分辨率。

Unfortunately when I've changed the mapping to something like:

不幸的是，当我将映射更改为以下内容时：

<result property="from_date" jdbcType="DATE" javaType="java.util.Date"/>

or

或者

<result property="from_date" jdbcType="DATETIME" javaType="java.util.Date"/>

It's still seemingly first translated the SQL DATE to a java.sql.Date and lost the time of day precision.

它似乎仍然首先将 SQL DATE 转换为 java.sql.Date 并丢失了时间精度。

回答by RogerV

I found out how to solve this problem. iBATIS permits custom type handlers to be registered. So in my sqlmap-config.xml file I added this:

我发现了如何解决这个问题。iBATIS 允许注册自定义类型处理程序。所以在我的 sqlmap-config.xml 文件中我添加了这个：

<typeAlias alias="OracleDateHandler" type="com.tideworks.ms.CustomDateHandler"/>
<typeHandler callback="OracleDateHandler" jdbcType="DATETIME" javaType="date"/>

And then added this class which implements the iBATIS TypeHandlerCallback interface:

然后添加了这个实现 iBATIS TypeHandlerCallback 接口的类：

// corrected getResult()/setParameter() to correctly deal with when value is null
public class CustomDateHandler implements TypeHandlerCallback {
    @Override
    public Object getResult(ResultGetter getter) throws SQLException {
        final Object obj = getter.getTimestamp();
        return obj != null ? (Date) obj : null;
    }

    @Override
    public void setParameter(ParameterSetter setter,Object value) throws SQLException {
        setter.setTimestamp(value != null ? new Timestamp(((Date)value).getTime()) : null);
    }

    @Override
    public Object valueOf(String datetime) {
        return Timestamp.valueOf(datetime);
    }
}

Whennever I need to map an Oracle DATE I now describe it like so:

当我从不需要映射 Oracle DATE 时，我现在这样描述它：

<result property="from_date" jdbcType="DATETIME" javaType="date"/>

回答by Gwyn Evans

I have solved my problem using jdbcType="TIMESTAMP"instead of jdbcType="DATE"

我已经解决了我的问题，jdbcType="TIMESTAMP"而不是jdbcType="DATE"

? PROBLEM:

? 问题：

<result column="MY_UTC_POS" property="myUtcPosition" jdbcType="DATE" />

? SOLVED:

? 解决了：

<result column="MY_UTC_POS" property="myUtcPosition" jdbcType="TIMESTAMP" />

回答by bob

The problem is with the Oracle Driver.

问题出在 Oracle 驱动程序上。

The best solution I found was to change all jdbcType="DATE" to jdbcType="TIMESTAMP" and all #column_name:DATE# to #column_name:TIMESTAMP#

我发现的最佳解决方案是将所有 jdbcType="DATE" 更改为 jdbcType="TIMESTAMP" 并将所有 #column_name:DATE# 更改为 #column_name:TIMESTAMP#

So change:

所以改变：

<result property="from_date" jdbcType="DATE" javaType="java.sql.Date"/>

to

到

<result property="from_date" jdbcType="TIMESTAMP" javaType="java.sql.Date"/>

回答by wallenborn

Richard Yee mentions that Oracle's latest drivers fix the problem. I can confirm that. Had the same problem here with 10.2 drivers, upgraded today to ojdbc5.jar (11.2.0.1.0), and the problem is gone now.

Richard Yee 提到 Oracle 的最新驱动程序解决了这个问题。我可以确认。10.2 驱动也有同样的问题，今天升级到 ojdbc5.jar (11.2.0.1.0)，现在问题消失了。