Java – Avoid implicit conversion from date to timestamp for selects with Oracle using Hibernate

hibernatejavaoracle

I'm using Hibernate 3.2.7.GA criteria queries to select rows from an Oracle Enterprise Edition 10.2.0.4.0 database, filtering by a timestamp field. The field in question is of type java.util.Date in Java, and DATE in Oracle.

It turns out that the field gets mapped to java.sql.Timestamp, and Oracle converts all rows to TIMESTAMP before comparing to the passed in value, bypassing the index and thereby ruining performance.

One solution would be to use Hibernate's sqlRestriction() along with Oracle's TO_DATE function. That would fix performance, but requires rewriting the application code (lots of queries).

So is there a more elegant solution? Since Hibernate already does type mapping, could it be configured to do the right thing?

Update: The problem occurs in a variety of configurations, but here's one specific example:

Oracle Enterprise Edition 10.2.0.4.0
Oracle JDBC Driver 11.1.0.7.0
Hibernate 3.2.7.GA
Hibernate's Oracle10gDialect
Java 1.6.0_16

Best Answer

This might sound drastic, but when faced with this problem we ended up converting all DATE columns to TIMESTAMP types in the database. There's no drawback to this that I can see, and if Hibernate is your primary application platform then you'll save yourself future aggravation.

Notes:

The column types may be changed with a simple "ALTER tableName MODIFY columnName TIMESTAMP(precisionVal)".
I was surprised to find that indexes on these columns did NOT have to be
rebuilt.

Again, this only makes sense if you're committed to Hibernate.

Related Solutions

Java – Creation timestamp and last update timestamp with Hibernate and MySQL

If you are using the JPA annotations, you can use @PrePersist and @PreUpdate event hooks do this:

@Entity
@Table(name = "entities")    
public class Entity {
  ...

  private Date created;
  private Date updated;

  @PrePersist
  protected void onCreate() {
    created = new Date();
  }

  @PreUpdate
  protected void onUpdate() {
    updated = new Date();
  }
}

or you can use the @EntityListener annotation on the class and place the event code in an external class.

Java – How to print a query string with parameter values when using Hibernate

You need to enable logging for the the following categories:

org.hibernate.SQL - set to debug to log all SQL DML statements as they are executed
org.hibernate.type - set to trace to log all JDBC parameters

So a log4j configuration could look like:

# logs the SQL statements
log4j.logger.org.hibernate.SQL=debug 

# Logs the JDBC parameters passed to a query
log4j.logger.org.hibernate.type=trace

The first is equivalent to hibernate.show_sql=true legacy property, the second prints the bound parameters among other things.

Another solution (non hibernate based) would be to use a JDBC proxy driver like P6Spy.

Best Answer

Related Solutions

Java – Creation timestamp and last update timestamp with Hibernate and MySQL

Java – How to print a query string with parameter values when using Hibernate

Related Topic