Find Hibernate entities where date is between start and end dates that can be null

I'd like to find all Event entities that that are active at a certain date. For instance:

from Event where startDate <= :specifiedDate and endDate > :specifiedDate

However, Event.startDate and Event.endDate can be null. A null valu开发者_JAVA百科e indicates that there is no start or end date (the start is infinitely in the past, or the end is infinitely in the future).

So, if Event.startDate = null and Event.endDate = 2011-05-01, then that Event should be returned for all specifiedDate values less than 2011-05-01.

Also if Event.startDate = 2011-03-01 and Event.endDate = null, then that Event should be returned for all specifiedDate values greater than or equal to 2011-03-01.

Lastly, if Event.startDate = null and Event.endDate = null, then the Event should be returned for any specifiedDate.

Is this possible using Hibernate and a MySQL backend?

This is certainly possible.

Using criteria it should be something like:

List events = sess.createCriteria(Event.class)
                 .add( Restrictions.or(
                          Restrictions.isNull("startDate"),
                          Restrictions.ge("startDate", specifiedDate))
                 .add( Restrictions.or(
                          Restrictions.isNull("endDate"),
                          Restrictions.lt("endDate", specifiedDate))
               .list();