Support VALUES list in SELECT statements in NamedParameterJdbcTemplate

[roookeee]

Preamble: I have been sent here by @schauder in spring-data-relational in regards to https://github.com/spring-projects/spring-data-relational/issues/1300.

Currently JdbcTemplate does not support SELECT statements that use the VALUES keyword. Here is an example from spring-data-jdbc that uses JdbcTemplate under the hood:

@Repository
interface MyRepository {
    @Query("SELECT * FROM my_entity WHERE id IN (VALUES (:ids))")
    List<MyEntity> myQuery(List<String> ids)
}

:ids is not properly expanded (it needs to put every list entry into ()) and thus generates wrong SQL. I know this would be complex to support for complex objects types as IN-statements works with tuples in some databases but the simple, one-valued variant should be pretty straightforward.

At least PostgreSQL generates different plans for a simple IN vs IN VALUES clause, especially when the input list is big (>100) which perform quite differently (10-30% worse for us). Using a VALUES list is also interesting when using CTE (WITH) to populate a temporary table with user provided input, this is not achievable with an IN statement.

Disclaimer: I haven't checked if VALUES expansion works in custom INSERTS

[jhoeller]

I suppose this works fine with regular positional parameters (where we do not parse the provided SQL ourselves), and you are effectively asking for NamedParameterJdbcTemplate support for VALUES, expanding a nested named parameter accordingly?

[schauder]

I suppose this works fine with regular positional parameters (where we do not parse the provided SQL ourselves), and you are effectively asking for NamedParameterJdbcTemplate support for VALUES, expanding a nested named parameter accordingly?

Yes, this is correct. Spring Data JDBC uses NamedParameterJdbcTemplate and it's named parameters exclusively.

[encircled]

@jhoeller, I can prepare a PR for this, but I'm not sure if such a feature belongs in the framework, as it is not supported by jdbc statements.