JPA Criteria : in clause with many columns

Question

I am trying to write the following SQL query using the JPA Criteria API

SELECT * FROM Table1 a
WHERE (a.category, a.priority) IN (
SELECT a1.category, max(a1.priority) FROM Table1 a1 GROUP BY a1.category
)

Functionally, the query select the element of Table1 with the highest priority for each category.

categoryPriority = // defines the pair category/priority
mySubQuery = // defines the subquery
query.where(categoryPriority.in(mySubQuery)

This is schematically what I am looking for.

Defining the subquery is not a problem since it is well documented.

But I cannot find a way to define the couple (a.category, a.priority).

score 6 · Answer 1 · edited May 23 '17 at 11:44

6

Multiple columns in a IN clause is not provided for in JPA. I think you would need to use a native query.

Reference: query for select which multiple values in the “IN” clause

edited May 23 '17 at 11:44

Community

1
1

answered May 13 '16 at 14:41

K.Nicholas

10,956
4
46
66

3

Thanks you for your answer. Since I don't want to use native queries I replaced the many column part by an ugly concatenation of the value of the category and priority columns but I'm kind of disappointed. – Manuel Leduc May 13 '16 at 15:32
please submit a request for JPA spec. – eastwater Jan 25 '19 at 16:33

score 2 · Answer 2 · answered May 09 '18 at 18:13

An alternative approach is to use field concatenation

Create a method that returns the two fields you want to search in your DTO/Entity.

  public String getField1Field2Concatenated() {
    return field1+ field2;
  }


List<String> ids = list.stream().map(r -> r.getField1Field2Concatenated()).collect(Collectors.toList());

You can concatenate two fields and do the search.

Select e from Entity e where concat(e.field1,  c.field2) in (:ids)

If any of the fields are not text you can cast

Select e from Entity e where concat(cast(c.field1 as string),  c.field2) in (:ids)

JPA Criteria : in clause with many columns

2 Answers2

Linked

Related