What I want to accomplish is the following. Please let me know how I can combine these two cases into one HQL conditional query. If myID is not null then use this query
@Query("select item from Item where a = ?1 and b=?2 and c=?3 and myID=?4)
If If myID is null then use this query
@Query("select item from Item where a = ?1 and b=?2 and c=?3)
Thanks in advance!
You need to use Criteria API to build the query dynamically. Check out the User Guide for more details.
@vlad Wouldn’t this work? Not sure the criteria API is necessary…
@Query("SELECT item FROM Item WHERE a = ?1 AND b=?2 AND c=?3 AND (myID IS NULL OR myId = ?4)")
@yrodiere It will work, but if the condition gets more complicated or there are more nullable arguments, then it’s better to use the Criteria API.
Hello all, thanks so much for your quick responses to my question. I tried this approach (myID IS NULL OR myId = ?4) but the resultset returns as empty when I don’t enter any value in the field for myId filed on the Swagger page. The debugger shows that the value passed in is a null in this case.
Could you please help me understand what does (myID IS NULL OR myId = ?4) query mean here? I think the case I am dealing with here should be pretty simple. I hope I don’t have to use the Criteria API.
Once again, the scenario I am dealing with is that I can enter a value in the myId field or I can leave it empty. I want to use one consolidate HQL to handle this. If the myId field is null then the HQL should just search other fields altogether.
Thanks again!
Ok, from what I understand you don’t want the condition to apply to “myId” as you initially stated, but rather to “?4”. In which case you could try something like this:
@Query("SELECT item FROM Item WHERE a = ?1 AND b=?2 AND c=?3 AND (?4 IS NULL OR myId = ?4)")
Not sure it will work, as I don’t know if a parameter can be mentioned twice in a given query. You’ll have to try.
But even if it works, I must admit I agree with Vlad: if you want the query to change depending on your query parameters, then it’s better to use the Criteria API. You can use it indirectly, for example through a third party library. QueryDSL is a very good one.
Actually, it should work with the previous query.
Please add a test acse that proves the empty result so we can take a look. You need to use this template:
http://in.relation.to/2016/01/14/hibernate-jpa-test-case-template/
I may have misunderstood, but I don’t think it will… It may depend on the database, but for postgresql at least, something = someotherthing evaluates to NULL when either operand is NULL. And a WHERE clause whose condition evaluates to null will not match.
So if a row has myId set to the string 'something' and ?4 is set to NULL, then myId = ?4 evaluates to NULL, so (myID IS NULL OR myId = ?4)") evaluates to NULL, and so does the whole condition. In that case I think that, as @springnotes told us, the result set will be empty, and it’s perfectly normal. Which is why I suggested a different condition that should work if it’s ?4 that may be null (instead of myId).
Hello
The suggestion (?4 IS NULL OR myId = ?4)") worked very well.
Thanks so much for your great help on this!
For PostgreSQL, if you run:
SELECT 'something' OR NULL
returns ‘something’.
So, in case you pass NULL, but myId is NOT NULL, then (myID IS NULL OR myId = ?4) will return true.
If myId is NULL, then myId = ?4 will return NULL, hence that record will be taken out of the result set.
@vlad That’s not the same though.
First you used a string with an OR operator, and I wouldn’t swear that the OR operator works exactly the same with strings as it does with booleans.
Second, even if it does, your example would be akin to TRUE OR NULL. Whereas (myID IS NULL OR myId = ?4), when myId is NOT NULL but ?4 is NULL, is actually FALSE OR NULL. Which doesn’t have the same value.
If more explanation is necessary, here’s the full reasoning:
TRUE OR NULL is TRUE, but FALSE OR NULL is NULL. That’s because NULL means “unknown”; TRUE OR <something> is always true, regardless of the value of <something>, so TRUE OR <something> is always true; but we can’t apply the same reasoning to FALSE OR NULL, so FALSE OR NULL is also unknown, hence it evaluates to NULL.
Also, still because NULL means “unknown”, <something> = NULL is always evaluated to NULL. Makes sense: whether some value is equal to some unknown value is… well, unknown.
In the case of @springnotes, from what I can see, myId is generally not NULL, but his param may be NULL.
So the first condition (the one that doesn’t work) I gave will evaluate to:
(myID IS NULL OR myId = ?4)
=> ('something' IS NULL OR 'something' = NULL)
=> (FALSE OR NULL)
=> NULL
The second one, on the other hand, will evaluate to:
(?4 NULL OR myId = ?4)
=> (NULL IS NULL OR 'something' = NULL)
=> (TRUE OR NULL)
=> TRUE
Anyway, you may disagree with the reasoning, but postgres cannot lie. You can try this on any psql shell:
hibernate_orm_test=# \set myParam NULL
hibernate_orm_test=# SELECT * FROM (VALUES ('foo'), ('bar')) AS vals(myId) WHERE myId IS NULL OR myId = :myParam;
myid
------
(0 rows)
hibernate_orm_test=# SELECT * FROM (VALUES ('foo'), ('bar')) AS vals(myId) WHERE :myParam IS NULL OR myId = :myParam;
myid
------
foo
bar
(2 rows)
@springnotes Glad we could help!
That’s a good argument which shows how complicated this logic can become once NULL is involved either in the column value or the argument itself.
In the case of @springnotes, from what I can see,
myIdis generally notNULL, but his param may beNULL.
I think that myId can be null as mentioned by the OP in his first post:
If myID is null then use this query
So, if we run this query:
SELECT * FROM (VALUES ('foo'), ('bar'), (null)) AS vals(myId) WHERE null IS NULL OR myId = null;
we get:
"foo"
"bar"
""
Which is not exactly what the OP requested either since he might have wanted to skip that row with NULL values.
Right, a fully working solution, that would take into account the fact that myId can be null, would be:
@Query("SELECT item FROM Item WHERE a = ?1 AND b=?2 AND c=?3 AND (?4 IS NULL OR (myId IS NOT NULL AND myId = ?4))")
Quite a mouthful, even if there might be utility functions that allow a more concise expression of the same condition.
I agree, it’s too complex for its own good. Using the Criteria API or QueryDSL would definitely be a better solution. But then we can’t force anyone to use it 
Is not the LNNVL() function from ORACLE now available in Postgresql ?
(see https://www.postgresql.org/message-id/20060901113532.A160E86CA8D%40pgfoundry.org)
if registered with Hibernate,
select item from Item where a = ?1 and b=?2 and c=?3 and lnnvl(myID<>?4)
should work.
(ORACLE doc: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions078.htm)