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

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

I solved this by constructing the SQL string with as many ? as I have values to look for.

SELECT * FROM MYTABLE WHERE MYCOL in (?,?,?,?)

First I searched for an array type I can pass into the statement, but all JDBC array types are vendor specific. So I stayed with the multiple ?.

Answer 2

One way i can think of is to use the java.sql.PreparedStatement and a bit of jury rigging

PreparedStatement preparedStmt = conn.prepareStatement("SELECT * FROM MYTABLE WHERE MYCOL in (?)");

... and then ...

preparedStmt.setString(1, [your stringged params]);

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

Answer 3

AFAIK, there is no standard support in JDBC for handling Collections as parameters. It would be great if you could just pass in a List and that would be expanded.

Spring's JDBC access supports passing collections as parameters. You could look at how this is done for inspiration on coding this securely.

See Auto-expanding collections as JDBC parameters

(The article first discusses Hibernate, then goes on to discuss JDBC.)

Answer 4

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. I myself just use String#format() and a helper method to generate the placeholders for IN clause in a loop (which could be shortened if there was some String#join() method in Java API) and another helper method to set all the values in a loop.

public static String preparePlaceHolders(int length) {
    StringBuilder builder = new StringBuilder(length * 2 - 1);
    for (int i = 0; i < length; i++) {
        if (i > 0) builder.append(',');
        builder.append('?');
    }
    return builder.toString();
}

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:

import static com.example.SQLUtil.*;

// ...

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 {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<Entity> entities = new ArrayList<Entity>();
    String sql = String.format(SQL_FIND, preparePlaceHolders(ids.size()));

    try{
        connection = database.getConnection();
        statement = connection.prepareStatement(sql);
        setValues(statement, ids.toArray());
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            Enitity entity = new Entity();
            entity.setId(resultSet.getLong("id"));
            entity.setName(resultSet.getString("name"));
            entity.setValue(resultSet.getInt("value"));
            entities.add(entity);
        }
    } finally {
        close(connection, statement, resultSet);
    }

    return entities;
}

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.