使用 JDBC 参数化 IN 子句的最佳方法是什么？

java sql in-clause jdbc

2022-09-01 00:32:11

假设我有一个表单的查询

SELECT * FROM MYTABLE WHERE MYCOL in (?)

我想将参数化为 in。

有没有一种直接的方法可以在Java中使用JDBC做到这一点，以一种可以在多个数据库上工作而无需修改SQL本身的方式？

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

答案 1

在JDBC中确实没有直接的方法可以做到这一点。一些JDBC 驱动程序似乎支持该子句上的 PreparedStatement#setArray（）。我只是不确定哪些是。IN

您可以使用 String#join（） 和 Collections#nCopies（） 的帮助器方法来生成子句的占位符，并使用另一个帮助器方法使用 PreparedStatement#setObject（） 设置循环中的所有值。IN

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

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

答案 2

由于没有人回答大型IN子句（超过100）的情况，我将抛出我对这个问题的解决方案，这对JDBC来说效果很好。简而言之，我用tmp表上的一个代替了。ININNER JOIN

我所做的是制作我所谓的批处理IDS表，并且根据RDBMS，我可能会将其设置为tmp表或内存表。

该表有两列。一列的 ID 来自 IN 子句，另一列具有我动态生成的批处理 ID。

SELECT * FROM MYTABLE M INNER JOIN IDTABLE T ON T.MYCOL = M.MYCOL WHERE T.BATCH = ?

在选择之前，请将 ID 塞入具有给定批处理 ID 的表中。然后，只需将原始查询 IN 子句替换为 ids 表上的 INNER JOIN 匹配，其中 batch_id 等于当前批处理。完成后，您可以批量删除条目。