Fetch dates (by month or year) stored in TIMESTAMP using JPA

Question

I am facing a problem and I would like you to help me.

It turns out I have one table in my Oracle 11g database where I store failures of one electronic device. The table definition is following:

CREATE TABLE failure
( failure_id NUMERIC NOT NULL
, fecha TIMESTAMP NOT NULL
, module_id NUMERIC NOT NULL
, code NUMERIC
, PRIMARY KEY(failure_id)
);

Where 'fecha' means 'date'.

I need to fetch failures by YEAR or by MONTH for one specific module but I can't. My ORM maps the TIMESTAMP type to java.sql.Date but I don't know how to compare the month in the JPQL sentence. I have tried to use ORACLE functions with native queries but I front with another issue: to cast the results. I am using JPA 2.0 with Eclipselink 2.3.2.

My doubts are:

Can I use Oracle functions with this version of Eclipselink library? My experience say no.

Query query = entityManager.createQuery("SELECT f FROM Failure f "
            + "WHERE EXTRACT(YEAR FROM f.fecha) = ?1 "
            + "AND f.moduleId.moduleId = ?2");
    query.setParameter(1, year);
    query.setParameter(2, idModule);

I get this error: Unexpected token [(]

Can I use Eclipselink functions? My experience say no.

Query query = entityManager.createQuery("SELECT f FROM Failure f "
            + "WHERE EXTRACT('YEAR', f.fecha) = ?1 "
            + "AND f.moduleId.moduleId = ?2");
    query.setParameter(1, year);
    query.setParameter(2, idModule);

Same error.

Do you know a simple way to fetch this data using only one query? I know I can fetch one module and then check failures with loops but I think it is not the best performing solution.

Thanks.

My sources:

• Eclipselink JPA functions link
• Eclipselink Query Enhancements link

Answer 1

I used Eclipselink v 2.4 functions and I am getting values using this:

Query query = entityManager.createQuery("SELECT f FROM Failure f "
                + "WHERE SQL('EXTRACT (YEAR FROM ?)', f.fecha) = ?1 "
                + "AND f.moduleId.moduleId = ?2 ");

Extracting year from date stored in database avoids to me one comparison between two dates.