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

Question

我目前正在使用 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 类型的列：

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

包过程调用返回一个引用游标，该游标被包装在 TABLE 中，然后可以轻松读取结果集，就像选择查询一样靠在桌子上。

在 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 milliseconds since epoch
Tuesday, December 16, 2008 11:59:00 PM PST

但得到这个：

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

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

iBATIS 的映射来源是这样的：

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

当前的 iBATIS 映射是这样的：

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

我也尝试过：

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

或

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

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

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

Answer 1

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

---

在 Nabble 过期之前快速抓取...

罗杰，
请参阅：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不支持TIMESTAMP SQL类型，所以将DATE映射到Timestamp没有问题。

在 9.2 中，RDBMS 添加了 TIMESTAMP 支持。 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="true" MyApp
Oracle JDBC 11.1 修复了此问题。 从此版本开始，驱动程序默认将 SQL DATE 列映射到 java.sql.Timestamp。 无需设置 V8Compatible 即可获得正确的映射。 V8Compatible 已被强烈弃用。 你根本不应该使用它。 如果你确实将其设置为 true，它不会造成任何伤害，但你应该停止使用它。

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

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

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

如果可能，您应该将列类型更改为 TIMESTAMP 而不是 DATE。

——理查德

---

·罗杰·沃斯写道：
我在 stackoverflow 上发布了以下问题，所以如果有人知道解决方案，很高兴看到它在那里得到解答：

Oracle SQL DATE conversion Problem using iBATIS via 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 中，然后位于易于读取结果集，就像针对表进行选择查询一样。

在 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
（作为类 java.sql.Date 的实例）

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

iBATIS 的映射来源是这样的：

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

当前的 iBATIS 映射是这样的：

我也尝试过：

或

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

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

Answer 2

我找到了如何解决这个问题。 iBATIS 允许注册自定义类型处理程序。 因此，在我的 sqlmap-config.xml 文件中，我添加了以下内容：

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

然后添加了实现 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);
    }
}

每当我需要映射 Oracle DATE 时，我现在都会这样描述它：

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

Answer 3

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

• 问题：

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

• 已解决：

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

Answer 4

问题出在 Oracle 驱动程序上。

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

所以更改

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

为：

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

Answer 5

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

Answer 6

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

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

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

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

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

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

或者

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

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

Answer 7

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