你可以使用PreparedStatement的setArray()方法

PreparedStatement statement = connection.prepareStatement("Select * from emp where field in (?)");
statement.setArray(1, Arrays.asList(1,2,3,4,5));
ResultSet rs = statement.executeQuery();

下面是一个完整的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;

    }

}

您可以使用集合。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
     */
}

SetArray是最好的解决方案，但它不适用于许多老司机。下面的解决方法可以在java8中使用

String baseQuery ="SELECT my_column FROM my_table where search_column IN (%s)"

String markersString = inputArray.stream().map(e -> "?").collect(joining(","));
String sqlQuery = String.format(baseSQL, markersString);

//Now create Prepared Statement and use loop to Set entries
int index=1;

for (String input : inputArray) {
     preparedStatement.setString(index++, input);
}

这个解决方案比其他难看的while循环解决方案好，其中查询字符串是通过手动迭代构建的

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

我只是为此制定了一个特定于postgresql的选项。它有点像黑客，有自己的优缺点和局限性，但它似乎是有效的，而且不局限于特定的开发语言、平台或PG驱动程序。

当然，诀窍是找到一种方法，将任意长度的值集合作为单个参数传递，并让db将其识别为多个值。我的解决方案是从集合中的值构造一个带分隔符的字符串，将该字符串作为单个参数传递，并使用string_to_array()与PostgreSQL正确使用它所需的强制转换。

因此，如果你想搜索“foo”，“blah”和“abc”，你可以将它们连接成一个字符串，如:'foo,blah,abc'。下面是直接的SQL语句:

select column from table
where search_column = any (string_to_array('foo,blah,abc', ',')::text[]);

显然，您可以将显式强制转换更改为您想要的结果值数组——int、text、uuid等。因为函数接受一个字符串值(或者两个，如果你也想自定义分隔符)，你可以在准备好的语句中作为参数传递它:

select column from table
where search_column = any (string_to_array($1, ',')::text[]);

这甚至足够灵活，可以支持like比较:

select column from table
where search_column like any (string_to_array('foo%,blah%,abc%', ',')::text[]);

Again, no question it's a hack, but it works and allows you to still use pre-compiled prepared statements that take *ahem* discrete parameters, with the accompanying security and (maybe) performance benefits. Is it advisable and actually performant? Naturally, it depends, as you've got string parsing and possibly casting going on before your query even runs. If you're expecting to send three, five, a few dozen values, sure, it's probably fine. A few thousand? Yeah, maybe not so much. YMMV, limitations and exclusions apply, no warranty express or implied.

但它确实有效。