如何通过Spring JPA实现PostgreSQL多列IN子句查询

问题背景 #

现有PostgreSQL表construction结构及数据如下：

需要实现等价于以下SQL的查询逻辑：

select c.id from construction c
where (c.model, c.status) in (
    ('TO_BE_DECIDED', 'ABANDONED'),
    ('OLD_MODEL', 'COMPLETE') )

要求通过Spring JPA实现，传入List<ConstructionFilter>作为查询条件，同时满足：

• 禁止字符串拼接，确保数据库索引可正常使用
• 支持2列及以上的多列IN查询

原尝试代码因语法错误抛出QuerySyntaxException，代码如下：

@Query("select c.id " +
    "from Construction c " +
    "where (c.model, c.status) in (:filter.model, :filter.status)"
)
List<Long> getConstructions(@Param("filter") List<ConstructionFilter> filter);

// ConstructionFilter定义
@Builder
@Getter
@Setter
public class ConstructionFilter {
    private String model;
    private String status;
}

可行解决方案 #

方案1：原生SQL查询 #

Spring Data JPA的JPQL不支持直接将实体类集合映射为多列元组参数，但原生SQL可直接适配PostgreSQL语法，只需标记查询为原生并正确绑定参数：

@Query(value = "select c.id from construction c " +
               "where (c.model, c.status) in (:filters)",
       nativeQuery = true)
List<Long> getConstructions(@Param("filters") List<ConstructionFilter> filters);

Spring Data JPA会自动将List<ConstructionFilter>中的每个对象转换为PostgreSQL所需的元组格式，参数绑定过程安全无字符串拼接，可正常使用(model, status)联合索引。

方案2：JPA Criteria API动态构建 #

若需跨数据库兼容，可使用Criteria API手动构建等价查询逻辑：

@Autowired
private EntityManager entityManager;

public List<Long> getConstructions(List<ConstructionFilter> filters) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> query = cb.createQuery(Long.class);
    Root<Construction> root = query.from(Construction.class);

    List<Predicate> conditionGroups = new ArrayList<>();
    for (ConstructionFilter filter : filters) {
        // 为每个过滤条件构建(model匹配 AND status匹配)的断言
        Predicate modelEq = cb.equal(root.get("model"), filter.getModel());
        Predicate statusEq = cb.equal(root.get("status"), filter.getStatus());
        conditionGroups.add(cb.and(modelEq, statusEq));
    }

    // 将所有条件组用OR连接，等价于多列IN的逻辑
    query.select(root.get("id")).where(cb.or(conditionGroups.toArray(new Predicate[0])));
    return entityManager.createQuery(query).getResultList();
}

该方式将(a,b) in ((v1,v2),(v3,v4))转换为((a=v1 AND b=v2) OR (a=v3 AND b=v4))，数据库会自动优化此查询，同样能利用联合索引。

原代码报错原因 #

原JPQL写法(:filter.model, :filter.status)不符合JPQL语法规范：JPQL不支持从集合类型参数中直接提取属性组成元组列表，Spring Data JPA无法解析该语法，因此抛出QuerySyntaxException。

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