在java.sql实例中使用SQL IN子句的最佳变通方法是什么?由于SQL注入攻击安全问题,不支持多值的PreparedStatement:一个?占位符表示一个值,而不是一个值列表。

考虑下面的SQL语句:

SELECT my_column FROM my_table where search_column IN (?)

使用preparedStatement。setString(1, "'A', 'B', 'C'");本质上是一种无用的尝试,试图解决使用原因?首先。

有什么可行的解决办法?


当前回答

您可以使用集合。nCopies生成一个占位符集合,并使用String.join将它们连接起来:

List<String> params = getParams();
String placeHolders = String.join(",", Collections.nCopies(params.size(), "?"));
String sql = "select * from your_table where some_column in (" + placeHolders + ")";
try (   Connection connection = getConnection();
        PreparedStatement ps = connection.prepareStatement(sql)) {
    int i = 1;
    for (String param : params) {
        ps.setString(i++, param);
    }
    /*
     * Execute query/do stuff
     */
}

其他回答

在javaranche Journal上Jeanne Boyarsky的JDBC批处理选择语句条目中,有对各种可用选项及其优缺点的分析。

建议的方案是:

Prepare SELECT my_column FROM my_table WHERE search_column = ?, execute it for each value and UNION the results client-side. Requires only one prepared statement. Slow and painful. Prepare SELECT my_column FROM my_table WHERE search_column IN (?,?,?) and execute it. Requires one prepared statement per size-of-IN-list. Fast and obvious. Prepare SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ... and execute it. [Or use UNION ALL in place of those semicolons. --ed] Requires one prepared statement per size-of-IN-list. Stupidly slow, strictly worse than WHERE search_column IN (?,?,?), so I don't know why the blogger even suggested it. Use a stored procedure to construct the result set. Prepare N different size-of-IN-list queries; say, with 2, 10, and 50 values. To search for an IN-list with 6 different values, populate the size-10 query so that it looks like SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6). Any decent server will optimize out the duplicate values before running the query.

这些选择都不理想。

如果您正在使用JDBC4并且服务器支持x = ANY(y),那么最好的选择是使用PreparedStatement。setArray的描述在鲍里斯的回答。

不过,似乎没有任何方法可以使setArray与IN-lists一起工作。


有时SQL语句是在运行时加载的(例如,从属性文件中加载),但需要可变数量的参数。在这种情况下,首先定义查询:

query=SELECT * FROM table t WHERE t.column IN (?)

接下来,加载查询。然后在运行它之前确定参数的数量。一旦参数计数已知,运行:

sql = any( sql, count );

例如:

/**
 * Converts a SQL statement containing exactly one IN clause to an IN clause
 * using multiple comma-delimited parameters.
 *
 * @param sql The SQL statement string with one IN clause.
 * @param params The number of parameters the SQL statement requires.
 * @return The SQL statement with (?) replaced with multiple parameter
 * placeholders.
 */
public static String any(String sql, final int params) {
    // Create a comma-delimited list based on the number of parameters.
    final StringBuilder sb = new StringBuilder(
        String.join(", ", Collections.nCopies(possibleValue.size(), "?")));

    // For more than 1 parameter, replace the single parameter with
    // multiple parameter placeholders.
    if (sb.length() > 1) {
        sql = sql.replace("(?)", "(" + sb + ")");
    }

    // Return the modified comma-delimited list of parameters.
    return sql;
}

对于某些不支持通过JDBC 4规范传递数组的数据库,此方法可以方便地转换缓慢的= ?转换为更快的IN(?)子句条件,然后可以通过调用any方法进行扩展。

没有简单的办法。 如果目标是保持较高的语句缓存比(即不是每个参数都创建一条语句),您可以执行以下操作:

创建带有几个参数(例如10个)的语句: ... 一个在 (?,?,?,?,?,?,?,?,?,?) ... 绑定所有实际参数 setString(1、“foo”); setString(2,“酒吧”); 其余的绑定为NULL Types.VARCHAR setNull (3) ... Types.VARCHAR setNull (10)

NULL从不匹配任何东西,因此它会被SQL计划构建器优化。

当你将List传递给DAO函数时,逻辑很容易自动化:

while( i < param.size() ) {
  ps.setString(i+1,param.get(i));
  i++;
}

while( i < MAX_PARAMS ) {
  ps.setNull(i+1,Types.VARCHAR);
  i++;
}

似乎还没有人建议使用现成的查询构建器,比如jOOQ或QueryDSL,甚至Criteria query,它们可以开箱即用地管理动态IN列表,可能包括对可能出现的所有边缘情况的管理,例如:

运行到Oracle的每个IN列表最多1000个元素(与绑定值的数量无关) 遇到任何驱动程序的绑定值的最大数目,这是我在这个答案中记录的 遇到游标缓存争用问题,因为太多不同的SQL字符串被“硬解析”,执行计划不能再缓存了(jOOQ和最近Hibernate也通过提供IN列表填充来解决这个问题)

(免责声明:我为jOOQ背后的公司工作)

使用嵌套查询是一种令人不快的变通方法,但肯定是可行的。创建一个包含列的临时表MYVALUES。将值列表插入到MYVALUES表中。然后执行

select my_column from my_table where search_column in ( SELECT value FROM MYVALUES )

很丑,但如果你的价值列表非常大,这是一个可行的选择。

如果您的数据库没有缓存准备好的语句,这种技术还有一个额外的好处,那就是可能会从优化器获得更好的查询计划(检查一个页面是否有多个值,表只能检查一次,而不是每个值检查一次,等等),这样可以节省开销。您的“insert”将需要批处理,并且可能需要调整MYVALUES表以使锁定或其他高开销保护最小化。

遵循亚当的想法。让你的准备语句select my_column from my_table where search_column in (#) 创建一个字符串x,并根据你的值列表用一些“?,?,?”填充它 然后只需更改查询中的#为您的新字符串x填充