在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方法进行扩展。

在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方法进行扩展。

好吧，所以我不记得我之前是如何(或在哪里)这样做的，所以我来堆栈溢出来快速找到答案。我很惊讶我不能。

所以，很久以前我是用这样的语句来解决IN问题的:

where myColumn in (select regexp_substr(:myList，'[^，]+'， 1, level) from dual connect by regexp_substr(:myList，'[^，]+'， 1, level) not null)

将myList参数设置为逗号分隔的字符串:a,B,C,D…

注意:该参数必须设置两次!

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

创建带有几个参数(例如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++;
}

我遇到了一些与准备好的语句相关的限制:

The prepared statements are cached only inside the same session (Postgres), so it will really work only with connection pooling A lot of different prepared statements as proposed by @BalusC may cause the cache to overfill and previously cached statements will be dropped The query has to be optimized and use indices. Sounds obvious, however e.g. the ANY(ARRAY...) statement proposed by @Boris in one of the top answers cannot use indices and query will be slow despite caching The prepared statement caches the query plan as well and the actual values of any parameters specified in the statement are unavailable.

在建议的解决方案中，我会选择一个不会降低查询性能并且查询次数较少的解决方案。这将是#4(批处理少数查询)从@Don链接或指定NULL值为不需要的'?@Vladimir Dyuzhev提出的标记

这不是理想的做法，但它很简单，大多数时候对我来说都很有效。

where ? like concat( "%|", TABLE_ID , "|%" ) 

然后你穿过?|1|，|2|，|3|，…|