lunedì, marzo 01, 2010

HQL Date Comparison

It appeared rather complicated to perform a date comparison using HQL. The only way is to use the native functions provided by hibernate: day(), month(), year(), current_date().
Here the named query used in JUGEvents for the scheduler reminder:

query =
"from Participant p where p.event.id = ? and (p.winner = null or p.winner = false)"),
@NamedQuery(name = "Participant.findParticipantsToBeReminded",
query = "from Participant p where p.confirmed = true and (p.cancelled is null or p.cancelled = false) "+
"and p.reminderEnabled = true and p.reminderSentDate is null and p.event.numOfDaysReminder >= 0 "+
"and (day(p.event.startDate) - day(current_date())) <= p.event.numOfDaysReminder "+
"and month(p.event.startDate) = month(current_date()) and year(p.event.startDate) = year(current_date()) "+
"and p.event.startDate >= current_date() order by p.event.id")})