CODEWITHSUNDEEP
javajpajpql
Kiva
Kiva

Reputation: 9353

Compare two dates with JPA

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

Here is my query:

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 a java.util.Date

My query return a objects list but the dates are upper and lower to my parameter.

Someone kown how to do this ?

Thanks.

Upvotes: 3

Views: 38235

Answers (2)

ChssPly76
ChssPly76

Reputation: 100686

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 h.date is rather pointless (as it points to SeoDate entity and thus in effect you're ordering by PK) and you can't order by h.date.date since SeoDate is mapped as many-to-one.

Also note that INNER JOIN FETCH h.date line is not necessary per se but it will save you some overhead on additional lazy queries IF you will actually need to use SeoDate attributes in the returned list.

Upvotes: 2

Nettogrof
Nettogrof

Reputation: 2136

I suggest to use java.sql.Date instead of java.util.Date

Because you pass it to a JPQL(kind of SQL).

I usually use TIMESTAMP instead of Date, but if you didn't have the choice..

And is it : h.date = d.date instead h.date = d ?

Upvotes: 0

Related Questions