Java PreparedStatement IN 子句的替代方案？

PreparedStatement IN clause alternatives?

提问by Chris Mazzola

What are the best workarounds for using a SQL INclause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ?placeholder represents one value, rather than a list of values.

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

Consider the following SQL statement:

考虑以下 SQL 语句：

SELECT my_column FROM my_table where search_column IN (?)

Using preparedStatement.setString( 1, "'A', 'B', 'C'" );is essentially a non-working attempt at a workaround of the reasons for using ?in the first place.

使用preparedStatement.setString( 1, "'A', 'B', 'C'" );本质上是?对首先使用原因的解决方法的非工作尝试。

What workarounds are available?

有哪些解决方法？

采纳答案by Dónal

An analysis of the various options available, and the pros and cons of each is available here.

此处提供了对各种可用选项的分析以及每种选项的优缺点。

The suggested options are:

建议的选项是：

• 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 ALLin 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.

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

None of these options are super great, though.

不过，这些选项都不是超级棒的。

Duplicate questions have been answered in these places with equally sane alternatives, still none of them super great:

在这些地方已经用同样理智的替代方法回答了重复的问题，但仍然没有一个是超级棒的：

• PreparedStatement with list of parameters in a IN clause
• How to set list of parameters on prepared statement?

• 在 IN 子句中带有参数列表的 PreparedStatement
• 如何在准备好的语句上设置参数列表？

The Right Answer, if you are using JDBC4 and a server that supports x = ANY(y), is to use PreparedStatement.setArrayas described here:

如果您使用的是 JDBC4 和支持 的服务器，正确的答案x = ANY(y)是PreparedStatement.setArray按照此处所述使用：

• PreparedStatement IN clause alternatives?

• PreparedStatement IN 子句的替代方案？

There doesn't seem to be any way to make setArraywork with IN-lists, though.

不过，似乎没有任何方法可以setArray使用 IN 列表。

---

---

Sometimes SQL statements are loaded at runtime (e.g., from a properties file) but require a variable number of parameters. In such cases, first define the query:

有时 SQL 语句在运行时加载（例如，从属性文件）但需要可变数量的参数。在这种情况下，首先定义查询：

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

Next, load the query. Then determine the number of parameters prior to running it. Once the parameter count is known, run:

接下来，加载查询。然后在运行之前确定参数的数量。知道参数计数后，运行：

sql = any( sql, count );

For example:

例如：

