Avoid implicit conversion from date to timestamp for selects with Oracle 11g using Hibernate
- by sapporo
I'm using Hibernate 3.2.1 criteria queries to select rows from an Oracle 11g 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?
