如何使用 WHERE x IN 子句编写PreparedStatement 的SQL？

Question

我有一个如下所示的查询：

SELECT last_name,
       first_name,
       middle_initial
FROM   names
WHERE  last_name IN ('smith', 'jones', 'brown')

我需要能够对 IN 子句中的列表进行参数化，以将其编写为 JDBCPreparedStatement。该列表中可以包含任意数量的名称。

正确的方法是：

SELECT last_name,
       first_name,
       middle_initial
FROM   names
WHERE  last_name IN (?)

然后构建参数列表？或者有更好（更正确）的方法来做到这一点？

Answer 1

简而言之，你无法开箱即用。然而，有了Spring你就可以做你想做的事。请参阅如何在 (. ..)”通过 Spring JdbcTemplate 的 sql 列表？

Answer 2

标准 SQL 不允许将 IN 子句参数化为单个变量 - 仅支持动态 SQL，即在执行之前使用逗号分隔的值列表将 SQL 查询构造为字符串。

Answer 3

我也打算研究这个话题。我曾因编写类似的代码而感到内疚，并且从未对它感到 100% 舒服。我想我想在“可变 SQL 参数列表”上找到一些内容。

在代码中，使用 hibernate，并给定一个逗号分隔的订单 Id 字符串，我使用了：

Session s = getSession();
Criteria crit = s.createCriteria(this.getOrderListingClass());
crit.add(Expression.sql(String.format("{alias}.orderId in (%s)", orderIds)));
crit.add(Expression.eq("status", OrderInfo.Order_STATUS_UNFILLED));
orders = crit.list();

而 orderId 实际上是“SELECT x FROM y WHERE IN (%s)”的一部分。

1. 在将 orderIds 字符串传递给 hibernate 之前，我确实通过验证器运行了它 - 担心注入等。

2. 我一直想做的其他事情是检查 SQL 参数的限制和查询中的字符数。我似乎记得在 2000+ 左右达到了极限（使用 MS SQL）。如果您采用这种方法，这是需要考虑的事情。

我认为这是笨拙的...在Where 子句中传递这么多ID，但这是需要重构的代码部分。值得庆幸的是，该用例在任何时候都只看到少数 Id 被查询。

Answer 4

您还可以将查询构建为存储过程，该存储过程将参数化列表作为 varchar。例如，在 sql server 中：

  CREATE PROCEDURE dbo.[procedure_name]

        @IN_LIST VARCHAR(MAX)
    AS
    BEGIN

        DECLARE @SQL VARCHAR(MAX)
        SET @SQL = '
            SELECT last_name,
                first_name,
                middle_initial
            FROM names
            WHERE  last_name IN (' + @IN_LIST + ')'

        EXECUTE(@SQL)
    END

只需确保 @IN_LIST 的格式为包含单引号和逗号的字符串。例如在java中：

String inList = "'smith','jones','brown'";

Answer 5

如果您使用 MS SQL Server，请尝试重塑您的 TSQL 以使用 UDF，也许 这我的帖子可以帮助你