如何在Spring Boot+JPA/Hibernate 6中实现带复杂聚合的动态SQL

针对你提出的多表关联、聚合运算、动态WHERE/ORDER BY及分页需求，Spring Boot结合JPA完全可以实现，无需编写数百个Repository方法，以下是几种可行方案：

1. 利用JPA Criteria API构建全动态查询 #

Criteria API并非只能处理单表查询，它支持多表关联、自定义聚合函数、动态条件拼接、分组排序和分页。示例代码如下：

@Repository
public class PlayerQueryRepository {

    @PersistenceContext
    private EntityManager entityManager;

    public Page<ResultsDTO> getComplexResults(Integer age, List<Integer> transactionTypes, LocalDateTime createdAt, Pageable pageable) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<ResultsDTO> query = cb.createQuery(ResultsDTO.class);

        // 关联实体
        Root<Player> p = query.from(Player.class);
        Join<Player, Account> a = p.join("account"); // 假设Player和Account有关联映射
        Join<Account, Transaction> t = a.join("transactions");

        // 定义聚合表达式
        Expression<Long> numTransactions = cb.count(t);
        Expression<BigDecimal> totalCredit = cb.sum(t.get("credit").as(BigDecimal.class));
        Expression<BigDecimal> percentage = cb.function(
            "round", BigDecimal.class,
            cb.prod(
                cb.divide(
                    cb.sum(cb.function("cast", BigDecimal.class, t.get("credit"), cb.literal("numeric(32,2)"))),
                    a.get("balance").as(BigDecimal.class)
                ),
                cb.literal(100)
            ),
            cb.literal(2)
        );

        // 构建SELECT子句
        query.select(cb.construct(ResultsDTO.class,
            p.get("id"), p.get("name"), a.get("balance"),
            numTransactions, totalCredit, percentage));

        // 动态拼接WHERE条件
        List<Predicate> predicates = new ArrayList<>();
        if (age != null) {
            predicates.add(cb.greaterThan(p.get("age"), age));
        }
        if (transactionTypes != null && !transactionTypes.isEmpty()) {
            predicates.add(t.get("transactionType").in(transactionTypes));
        }
        if (createdAt != null) {
            predicates.add(cb.greaterThan(p.get("createdAt"), createdAt));
        }
        query.where(predicates.toArray(new Predicate[0]));

        // 分组和排序
        query.groupBy(p.get("id"), p.get("name"), a.get("balance"));
        if (pageable.getSort().isSorted()) {
            List<Order> orders = new ArrayList<>();
            pageable.getSort().forEach(order -> {
                Path<?> path = switch(order.getProperty()) {
                    case "num_transactions" -> numTransactions;
                    case "total_credit" -> totalCredit;
                    case "percentage" -> percentage;
                    default -> p.get(order.getProperty());
                };
                orders.add(order.isAscending() ? cb.asc(path) : cb.desc(path));
            });
            query.orderBy(orders);
        }

        // 执行分页查询
        TypedQuery<ResultsDTO> typedQuery = entityManager.createQuery(query);
        typedQuery.setFirstResult((int) pageable.getOffset());
        typedQuery.setMaxResults(pageable.getPageSize());
        List<ResultsDTO> results = typedQuery.getResultList();

        // 统计总数
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        countQuery.select(cb.countDistinct(p.get("id")));
        countQuery.from(p);
        countQuery.join(p.get("account"));
        countQuery.join(a.get("transactions"));
        countQuery.where(predicates.toArray(new Predicate[0]));
        long total = entityManager.createQuery(countQuery).getSingleResult();

        return new PageImpl<>(results, pageable, total);
    }
}

2. 结合@Query与SpEL表达式实现动态SQL #

通过Spring Data JPA的SpEL表达式，可以在静态@Query中动态插入条件和排序逻辑，同时支持原生SQL：

public interface PlayerRepository extends CrudRepository<Player, Long> {

    @Query(value = """
        select p.id, p.name, a.balance, count('x') as num_transactions,
               sum(t.credit) as total_credit,
               round(sum(t.credit::numeric(32,2) / a.balance) * 100, 2) as percentage
        from player p
        join account a on a.player_id = p.id
        join transactions t on t.account_id = a.id
        where 1=1
        #{#age != null ? 'and p.age > :age' : ''}
        #{#transactionTypes != null and !#transactionTypes.isEmpty() ? 'and t.transaction_type in (:transactionTypes)' : ''}
        #{#createdAt != null ? 'and p.created_at > :createdAt' : ''}
        group by p.id, p.name, a.balance
        order by :sortField :sortDir
        """, nativeQuery = true,
        countQuery = """
            select count(distinct p.id)
            from player p
            join account a on a.player_id = p.id
            join transactions t on t.account_id = a.id
            where 1=1
            #{#age != null ? 'and p.age > :age' : ''}
            #{#transactionTypes != null and !#transactionTypes.isEmpty() ? 'and t.transaction_type in (:transactionTypes)' : ''}
            #{#createdAt != null ? 'and p.created_at > :createdAt' : ''}
            """)
    Page<ResultsDTO> getComplexResults(
        @Param("age") Integer age,
        @Param("transactionTypes") List<Integer> transactionTypes,
        @Param("createdAt") LocalDateTime createdAt,
        @Param("sortField") String sortField,
        @Param("sortDir") String sortDir,
        Pageable pageable);
}

注意：需要对sortField做白名单校验，仅允许指定的字段名（如num_transactions、total_credit等），避免SQL注入风险。

