使用JDBC，如何最好地对IN子句进行参数化处理？

Question

假设我有一个查询形式如下：

SELECT * FROM MYTABLE WHERE MYCOL in (?)

我希望将参数化为" in "的参数。

在Java中，使用JDBC是否有一种简单的方法可以实现这一点，而不需要修改SQL本身，可以适用于多个数据库？

最接近的问题是关于C#的, 我想知道Java/JDBC是否有不同的解决方案。

- Uri

Answer 1

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

你可以使用带有String#join()和Collections#nCopies()的辅助方法为IN子句生成占位符，并使用另一个辅助方法循环设置所有值并用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]);
    }
}

以下是如何使用它的示例：

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

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

- BalusC

---

这种方法会导致 SQL 注入吗？ - Kalyan Raju

@Kaylan说：没有任何单独的代码行将用户控制的输入原始数据添加到SQL查询字符串中。因此，绝对不存在SQL注入风险。 - BalusC

jtds驱动程序的最大参数变量列表为2,000。 - CrashCodes

在MySQL（驱动程序5.1.37）和PostgreSQL（驱动程序9.1-901）之间，只有PostgreSQL对PreparedStatement＃setArray（）提供了一些支持。 - gkephorus

SQL Server 没有 setArray 方法：https://github.com/microsoft/mssql-jdbc/blob/main/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerPreparedStatement.java - simon04

Answer 2

由于没有人回答一个包含超过100个元素的大型IN语句问题，我将提供我的解决方案，对于JDBC来说，这个方法可以很好地解决问题。简而言之，我将用一个临时表上的INNER JOIN替换IN。

我所做的是创建一个批处理ID表，根据关系数据库管理系统的不同，我可能会将其设置为临时表或内存表。

该表有两列，一列是IN子句中的ID，另一列是我动态生成的批次ID。

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

在将您的 ID 插入具有给定批次 ID 的表之前，您需要选择。然后，您只需用 INNER JOIN 替换原始查询中的 IN 子句，匹配您的 ID 表并且 WHERE 子句中的 batch_id 等于当前批次。完成后，您可以删除该批次对应的条目。

- Adam Gent

---

+1 对于大数据集来说，这将是相当高效的，并且不会破坏你的数据库。 - jasonk

嗯，使用半连接（IN或EXISTS谓词）可能会比内连接更高效吧？ - Lukas Eder

@LukasEder 我的 SQL 伪代码可能会因人而异。一如既往，请进行测试/基准测试。这是一个有趣的想法。说到这个，我应该去看看我们实际执行此操作时的 SQL 是什么。 - Adam Gent

确实，在MySQL上，半连接仍然有可能变慢。但是内部连接的风险则是不正确的 ;) - Lukas Eder

@男士，当您需要从JDBC函数中输出包含所有结果的完整ResultSet（例如具有约定/固定API的ResultSet）时，这将是一个不错的选择：填充临时表并进行连接，而不是使用无法合并ResultSet的IN-in-batches。（注意：如果您只拥有SELECT权限，则不幸的是您将无法创建临时表） - gkephorus

Answer 3

这个问题的标准解决方法（如果您正在使用Spring JDBC）是使用org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate类。使用这个类，可以将List定义为SQL参数，并使用NamedParameterJdbcTemplate替换命名参数。例如：

public List<MyObject> getDatabaseObjects(List<String> params) {
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    String sql = "select * from my_table where my_col in (:params)";
    List<MyObject> result = jdbcTemplate.query(sql, Collections.singletonMap("params", params), myRowMapper);
    return result;
}

- Richard

Answer 4

我通过构建SQL字符串，使用与我要查找的值相同数量的?来解决了这个问题。

SELECT * FROM MYTABLE WHERE MYCOL in (?,?,?,?)

首先，我搜索了可以传递到语句中的数组类型，但所有的JDBC数组类型都是特定于供应商的。因此，我仍然使用了多个?。

- tangens

---

这就是我们正在做的事情，但我希望有一种统一的方法来避免使用自定义SQL... - Uri

同时，如果是Oracle这样的东西，它会需要重新解析几乎每条语句。 - orbfish

Answer 5

我从docs.spring(19.7.3)中得到答案。

SQL标准允许根据包含变量值列表的表达式选择行。一个典型的例子是select * from T_ACTOR where id in (1, 2, 3)。然而，JDBC标准不直接支持预处理语句中的变量列表；您无法声明可变数量的占位符。您需要准备所需数量的占位符的多个变化版本，或者在知道需要多少个占位符后动态生成SQL字符串。NamedParameterJdbcTemplate和JdbcTemplate提供的命名参数支持采用后一种方法。将值作为java.util.List原始对象传递。此列表将用于插入所需的占位符并在语句执行期间传递值。

希望这可以帮助您。

- guangleiw

Answer 6

据我所知，JDBC没有标准支持处理集合作为参数。如果您可以只传递一个列表并进行扩展，那将是很好的。

Spring的JDBC访问支持将集合作为参数传递。您可以查看如何安全编码以获取灵感。

请参阅自动扩展集合作为JDBC参数 （该文章首先讨论Hibernate，然后继续讨论JDBC。）

- mdma

Answer 7

看到我的试验成功了，据说列表大小有潜在的限制。 List l = Arrays.asList(new Integer[]{12496,12497,12498,12499}); Map param = Collections.singletonMap("goodsid",l);

    NamedParameterJdbcTemplate  namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
    String sql = "SELECT bg.goodsid FROM beiker_goods bg WHERE bg.goodsid in(:goodsid)";
    List<Long> list = namedParameterJdbcTemplate.queryForList(sql, param2, Long.class);

- Mr Lou

Answer 8

sormula让这变得简单（参见示例4）：

ArrayList<Integer> partNumbers = new ArrayList<Integer>();
partNumbers.add(999);
partNumbers.add(777);
partNumbers.add(1234);

// set up
Database database = new Database(getConnection());
Table<Inventory> inventoryTable = database.getTable(Inventory.class);

// select operation for list "...WHERE PARTNUMBER IN (?, ?, ?)..."
for (Inventory inventory: inventoryTable.
    selectAllWhere("partNumberIn", partNumbers))    
{
    System.out.println(inventory.getPartNumber());
}

- Jeff Miller

Answer 9

我们可以使用不同的替代方法。

1. 执行单个查询-慢且不建议
2. 使用存储过程-数据库特定
3. 动态创建PreparedStatement查询-性能良好，但失去了缓存的好处并需要重新编译
4. 在PreparedStatement查询中使用NULL-我认为这是一种具有最佳性能的良好方法。

有关这些的更多详细信息在此处查看。

- Pankaj

Answer 10

一种我能想到的方法是使用java.sql.PreparedStatement和一点儿临时修补。

PreparedStatement preparedStmt = conn.prepareStatement("SELECT * FROM MYTABLE WHERE MYCOL in (?)");

...然后... preparedStmt.setString(1, [你的字符串参数]);

http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

- LoudNPossiblyWrong

---

这样做不会起作用，因为它可能会创建一个查询，类似于 ... WHERE MYCOL IN ('2,3,5,6') ，这不是你想要的查询。 - Progman