在JDBC中没有直接的方法。一些JDBC驱动程序似乎在IN子句上支持
PreparedStatement#setArray()。我只是不知道哪些是。
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]);
}
}
以下是如何使用它:
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;
}
注意,一些数据库在IN子句中具有允许的值的限制。例如,Oracle有1000个项目的此限制。