JPA NamedQuery with Conditions

Question

I am new to JPA and need assistance writing following query. I am on wildfly, javaEE with hibernate behind etc etc.. The idea is to have a where inside my select statement with two conditions. The first condition shall be matched first against all rows and then in case nothing is found the second one shall be matched against all rows.

for example (pseudo code):

select * from myTable
where (con1) and (if no con1 try match con2)

rows matching con1 shall be returned because con1 has prio

rows matching both conditions shall never be returned

how to write such a query in jpa 2.1? thanks in advance

hi, dont think this will work since rows matching con2 may be returned even though rows matching con1 have priority — Niki, Jul 13 '17 at 06:53
Write 2 SQLs and check if your 1st SQL with _con1_ returns some rows or not, If no than execute 2nd SQL with _con2_. — Darshit Chokshi, Jul 13 '17 at 06:55
@DarshitChokshi that would mean too calls from service to db and that is something I would like to avoid — Niki, Jul 13 '17 at 06:56
I know its not recommanded but i don't think any SQL will first check if all rows matching _con1_ and if not than and only than try to match with _con2_. — Darshit Chokshi, Jul 13 '17 at 06:58

vortigern · Accepted Answer · 2017-07-13T06:55:12.593

0

select * from myTable where (CON1) OR (CON2)

Edit: the above might not be correct. You should probably use the case statement:

If you need it, I suggest a Case statement:

Where Case when Expr1 then Expr2 else Expr3 end = desiredResult

as in https://stackoverflow.com/a/12320806/7913706

edited Jul 13 '17 at 06:55

answered Jul 13 '17 at 06:44

vortigern

49
6

i dont think this will work since rows matching con2 may be returned even though rows matching con1 have priority – Niki Jul 13 '17 at 06:47
You're right, turns out short-circuit evaluation isn't guaranteed in SQL. I've updated the answer. – vortigern Jul 13 '17 at 06:53
I dont think that is what I am looking for.. sorry – Niki Jul 13 '17 at 07:12

JPA NamedQuery with Conditions

1 Answers1