使用JDBC参数化IN子句的最佳方法是什么？ [英] What is the best approach using JDBC for parameterizing an IN clause?

问题描述

说我有一个表格的查询

SELECT * FROM MYTABLE WHERE MYCOL in (?)

我想将参数参数化为。

有没有一种直接的方法在Java中使用JDBC，这种方式可以在不修改SQL本身的情况下在多个数据库上运行？

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?

最近的我发现的问题与C＃有关，我想知道Java / JDBC是否有不同之处。

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

推荐答案

在JDBC中确实没有直接的方法。 某些 JDBC驱动程序似乎支持 IN 子句中的sql.Array-rel =noreferrer> PreparedStatement＃setArray（） 。我只是不确定是哪一个。

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.

您可以使用辅助方法 String #join（） 和 集合＃ nCopies（） 生成 IN 子句的占位符，另一个辅助方法用 PreparedStatement＃setObject（） 。

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;
}

请注意，某些数据库在<$中有可允许的值限制c $ c> IN 子句。例如，Oracle对1000个项目有此限制。

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.

这篇关于使用JDBC参数化IN子句的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！