CODEWITHSUNDEEP
javaoracle-databasedatejpa
Uluk Biy
Uluk Biy

Reputation: 49185

How to convert correctly an Oracle Date field into java.util.Date through JPA?

I have an Oracle Date column in a db table, and fetching this field by,

EntityManagerFactory emf = Persistence.createEntityManagerFactory("dbPU");
EntityManager em = emf.createEntityManager();

DateFormat dateFormat = new SimpleDateFormat("dd.MM.yyyy, HH:mm");
String query = "SELECT T.MDATE FROM MTABLE T";

List<Object[]> resultList = em.createNativeQuery(query).getResultList();
for (Object[] data : resultList) {
    System.out.println(dateFormat.format((java.util.Date) data[0]));
}

The "date" part of printed dates are correct but the "time" (ie. hour and second) parts are "00:00" for all of them. However when I checked this field in SQL Developer, the actual "time" parts are different than "00:00". Where is the problem? Thanks in advance.

EDIT: I am using,

Hibernate-Version: 3.2.5.ga
hibernate.dialect = org.hibernate.dialect.Oracle10gDialect
Oracle JDBC Driver version 10.2.0.1.0
Oracle Database 11g Express Edition Release 11.2.0.2.0

Upvotes: 2

Views: 13685

Answers (1)

Uluk Biy
Uluk Biy

Reputation: 49185

Ok I finally resolved my problem. The cause was the adventure of Date (and Timestamp) datatype mappings of Oracle jdbc driver through its version timeline. In short, the problem was resolved by upgrading the ojdbc14.jar driver to ojdbc6.jar. For more info refer to What is going on with DATE and TIMESTAMP?

In other words, the Oracle DATE type is mapped to Java as follows:
ojdbc14.jar : DATE > java.sql.Date
ojdbc6.jar : DATE > java.sql.Timestamp
where java.sql.Date has no time parts.

EDIT 17.03.2012 :
If there is no chance to upgrade the driver due to other restrictions, then here is workaround for mapping Oracle DATE to java.sql.Timestamp in JPA with Hibernate: 

String queryStr = "SELECT T.MDATE FROM MTABLE T";
Query query = em.createNativeQuery(queryStr);
SQLQuery sqlQuery = (SQLQuery) ((HibernateQuery) query).getHibernateQuery();
sqlQuery.addScalar("MDATE", Hibernate.TIMESTAMP);
List<Object[]> resultList = query.getResultList();

Upvotes: 6

Related Questions