1 Reply Latest reply: Jun 28, 2012 2:35 PM by chris_delahunt RSS

    Date datatype mapping in JPA

      I would ask you what is the most properly method of mapping a Date column into an object attribute using JPA.

      I'm using Oracle DB 11g and EclipseLink 2.1.1 based on JPA 2.0.

      Suppose that my table has been created using the following SQL statement:

                UIDTEST NUMBER(19) PRIMARY KEY,
                MYDATE DATE,
                ... --other stuffs

      and there exists an index that uses MYDATE:

      The column MYDATE has to store both date and time, expressed in the format 'dd-mm-yyyy hh24:mi:ss'.

      So, I mapped the column in the following way in the class Jpatest:

           Date mydate;

           @Temporal( TemporalType.TIMESTAMP)
                public Date getMydate() {
                return this.mydate;

      This mapping works, but not in an optimized way: infacts, when I try to find rows filtering by MYDATE, JPA makes an implicit datatype conversion from Date to Timestamp, so the database doesn't use IX_JPATESTINDEX index.

      The query has been created in the following way:

           Query q = entityManager.createQuery("SELECT entity FROM " + entityClass.getSimpleName() + " entity"
                     + " AND entity.mydate <= :parameter ...");

      The Oracle Grid says that:

      The predicate "T1"."MYDATE"=:B1 used at line ID 4 of the execution plan contains an implicit data type conversion on indexed column "MYDATE". This implicit data type conversion prevents the optimizer from efficiently using indices on table "JPATEST".

      How can I fix the situation, allowing the database to use the index and without make any changes to the Table definition and to the core Java code?

      Thank you,
        • 1. Re: Date datatype mapping in JPA

          I'm not sure this is the best forum to deal with this question. While you are using JPA, the problem seems to be at the JDBC/database level which is doing the conversion between Java types to Oracle types, as described here:

          If using the 10g drivers, the "V8Compatible" property may help. But if not, I cannot suggest much without modifying the table column type to TIMESTAMP and rebuilding the index or using Oracle.sql.Date in your object directly instead.

          Best Regards,