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.

1
2
3
4
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%20Configurations) 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.

1
2
3
4
5
<resource name="jdbc/dsname" auth="Container" type="javax.sql.DataSource"
  driverClassName="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@host:1526:SID" username="user"
  password="password" maxActive="20" maxIdle="10" maxWait="-1"
  connectionProperties="oracle.jdbc.V8Compatible=true;"
/>

Comments