JPA Criteria API如何投影关联集合ID并构造自定义轻量化DTO

• 调用join关联contextTypes集合时，JPA会将结果打平：每条SQL结果行对应一个ContextType记录，因此返回的contextTypeId是单个Long类型，和SlimAssessment构造方法要求的Set<Long>参数不匹配，才会报构造方法找不到的错误。
• 直接joinBusinessUnit表属于多余操作：Assessment实体的businessUnit是多对一关联，外键字段本身就存在assessment表中，无需关联查询BusinessUnit表即可直接获取外键ID。

方案1：兼容所有JPA实现的内存分组方案（无数据库依赖） #

这种方案不需要依赖数据库的聚合函数，兼容性最强：
首先调整Criteria查询，只查询打平的基础字段，然后在内存中分组组装SlimAssessment：

CriteriaBuilder builder = em.getCriteriaBuilder();
// 先定义Tuple查询，接收打平的结果
CriteriaQuery<Tuple> query = builder.createTupleQuery();
Root<Assessment> ra = query.from(Assessment.class);
// 不需要join BusinessUnit，直接取外键ID，JPA会自动读取assessment表的business_unit_id字段
Path<Long> buIdPath = ra.get(Assessment_.businessUnit).get(BusinessUnit_.id);
SetJoin<Assessment, ContextType> contextTypeJoin = ra.join(Assessment_.contextTypes, JoinType.LEFT); // 如果允许contextTypes为空用LEFT JOIN

query.select(builder.tuple(
        ra.get(Assessment_.ID),
        ra.get(Assessment_.CREATED_DATE),
        ra.get(Assessment_.SCORE),
        buIdPath,
        contextTypeJoin.get(ContextType_.ID)
)).orderBy(builder.asc(ra.get(Assessment_.CREATED_DATE)));

// 执行查询得到打平结果
List<Tuple> tuples = em.createQuery(query).getResultList();

// 内存分组组装SlimAssessment
Map<Long, SlimAssessment> assessmentMap = new LinkedHashMap<>();
for (Tuple tuple : tuples) {
    Long id = tuple.get(0, Long.class);
    SlimAssessment assessment = assessmentMap.get(id);
    if (assessment == null) {
        ZonedDateTime createdDate = tuple.get(1, ZonedDateTime.class);
        Float score = tuple.get(2, Float.class);
        Long buId = tuple.get(3, Long.class);
        assessment = new SlimAssessment(id, createdDate, score, buId, new HashSet<>());
        assessmentMap.put(id, assessment);
    }
    Long contextTypeId = tuple.get(4, Long.class);
    if (contextTypeId != null) {
        assessment.contextTypeIds.add(contextTypeId);
    }
}
// 最终结果
List<SlimAssessment> result = new ArrayList<>(assessmentMap.values());

该方案唯一的开销是内存分组，数据量不大的情况下性能足够好。

方案2：基于数据库聚合函数的单查询方案（性能更优） #

如果使用支持集合聚合的数据库（如PostgreSQL的array_agg、MySQL的group_concat），可以直接在查询层聚合contextTypeIds，避免内存分组。以PostgreSQL为例：

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<SlimAssessment> query = builder.createQuery(SlimAssessment.class);
Root<Assessment> ra = query.from(Assessment.class);
Path<Long> buIdPath = ra.get(Assessment_.businessUnit).get(BusinessUnit_.id);
SetJoin<Assessment, ContextType> contextTypeJoin = ra.join(Assessment_.contextTypes, JoinType.LEFT);

// 使用array_agg聚合contextType id
Expression<Set<Long>> contextTypeIdsExp = builder.function(
        "array_agg", 
        Set.class, 
        contextTypeJoin.get(ContextType_.ID)
);

query.multiselect(
        ra.get(Assessment_.ID),
        ra.get(Assessment_.CREATED_DATE),
        ra.get(Assessment_.SCORE),
        buIdPath,
        contextTypeIdsExp
).groupBy(
        ra.get(Assessment_.ID),
        ra.get(Assessment_.CREATED_DATE),
        ra.get(Assessment_.SCORE),
        buIdPath
).orderBy(builder.asc(ra.get(Assessment_.CREATED_DATE)));

List<SlimAssessment> result = em.createQuery(query).getResultList();

该方案只执行一次SQL，性能最优，但是需要依赖数据库的聚合函数支持。

方案3：原生SQL查询（性能最高，完全避免不必要的表关联） #

如果要彻底去掉context_type表的关联，直接查中间表assessment_context_type拿ID，可以用原生SQL实现：

String sql = """
    SELECT a.id, a.created_date, a.score, a.business_unit_id, array_agg(act.context_type_id) as context_type_ids
    FROM assessment a
    LEFT JOIN assessment_context_type act ON a.id = act.assessment_id
    GROUP BY a.id, a.created_date, a.score, a.business_unit_id
    ORDER BY a.created_date ASC
    """;
// 可通过@SqlResultSetMapping注解直接映射到SlimAssessment，或者用Tuple接收后手动组装
List<SlimAssessment> result = em.createNativeQuery(sql, "SlimAssessmentMapping")
        .getResultList();

该方案完全没有关联business_unit和context_type表，性能最高，适合对性能要求高的场景。

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