Optional parameters with named query in Hibernate?

java hibernate orm hql named-query
Ickster picture Ickster · Mar 15, 2010 · Viewed 63k times · Source

Is there any way to specify optional parameters (such as when search parameters are provided from a form and not all parameters are required) in a named query when using Hibernate? I'm using a native SQL query, but the question is probably applicable to named HQL queries as well.

I'm pretty sure the answer to this is 'no', but I haven't found the definitive answer in the documentation yet.

Answer

Ian Jones picture Ian Jones · Mar 13, 2012

As mentioned in a different answer to the question referenced previously, the following HQL construct works for me:

select o from Product o WHERE :value is null or o.category = :value

if :value is passed in as null, all Products are returned.

See also Optional or Null Parameters

Note that this won't work in some versions of Sybase due to this bug, so the following is an alternative:

select o from Product o WHERE isnull(:value, 1) = 1 or o.category = :value

Related questions

"where exists" in Hibernate HQL

How can I write a "not exists" query in HQL? I am trying to get an HQL not exists query which returns the same results as this Oracle SQL query: select * from SCHOOL a where not exists (select 1 from STUDENT …

Read More
Hibernate Named Query Order By parameter

Can anyone point me to how we can pass an order by clause as a named parameter to HQL? Example which works: select tb from TransportBooking as tb and TIMESTAMP(tb.bookingDate, tb.bookingTime) >= current_timestamp() order by tb.…

Read More
HQL recursion, how do I do this?

I have a tree structure where each Node has a parent and a Set<Node> children. Each Node has a String title, and I want to make a query where I select Set<String> titles, being …

Read More