Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I query "older than three minutes" in JPA?

Tags:

Is there a way to put a date range condition referencing the current time in a JPA query (that is compatible across databases)?

I can do

SELECT m FROM Mail m WHERE m.sentAt < :date

but I want to do it without having to bind a parameter (so that this can be configured as part of the named query repository and the date calculation logic does not have to enter the calling code).

So instead of :date I need "currentTime minus 3 minutes" as a hard-code literal.

like image 227
Thilo Avatar asked Aug 29 '13 07:08

Thilo


People also ask

How do I limit a JPA query?

Limiting query results in JPA is slightly different to SQL; we don't include the limit keyword directly into our JPQL. Instead, we just make a single method call to Query#maxResults, or include the keyword first or top in our Spring Data JPA method name. As always, the code is available over on GitHub.


1 Answers

JPA supports the CURRENT_TIMESTAMP and CURRENT_TIME functions.

https://en.wikibooks.org/wiki/Java_Persistence/JPQL#Functions

The JPA spec does not have date functions, but some JPA providers such as EclipseLink do.

http://java-persistence-performance.blogspot.com/2012/05/jpql-vs-sql-have-both-with-eclipselink.html

JPA 2.1 also defines the FUNCTION operator to call database functions.

In EclipseLink I would try,

SELECT m FROM Mail m WHERE m.sentAt < SQL("(sysdate - interval '3' minute)")

or,

SELECT m FROM Mail m WHERE m.sentAt < SQL("(? - interval '3' minute)", CURRENT_TIMESTAMP)
like image 102
James Avatar answered Oct 22 '22 05:10

James