How to query a date in HQL (Hibernate) with Joda Time?

Question

I am sure that someone familiar with HQL (I am myself a newbie) can easily answer this question.

In my Grails application, I have the following domain class.

class Book {
  org.joda.time.DateTime releaseDate //I use the PersistentDateTime for persisting via Hibernate (that use a DATETIME type for MySQL DB)
}

In my HQL query, I want to retrieve books whose release date is included in range date1..date2

For instance I tried:

DateTime date1, date2
... 
def queryStr = "select * from Book as b where b.releaseDate > $date1 and b.releaseDate < $date2" 
def res = Book.executeQuery(queryStr)

But I got the exception ...caused by: org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: The error token points to date format (for instance 2009-11-27T21:57:18.010+01:00 or Fri Nov 27 22:01:20 CET 2009)

I have also tried to convert date1 into a Date class without success

So what is the correct HQL code ? Should I convert to a specific format (which one?) using the patternForStyle method or is there another -cleaner- way to do it?

Thanks,

Fabien.

Answer 1

I'm no Grails expert, but in java you'd normally make date1 and date2 query parameters and bind them as such:

String hql = "select * from Book as b where b.releaseDate > :date1 and b.releaseDate < :date2";
session.createQuery(hql).setDate("date1", date1).setDate("date2", date2).list();

I'm sure you can do something similar in Grails. If not, format your dates as yyyyMMddhhmmss (no spaces) and enclose them in single quotes - that way Hibernate would treat them as constants and MySQL will implicitly convert them to dates.