Hibernate Community • View topic - Oracle 11G and Timestamp

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Oracle 11G and Timestamp - Date mapping

Page 1 of 1

[ 3 posts ]

Previous topic | Next topic

Author

Message

rgun

Post subject: Oracle 11G and Timestamp - Date mapping

Posted: Tue Nov 24, 2009 7:21 am

Newbie

Joined: Tue Nov 24, 2009 6:43 am
Posts: 2

Hi,

We are using Hibernate 3.3.1 GA. In our DB schema we have lots of tables with column type Oracle Date. In Java these columns are mapped to java.util.Date (We are only interested up to second precision)

We recently migrated to Oracle 11g, we noticed that some of our queries are not using the Indexes anymore. It turns out this is because these columns are mapped to java.sql.Timestamp which gets mapped to Oracle TIMESTAMP type rather than Date. This causes it to not to use the Indexes on columns type Date.

From what I can gather Hibernate maps java.util.Date to java.sql.Timestamp. Then its upto the JDBC driver to map this to the database type. Is there anything I can do in hibernate to change this mapping ?

Looking at Hibernate source code I noticed in the Dialect there are type mappings. So I tried changing it from

registerColumnType(Types.TIMESTAMP, "timestamp");

registerColumnType(Types.TIMESTAMP, "date");

which I believe is a change made for http://opensource.atlassian.com/project ... se/HB-1049 in 3.0 but have no effect. I couldn't see it using this mapping when I do a query anyway. Is this only used for generating the DDL schema from mappings ?

If we downgrade the Oracle JDBC driver to 10g with the flag -Doracle.jdbc.V8Compatible=true it works fine. But on 11G driver the flag -Doracle.jdbc.V8Compatible is deprecated and doesn't seems to have any effect. But this is not ideal.

Any help greatly appreciated.

Regards,
Roshan

Top

dan.baumann

Post subject: Re: Oracle 11G and Timestamp - Date mapping

Posted: Wed Mar 17, 2010 12:39 pm

Newbie

Joined: Thu Feb 24, 2005 6:19 am
Posts: 9

I have the exact same problem and would be very interested in a solution or workaround. Thanks!

Top

rgun

Post subject: Re: Oracle 11G and Timestamp - Date mapping

Posted: Wed Mar 17, 2010 1:58 pm

Newbie

Joined: Tue Nov 24, 2009 6:43 am
Posts: 2

The workaround is you create a new Hibernate type that explicitly map it to an Oracle DATE type. Then change the hibernate mappings to use this type when mapping java.util.Date columns.

In theory you should be able to have a package level annotation to do this mapping change. But I haven't quite got it working yet.

Code:

public class OracleDateType extends TimestampType {

    @Override
    public void set(PreparedStatement st, Object value, int index) throws SQLException {
        Timestamp ts;
        if (value instanceof Timestamp) {
            ts = (Timestamp) value;
            st.setTimestamp(index, ts);
        } else {
            ts = new Timestamp(((java.util.Date) value).getTime());
            st.setObject(index, new oracle.sql.DATE(ts));
        }

    }
}

In theory this should work if you put this in package-info.java - but I am yet to get it working, so I am missing something :-(

Code:

@TypeDefs(
    {
    @TypeDef(
        name="java.util.Date",
        typeClass = oracle.sql.DATE.class
    )
    }
)

/Roshan

Top

Page 1 of 1

[ 3 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum