如何将含join和group by的PostgreSQL查询转换为JPA Criteria API实现

以下代码完全匹配你提供的原生PostgreSQL查询逻辑，无需修改现有实体类结构，提供两种常用返回形式的实现：

实现1：使用Tuple接收查询结果 #

无需额外定义类，直接用JPA自带的Tuple接收多字段返回值：

import jakarta.persistence.EntityManager;
import jakarta.persistence.Tuple;
import jakarta.persistence.criteria.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public class UserStatisticsRepository {

    @Autowired
    private EntityManager entityManager;

    public List<Tuple> listUserApplicationCount() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        // 定义返回类型为Tuple
        CriteriaQuery<Tuple> criteriaQuery = cb.createTupleQuery();
        // 根查询为项目申请实体，对应原生SQL的project_applications表
        Root<ProjectApplication> paRoot = criteriaQuery.from(ProjectApplication.class);
        // 关联创建用户，对应原生SQL的JOIN逻辑
        Join<ProjectApplication, User> userJoin = paRoot.join("createdBy", JoinType.INNER);

        // 定义统计count表达式，对应count(*)
        Expression<Long> countExpr = cb.count(paRoot);
        // 选择查询字段，对应原生SQL的select部分
        criteriaQuery.multiselect(
                userJoin.get("id").alias("userId"),
                userJoin.get("fullName").alias("fullName"),
                countExpr.alias("projectApplicationsCount")
        );

        // 分组逻辑，对应group by u.id, u.full_name
        criteriaQuery.groupBy(userJoin.get("id"), userJoin.get("fullName"));

        // HAVING条件，对应count(*) >=1 and count(*) <=5
        Predicate havingCondition = cb.and(
                cb.greaterThanOrEqualTo(countExpr, 1L),
                cb.lessThanOrEqualTo(countExpr, 5L)
        );
        criteriaQuery.having(havingCondition);

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

结果取值示例： #

List<Tuple> result = userStatisticsRepository.listUserApplicationCount();
for (Tuple tuple : result) {
    Long userId = tuple.get("userId", Long.class);
    String fullName = tuple.get("fullName", String.class);
    Long count = tuple.get("projectApplicationsCount", Long.class);
}

实现2：使用自定义DTO接收查询结果 #

适合业务层直接使用的场景，先定义DTO类：

public class UserApplicationCountDTO {
    private Long userId;
    private String fullName;
    private Long projectApplicationsCount;

    // 必须保留与查询字段顺序、类型完全匹配的全参构造方法
    public UserApplicationCountDTO(Long userId, String fullName, Long projectApplicationsCount) {
        this.userId = userId;
        this.fullName = fullName;
        this.projectApplicationsCount = projectApplicationsCount;
    }

    // 此处省略Getter、Setter方法
}

对应的查询代码：

import jakarta.persistence.EntityManager;
import jakarta.persistence.criteria.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public class UserStatisticsRepository {

    @Autowired
    private EntityManager entityManager;

    public List<UserApplicationCountDTO> listUserApplicationCount() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        // 定义返回类型为自定义DTO
        CriteriaQuery<UserApplicationCountDTO> criteriaQuery = cb.createQuery(UserApplicationCountDTO.class);
        Root<ProjectApplication> paRoot = criteriaQuery.from(ProjectApplication.class);
        Join<ProjectApplication, User> userJoin = paRoot.join("createdBy", JoinType.INNER);

        Expression<Long> countExpr = cb.count(paRoot);
        // 使用construct方法映射DTO构造参数
        criteriaQuery.select(cb.construct(
                UserApplicationCountDTO.class,
                userJoin.get("id"),
                userJoin.get("fullName"),
                countExpr
        ));

        criteriaQuery.groupBy(userJoin.get("id"), userJoin.get("fullName"));
        Predicate havingCondition = cb.and(
                cb.greaterThanOrEqualTo(countExpr, 1L),
                cb.lessThanOrEqualTo(countExpr, 5L)
        );
        criteriaQuery.having(havingCondition);

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

注意事项 #

• 若使用Spring Boot 2.x版本，将所有jakarta.persistence包名替换为javax.persistence即可
• 代码中填写的是实体类的属性名，不是数据库表的字段名，例如实体属性为fullName，不要写数据库字段full_name
• 默认使用INNER JOIN，与原生SQL的JOIN逻辑一致，如需左连接可将JoinType.INNER改为JoinType.LEFT

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