Integration

Hi,
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:

CREATE TABLE JPATEST
(
UIDTEST NUMBER(19) PRIMARY KEY,
MYDATE DATE,
... --other stuffs
);

and there exists an index that uses MYDATE:
CREATE INDEX IX_JPATESTINDEX ON JPATEST(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,
Alessandro