如何使用JPA Criteria API调用函数 解决WHERE不允许返回集合函数报错

报错原因 #

这个错误是因为你调用的GET_RECORD_IDS属于返回多行结果的集合函数，绝大多数关系型数据库（PostgreSQL、Oracle等）都不允许直接在WHERE子句的IN条件中调用这类返回结果集的函数，必须将函数调用放到FROM子句中，以临时表的形式关联后再做过滤。

方案1：改写Specification使用子查询（推荐，单次查询性能好） #

通过子查询将函数调用放到FROM子句中，避免在WHERE里直接调用集合函数，适配绝大多数JPA实现：

return ((root, query, criteriaBuilder) -> {
    // 构造子查询，查询函数返回的ID列表
    Subquery<Long> idSubquery = query.subquery(Long.class);
    Root<?> functionResultRoot = idSubquery.from(
        criteriaBuilder.function("GET_RECORD_IDS", List.class, criteriaBuilder.literal(str))
    );
    // 如果函数返回的是单值集合直接选root，如果返回的是带id字段的行结构改成 functionResultRoot.get("id")
    idSubquery.select(functionResultRoot);
    // 主表id匹配子查询结果
    return root.get("id").in(idSubquery);
});

方案2：分两步查询（写法最简单，兼容所有场景） #

先单独调用函数拿到ID列表，再传入IN条件，适配所有JPA实现和数据库：

// 第一步执行原生查询获取所有目标ID，注意要和你的实体ID类型匹配
List<Long> targetIds = entityManager.createNativeQuery("SELECT GET_RECORD_IDS(:inputStr)")
    .setParameter("inputStr", str)
    .getResultList();

// 第二步构造Specification
return ((root, query, criteriaBuilder) -> root.get("id").in(targetIds));

注意 #

该方案要注意如果返回的ID数量超过数据库IN子句的参数上限（比如Oracle默认上限1000），需要对ID列表做分片处理。

方案3：直接关联函数结果集 #

如果你的JPA实现支持直接关联自定义函数返回的结果集，可以用交叉关联的方式实现，性能最优：

return ((root, query, criteriaBuilder) -> {
    // 把函数返回的结果集作为查询根
    Root<?> functionRoot = query.from(
        criteriaBuilder.function("GET_RECORD_IDS", List.class, criteriaBuilder.literal(str))
    );
    // 关联主表ID和函数返回的ID
    return criteriaBuilder.equal(root.get("id"), functionRoot);
});

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