- 浏览: 33399 次
- 性别:
- 来自: 天津
最新评论
Oracle10g JDBC ojdbc14 DATE类型hibernate查询时分秒问题(纠结困扰了半天,汗)
一般的数据库中,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. 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.
故用此方法:
- 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中加入:
一样也可以解决
网上看了一下,好像以下版本的 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
最好还是把 date 的在 数据库中设为 timestamp 类型。。。。
发表评论
-
JDK,JRE,JVM区别与联系
2014-08-14 19:23 612很多朋友可能跟我一 ... -
ORA-01578(数据块损坏)错误解决方法
2012-08-17 16:30 1968错误:在 exp 时出现以下错误: EXP-00056: 遇 ... -
oracle 只有数据文件时的恢复
2012-08-17 15:26 2570兄弟刚用ORACLE,因为IP地址变了下,玩了半天, ... -
ORACLE中NOT IN 的替代
2012-08-07 14:01 905典型的查询方式为:SELECT TITLEFROM BO ... -
eclipse正则表达式批量查找替换
2012-07-26 13:50 1264我们经常使用一些工具进行替换操作,有些工具在替换时支持使用正则 ... -
删除Oracle中奇怪的表名称BIN$…的方法
2012-04-02 17:07 976从Oracle10g开始删除数据库表的时候并不是真正删除 ... -
oracle添加列到指定位置
2012-04-02 16:29 2747oracle中,1.如果表已经装载了大量数据应该是用视图 ... -
oracle hibernate 临时表 存储过程
2012-03-23 10:19 1538参考资料 1 ORACLE 存储 ... -
java好的编码习惯
2012-03-11 09:49 832最近的机器内存又爆 ... -
存储过程还是业务逻辑层
2012-02-25 20:55 12171.存储过程是基于计算密集型的业务逻辑。如果是基于操作密集型的 ... -
连接oracle 报 ORA-12519
2012-02-24 13:20 2527TNS-12519与processes参 ... -
HashMap原理
2012-01-13 10:46 1024Hashmap是一种非常常用的、应用广泛的数据类型,最近研究到 ... -
oracle no appropriate service handler found ORA-12519
2012-01-11 11:14 934今天下午,开发人员突然说不能连接数据库了,提示相关的 ... -
hibernate.jdbc.fetch_size 和 hibernate.jdbc.batch_size
2012-01-10 16:44 954hibernate.jdbc.fetch_size 和 h ... -
struts2 标签 <s:set> <s:if>
2011-12-23 10:40 1195Struts2中s:set标签和s:if标 ... -
spring中集成测试
2011-11-18 10:13 721详细讲解在Spring中进行集成测试 ... -
java 学习之路
2011-09-17 12:57 608《ThinkinginJava》。它是 ... -
spring 配置连接池
2011-09-17 12:57 1154不管通过何种持久化技术,都必须通过数据连接访问数据库,在 ... -
jvm 内存分布
2011-09-17 12:55 885一、JVM简介 JVM是Java Virtual ... -
java 内部类的应用场景
2011-09-17 12:54 850幕后英雄的用 ...
相关推荐
oracle_jdbc ojdbc14.jar ojdbc6.jar
oracle10G驱动ojdbc14.zip
oracle 10g 联接驱动 jar
oracle数据jdbc驱动jar包,包括ojdbc14,ojdbc6,ojdbc8等,java开发oracle数据库的好助手。
ojdbc6(此包解决mybatis_plus查询时分秒为0问题),springboot项目+oracle11g就用这个jdbc就行,放在项目的resources目录下lib目录下就行,然后配置下pom文件即可 <!-- maven oracle依赖包 --> <groupId>jcca ...
oracle11g的驱动包,包括classes12.jar,ojdbc5.jar,ojdbc6.jar;
Oracle_10g_10.1.0.2_JDBC_ojdbc14
命令:mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=10.2.0.5.0 -Dpackaging=jar -Dfile=D:\util\ojdbc14_g.jar 引用方式: <!-- 添加oracle jdbc driver --> ...
oracle 的jdbc 驱动 ojdbc.jar
连接oracle数据库的jdbc驱动,包括ojdbc5,ojdbc6,ojdbc14
<artifactId>ojdbc14 <version>10.2.0.4.0 安装到仓库 mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=10.2.0.4.0 -Dpackaging=jar -Dfile=D:\download\ojdbc14-10.2.0.4.0....
JDBC ojdbc14.jar 程序文件,oracle jdbc driver for jdk1.5
ojdbc14-10.2.0.4.0下载,jar包完整,积分最低,亲测可用。。。
1、解压,在ojdbc14.jar(把‘-副本’去掉)所在目录,shift+右击,在此处打开命令窗口; 2、执行 mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=14 -Dpackaging=jar -Dfile=...
oracle ojdbc8 ojdbc14 maven依赖
Oracle jdbc驱动 ,使用java连接Oracle 数据库需要jdbc驱动。
ojdbc678,oracle11g驱动
oracle jdbc 驱动(ojdbc8、ojdbc14)
Oracle Database 19c(19.3)JDBC驱动程序 通过JDK10认证; Oracle JDBC驱动程序
oracle数据库的jdbc驱动,包括ojdbc14,ojdbc5,ojdbc5_g,ojdbc6,ojdbc6_g