oracle 数据库,通过resultSet.getObject()获取时间类型截断的问题的解析(格式化时间不能完全输出,也就是只能到日,小时后面的没有了)
来源:互联网 发布:android游戏源码 编辑:程序博客网 时间:2024/05/16 14:26
一般的数据库中,DATE字段仅仅表示日期,不包括日期信息,而Oracle数据库中的DATE数据类型是包括日期、时间的,对于不同的Oracle jdbc驱动版本,对于该问题的处理都有些区别,如果你使用9i或者11g
的驱动程序,可能不会发现什么困惑,不幸的话,你使用Oracle10g的JDBC驱动,然后用ResultSet.getObject()得到各字段的值,问题就来了,你会发现时间不见了 ?
看下面的程序
• 表结构如下
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
16 public 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 }
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
16 public 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
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",例如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();
}
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];
- oracle 数据库,通过resultSet.getObject()获取时间类型截断的问题的解析(格式化时间不能完全输出,也就是只能到日,小时后面的没有了)
- Oracle数据库,resultSet.getObject获取时间类型问题的解析
- EL表达式获取日期时间类型后格式化的问题
- Python获取多少小时之前的时间并格式化
- android HH不能格式化时间的问题
- 读取数据库的时间格式时后面多了 .0
- 数据库获取时间的问题
- fmt 格式化时间的输出
- fortran-时间的格式化输出
- swift_将UIDatePicker获取到的时间传到下一个页面出现相差几个小时的问题
- oracle 时间相减获取具体的天数、小时、分钟
- 一个小小的问题耗费了两个小时的时间
- Java获取当前的系统时间并格式化输出
- java 日期的格式化、解析、数据库相关操作、获取系统时间、String与Date的转换
- php获取当前时间晚8个小时的问题
- java操作oracle数据库的时间类型
- java获取时间并加上2个小时,格式化输出
- 解析Excel超过24小时的时间
- 基于上下文访问控制(CBAC)介绍与分析
- 阻止js事件冒泡
- JVM调优总结
- 程序员面试宝典笔记6--位运算和嵌入式
- Remove PC Power Speed Scareware
- oracle 数据库,通过resultSet.getObject()获取时间类型截断的问题的解析(格式化时间不能完全输出,也就是只能到日,小时后面的没有了)
- hackthissite realistic mission 1
- CCS中如何生成LIB文件,并在另外CCS项目中引用LIB文件
- 路由器接口审查规则和ACL 分析
- 可以考虑使用goto语句的地方
- 形参和实参
- ABAP 中的几个表
- 多道程序设计:分离进程为独立功能
- MFC中Debug模式能够运行成功,但Release版本不能运行成功。