/**
 * 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(
            new String(new char[params]).replace("
select my_column from my_table where  instr(?, ','||search_column||',') > 0
", "?,")
    );

    // Remove trailing comma.
    sb.setLength(Math.max(sb.length() - 1, 0));

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

For certain databases where passing an array via the JDBC 4 specification is unsupported, this method can facilitate transforming the slow = ?into the faster IN (?)clause condition, which can then be expanded by calling the anymethod.

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

回答by Paul Tomblin

I've never tried it, but would .setArray() do what you're looking for?

我从来没有尝试过，但是 .setArray() 会做你想要的吗？

Update: Evidently not. setArray only seems to work with a java.sql.Array that comes from an ARRAY column that you've retrieved from a previous query, or a subquery with an ARRAY column.

更新：显然不是。setArray 似乎只适用于来自您从先前查询中检索到的 ARRAY 列或带有 ARRAY 列的子查询的 java.sql.Array。

回答by Adam Bellaire

I suppose you could (using basic string manipulation) generate the query string in the PreparedStatementto have a number of ?'s matching the number of items in your list.

我想您可以（使用基本的字符串操作）在 中生成查询字符串，PreparedStatement以便有多个?'s 与列表中的项目数相匹配。

Of course if you're doing that you're just a step away from generating a giant chained ORin your query, but without having the right number of ?in the query string, I don't see how else you can work around this.

当然，如果你这样做，你离OR在你的查询中生成一个巨大的链接只有一步之遥，但是?在查询字符串中没有正确的数量，我不知道你还能如何解决这个问题。

回答by stjohnroe

try using the instr function?

尝试使用 instr 函数？

ps.setString(1, ",A,B,C,"); 

then

然后

... WHERE tab.col = ? OR tab.col = ? OR tab.col = ?

Admittedly this is a bit of a dirty hack, but it does reduce the opportunities for sql injection. Works in oracle anyway.

诚然，这有点肮脏，但它确实减少了 sql 注入的机会。无论如何在oracle中工作。

回答by Carl Smotricz

Just for completeness: So long as the set of values is not too large, you couldalso simply string-construct a statement like

只是为了完整性：只要值集不是太大，您也可以简单地字符串构造一个语句，如

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

which you could then pass to prepare(), and then use setXXX() in a loop to set all the values. This looks yucky, but many "big" commercial systems routinely do this kind of thing until they hit DB-specific limits, such as 32 KB (I think it is) for statements in Oracle.

然后您可以将其传递给 prepare()，然后在循环中使用 setXXX() 来设置所有值。这看起来很恶心，但许多“大型”商业系统通常会做这种事情，直到它们达到特定于数据库的限制，例如 Oracle 中的语句为 32 KB（我认为是）。

Of course you need to ensure that the set will never be unreasonably large, or do error trapping in the event that it is.

当然，您需要确保该集合永远不会过大，或者在它是的情况下进行错误捕获。

回答by Carl Smotricz

Following Adam's idea. Make your prepared statement sort of select my_column from my_table where search_column in (#) Create a String x and fill it with a number of "?,?,?" depending on your list of values Then just change the # in the query for your new String x an populate

按照亚当的想法。使您准备好的语句从 my_table 中选择 my_column where search_column in (#) 创建一个字符串 x 并用一些“？，？，？”填充它。取决于您的值列表然后只需更改查询中的 # 以获取新的 String x an populate

回答by James Schek

An unpleasant work-around, but certainly feasible is to use a nested query. Create a temporary table MYVALUES with a column in it. Insert your list of values into the MYVALUES table. Then execute

一个令人不快的变通办法，但肯定可行的是使用嵌套查询。创建一个临时表 MYVALUES，其中有一列。将您的值列表插入到 MYVALUES 表中。然后执行

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

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

Ugly, but a viable alternative if your list of values is very large.

丑陋，但如果您的值列表非常大，这是一个可行的选择。

This technique has the added advantage of potentially better query plans from the optimizer (check a page for multiple values, tablescan only once instead once per value, etc) may save on overhead if your database doesn't cache prepared statements. Your "INSERTS" would need to be done in batch and the MYVALUES table may need to be tweaked to have minimal locking or other high-overhead protections.

如果您的数据库不缓存准备好的语句，则此技术具有额外的优势，即优化器可能会提供更好的查询计划（检查页面中的多个值，表扫描仅一次而不是每个值一次等）可以节省开销。您的“插入”需要批量完成，并且可能需要调整 MYVALUES 表以具有最小的锁定或其他高开销保护。

回答by Vladimir Dyuzhev

No simple way AFAIK. If the target is to keep statement cache ratio high (i.e to not create a statement per every parameter count), you may do the following:

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

1. create a statement with a few (e.g. 10) parameters:

   ... WHERE A IN (?,?,?,?,?,?,?,?,?,?) ...

2. Bind all actuall parameters

   setString(1,"foo"); setString(2,"bar");

3. Bind the rest as NULL

   setNull(3,Types.VARCHAR) ... setNull(10,Types.VARCHAR)

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

   ......哪里在（？，？，？，？，？，？，？，？，？，？）......

2. 绑定所有实际参数

   setString(1,"foo"); setString(2,"bar");

3. 其余绑定为NULL

   setNull(3,Types.VARCHAR) ... setNull(10,Types.VARCHAR)

NULL never matches anything, so it gets optimized out by the SQL plan builder.

NULL 永远不会匹配任何内容，因此它会被 SQL 计划构建器优化掉。

The logic is easy to automate when you pass a List into a DAO function:

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

public void myQuery(List<String> items, int other) {
  ...
  String q4in = generateQsForIn(items.size());
  String sql = "select * from stuff where foo in ( " + q4in + " ) and bar = ?";
  PreparedStatement ps = connection.prepareStatement(sql);
  int i = 1;
  for (String item : items) {
    ps.setString(i++, item);
  }
  ps.setInt(i++, other);
  ResultSet rs = ps.executeQuery();
  ...
}

private String generateQsForIn(int numQs) {
    String items = "";
    for (int i = 0; i < numQs; i++) {
        if (i != 0) items += ", ";
        items += "?";
    }
    return items;
}

回答by neu242

Generate the query string in the PreparedStatement to have a number of ?'s matching the number of items in your list. Here's an example:

在 PreparedStatement 中生成查询字符串，以便有多个 ? 与列表中的项目数相匹配。下面是一个例子：

create or replace type split_tbl as table of varchar(32767);
/

create or replace function split
(
  p_list varchar2,
  p_del varchar2 := ','
) return split_tbl pipelined
is
  l_idx    pls_integer;
  l_list    varchar2(32767) := p_list;
  l_value    varchar2(32767);
begin
  loop
    l_idx := instr(l_list,p_del);
    if l_idx > 0 then
      pipe row(substr(l_list,1,l_idx-1));
      l_list := substr(l_list,l_idx+length(p_del));
    else
      pipe row(l_list);
      exit;
    end if;
  end loop;
  return;
end split;
/

回答by Javier Ibanez

My workaround is:

我的解决方法是：

select * from table(split('one,two,three'))
  one
  two
  three

select * from TABLE1 where COL1 in (select * from table(split('value1,value2')))
  value1 AAA
  value2 BBB

Now you can use one variable to obtain some values in a table:

现在您可以使用一个变量来获取表中的一些值：

  "select * from TABLE where COL in (select * from table(split(?)))"

So, the prepared statement could be:

因此，准备好的语句可以是：

##代码##

Regards,

问候，

Javier Ibanez

哈维尔·伊瓦内斯