How to match timestamp with JPA request?

Question

We need to make sure that only results from the last 30 days are returned for a JPQL query. The following is an example:

Date now = new Date(); Timestamp thirtyDaysAgo = new Timestamp(now.getTime() - 86400000*30); Query query = em.createQuery( "SELECT msg FROM Message msg "+ "WHERE msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > {ts, '"+thirtyDaysAgo+"'}"); List result = query.getResultList(); 

Here is the error received:

  <openjpa-1.2.3-SNAPSHOT-r422266: 907835 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: An error occurred while parsing the query filter 'SELECT msg FROM BroadcastMessage msg WHERE msg.targetTime <CURRENT_TIMESTget AND msg. > {ts, '2010-04-18 04:15: 37.827'} '.  Error message: org.apache.openjpa.kernel.jpql.TokenMgrError: Lexical error at line 1, column 217. Encountered: "{" (123), after: "" 

Help!

Answer 1

So the query you enter is not JPQL (which you could see referring to the JPA specification). If you want to compare a field with a date, you enter Date as a parameter in the query

 msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > :param 

THIS IS NOT SQL.

Answer 2

The JDBC-escape syntax is not supported in the version of OpenJPA you are using. The documentation for the latest version 1.2.x is here: http://openjpa.apache.org/builds/1.2.2/apache-openjpa-1.2.2/docs/manual/manual.html#jpa_langref_lit .

The documentation mentioned earlier relates to documents for OpenJPA 2.0.0 (latest): http://openjpa.apache.org/builds/latest/docs/manual/jpa_langref.html#jpa_langref_lit

It is said, is there any reason why you want to insert a string into your JPQL? What about the next snippet?

 Date now = new Date(); Date thirtyDaysAgo = new Date(now.getTime() - (30 * MS_IN_DAY)); Query q = em.createQuery("Select m from Message m " + "where m.targetTime < :now and m.targetTime > :thirtyDays"); q.setParameter("now", now); q.setParameter("thirtyDays", thirtyDaysAgo); List<Message> results = (List<Message>) q.getResultList();