多列 IN 子句 spring 数据 jpa [英] Multiple column IN clause spring data jpa

问题描述

IN 子句中是否可以有多个列?

Is it possible to have multiple columns in IN clause?

@Query(nativeQuery = true, value = "select * from table where (column1, column2) in (:column1, :column2)")
List<Table> findByColumn1Column2In(@Param("column1") List<BigDecimal> column1, @Param("column2") List<BigDecimal> column2);`

期待这样的查询:

select * from table where (column1, column2) in ((1,2), (3,4), (5,6))

推荐答案

由于 JPA 没有支持多列 IN 子句 我通过在 DB 值上使用 SQL CONCAT 函数来克服这个问题，当然，在提供的范围值上，如下所示:

Since JPA doesn't support multi-columns IN clauses I overcome this by using SQL CONCAT function on the DB values, and of course, on the supplied range values, as follows:

首先，创建我们要查找的范围:

first, create the range we looking for:

List<String> columns;
for (i=0, i<column1.size(), i++){
    columns.add(column1.get(i) + '-' + column2.get(i));
}

修改查询:

@Query(nativeQuery = true,
       value = "select * from table where CONCAT(column1, '-', column2) in (:columns)")
List<Table> findByColumn1Column2In(@Param("columns") List<String> columns);

然后你就搞定了:-)

这篇关于多列 IN 子句 spring 数据 jpa的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！