Search code examples
hqlnamed-parameters

HQL Query with multiple Criteria

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.

Solution

  • 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)