What is the best approach using JDBC for parameterizing an IN clause? [duplicate]

Question

Say that I have a query of the form

SELECT * FROM MYTABLE WHERE MYCOL in (?)

And I want to parameterize the arguments to in.

Is there a straightforward way to do this in Java with JDBC, in a way that could work on multiple databases without modifying the SQL itself?

The closest question I've found had to do with C#, I'm wondering if there is something different for Java/JDBC.

Answer 1

There's indeed no straightforward way to do this in JDBC. Some JDBC drivers seem to support PreparedStatement#setArray() on the IN clause. I am only not sure which ones that are.

You could just use a helper method with String#join() and Collections#nCopies() to generate the placeholders for IN clause and another helper method to set all the values in a loop with PreparedStatement#setObject().

public static String preparePlaceHolders(int length) {     return String.join(",", Collections.nCopies(length, "?")); }  public static void setValues(PreparedStatement preparedStatement, Object... values) throws SQLException {     for (int i = 0; i < values.length; i++) {         preparedStatement.setObject(i + 1, values[i]);     } } 

Here's how you could use it:

private static final String SQL_FIND = "SELECT id, name, value FROM entity WHERE id IN (%s)";  public List<Entity> find(Set<Long> ids) throws SQLException {     List<Entity> entities = new ArrayList<Entity>();     String sql = String.format(SQL_FIND, preparePlaceHolders(ids.size()));      try (         Connection connection = dataSource.getConnection();         PreparedStatement statement = connection.prepareStatement(sql);     ) {         setValues(statement, ids.toArray());          try (ResultSet resultSet = statement.executeQuery()) {             while (resultSet.next()) {                 entities.add(map(resultSet));             }         }     }      return entities; }  private static Entity map(ResultSet resultSet) throws SQLException {     Enitity entity = new Entity();     entity.setId(resultSet.getLong("id"));     entity.setName(resultSet.getString("name"));     entity.setValue(resultSet.getInt("value"));     return entity; } 

Note that some databases have a limit of allowable amount of values in the IN clause. Oracle for example has this limit on 1000 items.

Answer 2

Since nobody answer the case for a large IN clause (more than 100) I'll throw my solution to this problem which works nicely for JDBC. In short I replace the IN with a INNER JOIN on a tmp table.

What I do is make what I call a batch ids table and depending on the RDBMS I may make that a tmp table or in memory table.

The table has two columns. One column with the id from the IN Clause and another column with a batch id that I generate on the fly.

SELECT * FROM MYTABLE M INNER JOIN IDTABLE T ON T.MYCOL = M.MYCOL WHERE T.BATCH = ?

Before you select you shove your ids into the table with a given batch id. Then you just replace your original queries IN clause with a INNER JOIN matching on your ids table WHERE batch_id equals your current batch. After your done your delete the entries for you batch.

Answer 3

The standard way to do this is (if you are using Spring JDBC) is to use the org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate class.

Using this class, it is possible to define a List as your SQL parameter and use the NamedParameterJdbcTemplate to replace a named parameter. For example:

public List<MyObject> getDatabaseObjects(List<String> params) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    String sql = "select * from my_table where my_col in (:params)";
    List<MyObject> result = jdbcTemplate.query(sql, Collections.singletonMap("params", params), myRowMapper);
    return result;
}