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).

Zhukov

Answer 1

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

Laux

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. – Zhukov

please submit a request for JPA spec. – Strudel

Answer 2

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)

Ampulla

Answer 3

Just like I answered in the Discourse question that was referring to this Stackoverflow post, the Hibernate extension of the JPA CriteriaBuilder is HibernateCriteriaBuilder which offers you a more or less stable API. I might be wrong with this, but I believe that tuple comparisons are not deemed stable enough yet, which is why this feature is only defined on NodeBuilder, which is defined in terms of the incubating SQM APIs. You can use NodeBuilder.tuple(null, root.get("attr1"), root.get("attr2")).in(subquery) to model the same thing in terms of JPA Criteria API.

Tripartition