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

You can do it by @Embeddable annotation [hibernate-in-clause-with-multiple-columns](https://stackoverflow.com/questions/14897082/hibernate-in-clause-with-multiple-columns) — pratik deshai, Oct 25 '19 at 05:15

score 7 · Answer 1 · answered Jul 15 '20 at 08:29

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 :-)

1

this won't use indexes – Sam Ivichuk May 09 '23 at 12:30

score 1 · Answer 2 · answered Mar 29 '19 at 13:18

Yes, It is possible to have multiple "In" clause in a method.

Using spring data jpa and spring boot we can achieve this as below:

For your case you can just write the below method in your repository and it would work fine.

List<Table> findByColumn1InAndColumn2In(List<BigDecimal> column1,List<BigDecimal> column2);

score 1 · Answer 3 · answered Sep 03 '19 at 19:31

1

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

answered Sep 03 '19 at 19:31

Vipin CP

3,642
3
33
55

Multiple column IN clause spring data jpa

3 Answers3