如何将PostgreSQL带过滤的count()查询转换为Java Criteria Builder？

核心说明 #

PostgreSQL的COUNT(...) FILTER(WHERE ...)语法在JPA标准API中没有直接对应的方法，我们可以通过CASE WHEN表达式模拟：当满足过滤条件时返回目标字段，否则返回NULL，由于COUNT会自动忽略NULL值，最终效果和原生FILTER完全一致。

完整Criteria Builder代码 #

假设你已定义对应数据库表的JPA实体类（Batch、Journey、Segment、Booking、Item、BatchRun）且实体间关联关系已正确映射，以下是转换后的代码：

import jakarta.persistence.criteria.*;
import java.util.List;

// 在Repository或Service方法中实现
public List<Object[]> getBatchStatistics() {
    // 获取CriteriaBuilder和CriteriaQuery
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);

    // 根实体为Batch
    Root<Batch> batchRoot = query.from(Batch.class);

    // 执行Left Join，对应原SQL的关联逻辑
    Join<Batch, Journey> journeyJoin = batchRoot.join("journeys", JoinType.LEFT);
    Join<Journey, Segment> segmentJoin = journeyJoin.join("segments", JoinType.LEFT);
    Join<Journey, Booking> bookingJoin = journeyJoin.join("bookings", JoinType.LEFT);
    Join<Booking, Item> itemJoin = bookingJoin.join("items", JoinType.LEFT);
    // 原SQL中关联了batch_run但未使用，仍保留关联
    Join<Batch, BatchRun> batchRunJoin = batchRoot.join("batchRuns", JoinType.LEFT);

    // 构建聚合表达式
    // 总预订数：COUNT(DISTINCT b.id)
    Expression<Long> bookingCount = cb.countDistinct(bookingJoin.get("id"));

    // 唯一行程数：COUNT(DISTINCT j.id)
    Expression<Long> distinctJourneyCount = cb.countDistinct(journeyJoin.get("id"));

    // 成功预订数：COUNT(DISTINCT b.status) FILTER(WHERE b.status = 'succeeded')
    Expression<String> succeedStatusCase = cb.selectCase()
            .when(cb.equal(bookingJoin.get("status"), "succeeded"), bookingJoin.get("status"))
            .otherwise(null);
    Expression<Long> succeedBookingCount = cb.countDistinct(succeedStatusCase);

    // 失败预订数：COUNT(DISTINCT b.status) FILTER(WHERE b.status = 'failed')
    Expression<String> failedStatusCase = cb.selectCase()
            .when(cb.equal(bookingJoin.get("status"), "failed"), bookingJoin.get("status"))
            .otherwise(null);
    Expression<Long> failedBookingCount = cb.countDistinct(failedStatusCase);

    // 跳过预订数：COUNT(DISTINCT b.status) FILTER(WHERE b.status = 'skipped')
    Expression<String> skippedStatusCase = cb.selectCase()
            .when(cb.equal(bookingJoin.get("status"), "skipped"), bookingJoin.get("status"))
            .otherwise(null);
    Expression<Long> skippedBookingCount = cb.countDistinct(skippedStatusCase);

    // 未处理预订数：COUNT(DISTINCT b.status) FILTER(WHERE b.status = 'unprocessed')
    Expression<String> unprocessedStatusCase = cb.selectCase()
            .when(cb.equal(bookingJoin.get("status"), "unprocessed"), bookingJoin.get("status"))
            .otherwise(null);
    Expression<Long> unprocessedBookingCount = cb.countDistinct(unprocessedStatusCase);

    // 唯一乘客数：COUNT(DISTINCT i.passenger_uuid)
    Expression<Long> distinctPassengerCount = cb.countDistinct(itemJoin.get("passengerUuid"));

    // 最早出发时间：MIN(s.departure_time)
    Expression<java.time.LocalDateTime> minimumDepartureTime = cb.min(segmentJoin.get("departureTime"));

    // 最晚出发时间：MAX(s.departure_time)
    Expression<java.time.LocalDateTime> maximumDepartureTime = cb.max(segmentJoin.get("departureTime"));

    // 组装查询选择列表，对应原SQL的SELECT字段
    query.multiselect(
            batchRoot.get("id"),
            batchRoot.get("name"),
            batchRoot.get("description"),
            batchRoot.get("createTimestamp"),
            batchRoot.get("createdBy"),
            bookingCount.alias("bookingCount"),
            distinctJourneyCount.alias("distinctJourneyCount"),
            succeedBookingCount.alias("succeedBookingCount"),
            failedBookingCount.alias("failedBookingCount"),
            skippedBookingCount.alias("skippedBookingCount"),
            unprocessedBookingCount.alias("unprocessedBookingCount"),
            distinctPassengerCount.alias("distinctPassengerCount"),
            minimumDepartureTime.alias("minimumDepartureTime"),
            maximumDepartureTime.alias("maximumDepartureTime"),
            batchRoot.get("status")
    );

    // 分组依据，对应原SQL的GROUP BY batch.id
    query.groupBy(batchRoot.get("id"));

    // 执行查询
    return entityManager.createQuery(query).getResultList();
}

代码说明 #

• 所有LEFT JOIN操作通过join()方法指定JoinType.LEFT实现，与原SQL关联逻辑完全一致；
• 带过滤的COUNT聚合通过cb.selectCase()构建条件表达式，仅状态匹配时返回对应字段，否则返回NULL，再用countDistinct()统计；
• multiselect()方法对应原SQL的SELECT列表，通过alias()指定结果别名；
• 分组依据直接使用batchRoot.get("id")，由于id是Batch实体的主键，PostgreSQL允许GROUP BY主键时包含实体其他字段。

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