java PreparedStatement IN子句替换？

Question

对于将SQL IN子句与java.sql.PreparedStatement实例一起使用，最好的解决方法是什么？由于SQL注入攻击安全问题，多个值不支持该子句：一个?占位符代表一个值，而不是一个值列表

考虑下面的SQL语句：

SELECT my_column FROM my_table where search_column IN (?)

使用preparedStatement.setString( 1, "'A', 'B', 'C'" );基本上是一种不起作用的尝试，试图从一开始就解决使用?的原因

有哪些解决办法

Answer 1

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

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

丑陋，但如果你的价值观清单很大，这是一个可行的选择

这种技术的另一个优点是，如果数据库不缓存准备好的语句，优化器可能会提供更好的查询计划（检查一个页面是否有多个值，每个值只能检查一次，等等），这样可以节省开销。您的“插入”需要批量完成，MYVALUES表可能需要调整，以具有最小的锁定或其他高开销保护

Answer 2

in（）运算符的局限性是万恶之源

它适用于琐碎的情况，您可以通过“自动生成准备好的语句”来扩展它，但它总是有其局限性

• 如果要创建一个参数数量可变的语句，那么每次调用都会产生sql解析开销
• 在许多平台上，in（）运算符的参数数量是有限的
• 在所有平台上，SQL文本的总大小都是有限的，因此无法为in参数发送2000个占位符
• 发送1000-10k的绑定变量是不可能的，因为JDBC驱动程序有其局限性

in（）方法在某些情况下已经足够好了，但还不够可靠：）

防火箭的解决方案是在一个单独的调用中传递任意数量的参数（例如，通过传递一个clob的参数），然后使用视图（或任何其他方式）在SQL中表示这些参数，并在where条件中使用

这里有一种蛮力变体http://tkyte.blogspot.hu/2006/06/varying-in-lists.html

然而，如果可以使用PL/SQL，这种混乱会变得相当整洁

function getCustomers(in_customerIdList clob) return sys_refcursor is 
begin
    aux_in_list.parse(in_customerIdList);
    open res for
        select * 
        from   customer c,
               in_list v
        where  c.customer_id=v.token;
    return res;
end;

然后可以在参数中传递任意数量的逗号分隔的客户ID，并且：

• 将不会得到解析延迟，因为select的SQL是稳定的
• 没有流水线函数的复杂性——它只是一个查询
• SQL使用的是一个简单的连接，而不是IN运算符，这非常快
• 毕竟，这是一个很好的经验法则，即不要用任何简单的select或DML攻击数据库，因为它是Oracle，它提供的光年数超过MySQL或类似的简单数据库引擎。PL/SQL允许您以有效的方式从应用程序域模型中隐藏存储模型

这里的诀窍是：

• 我们需要一个接受长字符串的调用，并将其存储在db会话可以访问它的地方（例如，简单包变量，或dbms_session.set_context）
• 然后我们需要一个可以将其解析为行的视图
• 然后你有一个视图，其中包含你正在查询的ID，所以你所需要的只是一个到查询表的简单连接

该视图看起来像：

create or replace view in_list
as
select
    trim( substr (txt,
          instr (txt, ',', 1, level  ) + 1,
          instr (txt, ',', 1, level+1)
             - instr (txt, ',', 1, level) -1 ) ) as token
    from (select ','||aux_in_list.getpayload||',' txt from dual)
connect by level <= length(aux_in_list.getpayload)-length(replace(aux_in_list.getpayload,',',''))+1

aux_在_列表中的位置。getpayload指的是原始输入字符串

---

一种可能的方法是传递pl/sql数组（仅由Oracle支持），但是不能在纯sql中使用这些数组，因此始终需要一个转换步骤。转换不能在SQL中完成，因此，毕竟，以字符串形式传递包含所有参数的clob并在视图中转换它是最有效的解决方案

Answer 3

没有简单的方法。 如果目标是保持语句缓存比率高（即不为每个参数计数创建语句），则可以执行以下操作：

1. 创建一个包含几个（例如10个）参数的语句：

   。。。其中A在（？，，，，，，，，，，，，，，，，，，，？）

2. 绑定所有实际参数

   设置字符串（1，“foo”）； 设置管柱（2，“巴”）

3. 将其余的绑定为NULL

   setNull（3，Types.VARCHAR） ... setNull（10，Types.VARCHAR）

NULL从不匹配任何内容，因此它会被SQL计划生成器优化

当您将列表传递给DAO函数时，逻辑很容易自动化：

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

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

Answer 4

PostgreSQL的解决方案：

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table where search_column = ANY (?)"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

或者

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table " + 
        "where search_column IN (SELECT * FROM unnest(?))"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

Answer 5

您可以使用Collections.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
     */
}

Answer 6

对各种可用选项的分析，以及每种选项的优缺点都是可用的

建议的选项包括：

• 准备SELECT my_column FROM my_table WHERE search_column = ?，为每个值执行它，并在客户端合并结果。只需要一份准备好的声明。缓慢而痛苦
• 准备SELECT my_column FROM my_table WHERE search_column IN (?,?,?)并执行它。列表中的每种大小都需要一份准备好的声明。快速而明显
• 准备SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ...并执行它。[或使用UNION ALL代替这些分号。--ed]要求列表中每种大小都有一条准备好的语句。速度太慢了，比WHERE search_column IN (?,?,?)还差，所以我不知道为什么博主会这么建议
• 使用存储过程构造结果集
• 准备N个不同大小的IN-list查询；比如，使用2、10和50个值。要搜索具有6个不同值的IN列表，请填充size-10查询，使其看起来像SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6)。任何体面的服务器都会在运行查询之前优化掉重复的值

这些选项都不理想

如果您使用的是JDBC4和支持x = ANY(y)的服务器，那么最好的选择是使用PreparedStatement.setArray作为described here

不过，似乎没有任何方法可以让setArray在列表中工作

---

有时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方法来扩展该条件