JdbcClient should reject a List as the parameter of the IN clause of an SQL statement when using positional parameters

[hansdesmet]

Most of the methods of JdbcClient work with positional parameters in the SQL statement as well as with named parameters 👍.

When I use a named parameter, i can use a List as the value for a named parameter in the IN clause of an SQL statement 👍:

List<Person> persons=jdbcClient.sql("select id,name from persons where id in (:ids)")
  .param("ids", List.of(1,3))
  .query(Person.class)
  .list();

When I try the same with a positional parameter, I get an exception 😔:
org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [select id,name from persons where id in (?)]; Data conversion error converting "JAVA_OBJECT to BIGINT"; SQL statement:
select id,name from persons where id in (?)

List<Person> persons=jdbcClient.sql("select id,name from persons where id in (?)")
  .param(List.of(1,3))
  .query(Person.class)
  .list();

I put a project on https://github.com/hansdesmet/jdbcclientproposal2 that shows the problem.

Is it possible to make this work with a positional parameter ?

[sbrannen]

Thanks for raising the issue. We'll look into it.

[jhoeller]

I'm afraid this is not idiomatic with positional parameters since the given SQL statement remains unmodified there, without any expansion of placeholders. Just like with direct JDBC usage, the parameter positions need to match the exact "?" placeholders in the SQL statement.

That said, we could detect a provided Collection parameter in param(Object) and raise a meaningful exception, indicating that such parameter expansion is only supported with named parameters.