How to use key value to search in map in HQL query

Question

In Java when having Object Book with fields price and name and Map<Long, String> with books objects I have trouble with where clause I want to make following query:

SELECT b FROM books
WHERE (b.price, b.name) IN ((20, 'book1'), (30, 'book2'), (15, 'book3'))

I couldn't find result, tried HQL query for id pairs / tuples and How to check if a pair exists using HQL in query? and JPA named query match a list of tuples in IN clause

what is the problem in the 3rd option: JPA named query match a list of tuples in IN clause? I haven't tried but looks good. Can you please share the error you are getting while using the 3rd option? — naren, Jan 29 '19 at 12:54
3rd option involves creating another entity, which I want to avoid — Krzysztof Kvv, Jan 29 '19 at 14:52

score 0 · Accepted Answer · answered Apr 18 '19 at 18:33

Most row value expression predicates are just syntax sugar for some more verbose syntax. In your case:

SELECT * 
FROM books b
WHERE (b.price = 20 AND b.name = 'book1')
   OR (b.price = 30 AND b.name = 'book2')
   OR (b.price = 15 AND b.name = 'book3')

Note that if you want to build this kind of predicate dynamically, you have to either write a SQL builder yourself, mapping your map to the SQL string and bind variable set, or use an existing one like jOOQ

If you have more complex syntaxes that you want to emulate this way, you can use the jOOQ SQL translator to translate them from standard SQL to more basic SQL

(Disclaimer: I work for the company behind jOOQ)

How to use key value to search in map in HQL query

1 Answers1