How to generate a dynamic "in (...)" sql list through Spring JdbcTemplate?

Question

Hi, all.

Is it possible to generate arbitrary "in ()" lists in a SQL query through Jdbc template:

example:

"select * from t where c in (#)" , However '#' could be an arbitrary list of values only known at runtime.

Answer 1

This is not possible with Spring, JDBC, or anything else. You have to do it in two steps:

1. Generate the SQL string with sufficient bind parameters '?'
2. Loop through the array and bind the values to the parameters.

There's no shortcut that I know of - not in JDBC, Spring, Hibernate, or anything else.

Answer 2

Yes, it's possible in Spring if you use NamedParameterJdbcTemplate or SimpleJdbcTemplate with named parameters. List parameter can be set as a java.util.List:

List<String> list = new ArrayList<String>();

list.add("A");
list.add("B");
list.add("C");

List<SomeObject> result = simpleJdbcTemplate.query("SELECT * FROM t WHERE c in (:list)",
    new RowMapper<SomeObject>() { ... },
    Collections.singletonMap("list", list));

In this case Spring internally creates the SQL query with the required number of placeholders based on the size of the actual list when replacing named parameters with ?s.