Java JPA Queries

Question

i'm trying to select all enteties from a databse where a certain date is older than 7 days. It works fine via SQLyog, but in Java it always throws this error:

[33, 76] The expression is not a valid conditional expression.
[76, 101] The query contains a malformed ending.

This is my query in Java:

SELECT a FROM Applicants a WHERE (a.lastMod <= CURRENT_DATE - INTERVAL 7 DAY) ORDER BY a.applDate ASC

May the problem be the "CURRENT_DATE"-part?

Answer 1

CURRENT_DATE is ok, but INTERVAL 7 DAY is not a valid JPQL expression. You'll need to supply the date as parameter

WHERE a.lastMod <= :dateParam

Example:

Query q = em.createQuery("SELECT a FROM Applicants a WHERE a.lastMod <= :dateParam ORDER BY a.applDate ASC");
q.setParameter("dateParam", dateParam);
List<Applicants> applicants = (List<Applicants>)q.getResultList();
// or, to avoid casting (thanks to @DavidSN)
TypedQuery<Applicants> q = em.createQuery("SELECT a FROM Applicants a WHERE a.lastMod <= :dateParam ORDER BY a.applDate ASC", Applicants.class);
q.setParameter("dateParam", dateParam);
List<Applicants> applicants = q.getResultList();

• Denis Hartwood: I never worked with parameters. Can you please supply an example?
• David SN: The example produces a unchecked cast warning. You have the option to use the typesafe version of the createQuery method: TypedQuery<Applicants> q = em.createQuery("select ...", Applicants.class); and gets the results without the List<Applicants> cast: List<Applicants> applicants = q.getResultList();

Answer 2

EntityManager em = ...
Query q = em.createQuery ("SELECT a FROM Applicants a WHERE a.lastMod <= :dateParam");
q.setParameter("dateParam" , dateParam);
List<blabla> results = q.getResultList ();