Oracle10g JDBC ojdbc14 DATE类型hibernate查询时分秒有关问题(纠结困扰了半天，汗)
日期：2014-05-16 浏览次数：20889 次
Oracle10g JDBC ojdbc14 DATE类型hibernate查询时分秒问题(纠结困扰了半天，汗)
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. Beginni
一般的数据库中，DATE字段仅仅表示日期，不包括日期信息，而Oracle数据库中的DATE数据类型是包括日期、时间的，对于不同的Oracle jdbc驱动版本，对于该问题的处理都有些区别。?
??? 最近我从sql server2000 换到 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. Beginni
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