3. 使用Querydsl支持自定义聚合表达式 #

Querydsl可以通过Expressions.numberTemplate定义原生SQL片段，满足复杂运算需求，同时保持类型安全：

@Repository
public class PlayerQuerydslRepository {

    @PersistenceContext
    private EntityManager entityManager;

    public Page<ResultsDTO> getComplexResults(Integer age, List<Integer> transactionTypes, LocalDateTime createdAt, String sortField, String sortDir, Pageable pageable) {
        QPlayer p = QPlayer.player;
        QAccount a = QAccount.account;
        QTransaction t = QTransaction.transaction;

        // 定义自定义聚合运算
        Expression<BigDecimal> percentage = Expressions.numberTemplate(BigDecimal.class,
            "round(sum({0}::numeric(32,2) / {1}) * 100, 2)", t.credit, a.balance);

        // 构建查询
        JPAQuery<ResultsDTO> query = new JPAQuery<>(entityManager)
            .select(Projections.constructor(ResultsDTO.class,
                p.id, p.name, a.balance,
                t.count(), t.credit.sum(),
                percentage))
            .from(p)
            .join(a).on(a.playerId.eq(p.id))
            .join(t).on(t.accountId.eq(a.id));

        // 动态添加条件
        if (age != null) {
            query.where(p.age.gt(age));
        }
        if (transactionTypes != null && !transactionTypes.isEmpty()) {
            query.where(t.transactionType.in(transactionTypes));
        }
        if (createdAt != null) {
            query.where(p.createdAt.gt(createdAt));
        }

        // 分组与排序
        query.groupBy(p.id, p.name, a.balance);
        if (sortField != null) {
            Path<?> sortPath = switch(sortField) {
                case "num_transactions" -> t.count();
                case "total_credit" -> t.credit.sum();
                case "percentage" -> percentage;
                default -> p.id;
            };
            query.orderBy(sortDir.equalsIgnoreCase("desc") ? sortPath.desc() : sortPath.asc());
        }

        // 分页处理
        query.offset(pageable.getOffset()).limit(pageable.getPageSize());
        List<ResultsDTO> results = query.fetch();

        // 统计总数
        long total = new JPAQuery<>(entityManager)
            .select(p.id.countDistinct())
            .from(p)
            .join(a).on(a.playerId.eq(p.id))
            .join(t).on(t.accountId.eq(a.id))
            .where(query.getMetadata().getWhere())
            .fetchOne();

        return new PageImpl<>(results, pageable, total);
    }
}

4. 自定义Repository实现类 #

如果以上方案不符合需求，可以直接编写Repository实现类，手动拼接SQL并处理参数绑定：

@Repository
public class PlayerCustomRepositoryImpl implements PlayerCustomRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page<ResultsDTO> getComplexResults(Integer age, List<Integer> transactionTypes, LocalDateTime createdAt, String sortField, String sortDir, Pageable pageable) {
        StringBuilder sql = new StringBuilder();
        sql.append("""
            select p.id, p.name, a.balance, count('x') as num_transactions,
                   sum(t.credit) as total_credit,
                   round(sum(t.credit::numeric(32,2) / a.balance) * 100, 2) as percentage
            from player p
            join account a on a.player_id = p.id
            join transactions t on t.account_id = a.id
            where 1=1
            """);

        Map<String, Object> params = new HashMap<>();

        // 动态添加WHERE条件
        if (age != null) {
            sql.append(" and p.age > :age");
            params.put("age", age);
        }
        if (transactionTypes != null && !transactionTypes.isEmpty()) {
            sql.append(" and t.transaction_type in (:transactionTypes)");
            params.put("transactionTypes", transactionTypes);
        }
        if (createdAt != null) {
            sql.append(" and p.created_at > :createdAt");
            params.put("createdAt", createdAt);
        }

        sql.append(" group by p.id, p.name, a.balance");

        // 安全处理排序字段
        String validSortField = switch(sortField) {
            case "num_transactions", "total_credit", "percentage", "name" -> sortField;
            default -> "p.id";
        };
        sql.append(" order by ").append(validSortField).append(" ").append(sortDir.equalsIgnoreCase("desc") ? "desc" : "asc");

        // 执行查询
        Query query = entityManager.createNativeQuery(sql.toString(), ResultsDTO.class);
        params.forEach(query::setParameter);
        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());
        List<ResultsDTO> results = query.getResultList();

        // 统计总数
        StringBuilder countSql = new StringBuilder();
        countSql.append("""
            select count(distinct p.id)
            from player p
            join account a on a.player_id = p.id
            join transactions t on t.account_id = a.id
            where 1=1
            """);
        countSql.append(sql.substring(sql.indexOf("where 1=1") + 9, sql.indexOf(" group by")));

        Query countQuery = entityManager.createNativeQuery(countSql.toString());
        params.forEach(countQuery::setParameter);
        long total = ((Number) countQuery.getSingleResult()).longValue();

        return new PageImpl<>(results, pageable, total);
    }
}

对应的接口定义：

public interface PlayerCustomRepository {
    Page<ResultsDTO> getComplexResults(Integer age, List<Integer> transactionTypes, LocalDateTime createdAt, String sortField, String sortDir, Pageable pageable);
}

public interface PlayerRepository extends CrudRepository<Player, Long>, PlayerCustomRepository {
}

以上方案均可满足你的动态查询、聚合运算及分页需求，可根据团队技术栈和熟悉程度选择合适的实现方式。

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