慕粉4167745
在JDBC中确实没有直接的方法来做到这一点。一些 JDBC驱动程序似乎支持PreparedStatement#setArray()该IN子句。我只是不确定那是哪一个。你可以使用helper方法String#join()和Collections#nCopies()to生成占位符for IN子句和另一个helper方法来设置循环中的所有值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
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个项目有此限制。