如何用JPA CriteriaBuilder处理@OneToMany关联对象的字段拼接或求和？

实体类定义 #

RegistryGroupEntity #

@Entity
@Table(name = "registry_group")
public class RegistryGroupEntity {
    @Id
    @SequenceGenerator(name="registry_groups_gen", sequenceName="registry_groups_id_seq", allocationSize = 1)
    @GeneratedValue(generator="registry_groups_gen")
    private Long id;
    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "registryGroup")
    private Collection<ServiceEntity> services;
}

ServiceEntity #

@Entity
@Table(name = "services")
public class ServiceEntity {
    @Id
    @SequenceGenerator(name="service_gen", sequenceName="services_id_seq", allocationSize = 1)
    @GeneratedValue(generator="service_gen")
    private Integer id;
    @Column(nullable = false, length = 100)
    private String name;    
}

目标DTO类 #

public class RegistryGroupRow {
    private Long id;
    private String name;    
    private String serviceNames;

    // 用于接收List<String>后自动拼接
    public RegistryGroupRow(Long id, String name, List<String> serviceNames) {
        this.id = id;
        this.name = name;
        this.serviceNames = serviceNames.stream().collect(Collectors.joining(","));
    }

    // 用于直接接收数据库拼接好的字符串
    public RegistryGroupRow(Long id, String name, String serviceNames) {
        this.id = id;
        this.name = name;
        this.serviceNames = serviceNames;
    }
}

当前问题代码 #

你写的Criteria查询直接传入集合字段，无法匹配DTO构造函数的参数类型：

public List<RegistryGroupRow> getRegistryGroupRows(RegistryGroupFilter filter, Integer offset, Integer limit) {

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<RegistryGroupRow> c = cb.createQuery(RegistryGroupRow.class);
    Root<RegistryGroupEntity> registryGroup = c.from(RegistryGroupEntity.class);


    c.multiselect(registryGroup.get(RegistryGroupEntity_.id),
            registryGroup.get(RegistryGroupEntity_.name),
            registryGroup.get(RegistryGroupEntity_.services) // 直接传集合，无法映射到String或List<String>
    );
}

解决方案 #

1. 拼接服务名为字符串（用数据库聚合函数） #

JPA CriteriaBuilder没有原生的集合字符串拼接API，得借助数据库的聚合函数，不同数据库函数名不同：

• PostgreSQL: string_agg
• MySQL: group_concat
• SQL Server: STRING_AGG

以下是PostgreSQL为例的实现代码：

public List<RegistryGroupRow> getRegistryGroupRows(RegistryGroupFilter filter, Integer offset, Integer limit) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<RegistryGroupRow> cq = cb.createQuery(RegistryGroupRow.class);
    Root<RegistryGroupEntity> registryGroup = cq.from(RegistryGroupEntity.class);
    // 左连接服务表，避免没有服务的分组被过滤
    Join<RegistryGroupEntity, ServiceEntity> servicesJoin = registryGroup.join(RegistryGroupEntity_.services, JoinType.LEFT);

    // 调用数据库的string_agg函数，用逗号分隔拼接服务名
    Expression<String> serviceNamesConcat = cb.function(
        "string_agg",
        String.class,
        servicesJoin.get(ServiceEntity_.name),
        cb.literal(",")
    );

    // 必须按分组字段分组，否则会返回重复数据
    cq.groupBy(registryGroup.get(RegistryGroupEntity_.id), registryGroup.get(RegistryGroupEntity_.name));

    // 用构造函数映射查询结果
    cq.select(cb.construct(
        RegistryGroupRow.class,
        registryGroup.get(RegistryGroupEntity_.id),
        registryGroup.get(RegistryGroupEntity_.name),
        serviceNamesConcat
    ));

    // 添加分页逻辑
    var query = entityManager.createQuery(cq);
    if (offset != null && limit != null) {
        query.setFirstResult(offset).setMaxResults(limit);
    }
    return query.getResultList();
}

如果是MySQL，把string_agg换成group_concat即可，MySQL默认用逗号分隔，也可以手动指定：cb.function("group_concat", String.class, servicesJoin.get(ServiceEntity_.name), cb.literal(","))

2. 收集服务名为List #

JPA Criteria无法直接将集合字段映射为List到DTO，有两种可行方式：

方式A：查询实体后内存转换（简单通用）

先查询RegistryGroupEntity并预加载services集合（避免N+1查询），然后在内存中转换为DTO：

public List<RegistryGroupRow> getRegistryGroupRows(RegistryGroupFilter filter, Integer offset, Integer limit) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<RegistryGroupEntity> cq = cb.createQuery(RegistryGroupEntity.class);
    Root<RegistryGroupEntity> registryGroup = cq.from(RegistryGroupEntity.class);
    // 预加载services集合，避免后续N+1查询
    registryGroup.fetch(RegistryGroupEntity_.services, JoinType.LEFT);

    // 添加你的filter条件（根据实际需求补充）
    // ...

    // 执行查询并转换为DTO
    var query = entityManager.createQuery(cq);
    if (offset != null && limit != null) {
        query.setFirstResult(offset).setMaxResults(limit);
    }
    return query.getResultList().stream()
            .map(group -> new RegistryGroupRow(
                group.getId(),
                group.getName(),
                group.getServices().stream()
                        .map(ServiceEntity::getName)
                        .collect(Collectors.toList())
            ))
            .collect(Collectors.toList());
}

这种方式不依赖数据库特性，兼容性好，适合数据量不大的场景。

方式B：依赖Hibernate扩展（仅适用于Hibernate作为JPA实现）

如果必须用Criteria直接映射，可以使用Hibernate的ResultTransformer，但耦合性较高，示例代码如下：

public List<RegistryGroupRow> getRegistryGroupRows(RegistryGroupFilter filter, Integer offset, Integer limit) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
    Root<RegistryGroupEntity> registryGroup = cq.from(RegistryGroupEntity.class);
    Join<RegistryGroupEntity, ServiceEntity> servicesJoin = registryGroup.join(RegistryGroupEntity_.services, JoinType.LEFT);

    cq.multiselect(
        registryGroup.get(RegistryGroupEntity_.id),
        registryGroup.get(RegistryGroupEntity_.name),
        servicesJoin.get(ServiceEntity_.name)
    );

    var query = entityManager.createQuery(cq);
    if (offset != null && limit != null) {
        query.setFirstResult(offset).setMaxResults(limit);
    }

    // 使用Hibernate的ResultTransformer分组转换结果
    return ((org.hibernate.query.Query<Object[]>) query.unwrap(org.hibernate.query.Query.class))
            .setResultTransformer(new ResultTransformer() {
                private final Map<Long, RegistryGroupRow> groupMap = new HashMap<>();

                @Override
                public Object transformTuple(Object[] tuple, String[] aliases) {
                    Long groupId = (Long) tuple[0];
                    String groupName = (String) tuple[1];
                    String serviceName = (String) tuple[2];

                    RegistryGroupRow row = groupMap.get(groupId);
                    if (row == null) {
                        // 假设RegistryGroupRow新增了List<String>类型的serviceNamesList属性及getter
                        row = new RegistryGroupRow(groupId, groupName, new ArrayList<>());
                        groupMap.put(groupId, row);
                    }
                    if (serviceName != null) {
                        row.getServiceNamesList().add(serviceName);
                    }
                    return row;
                }

                @Override
                public List transformList(List collection) {
                    return new ArrayList<>(groupMap.values());
                }
            })
            .getResultList();
}

这种方式需要修改RegistryGroupRow添加List属性，仅在必要时使用。

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