Given an entity definition like the following:
public class FooBar{
@Id
private long id;
private Long fooNumber;
private Long barNumber;
...
//Other inconsequential properties
}
I would like to run the following query:
SELECT * FROM foo_bar WHERE (foo_number, bar_number) IN ((1,2),(1,4),(99,45))
However, I would like the IN clause to take in an arbitrary list of tuples that describe the WHERE parameters. Kind of like:
SELECT * FROM foo_bar WHERE (fooNumber, barNumber) IN :List(FooBarTuple)
I tried to implement this using something like:
@Repository
public interface FooBarRepository extends CrudRepository<FooBar, Long> {
@Query("SELECT id, foo_number, bar_number FROM foo_bar WHERE (foo_number, bar_number) IN :fooBarList;")
List<FooBar> findFooBarIn(@Param("fooBarList") List<FooBarTuple> fooBarList);
}
public class FooBarTuple {
private Long fooNumber;
private Long barNumber;
}
@Service
public class FooBarService {
//Repository inject, other methods
public List<FooBar> getFooBar(List<FooBarTuple> fooBarTupleList) {
return fooBarRepository.findFooBarIn(fooBarTupleList);
}
}
When I try to do this, I unsurprisingly get an exception because, I assume, Spring Data JDBC doesn't know how to map the FooBarTuple class to the IN clause. For completeness, here's an example of the exception I'm seeing:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
Finally, here's an example of what Spring Boot is telling me is being generated by SD JDBC for the above query:
Executing prepared SQL statement [SELECT id, foo_number, bar_number FROM foo_bar WHERE (foo_number, bar_number) IN ?, ?, ?;]
Is it possible to do an IN clause with a tuple in Spring Data JDBC? If so, could someone provide an example for me to use?
PS: Please note this is for Spring Data JDBC not JPA.
> and I'm getting a different exception and a slightly different SD JDBC generated query. Exception: `org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.` Query: `SELECT id, foo_number, bar_number FROM foo_bar WHERE (foo_number, bar_number) IN ?, ?`
– jspillers Jul 18 '23 at 23:42