对于PreparedStatement中的IN子句，我们可以使用不同的替代方法。

Using Single Queries - slowest performance and resource intensive Using StoredProcedure - Fastest but database specific Creating dynamic query for PreparedStatement - Good Performance but doesn't get benefit of caching and PreparedStatement is recompiled every time. Use NULL in PreparedStatement queries - Optimal performance, works great when you know the limit of IN clause arguments. If there is no limit, then you can execute queries in batch. Sample code snippet is; int i = 1; for(; i <=ids.length; i++){ ps.setInt(i, ids[i-1]); } //set null for remaining ones for(; i<=PARAM_SIZE;i++){ ps.setNull(i, java.sql.Types.INTEGER); }

你可以在这里查看更多关于这些替代方法的细节。

下面是一个完整的Java解决方案来为你创建准备好的语句:

/*usage:

Util u = new Util(500); //500 items per bracket. 
String sqlBefore  = "select * from myTable where (";
List<Integer> values = new ArrayList<Integer>(Arrays.asList(1,2,4,5)); 
string sqlAfter = ") and foo = 'bar'"; 

PreparedStatement ps = u.prepareStatements(sqlBefore, values, sqlAfter, connection, "someId");
*/



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Util {

    private int numValuesInClause;

    public Util(int numValuesInClause) {
        super();
        this.numValuesInClause = numValuesInClause;
    }

    public int getNumValuesInClause() {
        return numValuesInClause;
    }

    public void setNumValuesInClause(int numValuesInClause) {
        this.numValuesInClause = numValuesInClause;
    }

    /** Split a given list into a list of lists for the given size of numValuesInClause*/
    public List<List<Integer>> splitList(
            List<Integer> values) {


        List<List<Integer>> newList = new ArrayList<List<Integer>>(); 
        while (values.size() > numValuesInClause) {
            List<Integer> sublist = values.subList(0,numValuesInClause);
            List<Integer> values2 = values.subList(numValuesInClause, values.size());   
            values = values2; 

            newList.add( sublist);
        }
        newList.add(values);

        return newList;
    }

    /**
     * Generates a series of split out in clause statements. 
     * @param sqlBefore ""select * from dual where ("
     * @param values [1,2,3,4,5,6,7,8,9,10]
     * @param "sqlAfter ) and id = 5"
     * @return "select * from dual where (id in (1,2,3) or id in (4,5,6) or id in (7,8,9) or id in (10)"
     */
    public String genInClauseSql(String sqlBefore, List<Integer> values,
            String sqlAfter, String identifier) 
    {
        List<List<Integer>> newLists = splitList(values);
        String stmt = sqlBefore;

        /* now generate the in clause for each list */
        int j = 0; /* keep track of list:newLists index */
        for (List<Integer> list : newLists) {
            stmt = stmt + identifier +" in (";
            StringBuilder innerBuilder = new StringBuilder();

            for (int i = 0; i < list.size(); i++) {
                innerBuilder.append("?,");
            }



            String inClause = innerBuilder.deleteCharAt(
                    innerBuilder.length() - 1).toString();

            stmt = stmt + inClause;
            stmt = stmt + ")";


            if (++j < newLists.size()) {
                stmt = stmt + " OR ";
            }

        }

        stmt = stmt + sqlAfter;
        return stmt;
    }

    /**
     * Method to convert your SQL and a list of ID into a safe prepared
     * statements
     * 
     * @throws SQLException
     */
    public PreparedStatement prepareStatements(String sqlBefore,
            ArrayList<Integer> values, String sqlAfter, Connection c, String identifier)
            throws SQLException {

        /* First split our potentially big list into lots of lists */
        String stmt = genInClauseSql(sqlBefore, values, sqlAfter, identifier);
        PreparedStatement ps = c.prepareStatement(stmt);

        int i = 1;
        for (int val : values)
        {

            ps.setInt(i++, val);

        }
        return ps;

    }

}

对于某些情况，regexp可能会有所帮助。 下面是我在Oracle上查看的一个例子，它是有效的。

select * from my_table where REGEXP_LIKE (search_column, 'value1|value2')

但它也有一些缺点:

它应用的任何列都应该转换为varchar/char，至少是隐式转换。 使用特殊字符时要小心。 它会降低性能——在我的例子中，in版本使用索引和范围扫描，而REGEXP版本执行全扫描。

只是为了完整起见，因为我没有看到其他人提出这个建议:

在实现上述任何复杂的建议之前，请考虑SQL注入是否确实是您的场景中的一个问题。

在许多情况下，提供给In(…)的值是一个id列表，这些id以某种方式生成，您可以确保不可能进行注入…(例如，之前select some_id from some_table where some_condition.)

如果是这种情况，您可能只是连接这个值，而不为它使用服务或准备好的语句，或将它们用于此查询的其他参数。

query="select f1,f2 from t1 where f3=? and f2 in (" + sListOfIds + ");";

我的解决方案是:

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

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

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

因此，预处理语句可以是:

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

问候,

哈维尔·伊瓦内斯

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

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提出的标记