Hibernate HQL NULL comparison pattern

Question

Imagine a simple web page allowing you to search Bookings like this:

• If the Booking Number field is not populated I want to return all rows.
• If the Booking Number field is populated I only want to return that row.

I have been using a pattern in Hibernate (with an underlying MySQL database) to accomplish this that goes something like:

AND (:bookingNum IS NULL OR (:bookingNum IS NOT NULL AND :bookingNum = booking.bookingNumber))

I've recently been seeing some weird performance issues and that got me wondering if there is a better pattern to use here. I'm sure non-Hibernate queries must have similar issues.

What do others do in this siutation?

Thanks,

D.

Answer 1

Use a Hibernate criteria query, adding the predicate only if bookingNum isn't null.