Nested JPA Criteria Query with GroupBy and Max-CodePudding

I would like to query a table using Criteria Query in JPA Spring boot for the following sql:

SELECT p.id, p.spending, ms.maxspending from person p
INNER JOIN (SELECT p.id, max(p.spending) from person as ms GROUP BY p.id)
ON p.id = ms.id;

the table I want to derive from should look as follow: I have no issue joining values from different tables (entities) but I can't find a way to produce the sql I show above when there is a nested SELECT clause.... any help is greatly appreciated!!!

CodePudding user response：

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> criteria = builder.createQuery(Object[].class);

Root<Person> root = criteria.from(Person.class);

// Create a subquery to get the maximum spending for each Person
Subquery<Integer> subquery = criteria.subquery(Integer.class);
Root<Person> subRoot = subquery.from(Person.class);

subquery.select(builder.max(subRoot.get("spending"))).groupBy(subRoot.get("id"));

// Select the id, spending, and maximum spending for each person
criteria.multiselect(root.get("id"), root.get("spending"), 
builder.greatest(root.get("spending")));

// Join the table with the subquery on the id
criteria.where(builder.equal(root.get("id"), subRoot.get("id")));

List<Object[]> results = entityManager.createQuery(criteria).getResultList();

Note: The above JPA criteria might not directly work for you. You might need to make necessary changes according to your need