如何用JPA Criteria API实现PostgreSQL STRING_AGG的有序聚合？

在PostgreSQL的Film与Actor多对多关系场景下，标准JPA Criteria API不直接支持STRING_AGG聚合函数内的排序子句，以下是三种可行的解决方式：

方案1：利用Hibernate CriteriaBuilder扩展构造带排序的STRING_AGG #

若你的JPA提供者为Hibernate，可使用其扩展的HibernateCriteriaBuilder构造符合PostgreSQL语法的带排序聚合函数：

EntityManager em = getEntityManager();
HibernateCriteriaBuilder hcb = em.unwrap(Session.class).getCriteriaBuilder();
CriteriaQuery<Tuple> cq = hcb.createTupleQuery();

// 关联实体
Root<Film> filmRoot = cq.from(Film.class);
Join<Film, Actor> actorJoin = filmRoot.join("actors");

// 构造带升序排序的STRING_AGG函数
Expression<String> actorNamesAgg = hcb.function(
    "STRING_AGG",
    String.class,
    actorJoin.get("name"),
    hcb.literal(", "),
    hcb.order(actorJoin.get("name"), SortOrder.ASC)
);

// 指定查询字段并分组
cq.multiselect(
    filmRoot.get("id"),
    filmRoot.get("title"),
    actorNamesAgg
);
cq.groupBy(filmRoot.get("id"), filmRoot.get("title"));

// 执行查询并处理结果
List<Tuple> result = em.createQuery(cq).getResultList();
for (Tuple tuple : result) {
    Long filmId = tuple.get(0, Long.class);
    String title = tuple.get(1, String.class);
    String actorNames = tuple.get(2, String.class);
    // 业务逻辑处理
}

方案2：在实体类中使用@Formula注解定义聚合字段 #

若无需动态构建查询，可直接在Film实体中通过@Formula映射聚合后的演员名字字段：

@Entity
@Table(name = "film")
public class Film {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;
    
    @ManyToMany
    @JoinTable(
        name = "film_actor",
        joinColumns = @JoinColumn(name = "film_id"),
        inverseJoinColumns = @JoinColumn(name = "actor_id")
    )
    private List<Actor> actors;
    
    // 自动按名字升序拼接演员列表
    @Formula("(SELECT STRING_AGG(a.name, ', ' ORDER BY a.name ASC) FROM film_actor fa JOIN actor a ON fa.actor_id = a.id WHERE fa.film_id = id)")
    private String actorNames;
    
    // Getters & Setters
}

查询时直接获取实体即可得到聚合结果：

List<Film> films = em.createQuery("SELECT f FROM Film f", Film.class).getResultList();
for (Film film : films) {
    System.out.println(film.getTitle() + ": " + film.getActorNames());
}

方案3：直接执行原生SQL查询 #

若已有成熟的原生SQL，可绕过Criteria API直接执行：

String nativeSql = """
    SELECT f.id, f.title, STRING_AGG(a.name, ', ' ORDER BY a.name ASC) AS actor_names
    FROM film f
    JOIN film_actor fa ON f.id = fa.film_id
    JOIN actor a ON fa.actor_id = a.id
    GROUP BY f.id, f.title
""";

List<Tuple> result = em.createNativeQuery(nativeSql, Tuple.class).getResultList();
for (Tuple tuple : result) {
    Long filmId = ((Number) tuple.get("id")).longValue();
    String title = (String) tuple.get("title");
    String actorNames = (String) tuple.get("actor_names");
    // 业务逻辑处理
}

方案对比 #

• 方案1：保留Criteria API的类型安全，适合动态查询场景，但依赖Hibernate特定API，移植性较弱。
• 方案2：实现简单，无需额外查询逻辑，但聚合逻辑固定在实体中，灵活性不足。
• 方案3：完全兼容PostgreSQL语法，无JPA API限制，适合已有成熟原生SQL的场景，但失去Criteria API的类型安全特性。

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