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.

Answer 2

I pointed out by ChssPly, you should declare date1 and date2 as query parameters (positional or named) and then bind them. Here, we'll use named parameters. The usual way to pass named query parameters with Grails is via a Map:

def queryStr = "from Book b where b.releaseDate > :date1 and b.releaseDate < :date2"
Book.executeQuery(queryStr, [date1: date1, date2: date2])

Check executeQuery() documentation for the syntax details of both options.