|
|
Posted on 2008-06-13 14:19 在路上... 阅读(7020) 评论(9) 编辑 收藏 所属分类: JAVA相关
一般的数据库中，DATE字段仅仅表示日期，不包括日期信息，而Oracle数据库中的DATE数据类型是包括日期、时间的，对于不同的Oracle jdbc驱动版本，对于该问题的处理都有些区别，如果你使用9i或者11g
的驱动程序，可能不会发现什么困惑，不幸的话，你使用Oracle10g的JDBC驱动，问题就来了，你会发现时间不见了？
看下面的程序
• 表结构如下
create table t_test(
id int,
date1 date,
date2 timestamp,
primary key(id)
)
1  try  {
2 Class.forName("oracle.jdbc.OracleDriver");
3 java.sql.Connection connection1 = DriverManager.getConnection("jdbc:oracle:thin:@192.168.8.200:1521:cdb", "sysusr", "sys");
4 System.out.println(connection1);
5 System.out.println(connection1.getMetaData().getDriverName()+" "+connection1.getMetaData().getDriverVersion());
6 ResultSet rs = connection1.createStatement().executeQuery("select date1,date2 from t_test");
7 rs.next();
8 printInfo(rs,1);
9 printInfo(rs,2);
10 }
11 catch (Exception exception1) {
12 exception1.printStackTrace();
13 }
14
15
16public static void printInfo(ResultSet rs,int i) throws SQLException{
17 ResultSetMetaData meta=rs.getMetaData();
18 System.out.printf("Colname=%s,Type=%s,TypeName=%s,val=[%s];\n",meta.getColumnName(i),meta.getColumnType(i),meta.getColumnTypeName(i),rs.getObject(i).toString());
19 }
• 如果使用9i或者11g的驱动连接数据库，返回结果如下：
9i数据库JDBC
oracle.jdbc.driver.OracleConnection@16930e2
Oracle JDBC driver 9.2.0.8.0
Colname=DATE1,Type=91,TypeName=DATE,val=[2008-06-13 13:48:21.0];
Colname=DATE2,Type=93,TypeName=TIMESTAMP,val=[oracle.sql.TIMESTAMP@18d107f];
11g数据库JDBC
oracle.jdbc.driver.T4CConnection@a61164
Oracle JDBC driver 11.1.0.6.0-Production+
Colname=DATE1,Type=93,TypeName=DATE,val=[2008-06-13 13:48:21.0];
Colname=DATE2,Type=93,TypeName=TIMESTAMP,val=[oracle.sql.TIMESTAMP@c4aad3];
如果使用10g JDBC驱动，结果如下：
oracle.jdbc.driver.T4CConnection@1bac748
Oracle JDBC driver 10.2.0.2.0
Colname=DATE1,Type=91,TypeName=DATE,val=[2008-06-13];
Colname=DATE2,Type=93,TypeName=TIMESTAMP,val=[oracle.sql.TIMESTAMP@b8df17];
结果是让人困惑，时间怎么不见了？
对于该问题，在Oracle的JDBC FAQ中有提到解决办法：
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:
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
参照上面的解释，修改代码如下可以解决10g JDBC驱动的问题：
try {
Class.forName("oracle.jdbc.OracleDriver");
Properties prop=new Properties();
prop.setProperty("user","sysuser");
prop.setProperty("password","sys");
prop.setProperty("oracle.jdbc.V8Compatible","true");
java.sql.Connection connection1 = DriverManager.getConnection("jdbc:oracle:thin:@192.168.8.200:1521:cdb", 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");
rs.next();
printInfo(rs,1);
printInfo(rs,2);
}
catch (Exception exception1) {
exception1.printStackTrace();
}
或者在系统变量中使用参数-Doracle.jdbc.V8Compatible="true"，例如
java -Doracle.jdbc.V8Compatible="true" MyApp
结果如下：
oracle.jdbc.driver.T4CConnection@9664a1
Oracle JDBC driver 10.2.0.2.0
Colname=DATE1,Type=93,TypeName=DATE,val=[2008-06-13 13:48:21.0];
Colname=DATE2,Type=93,TypeName=DATE,val=[oracle.sql.TIMESTAMP@1172e08];
Feedback
# re: Oracle JDBC驱动与时间不见了的问题 回复 更多评论
2008-06-13 15:48 by
传智播客ajax全套内部视频独家发布,免费下载
1.ajax 入门
2.ajax 原理
3.ajax 简单实例
4.ajax 无限级联动菜单
5.ajax 简易聊天室
6.ajax 开源框架简介
7.DWR 框架源码分析一
8.DWR 框架源码分析二
9.DWR 框架源码分析三
10.DWR 框架源码分析四
11.DWR框架源码分析五
12.SSH + DWR完成商城驱动
13. Extjs 简介
14 Extjs 简单实例
15.SSH + Extjs 开发系列之OA一
16. SSH + Extjs 开发系列之OA二
17. SSH + Extjs 开发系列之OA三
18. SSH + Extjs 开发系列之OA四
19 .SSH + Extjs 开发系列之OA五
20. SSH + Extjs 开发系列之OA六
21. SSH + Extjs 开发系列之OA七
22. SSH + Extjs 开发系列之OA八
23.SSH + Extjs 开发系列之OA九
24.SSH + Extjs 开发系列之OA十
25. ajax 前景之我见
下载地址: http://www.ibeifeng.com/read.php?tid=2338&u=5043
# re: Oracle JDBC驱动与时间不见了的问题 回复 更多评论
2008-06-13 17:20 by
学习了
# re: Oracle JDBC驱动与时间不见了的问题 回复 更多评论
2008-06-13 20:51 by
呵呵，对日期字段我的原则是允许的情况下，用字符串代替。
# re: Oracle JDBC驱动与时间不见了的问题 回复 更多评论
2008-06-15 14:14 by
原来如此，以前一直用8/9，现在准备11，10比较少。
一直以来用字符串代替日期类型，一直担心引起效率的下降特别是
时间作为条件时，字符串的比较和直接日期的比较不知道差多少？
# re: Oracle JDBC驱动与时间不见了的问题 回复 更多评论
2008-12-11 10:55 by
楼主，你好，我也遇到这个问题，我的服务器是websphere，我想你所说的
或者在系统变量中使用参数-Doracle.jdbc.V8Compatible="true"，例如
java -Doracle.jdbc.V8Compatible="true" MyApp
我本想这样解决的
oracle驱动缺少时分秒的解决办法
应用程序服务器 > server1 > 进程定义 > Java 虚拟机 > 定制属性
下新建
-Doracle.jdbc.V8Compatible=true
Doracle.jdbc.V8Compatible=true
oracle.jdbc.V8Compatible=true
但这样的话引起我们系统的RMI连接错误，想知道你到底是怎么解决，你的msn是多少，谢谢了！
# re: Oracle JDBC驱动与时间不见了的问题 回复 更多评论
2008-12-13 09:32 by
我这里使用weblogic,直接在启动weblogic的startweblogic.cmd中，
java ******
后面加上-Doracle.jdbc.V8Compatible=true 就好了
websphere，呵呵，很久没用了
# re: Oracle JDBC驱动与时间不见了的问题 回复 更多评论
2008-12-15 09:30 by
楼主你好，请问你MSN多少，或者你加我也可以， yqwcn@hotmail.com，谢谢了
# re: Oracle JDBC驱动与时间不见了的问题 回复 更多评论
2008-12-30 13:17 by
好
# re: Oracle JDBC驱动与时间不见了的问题 回复 更多评论
2014-11-16 15:57 by
多谢
|