java jdbc如何使用in,使用JDBC参数化IN子句的最佳方法是什么？

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.

解决方案

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 find(Set ids) throws SQLException {

List entities = new ArrayList();

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.