如何在Spring Boot中用JPA Criteria Query实现含GroupBy与Max的嵌套查询

你要实现的SQL核心逻辑是：查询Person表的每条记录，同时带上对应用户ID的最大消费金额。以下是具体实现方案：

1. 准备Person实体类 #

假设你的实体类定义如下：

@Entity
@Table(name = "person")
public class Person {
    @Id
    private Long id;
    private BigDecimal spending;

    // 全参/无参构造器、getter、setter省略
}

2. 用Criteria Query实现子查询关联 #

方式一：用Tuple接收查询结果 #

如果不需要自定义数据传输对象，直接用JPA的Tuple接收返回字段：

import jakarta.persistence.EntityManager;
import jakarta.persistence.Tuple;
import jakarta.persistence.criteria.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class PersonQueryRepository {

    private final EntityManager entityManager;

    public PersonQueryRepository(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    public List<Tuple> getPersonWithMaxSpending() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> mainQuery = cb.createTupleQuery();

        // 主查询的Person根对象
        Root<Person> mainRoot = mainQuery.from(Person.class);

        // 构建子查询：按id分组，获取每个id对应的最大spending
        Subquery<Tuple> subQuery = mainQuery.subquery(Tuple.class);
        Root<Person> subRoot = subQuery.from(Person.class);
        subQuery.select(cb.tuple(
                subRoot.get("id"),
                cb.max(subRoot.get("spending")).alias("maxspending")
        )).groupBy(subRoot.get("id"));

        // 关联主表和子查询（INNER JOIN逻辑）
        mainQuery.where(cb.equal(mainRoot.get("id"), subRoot.get("id")));

        // 指定主查询返回的字段
        mainQuery.select(cb.tuple(
                mainRoot.get("id").alias("id"),
                mainRoot.get("spending").alias("spending"),
                subQuery.getSelection().get("maxspending").alias("maxspending")
        ));

        return entityManager.createQuery(mainQuery).getResultList();
    }
}

方式二：用自定义DTO接收结果 #

如果希望结果更结构化，创建一个DTO类：

public class PersonWithMaxSpendingDTO {
    private Long id;
    private BigDecimal spending;
    private BigDecimal maxspending;

    // 必须提供对应参数的全参构造器
    public PersonWithMaxSpendingDTO(Long id, BigDecimal spending, BigDecimal maxspending) {
        this.id = id;
        this.spending = spending;
        this.maxspending = maxspending;
    }

    // getter、setter省略
}

修改查询代码，用构造器表达式映射结果：

public List<PersonWithMaxSpendingDTO> getPersonWithMaxSpendingAsDTO() {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<PersonWithMaxSpendingDTO> mainQuery = cb.createQuery(PersonWithMaxSpendingDTO.class);

    Root<Person> mainRoot = mainQuery.from(Person.class);

    // 构建子查询
    Subquery<Tuple> subQuery = mainQuery.subquery(Tuple.class);
    Root<Person> subRoot = subQuery.from(Person.class);
    subQuery.select(cb.tuple(
            subRoot.get("id"),
            cb.max(subRoot.get("spending")).alias("maxspending")
    )).groupBy(subRoot.get("id"));

    // 关联主表与子查询
    mainQuery.where(cb.equal(mainRoot.get("id"), subRoot.get("id")));

    // 通过构造器将查询结果映射到DTO
    mainQuery.select(cb.construct(
            PersonWithMaxSpendingDTO.class,
            mainRoot.get("id"),
            mainRoot.get("spending"),
            cb.max(subRoot.get("spending"))
    ));

    return entityManager.createQuery(mainQuery).getResultList();
}

3. 可选优化：用窗口函数替代子查询关联 #

如果你的数据库支持窗口函数（如MySQL 8+、PostgreSQL），可以用更简洁高效的方式实现相同逻辑：

public List<PersonWithMaxSpendingDTO> getPersonWithMaxSpendingUsingWindow() {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<PersonWithMaxSpendingDTO> query = cb.createQuery(PersonWithMaxSpendingDTO.class);

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

    // 窗口函数：按id分区，计算每个分区的最大spending
    Expression<BigDecimal> maxSpending = cb.function(
            "MAX", BigDecimal.class,
            root.get("spending")
    ).over().partitionBy(root.get("id"));

    query.select(cb.construct(
            PersonWithMaxSpendingDTO.class,
            root.get("id"),
            root.get("spending"),
            maxSpending
    ));

    return entityManager.createQuery(query).getResultList();
}

内容的提问来源于stack exchange，提问作者ZhenKai