CODEWITHSUNDEEP
jpacriteria-apiin-clause
Manuel Leduc
Manuel Leduc

Reputation: 1919

JPA Criteria : in clause with many columns

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

Upvotes: 4

Views: 6933

Answers (3)

Christian Beikov
Christian Beikov

Reputation: 16452

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.

Upvotes: 0

Fernando Albuquerque
Fernando Albuquerque

Reputation: 620

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)

Upvotes: 2

K.Nicholas
K.Nicholas

Reputation: 11561

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

Upvotes: 6

Related Questions