Creating lists of arbitrary size in SQL queries

Question

I'd like to create a SQL statement like this:

SELECT COUNT(*) FROM table WHERE id IN (1, 2, 3, ..., n)

But I can't figure out how to insert an arbitrary number of values into this statement.

I've tried this:

SELECT COUNT(*) FROM table WHERE id IN (?)

where I use preparedStatement.setString(1, "1, 2, 3, ..., n") (The string is built through code)

But H2 throws a Data conversion exception

org.h2.jdbc.JdbcSQLException: Data conversion error converting "1, 2, ..."; SQL statement:

How do I bypass this and get H2 to correctly interpret this as a list of integers instead of a string?

You may try to use [`array_contains`](http://www.h2database.com/html/functions.html#array_contains) function and directly bind an array to the bind variable: `where array_contains(?, id)`, but I do not know if H2 JDBC supports an array. — astentx, Nov 15 '21 at 22:25

The Impaler · Accepted Answer · 2021-11-15T22:10:15.497

You need to add each value as a separate JDBC parameter (?). To do this you'll need to assemble the query in a dynamic way. Your code could look like:

List<Integer> values = ...
String sql = "SELECT COUNT(*) FROM table WHERE id IN (" +
  values.stream().map(v -> "?").collect(Collectors.joining(", ") +
  ")";
PreparedStatement ps = conn.prepareStatement(sql);
int param = 1;
for (Integer v : values) {
  ps.setInt(param++, v);
}

Note: You should also consider the H2 database and H2 JDBC driver may impose restrictions in the length of the SQL statement and also in the numbers of parameters. As a general rule, SQL statements less than 10000 chars with less than 100 parameters are accepted by all databases. However, I would check with H2's documentation if you exceed these basic limits.

Creating lists of arbitrary size in SQL queries

1 Answers1