Hibernate, NamedQuery & Handlling NamedParamater in the case of it is Null or not available

Question

Hi,

I am using one named query to search some result in my Project. e.g ( from StructureEventDAO as se where se.Structure.StructureId = :StructureId and se.eventMaster.eventName =:eventName ).

This is giving proper result if I pass required both the named parameter value.

I just wanted to know what if runtime I have one of the parameter as Null and I want to ignore it and get the result? (it means in my query , say StructureId = null so I will get all the StructureEventDAO which will have passed eventName )

Regards, Amit

Answer 1

from StructureEventDAO as se where (se.Structure.StructureId = :StructureId or 
se.Structure.StructureId is null) and se.eventMaster.eventName =:eventName

I dont remember exactly the sintaxe, but i think you can do something like this.

Answer 2

This is typically something that you would implement using a dynamically generated query (i.e. not a NamedQuery) and there are basically two options:

1. build a HQL string dynamically (i.e. not a named query) ~or~
2. use the Criteria API

And in my opinion, the Criteria API is more elegant and less verbose for dynamic queries, as discussed for example in Hibernate Querying 102 : Criteria API. Here is an example taken from the article:

Criteria criteria = session.createCriteria(Accommodation.class);
if (startDate != null) {
    criteria.add(Expression.ge("availabilityDate", startDate);
}                
if (endDate != null) {
    criteria.add(Expression.le("availabilityDate", endDate);
} 

Which is much easier than generating the equivalent HQL query string (read the whole article for all the details).

And see also:

Resources

• Hibernate Querying 102 : Criteria API
• Hibernate Criteria API: Multi-Criteria Search Made Easy

Related question

• Hibernate: Criteria vs. HQL