2

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
Spacemonkey
  • 1,725
  • 3
  • 20
  • 44
  • The page you linked to shows the EXTRACT function was added to EclipsLink 2.4 while you state you are using 2.3.2. Try the later version. – Chris Sep 29 '12 at 19:53
  • @Chris Yes, you are right. I tried to work with the EclipseLink 2.4 but I was not able to apply the new functions. I have no idea what was wrong.. Did you use it? Does it works? Thanks – Spacemonkey Oct 02 '12 at 11:44
  • EclipseLink 2.4 nightly tests verify Extract works: – Chris Oct 03 '12 at 15:53
  • Can you provide details on what isn't working for you? – Chris Oct 03 '12 at 15:59
  • @Chris I have updated eclipselink to version 2.4.0 and I have tried to run my tests using `EXTRACT()` like this: `Query query = entityManager.createQuery("SELECT f FROM Failure f " + "WHERE EXTRACT(YEAR,f.fecha) = ?1 " + "AND f.moduleId.moduleId = ?2 ");` Error I get is: The expression is not a valid conditional expression [42,42] The date part must be specified [42,42] The left parenthesis is missing from the EXTRACT expression [43, 68] The query contains a malformed ending. – Spacemonkey Nov 07 '12 at 16:03
  • I retract myself. It is working, but I had to modify the code: `Query query = entityManager.createQuery("SELECT f FROM Failure f " + "WHERE SQL('EXTRACT (YEAR FROM ?)', f.fecha) = ?1 " + "AND f.moduleId.moduleId = ?2 ");` – Spacemonkey Nov 07 '12 at 16:27

5 Answers5

1

A native query is written in the SQL dialect of your DB so can use DB specific functionality see the createNativeQuery methods of the EntityManager.

However there is another solution, test the timestamp against a lower and upper value:

WHERE f.fecha >= '2012-9-1' AND f.fecha < '2012-10-1'
Eelke
  • 20,897
  • 4
  • 50
  • 76
  • Hello Eelke,thanks for your reply. Yes, I know the CreateNativeQuery but it returns usually an Object and then I should cast it to my specific object. I don't like this way. Finally I decided to use two dates in order to fetch a full month or a full year. Thanks for your reply and attention. – Spacemonkey Sep 29 '12 at 09:37
  • I consider that using Eclipselink functions in this case is better than compare between two dates. That's why I changed the correct answer, even when your solution worked fine. – Spacemonkey Nov 07 '12 at 16:31
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.

Spacemonkey
  • 1,725
  • 3
  • 20
  • 44
1

The syntax in EclipseLink 2.4 for EXTRACT is,

EXTRACT(YEAR, f.fecha)

http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#Functions

James
  • 17,965
  • 11
  • 91
  • 146
  • Can I do that in `WHERE` side of a `SELECT`? – Spacemonkey Nov 08 '12 at 14:43
  • Is not working for me. `Query query = entityManager.createQuery("SELECT f FROM Failure f " + "WHERE EXTRACT (YEAR, f.fecha) = ?1 " + "AND f.moduleId.moduleId = ?2 ");` I see few errors The left parenthesis is missing from the EXTRACT expression, The date part must be specified, The query contains a malformed ending. – Spacemonkey Nov 08 '12 at 14:51
  • I get this error now: `unexpected token [(]. Internal Exception: NoViableAltException(83@[()* loopback of 383:9: (d=DOT right=attribute)*]` Can be something related to Eclipselink libraries? – Spacemonkey Nov 26 '12 at 06:41
  • try EXTRACT(YEAR from f.fecha), the function usage mentioned in user guide is wrong, @see http://www.eclipse.org/eclipselink/api/2.4/org/eclipse/persistence/jpa/jpql/parser/ExtractExpression.html – mickey Nov 06 '15 at 01:39
1

Use 

Query query = entityManager.createQuery("SELECT f FROM Failure f "
        + "WHERE EXTRACT(YEAR from f.fecha) = ?1 "
        + "AND f.moduleId.moduleId = ?2");
vinayakshukre
  • 185
  • 1
  • 10
0

I was facing the same problem. I only checked for the correct syntax of that EXTRACT function for oracle and it worked for me! (Notice the FROM clause into the function syntax.

@NamedQuery(name = "Registros.findByFechacaptura", query = "SELECT s FROM Registros s WHERE EXTRACT(YEAR FROM s.fechacaptura) = EXTRACT(YEAR FROM :fechacaptura)")

Liz Castillo
  • 59
  • 1
  • 9