Oracle 9.2+ JDBC Driver DATE type problem
With Oracle 9.2 some changes regarding the DATE type and the new TIMESTAMP type were introduced. These are causing problems with the JDBC driver when an application is expecting a DATE column to contain time information. These issues and a workaround are discussed in this article from Oracle.
This article proposes a few workarounds that you might be able to apply or not. Today I encountered a case that was troubling me for a while.
I’m using Hibernate 3.2.5.ga to map a class to some legacy table containing a DATE column holding date + time information. The property mapping looks as simple as <property name="timestamp" column= "MESSAGE_TIMESTAMP"/ >.
I have configured Hibernate with hibernate.hbm2ddl.auto=validate so my mapping files are checked against database metadata. As a result Hibernate would throw an exception at me telling that it can’t match the column type with the POJO property type.
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in ServletContext resource [/WEB-INF/context-web.xml]: Invocation of init method failed; nested exception is org.hibernate.HibernateException: Wrong column type: COMM_MSGDT, expected: timestamp
Caused by: org.hibernate.HibernateException: Wrong column type: COMM_MSGDT, expected: timestamp
at org.hibernate.mapping.Table.validateColumns(Table.java:261)
...
If you don’t need time information you can get around this execption by tweaking the type information for the property mapping (<property name="timestamp" column="MESSAGE_TIMESTAMP" type="date"/>) otherwise if time is required then you are in trouble!
The Oracle article above is suggesting to set a system property (as a JVM start parameter) which is kind of difficult to handle during deployment. So I was investigating the other option which is to set the connection property (oracle.jdbc.V8Compatible=true) for the Oracle JDBC driver, this option would allow a deployment configuration that is working “out-of-the-box”. Reading the Tomcat documentation on how to configure a JNDI DataSource led me to the DBCP documentation that is explaining this thoroughly.
I’m using a Tomcat managed DataSource. So I’m having a META-INF/context.xml file to set up the DataSource. To fix the type problem just add the property connectionProperties.
