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.

score 2 · Accepted Answer · answered Aug 04 '10 at 16:41

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)

score 0 · Answer 2 · answered Aug 04 '10 at 09:38

0

(: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

answered Aug 04 '10 at 09:38

Kiva

9,193
17
62
94

This is what I have and it still doesnt work. SELECT p FROM table p WHERE (:firstName is null OR p.firstName LIKE '%:firstName%') AND (:lastName is null OR p.lastName LIKE '%:lastName%') AND (:gender is null OR p.gender= :gender) where firstname and lastname are strings and gender is an integer – Anuj Gakhar Aug 04 '10 at 09:43
what I am trying to do is ignore any null or empty values passed in to the function. which is why I had :firstname = '' initially. – Anuj Gakhar Aug 04 '10 at 09:46
You can't make your like condition like this. You must do firstname = "%" + firstname + "%"; Then, in your query: p.firstName is null OR p.firstName LIKE :firstName not do :firstName is null (useless condition, do p.firstName is null) – Kiva Aug 04 '10 at 09:48
@Kiva What Jimmy is trying to do is to disable a part of the where clause when his Java method receives a null parameter. I-e if search("Jimmy", null) is called the query should only filter on firstname = "Jimmy" ignoring the lastName column value. – Tahir Akhtar Aug 04 '10 at 09:57
Yes, Tahir, that is exactly what I am trying to do. – Anuj Gakhar Aug 04 '10 at 10:02
Ok, make your query in several times like this: query = "select t from table t where 1 " if (fisrtname != null) query += " and t.firstName = :firstName" and so and so for all parameters (basic code, you can optimize it) – Kiva Aug 04 '10 at 10:07
I am trying to do it all in one query if possible. Generating a Dynamic Query is not an Option I want to take. How can we do this in one Query string ? – Anuj Gakhar Aug 04 '10 at 10:24

score 0 · Answer 3 · answered Aug 04 '10 at 10:08

0

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.

answered Aug 04 '10 at 10:08

Tahir Akhtar

11,385
7
42
69

I am trying to do it all in one query if possible. Generating a Dynamic Query is not an Option I want to take. How can we do this in one Query string ? – Anuj Gakhar Aug 04 '10 at 10:24
Why do you not want to do a dynamic query ? – Kiva Aug 04 '10 at 10:29
the option is not there. the project is setup that way. long story short - cant do a dynamic query. – Anuj Gakhar Aug 04 '10 at 10:36

score 0 · Answer 4 · answered Jan 19 '16 at 23:36

I had a similar requirement. I want dynamic but I'm using a tool that just gives an HQL editor, so no Java.

The query below allows for optional parameters. Essentially a pseudo-quazi XOR of sorts . . . wish there was real XOR :/

With this query you just put NA into a param instead of leaving it empty if it is not needed.

Yeah, yeah, yeah . . . it's ugly, but it works and it's easy to alter to any other scenario needing optional params in pure HQL.

SELECT t AS table
FROM Table t
WHERE (t.valSet = :valSet 
    AND (:category= 'NA' AND :subCategory= 'NA'))
OR (:category != 'NA'
    AND (t.valSet = :valSet 
        AND t.category= :category))
OR (:subCategory != 'NA'
    AND (t.valSet = :valSet 
        AND t.subCategory = :subCategory ))

HQL Query with multiple Criteria

4 Answers4