- 浏览: 1130786 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (411)
- Java Foundation (41)
- AI/机器学习/数据挖掘/模式识别/自然语言处理/信息检索 (2)
- 云计算/NoSQL/数据分析 (11)
- Linux (13)
- Open Source (12)
- J2EE (52)
- Data Structures (4)
- other (10)
- Dev Error (41)
- Ajax/JS/JSP/HTML5 (47)
- Oracle (68)
- FLEX (19)
- Tools (19)
- 设计模式 (4)
- Database (12)
- SQL Server (9)
- 例子程序 (4)
- mysql (2)
- Web Services (4)
- 面试 (8)
- 嵌入式/移动开发 (18)
- 软件工程/UML (15)
- C/C++ (7)
- 架构Architecture/分布式Distributed (1)
最新评论
-
a535114641:
LZ你好, 用了这个方法后子页面里的JS方法就全不能用了呀
页面局部刷新的两种方式:form+iframe 和 ajax -
di1984HIT:
学习了,真不错,做个记号啊
Machine Learning -
赵师傅临死前:
我一台老机器,myeclipse9 + FB3.5 可以正常使 ...
myeclipse 10 安装 flash builder 4.6 -
Wu_Jiang:
触发时间在将来的某个时间 但是第一次触发的时间超出了失效时间, ...
Based on configured schedule, the given trigger will never fire. -
cylove007:
找了好久,顶你
Editable Select 可编辑select
java Date(util.Date/sql.Date/sql.Timestamp/sql.Time) & Oracle DATE Type 时分秒 精度问题
- 博客分类:
- Java Foundation
遇到的问题描述:
数据库为Oracle,其jdbc驱动为ojdbc14.jar。打算用Hibernate原生SQL通过setResultTransformer()的方式将查询结果赋给不被Hibernate管理的bean:
如果使用的是HQL(或还是原生sql,只不过使用addEntity(Class clazz)的方式(这个方法接受的参数clazz应是被hibernate管理的持久化bean,否则会抛异常)),则避免这种精度丢失的方式有:
1 XML元数据的方式配置Hibernate: 将返回日期类型(且想保留时分秒)的列在XML配置文件中指明其type为Timestamp或java.util.Date;
2 注解方式配置Hibernate:在日期类型的get方法上使用@Temporal注解:
1 使用addScalar()为返回的列指定其java bean对应的类型:
2 原生sql语句中使用oracle的to_char()将Oracle内置数据类型DATE转换为varchar后在bean中用String类型接收它。
3 其他几个牵动比较大的解决办法(具体原因见下文论述):在使用Oracle 9.2 - 10.2 jdbc驱动的时候,数据库类型别用DATE,而用TIMESTAMP;设置数据连接属性V8Compatible为true;变更oracle驱动,使用11g的jdbc驱动。
造成时分秒精度丢失的根本原因是什么那?是Oracle 驱动的问题!
在Oracle9.2之后,引入了内置数据类型TIMESTAMP。之所以引入它,是因为内置数据类型DATE的最小单位为秒;DATE的主要问题是它粒度不能足够区别出两个事件哪个先发生。9.2版本后ORACLE在DATE数据类型上扩展出来了TIMESTAMP数据类型,它包括了所有DATE数据类型的年月日时分秒的信息,而且包括了小数秒(纳秒Nanoseconds级的)的信息。如果你想把DATE类型转换成TIMESTAMP类型,就使用CAST函数。
也正是从oracle 9.2开始,内置数据类型DATE和TIMESTAMP在使用9i的JDBC驱动做查询时,DATE被映射为java.sql.Date,TIMESTAMP被映射为java.sql.Timestamp。
幸运的是,从Oracle11开始,其JDBC驱动程序又重新开始回归为将内置类型DATE映射为java.sql.Timestamp(正如9.2之前的那样)。
综上,时分秒精度的丢失与hibernate无关,是oracle jdbc驱动的问题。最好的解决办法就是:换驱动。经测试,将最开始使用的10g的驱动ojdbc14.jar换为11g的驱动ojdbc5.jar后,时分秒精度丢失的问题成功解决!
英文原文:
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_00
oracle 9/10的jdbc驱动将内置数据类型DATE映射为java.sql.Date,为什么会造成时分秒精度的丢失那?
答:java.sql.Date can not hold time,but java.util.Date can。
JDK中的说法(下贴三楼):
http://stackoverflow.com/questions/383783/oracle-sql-date-conversion-problem-using-ibatis-via-java-jdbc
java.sql.Date is not a real date:
http://www.thunderguy.com/semicolon/2003/08/14/java-sql-date-is-not-a-real-date/
Native Query的自定义转换器:
http://www.cnblogs.com/highriver/archive/2011/03/03.html
数据库为Oracle,其jdbc驱动为ojdbc14.jar。打算用Hibernate原生SQL通过setResultTransformer()的方式将查询结果赋给不被Hibernate管理的bean:
public List<FidsDepfDto> queryAllFidsDepfDto() { String sql = "select id as \"id\",iata as \"iata\",flight as \"flight\",dest as \"dest\",domint as \"domint\" " + " ,sdt as \"sdt\",task_nature as \"taskNature\",est_date as \"estDate\",act_date as \"actDate\",remark as \"remark\" " + " from FIDS_DEPF"; return this.getSessionFactory().getCurrentSession().createSQLQuery(sql) .setResultTransformer(Transformers.aliasToBean(FidsDepfDto.class)) .list();结果发现:返回的FidsDepfDto list中所有FidsDepfDto对象的时间字段sdt/estDate/actDate都丢失了时分秒精度。
如果使用的是HQL(或还是原生sql,只不过使用addEntity(Class clazz)的方式(这个方法接受的参数clazz应是被hibernate管理的持久化bean,否则会抛异常)),则避免这种精度丢失的方式有:
1 XML元数据的方式配置Hibernate: 将返回日期类型(且想保留时分秒)的列在XML配置文件中指明其type为Timestamp或java.util.Date;
2 注解方式配置Hibernate:在日期类型的get方法上使用@Temporal注解:
@Temporal(TemporalType.TIMESTAMP) @Column(name = "UDATE", length = 7) public Date getUdate() { return this.udate; }但这里因为是想使用setResultTransformer()将原生SQL的查询结果赋给不被Hibernate管理的bean,所以上面的方式用不了。解决办法这里列举几个:
1 使用addScalar()为返回的列指定其java bean对应的类型:
String sql = "select id as \"id\",iata as \"iata\",flight as \"flight\",dest as \"dest\",domint as \"domint\" " + " ,sdt as \"sdt\",task_nature as \"taskNature\",est_date as \"estDate\",act_date as \"actDate\",remark as \"remark\" " + " from FIDS_DEPF"; return this.getSessionFactory().getCurrentSession().createSQLQuery(sql) .addScalar("sdt", Hibernate.TIMESTAMP) //必须为所有的select返回列都指定addScalar() //否则未指定的列所映射的属性值会为null //这里省略其他select返回列的addScalar()操作 .setResultTransformer(Transformers.aliasToBean(FidsDepfDto.class)) .list();
2 原生sql语句中使用oracle的to_char()将Oracle内置数据类型DATE转换为varchar后在bean中用String类型接收它。
3 其他几个牵动比较大的解决办法(具体原因见下文论述):在使用Oracle 9.2 - 10.2 jdbc驱动的时候,数据库类型别用DATE,而用TIMESTAMP;设置数据连接属性V8Compatible为true;变更oracle驱动,使用11g的jdbc驱动。
造成时分秒精度丢失的根本原因是什么那?是Oracle 驱动的问题!
在Oracle9.2之后,引入了内置数据类型TIMESTAMP。之所以引入它,是因为内置数据类型DATE的最小单位为秒;DATE的主要问题是它粒度不能足够区别出两个事件哪个先发生。9.2版本后ORACLE在DATE数据类型上扩展出来了TIMESTAMP数据类型,它包括了所有DATE数据类型的年月日时分秒的信息,而且包括了小数秒(纳秒Nanoseconds级的)的信息。如果你想把DATE类型转换成TIMESTAMP类型,就使用CAST函数。
也正是从oracle 9.2开始,内置数据类型DATE和TIMESTAMP在使用9i的JDBC驱动做查询时,DATE被映射为java.sql.Date,TIMESTAMP被映射为java.sql.Timestamp。
幸运的是,从Oracle11开始,其JDBC驱动程序又重新开始回归为将内置类型DATE映射为java.sql.Timestamp(正如9.2之前的那样)。
综上,时分秒精度的丢失与hibernate无关,是oracle jdbc驱动的问题。最好的解决办法就是:换驱动。经测试,将最开始使用的10g的驱动ojdbc14.jar换为11g的驱动ojdbc5.jar后,时分秒精度丢失的问题成功解决!
英文原文:
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#08_00
引用
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:
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.
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.
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.
oracle 9/10的jdbc驱动将内置数据类型DATE映射为java.sql.Date,为什么会造成时分秒精度的丢失那?
答:java.sql.Date can not hold time,but java.util.Date can。
JDK中的说法(下贴三楼):
http://stackoverflow.com/questions/383783/oracle-sql-date-conversion-problem-using-ibatis-via-java-jdbc
引用
To conform with the definition of SQL DATE, the millisecond values wrapped by a java.sql.Date instance must be 'normalized' by setting the hours, minutes, seconds, and milliseconds to zero in the particular time zone with which the instance is associated.
java.sql.Date is not a real date:
http://www.thunderguy.com/semicolon/2003/08/14/java-sql-date-is-not-a-real-date/
引用
java.sql.Date stores only date information, not times. Simply converting a java.util.Date into a java.sql.Date will silently set the time to midnight. So, to store date/times to be manipulated as java.util.Date objects
Native Query的自定义转换器:
http://www.cnblogs.com/highriver/archive/2011/03/03.html
发表评论
-
J2SE Evolution
2013-04-11 15:39 1161Java 7 New Features Java SE 7 ... -
未完 Java: IO & NIO(new I/O)
2013-01-11 20:56 1993适用: event and data-driven apps ... -
未完 java设计: naming convention | 命名规范
2012-11-20 16:45 2096应该遵循的规范: 类/接口/属性名,使用名词或形容词 ... -
未完 Java: enum 枚举
2012-11-19 20:29 1766http://stackoverflow.com/que ... -
Java多线程之 concurrent 并发包
2012-11-01 07:47 1959Java Tutorials -> Concur ... -
未完 Java Tips & Tricks & Notes
2012-09-12 10:00 1081Hidden Features of Java: h ... -
未完 Java Socket
2012-09-12 08:42 981Java SocketJava SocketJava Sock ... -
Java For-each Loop & Iterable | 增强型For循环和Iterable接口
2012-09-11 21:50 2007增强型For循环没什么好说的,Just see link ... -
未完 Java Collections | 容器
2012-09-06 11:35 1795Sources: http://docs.oracle.com ... -
Java object Initialization (class Instantiation) | 对象的初始化(即类的实例化)
2012-09-03 09:12 2950类实例即对象 ... -
未完Java class&interfac 's Loading, Linking and Initializing | 类与接口的加载、链接和初始化
2012-08-31 19:01 1620JVM装载一个类的时候,首先检查他有没有父类,如果有父类则装载 ... -
未完 java Static 总结
2012-08-31 18:47 1367static可以用来修饰: 字段 Fields 方法 Meth ... -
未完 JVM Runtime Data Areas & Java Memory Model | 内存分配模型 & Java数据存储
2012-08-31 18:43 1849Java虚拟机内存分配模型 需精读:Chapter 5 of ... -
Java Data Types & Literals | 数据类型 和 字面量
2012-08-30 18:12 3860Java数据类型划分: OR http:// ... -
未完 Variables 变量 (Instance/Class/Local)
2012-08-29 10:59 1647Local/Instance/Class Variables ... -
未完 Regular Expressions | 正则表达式
2011-08-25 11:43 1488Extended Regular Expression ... -
Java byte code (bytecode)
2011-05-04 02:55 3843keys: bytecode, byte code, opco ... -
Java Classloading Mechanism : ClassLoader & ASM & 动态字节码增强
2011-04-21 13:29 2384Setting the class path: http:// ... -
class literal & instance.getClass() & Class.forName(String className)
2011-04-20 12:33 2294常用的几种取得Class类实例的方式: 1 class lit ... -
Java 数值计算
2011-04-20 02:44 2059java.lang.Math's floor(),ceil() ...
相关推荐
java.util.Date与java.sql.Date互转及字符串转换为日期时间格式.docx
详细介绍java.util.Date和java.sql.Date相互转换的多种方法总结,希望对大家有帮助
java_sql_Date与java_util_Date转换
java中util.date和sql.date的类型转换问题.帮助大家很完善的了解关于java日期类型和数据库日期类型转换的问题,很值得拥有.
类型转换 ( Java.util.date与java.sql.date区别和转换
java.sql.date与java.util.date.pdf
Java的java.util.Date类是Java初的时间类之一。该类的大部分方法已不推荐使用,取而代之的是java.util.Calendar类。不过你仍然可以使用java.util.Date类去表示某个时间。下面是一个如何实例化java.util.Date的例子:...
JavaMail的java.lang.NoClassDefFoundError: com/sun/mail/util/LineInputStream错误 原因: MyEclipse6.5的javaee.jar中的mail包与JavaMail包有冲突。 解决: 在MyEclipse目录下(D:\Program Files\MyEclipse ...
java.util.ConcurrentModificationException 异常问题详解1
此java文件包括常用的日期类型之间的转换,包括java.util.Date、java.sql.Date、String之间的任意类型、格式转换......
本文通过对数据压缩算法的简要介绍,然后以详细的示例演示了利用java.util.zip包实现数据的压缩与解压,并扩展到在网络传输方面如何应用java.util.zip包现数据压缩与解压
1. java.util.concurrent - Java 并发工具包 2. 阻塞队列 BlockingQueue 3. 数组阻塞队列 ArrayBlockingQueue 4. 延迟队列 DelayQueue 5. 链阻塞队列 LinkedBlockingQueue 6. 具有优先级的阻塞队列 ...
详细介绍了java.util.logging.Logger的用法和结构,对如果扩展Logger起到抛砖引玉的作用!尊重劳动成果,亲下载了要给个评价!
世界范围内的时区列表。由 java.util.TimeZone 类导出
Tomcat内存溢出的解决方法(java.util.concurrent.ExecutionException:java.lang.OutOfMemoryError),内附解决方案!
java.util.Date到Json日期的转换