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

What is the best approach using JDBC for parameterizing an IN clause?

提问by Uri

Say that I have a query of the form

说我有一个表单的查询

SELECT * FROM MYTABLE WHERE MYCOL in (?)

And I want to parameterize the arguments to in.

我想将参数参数化为 in。

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?

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

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

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

回答by tangens

I solved this by constructing the SQL string with as many ?as I have values to look for.

我通过构造与?要查找的值一样多的 SQL 字符串来解决此问题。

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

First I searched for an array type I can pass into the statement, but all JDBC array types are vendor specific. So I stayed with the multiple ?.

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

回答by LoudNPossiblyWrong

One way i can think of is to use the java.sql.PreparedStatement and a bit of jury rigging

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

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

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

... and then ...

... 进而 ...

preparedStmt.setString(1, [your stringged params]);

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

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

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

回答by mdma

AFAIK, there is no standard support in JDBC for handling Collections as parameters. It would be great if you could just pass in a List and that would be expanded.

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

Spring's JDBC access supports passing collections as parameters. You could look at how this is done for inspiration on coding this securely.

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

See Auto-expanding collections as JDBC parameters

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

(The article first discusses Hibernate, then goes on to discuss JDBC.)

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

回答by BalusC

There's indeed no straightforward way to do this in JDBC. SomeJDBC drivers seem to support PreparedStatement#setArray()on the INclause. I am only not sure which ones that are.

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

You could just use a helper method with String#join()and Collections#nCopies()to generate the placeholders for INclause and another helper method to set all the values in a loop with PreparedStatement#setObject().

您可以只使用一个辅助方法 with String#join()andCollections#nCopies()来生成占位符 forIN子句和另一个辅助方法来设置循环中的所有值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;
}

Note that some databases have a limit of allowable amount of values in the INclause. Oracle for example has this limit on 1000 items.

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

回答by Mr Lou

See my trial and It success,It is said that the list size has potential limitation. List l = Arrays.asList(new Integer[]{12496,12497,12498,12499}); Map param = Collections.singletonMap("goodsid",l);

看到我的试用和It成功，据说列表大小有潜在限制。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);

回答by Jeff Miller

sormula makes this simple (see Example 4):

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

回答by Adam Gent

Since nobody answer the case for a large IN clause (more than 100)I'll throw my solution to this problem which works nicely for JDBC. In short I replace the INwith a INNER JOINon a tmp table.

由于没有人回答大 IN 子句（超过 100 个）的情况，我将把我的解决方案用于这个问题，这对 JDBC 非常有效。总之我更换IN了INNER JOIN一个tmp目录表。

What I do is make what I call a batch ids table and depending on the RDBMS I may make that a tmp table or in memory table.

我所做的是制作我所说的批处理 ids 表，并且根据 RDBMS，我可以将其制作为 tmp 表或内存表。

The table has two columns. One column with the id from the IN Clause and another column with a batch id that I generate on the fly.

该表有两列。一列带有 IN 子句中的 ID，另一列带有我即时生成的批处理 ID。

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

Before you select you shove your ids into the table with a given batch id. Then you just replace your original queries IN clause with a INNER JOIN matching on your ids table WHERE batch_id equals your current batch. After your done your delete the entries for you batch.

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

回答by Richard

The standard way to do this is (if you are using Spring JDBC) is to use the org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplateclass.

执行此操作的标准方法是（如果您使用 Spring JDBC）是使用org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate类。

Using this class, it is possible to define a List as your SQL parameter and use the NamedParameterJdbcTemplate to replace a named parameter. For example:

使用此类，可以将 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;
}

回答by Pankaj

There are different alternative approaches that we can use.

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

1. Execute Single Queries - slow and not recommended
2. Using Stored Procedure - database specific
3. Creating PreparedStatement Query dynamically - good performance but loose benefits of caching and needs recompilation
4. Using NULL in PreparedStatement Query - I think this is a good approach with optimal performance.

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

Check more details about these here.

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

回答by guangleiw

I got the answer from docs.spring(19.7.3)

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

The SQL standard allows for selecting rows based on an expression that includes a variable list of values. A typical example would be select * from T_ACTOR where id in (1, 2, 3). This variable list is not directly supported for prepared statements by the JDBC standard; you cannot declare a variable number of placeholders. You need a number of variations with the desired number of placeholders prepared, or you need to generate the SQL string dynamically once you know how many placeholders are required. The named parameter support provided in the NamedParameterJdbcTemplate and JdbcTemplate takes the latter approach. Pass in the values as a java.util.List of primitive objects. This list will be used to insert the required placeholders and pass in the values during the statement execution.

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

Hope this can help you.

希望这可以帮到你。