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

Question

假设我有一个以下形式的查询

SELECT * FROM MYTABLE WHERE MYCOL in (?)

，并且我想将参数参数化为 in。

是否有一种直接的方法可以在 Java 中使用 JDBC 来执行此操作，并且可以在不修改 SQL 本身的情况下在多个数据库上工作？

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

Answer 1

在 JDBC 中确实没有直接的方法来做到这一点。 一些 JDBC 驱动程序似乎支持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 个项目有此限制。

Answer 2

由于没有人回答大型 IN 子句（超过 100 个）的情况，我将针对这个问题提出我的解决方案，该解决方案非常适合 JDBC。简而言之，我将临时表上的 IN 替换为 INNER JOIN 。

我所做的就是创建一个批处理 ID 表，根据 RDBMS，我可以将其创建为临时表或内存表。

该表有两列。一列包含来自 IN 子句的 id，另一列包含我动态生成的批次 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 等于当前批次。完成后，批量删除条目。

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

Answer 4

我通过使用尽可能多的 ? 构建 SQL 字符串来解决这个问题，因为我有要查找的值。

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

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

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 传递。该列表将用于插入所需的占位符并在语句执行期间传入值。

希望这可以帮助你。

Answer 6

AFAIK，JDBC 中没有标准支持将集合作为参数处理。如果您可以传入一个列表并且该列表将被扩展，那就太好了。

Spring 的 JDBC 访问支持将集合作为参数传递。您可以看看这是如何完成的，以获得安全编码的灵感。

请参阅 自动扩展集合作为 JDBC 参数

（本文首先讨论 Hibernate，然后继续讨论 JDBC。）

Answer 7

看看我的尝试，它成功了，据说列表大小有潜在的限制。
List l = Arrays.asList(new Integer[]{12496,12497,12498,12499});
映射参数 = 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);

Answer 8

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

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

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

Answer 9

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

Answer 10

我能想到的一种方法是使用 java.sql.PreparedStatement 和一些陪审团操纵

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

...然后...

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

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