Multiple column IN clause spring data jpa

Question

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);`

Expecting a query like this:

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

Answer 1

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));
}

Modify the query:

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

And there you nail that :-)

Answer 2

Multiple column with IN clause in not yet supported by Spring data. You can use @Query annotation for custom query as below:

@Query( "select o from MyObject o where id in :ids" ) List findByIds(@Param("ids") List inventoryIdList);