基于JPA CriteriaBuilder处理Competency与ProficiencyLevel关联的可行性咨询

当然可以！CriteriaBuilder完全能搞定这种一对多关联的操作，不管你是要关联查询、过滤关联实体的条件，还是做数据投影、统计，都没问题。先给你举几个常见场景的例子，假设你的实体结构是这样的：

@Entity
public class Competency {
    @Id
    private Long id;
    private String name;
    @OneToMany(mappedBy = "competency")
    private List<ProficiencyLevel> proficiencyLevels;
    // 省略getter/setter
}

@Entity
public class ProficiencyLevel {
    @Id
    private Long id;
    private String levelName;
    private Integer score;
    @ManyToOne
    @JoinColumn(name = "competency_id")
    private Competency competency;
    // 省略getter/setter
}

1. 查询Competency并关联获取其所有ProficiencyLevel #

用fetch来避免N+1查询问题，同时保留关联数据：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Competency> query = cb.createQuery(Competency.class);
Root<Competency> competencyRoot = query.from(Competency.class);

// 左关联获取ProficiencyLevel，确保没有等级的Competency也能被查询到
competencyRoot.fetch("proficiencyLevels", JoinType.LEFT);
// 一对多关联会导致结果重复，所以要去重
query.distinct(true);

List<Competency> result = entityManager.createQuery(query).getResultList();

2. 根据ProficiencyLevel的条件过滤Competency #

比如筛选出包含分数大于80的ProficiencyLevel的Competency：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Competency> query = cb.createQuery(Competency.class);
Root<Competency> competencyRoot = query.from(Competency.class);

// 内关联到ProficiencyLevel，只保留有符合条件等级的Competency
Join<Competency, ProficiencyLevel> levelJoin = competencyRoot.join("proficiencyLevels", JoinType.INNER);
// 添加过滤条件
query.where(cb.greaterThan(levelJoin.get("score"), 80));
// 去重避免重复结果
query.distinct(true);

List<Competency> result = entityManager.createQuery(query).getResultList();

3. 投影关联实体的部分字段 #

比如只查询Competency名称和对应的ProficiencyLevel等级名称：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);
Root<Competency> competencyRoot = query.from(Competency.class);
Join<Competency, ProficiencyLevel> levelJoin = competencyRoot.join("proficiencyLevels");

// 指定要投影的字段
query.multiselect(competencyRoot.get("name"), levelJoin.get("levelName"));

List<Object[]> result = entityManager.createQuery(query).getResultList();
// 处理结果：每个数组的第一个元素是Competency名称，第二个是等级名称

4. 统计每个Competency的ProficiencyLevel数量 #

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<Competency> competencyRoot = query.from(Competency.class);
Join<Competency, ProficiencyLevel> levelJoin = competencyRoot.join("proficiencyLevels", JoinType.LEFT);

// 按Competency分组，统计关联的等级数量
query.groupBy(competencyRoot.get("id"), competencyRoot.get("name"));
query.multiselect(
    competencyRoot.get("name"),
    cb.count(levelJoin.get("id"))
);

List<Tuple> result = entityManager.createQuery(query).getResultList();
// 处理结果：tuple.get(0)是Competency名称，tuple.get(1)是对应的等级数量

只要你的实体间关联映射配置正确（比如@OneToMany和@ManyToOne的关联关系），CriteriaBuilder可以通过join()、fetch()等方法轻松处理一对多关联，几乎能覆盖JPQL支持的所有关联操作。如果有更具体的操作需求（比如复杂排序、多条件组合过滤），可以再细化场景~

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