Clever Geek Handbook

Compare two dates with JPA

I need to compare two dates in a JPQL query, but that doesn't work.

Here is my request:

Query query = em.createQuery(
    "SELECT h 
     FROM PositionHistoric h, 
          SeoDate d 
     WHERE h.primaryKey.siteDb = :site 
     AND h.primaryKey.engineDb = :engine 
     AND h.primaryKey.keywordDb = :keyword 
     AND h.date = d 
     AND d.date <= :date 
     ORDER BY h.date DESC");`

My parameter date is java.util.Date

My query is returning a list of objects, but the dates are top and bottom for my parameter.

Does anyone know how to do this?

Thank.

+2


source to share


2 answers


The query you should use is:

 FROM PositionHistoric h
   INNER JOIN FETCH h.date
WHERE h.primaryKey.siteDb = :site
  AND h.primaryKey.engineDb = :engine
  AND h.primaryKey.keywordDb = :keyword
  AND h.date.date <= :date


Note that ordering by using is h.date

rather pointless (since it points to the SeoDate entity and hence you are ordering PK) and you cannot order h.date.date

since SeoDate is displayed as many-to-one

.

Also note that the string is INNER JOIN FETCH h.date

not needed by itself, but it will save you some overhead of additional lazy queries if you really need to use the SeoDate attributes in the returned list.

+2


source


I suggest using java.sql.Date instead of java.util.Date

Because you are passing it to JPQL (like SQL).



I usually use TIMESTAMP instead of Date, but if you had no choice.

And this: h.date = d.date instead of h.date = d?

0


source






More articles:


All Articles