|
|
Posted on 2008-06-13 14:19 在路上... 閱讀(7043) 評(píng)論(9) 編輯 收藏 所屬分類: JAVA相關(guān)
一般的數(shù)據(jù)庫(kù)中，DATE字段僅僅表示日期，不包括日期信息，而Oracle數(shù)據(jù)庫(kù)中的DATE數(shù)據(jù)類型是包括日期、時(shí)間的，對(duì)于不同的Oracle jdbc驅(qū)動(dòng)版本，對(duì)于該問(wèn)題的處理都有些區(qū)別，如果你使用9i或者11g
的驅(qū)動(dòng)程序，可能不會(huì)發(fā)現(xiàn)什么困惑，不幸的話，你使用Oracle10g的JDBC驅(qū)動(dòng)，問(wèn)題就來(lái)了，你會(huì)發(fā)現(xiàn)時(shí)間不見了？
看下面的程序
• 表結(jié)構(gòu)如下
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的驅(qū)動(dòng)連接數(shù)據(jù)庫(kù)，返回結(jié)果如下：
9i數(shù)據(jù)庫(kù)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數(shù)據(jù)庫(kù)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驅(qū)動(dòng)，結(jié)果如下：
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];
結(jié)果是讓人困惑，時(shí)間怎么不見了？
對(duì)于該問(wèn)題，在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驅(qū)動(dòng)的問(wèn)題：
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();
}
或者在系統(tǒng)變量中使用參數(shù)-Doracle.jdbc.V8Compatible="true"，例如
java -Doracle.jdbc.V8Compatible="true" MyApp
結(jié)果如下：
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驅(qū)動(dòng)與時(shí)間不見了的問(wèn)題 回復(fù) 更多評(píng)論
2008-06-13 15:48 by
傳智播客ajax全套內(nèi)部視頻獨(dú)家發(fā)布,免費(fèi)下載
1.ajax 入門
2.ajax 原理
3.ajax 簡(jiǎn)單實(shí)例
4.ajax 無(wú)限級(jí)聯(lián)動(dòng)菜單
5.ajax 簡(jiǎn)易聊天室
6.ajax 開源框架簡(jiǎn)介
7.DWR 框架源碼分析一
8.DWR 框架源碼分析二
9.DWR 框架源碼分析三
10.DWR 框架源碼分析四
11.DWR框架源碼分析五
12.SSH + DWR完成商城驅(qū)動(dòng)
13. Extjs 簡(jiǎn)介
14 Extjs 簡(jiǎn)單實(shí)例
15.SSH + Extjs 開發(fā)系列之OA一
16. SSH + Extjs 開發(fā)系列之OA二
17. SSH + Extjs 開發(fā)系列之OA三
18. SSH + Extjs 開發(fā)系列之OA四
19 .SSH + Extjs 開發(fā)系列之OA五
20. SSH + Extjs 開發(fā)系列之OA六
21. SSH + Extjs 開發(fā)系列之OA七
22. SSH + Extjs 開發(fā)系列之OA八
23.SSH + Extjs 開發(fā)系列之OA九
24.SSH + Extjs 開發(fā)系列之OA十
25. ajax 前景之我見
下載地址: http://www.ibeifeng.com/read.php?tid=2338&u=5043
# re: Oracle JDBC驅(qū)動(dòng)與時(shí)間不見了的問(wèn)題 回復(fù) 更多評(píng)論
2008-06-13 17:20 by
學(xué)習(xí)了
# re: Oracle JDBC驅(qū)動(dòng)與時(shí)間不見了的問(wèn)題 回復(fù) 更多評(píng)論
2008-06-13 20:51 by
呵呵，對(duì)日期字段我的原則是允許的情況下，用字符串代替。
# re: Oracle JDBC驅(qū)動(dòng)與時(shí)間不見了的問(wèn)題 回復(fù) 更多評(píng)論
2008-06-15 14:14 by
原來(lái)如此，以前一直用8/9，現(xiàn)在準(zhǔn)備11，10比較少。
一直以來(lái)用字符串代替日期類型，一直擔(dān)心引起效率的下降特別是
時(shí)間作為條件時(shí)，字符串的比較和直接日期的比較不知道差多少？
# re: Oracle JDBC驅(qū)動(dòng)與時(shí)間不見了的問(wèn)題 回復(fù) 更多評(píng)論
2008-12-11 10:55 by
樓主，你好，我也遇到這個(gè)問(wèn)題，我的服務(wù)器是websphere，我想你所說(shuō)的
或者在系統(tǒng)變量中使用參數(shù)-Doracle.jdbc.V8Compatible="true"，例如
java -Doracle.jdbc.V8Compatible="true" MyApp
我本想這樣解決的
oracle驅(qū)動(dòng)缺少時(shí)分秒的解決辦法
應(yīng)用程序服務(wù)器 > server1 > 進(jìn)程定義 > Java 虛擬機(jī) > 定制屬性
下新建
-Doracle.jdbc.V8Compatible=true
Doracle.jdbc.V8Compatible=true
oracle.jdbc.V8Compatible=true
但這樣的話引起我們系統(tǒng)的RMI連接錯(cuò)誤，想知道你到底是怎么解決，你的msn是多少，謝謝了！
# re: Oracle JDBC驅(qū)動(dòng)與時(shí)間不見了的問(wèn)題 回復(fù) 更多評(píng)論
2008-12-13 09:32 by
我這里使用weblogic,直接在啟動(dòng)weblogic的startweblogic.cmd中，
java ******
后面加上-Doracle.jdbc.V8Compatible=true 就好了
websphere，呵呵，很久沒(méi)用了
# re: Oracle JDBC驅(qū)動(dòng)與時(shí)間不見了的問(wèn)題 回復(fù) 更多評(píng)論
2008-12-15 09:30 by
樓主你好，請(qǐng)問(wèn)你MSN多少，或者你加我也可以， yqwcn@hotmail.com，謝謝了
# re: Oracle JDBC驅(qū)動(dòng)與時(shí)間不見了的問(wèn)題 回復(fù) 更多評(píng)論
2008-12-30 13:17 by
好
# re: Oracle JDBC驅(qū)動(dòng)與時(shí)間不見了的問(wèn)題 回復(fù) 更多評(píng)論
2014-11-16 15:57 by
多謝
|