`
carvin
  • 浏览: 212591 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Oracle10g JDBC ojdbc14 DATE类型hibernate查询时分秒问题

阅读更多
一般的数据库中,DATE字段仅仅表示日期,不包括日期信息,而Oracle数据库中的DATE数据类型是包括日期、时间的,对于不同的Oracle jdbc驱动版本,对于该问题的处理都有些区别。

    最近项目中碰到此问题,用的是ORACLE 10G,时间字段因需求,设为了DATE类型,发现hibernate用native SQL 查询,显示不了时分秒,一看,原来是JDBC驱动自动把date映射为 java.sql.date,故截断了时分秒信息,如果你使用9i或者11g
的驱动程序,此问题不存在,但是Oracle10g的JDBC驱动,问题就来了,你会发现时间不见了!!!
   
网上看了资料,在oracle 官网Oracle JDBC FAQ 看到:

http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html#08_01


引用

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 in the 9.2 through 10.2 drivers:

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.



故用此方法:
Java代码
public static void main(String[] args) {   
    try {   
           Class.forName("oracle.jdbc.OracleDriver");   
           Properties prop=new Properties();   
           prop.setProperty("user","system");   
           prop.setProperty("password","dba");   
           prop.setProperty("oracle.jdbc.V8Compatible","true");   
           java.sql.Connection connection1 = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", prop);   
           System.out.println(connection1);   
           System.out.println(connection1.getMetaData().getDriverName()+" "+connection1.getMetaData().getDriverVersion());   
           ResultSet rs = connection1.createStatement().executeQuery("select date1,date2 from t_test");   
  
        while (rs.next()) {   
            String value1 = rs.getString("DATE1");   
            System.out.println("DATE1=" + value1);   
            String value2 = rs.getString("DATE2");   
            System.out.println("DATE2=" + value2);   
        }   
       }   
       catch (Exception exception1) {   
           exception1.printStackTrace();   
       }   
  
}     

可以解决!!!

在 hibernate中,在hibernate.cfg.xml中加入:

<property name="hibernate.connection.oracle.jdbc.V8Compatible">true</property>  

一样也可以解决
这种方式我在spring+hibernate测试,结果不尽人如意,后面换了数据库驱动,问题解决。

网上看了一下,好像以下版本的 jdbc 存在问题

引用

Driver Result 
with classes12.zip at C:\Oracle\Ora81\jdbc\lib, Oracle JDBC Drivers release 8.1.6 OK, no problem found 
with "oracle 8.1.7 driver\classes12.zip", oracle 8.1.7 driver OK, no problem found 
with "oracle 10i\classes12.jar", oracle 10i driver ID corrputed. Bug 
with "oracle9i driver\classes12.jar", oracle 9i driver ID corrputed. Bug 
with "oracle 10i\ojdbc14.jar", oracle 10i driver ID corrputed. Bug 
with "oracle9i driver\ojdbc14.jar", oracle 9i driver ID corrputed. Bug


我下了oracle11g 11.2.0.1.0 JDBC_ojdbc6.jar 以及 oracle11g 11.2.0.1.0 JDBC_orai18n.jar 替换了ojdbc14.jar
驱动下载见附件。

参考文章:http://www.fengfly.com/plus/view-168009-1.html
分享到:
评论

相关推荐

    oracle 10g jar ojdbc14.jar

    Oracle 10g JDBC驱动程序,以`ojdbc14.jar`的形式存在,是Oracle数据库与Java应用程序之间通信的关键组件。这个JAR文件包含了Oracle公司提供的Java Database Connectivity (JDBC)驱动,允许Java开发者在他们的应用...

    oracle jdbc驱动 ojdbc14-10.2.0.4.0下载

    ojdbc14-10.2.0.4.0是Oracle公司为Java开发者提供的一款针对Oracle 10g数据库的JDBC驱动版本。这个版本的驱动是Java 1.4兼容的,因此适合那些仍使用Java 1.4环境的项目。 Oracle JDBC驱动分为四种类型: 1. ** ...

    oracle jdbc驱动 ojdbc14-10.2.0.4.0.jar工具

    ojdbc14-10.2.0.4.0.jar是Oracle JDBC驱动的一个特定版本,适用于Oracle数据库10g Release 2。 这个驱动程序的核心功能包括: 1. **连接管理**:JDBC驱动负责建立、维护和关闭与Oracle数据库的连接。它使用URL、...

    oracle10G驱动ojdbc14.zip

    Oracle 10g驱动程序ojdbc14是Oracle公司为Java应用程序提供的一种连接到Oracle数据库的JDBC(Java Database Connectivity)驱动。Oracle JDBC驱动程序允许Java开发者通过编写Java代码来执行SQL语句,进行数据查询、...

    Oracle驱动包,ojdbc14_g

    命令:mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=10.2.0.5.0 -Dpackaging=jar -Dfile=D:\util\ojdbc14_g.jar 引用方式: &lt;!-- 添加oracle jdbc driver --&gt; ...

    oracle_jdbc ojdbc14.jar ojdbc6.jar

    在本主题中,我们将深入探讨Oracle JDBC驱动程序,特别是ojdbc14.jar和ojdbc6.jar这两个版本。 首先,了解JDBC的概念是至关重要的。JDBC是Java API,允许Java应用程序与各种类型的数据库进行通信。它提供了一套标准...

    oracle.jdbc.driver.oracledriver Oracle JDBC驱动包 ojdbc6

    ojdbc5.jar提供了一种方式,使得Java应用程序可以与Oracle 10g及更高版本的数据库进行交互。 3. **ojdbc6.jar**:这个是Oracle JDBC驱动的另一个版本,专为Java SE 6设计,支持JDBC 4.0规范。ojdbc6.jar包含所有...

    oracle jdbc驱动 ojdbc14-10.2.0.4.0.jar

    ojdbc14-10.2.0.4.0.jar是Oracle公司发布的针对JDBC版本14的驱动程序,适用于Oracle数据库10g的第4次修订版。这个驱动程序实现了Java Database Connectivity (JDBC) API,使得Java开发者能够通过编写Java代码来执行...

    oracle数据jdbc驱动包,包括ojdbc14,ojdbc6,ojdbc8

    ojdbc14_g.jar 包含了调试信息,方便开发者进行问题排查。 2. **ojdbc6**: ojdbc6 驱动对应的是 Java 6 及更高版本,它实现了 JDBC 4.0 规范。ojdbc6 提供了更高效的数据访问性能和新的特性,比如对 SQLXML 的...

    Oracle11G_JDBC驱动【ojdbc6.jar】.rar

    Oracle 11g JDBC驱动,即ojdbc6.jar,是Oracle公司提供的用于Java应用程序与Oracle数据库之间进行数据交互的关键组件。JDBC(Java Database Connectivity)是Java编程语言中用于访问数据库的标准API,它使得Java...

    oracle ojdbc8 ojdbc14 maven依赖

    Oracle Ojdbc8 和 Ojdbc14 是 Oracle 公司提供的两个不同版本的 JDBC 驱动,用于连接 Java 应用程序与 Oracle 数据库。在 Maven 工程中,正确配置这些依赖对于确保 Java 项目能够顺利连接并操作 Oracle 数据库至关...

    ojdbc6(此包解决mybatis_plus查询时分秒为0问题).7z

    ojdbc6(此包解决mybatis_plus查询时分秒为0问题),springboot项目+oracle11g就用这个jdbc就行,放在项目的resources目录下lib目录下就行,然后配置下pom文件即可 &lt;!-- maven oracle依赖包 --&gt; &lt;groupId&gt;jcca ...

    oracle数据库jdbc驱动ojdbc5,ojdbc6,ojdbc14

    本文将详细介绍Oracle数据库的三种JDBC驱动:ojdbc5、ojdbc6和ojdbc14,以及如何使用它们。 1. **ojdbc5**: ojdbc5是针对Java SE 5(也称为Java 1.5)发布的驱动版本。它属于Oracle JDBC Type 4驱动,提供了全面...

    oracle 10g jdbc 驱动

    - `ojdbc14.jar`:这是Oracle 10g的主JDBC驱动包,包含了Thin Driver和其他必要的类。 - `classes12.zip` 或 `classes12.jar`:包含Oracle JDBC驱动的其他类和资源,有时是Thick Driver的组成部分。 - `ocijdbc14....

    Oracle_10g_10.2.0.4_JDBC_ojdbc14.jar

    ojdbc14.jar 文件包含了Oracle数据库特定的JDBC驱动,使得Java应用程序能够连接到Oracle 10g数据库,执行SQL查询和其他数据库操作。 在MyBatis框架中,JDBC驱动扮演着至关重要的角色。MyBatis是一个轻量级的持久层...

    oracle各个版本的驱动 从ojdbc5开始 到ojdbc14_g,包含ojdbc6、ojdbc12、各个版本

    2. **ojdbc14_g**: 这个版本对应于Oracle数据库10g Release 2,其中“_g”表示通用版,它包含了对JDBC 4.0规范的部分支持,但并非完全兼容。ojdbc14_g在ojdbc5的基础上增加了自动注册驱动和获取数据库元数据的新特性...

    java连接oracle数据库jar包ojdbc8

    在Java编程中,连接Oracle数据库通常需要依赖特定的驱动,这就是Oracle JDBC驱动,也被称为ojdbc。在本案例中,我们关注的是ojdbc8.jar,这是Oracle公司为Java 8提供的JDBC驱动程序,用于与Oracle数据库进行通信。...

Global site tag (gtag.js) - Google Analytics