HQL Query with multiple Criteria

Question

I am trying to write a HQL Query which selectes rows from a table based on multiple criteria. firstName,lastName

the catch is that the query should be flexible to ignore any empty or null values

so

select t from table t where (:firstname = '' or t.firstName = :firstName) AND
(:lastName = '' OR t.lastName = :lastName)

I would have thought this would work? But it doesnt - it never returns any rows? Any ideas what could be wrong here? I am very new to HQL thats why this question.

Answer 1

(:firstname = '' or t.firstName = :firstName)

Your criteria is strange. If :firstname = '' and if a firstname (t.firstName) is equal '' in the database, the criteria t.firstName = :firstName is good ('' = '')

You don't need :firstname = ''

But If you want to check null value, you need to do:

t.firstName IS NULL or t.firstName = :firstname

Answer 2

What happens if you run following hql with firstname parameter set to empty string?

select t from table t where (:firstname = '') 

and following with firstname parameter set to null:

select t from table t where (:firstname is null) 

If any of the above returns the whole table then the HQLs named parameter might support what you are trying to do.

Otherwise you must use different queries for the null parameter cases. You can do this by generating the query dynamically.

Answer 3

If I am understanding correctly you want a way to allow the user to search by firstName, lastName or both. So you should be checking if the parameter passed in is empty then don't make it a condition. If they supply all blank parameters it would return the whole table. Try:

select t from table t 
where (:firstname IS NULL or t.firstName = :firstName) AND
(:lastName IS NULL OR t.lastName = :lastName)