1

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.

jspillers
  • 61
  • 3
  • 1
    IIRC if you pass a list of arrays it will work, not with arbitrary objects as there is now way Spring (Data) JDBC (or JDBC for that matter) knows how to convert this to your query. – M. Deinum Jul 18 '23 at 06:53
  • @M.Deinum I tried passing List> 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
  • I said a list of arrays not a list of lists... So I would expect something like `List` to work. – M. Deinum Jul 19 '23 at 09:04
  • Thanks for the suggestion, but that doesn't appear to work. I tried passing `List fooBarList= new ArrayList<>(); Long[] entry= {67L, 1L}; fooBarList.add(entry);` and get the following exception: `org.postgresql.util.PSQLException: Invalid elements [{"67","1"}].` Even though it indicates my inputs are Strings, they are definitely Long. It does change the autogenerate query a bit: `SELECT id, foo_number, bar_number FROM foo_bar WHERE (foo_number, bar_number) IN ?` I tried various variations of the above: using all primitive arrays rather than List and using a Long[][] in the list. – jspillers Jul 20 '23 at 01:49
  • Then I'm afraid this isn't going to work and you would have to manually construct that query using a `String` and execute it through a `JdbcTemplate`. – M. Deinum Jul 20 '23 at 05:58

0 Answers0