IN 子句中带有参数列表的PreparedStatement

Question

如何在执行查询时为 JDBC 中的 preparedStatement 中的 in 子句设置值。

示例：

connection.prepareStatement("Select * from test where field in (?)");

如果这个子句可以保存多个值，我该怎么做。有时我事先知道参数列表，有时我事先不知道。此案如何处理？

Answer 1

我要做的就是添加一个“？”对于每个可能的值。

var stmt = String.format("select * from test where field in (%s)",
                         values.stream()
                         .map(v -> "?")
                         .collect(Collectors.joining(", ")));

替代使用 StringBuilder （这是十多年前的原始答案）

List values = ... 
StringBuilder builder = new StringBuilder();

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

String placeHolders =  builder.deleteCharAt( builder.length() -1 ).toString();
String stmt = "select * from test where field in ("+ placeHolders + ")";
PreparedStatement pstmt = ... 

然后愉快地设置参数

int index = 1;
for( Object o : values ) {
   pstmt.setObject(  index++, o ); // or whatever it applies 
}

Answer 2

您可以使用下面的 javadoc 中提到的 setArray 方法：

http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setArray(int, java.sql.Array)
代码

：

PreparedStatement statement = connection.prepareStatement("Select * from test where field in (?)");
Array array = statement.getConnection().createArrayOf("VARCHAR", new Object[]{"A1", "B2","C3"});
statement.setArray(1, array);
ResultSet rs = statement.executeQuery();

Answer 3

您不能将查询中的 ? 替换为任意数量的值。每个 ? 仅是单个值的占位符。要支持任意数量的值，您必须动态构建一个包含 ?, ?, ?, ... , ? 的字符串，其中问号的数量与值的数量相同你想要在你的 in 子句中。

Answer 4

您不想将PreparedStatment与使用IN子句的动态查询一起使用，至少您确定您始终低于5个变量或类似的小值，但即使这样，我认为这是一个坏主意（不可怕，但很糟糕）。由于元素数量很大，情况会更糟（而且很糟糕）。

想象一下你的 IN 子句中有成百上千种可能性：

1. 这会适得其反，你会损失性能和内存，因为每次新请求时都会进行缓存，而PreparedStatement不仅仅是为了 SQL 注入，它还关乎性能。在这种情况下，Statement 更好。

2. 你的池有PreparedStatment的限制（默认为-1，但你必须限制它），你将达到这个限制！如果没有限制或限制非常大，则存在内存泄漏的风险，在极端情况下还会出现 OutofMemory 错误。因此，如果它是供 3 个用户使用的小型个人项目，那么这并不引人注目，但如果您在一家大公司，并且您的应用程序被数千​​人和数百万个请求使用，那么您不希望出现这种情况。

一些阅读。
IBM：使用准备时的内存利用率注意事项语句缓存

Answer 5

你需要jdbc4然后你可以使用setArray！

就我而言，它不起作用，因为 postgres 中的 UUID 数据类型似乎仍然有其弱点，但对于通常的类型它起作用。

ps.setArray(1, connection.createArrayOf("$VALUETYPE",myValuesAsArray));

当然，将 $VALUETYPE 和 myValuesAsArray 替换为正确的值。

备注以下标记注释：

您的数据库和驱动程序需要支持此功能！我尝试过 Postgres 9.4，但我认为这已经在之前介绍过了。您需要 jdbc 4 驱动程序，否则 setArray 将不可用。我使用了 Spring Boot 附带的 postgresql 9.4-1201-jdbc41 驱动程序

Answer 6

目前，MySQL 不允许在一个方法调用中设置多个值。
所以你必须把它置于你自己的控制之下。我通常为预定义数量的参数创建一个准备好的语句，然后根据需要添加尽可能多的批次。

    int paramSizeInClause = 10; // required to be greater than 0!
    String color = "FF0000"; // red
    String name = "Nathan"; 
    Date now = new Date();
    String[] ids = "15,21,45,48,77,145,158,321,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,358,1284,1587".split(",");

    // Build sql query 
    StringBuilder sql = new StringBuilder();
    sql.append("UPDATE book SET color=? update_by=?, update_date=? WHERE book_id in (");
    // number of max params in IN clause can be modified 
    // to get most efficient combination of number of batches
    // and number of parameters in each batch
    for (int n = 0; n < paramSizeInClause; n++) {
        sql.append("?,");
    }
    if (sql.length() > 0) {
        sql.deleteCharAt(sql.lastIndexOf(","));
    }
    sql.append(")");

    PreparedStatement pstm = null;
    try {
        pstm = connection.prepareStatement(sql.toString());
        int totalIdsToProcess = ids.length;
        int batchLoops = totalIdsToProcess / paramSizeInClause + (totalIdsToProcess % paramSizeInClause > 0 ? 1 : 0);
        for (int l = 0; l < batchLoops; l++) {
            int i = 1;
            pstm.setString(i++, color);
            pstm.setString(i++, name);
            pstm.setTimestamp(i++, new Timestamp(now.getTime()));
            for (int count = 0; count < paramSizeInClause; count++) {
                int param = (l * paramSizeInClause + count);
                if (param < totalIdsToProcess) {
                    pstm.setString(i++, ids[param]);
                } else {
                    pstm.setNull(i++, Types.VARCHAR);
                }
            }
            pstm.addBatch();
        }
    } catch (SQLException e) {
    } finally {
        //close statement(s)
    }

如果您不喜欢在没有更多参数时设置 NULL，则可以修改代码以构建两个查询和两个准备好的语句。第一个是相同的，但第二个声明是余数（模数）。
在此特定示例中，一个查询将包含 10 个参数，另一个查询将包含 8 个参数。您必须为第一个查询添加 3 个批次（前 30 个参数），然后为第二个查询添加一个批次（8 个参数）。

Answer 7

public static ResultSet getResult(Connection connection, List values) {
    try {
        String queryString = "Select * from table_name where column_name in";

        StringBuilder parameterBuilder = new StringBuilder();
        parameterBuilder.append(" (");
        for (int i = 0; i < values.size(); i++) {
            parameterBuilder.append("?");
            if (values.size() > i + 1) {
                parameterBuilder.append(",");
            }
        }
        parameterBuilder.append(")");

        PreparedStatement statement = connection.prepareStatement(queryString + parameterBuilder);
        for (int i = 1; i < values.size() + 1; i++) {
            statement.setInt(i, (int) values.get(i - 1));
        }

        return statement.executeQuery();
    } catch (Exception d) {
        return null;
    }
}

Answer 8

您可以做的是，一旦您知道需要在 IN 子句中放入多少个值，就可以通过简单的 for 循环动态构建选择字符串（“IN (?)”部分）。然后您可以实例化PreparedStatement。

Answer 9

您可以使用：

for( int i = 0 ; i < listField.size(); i++ ) {
    i < listField.size() - 1 ? request.append("?,") : request.append("?");
}

然后：

int i = 1;
for (String field : listField) {
    statement.setString(i++, field);
}

示例：

List<String> listField = new ArrayList<String>();
listField.add("test1");
listField.add("test2");
listField.add("test3");

StringBuilder request = new StringBuilder("SELECT * FROM TABLE WHERE FIELD IN (");

for( int i = 0 ; i < listField.size(); i++ ) {
    request = i < (listField.size() - 1) ? request.append("?,") : request.append("?");
}


DNAPreparedStatement statement = DNAPreparedStatement.newInstance(connection, request.toString);

int i = 1;
for (String field : listField) {
    statement.setString(i++, field);
}

ResultSet rs = statement.executeQuery();

Answer 10

public class Test1 {
    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        System.out.println("helow");
String where="where task in ";
        where+="(";
    //  where+="'task1'";
        int num[]={1,2,3,4};
        for (int i=0;i<num.length+1;i++) {
            if(i==1){
                where +="'"+i+"'";
            }
            if(i>1 && i<num.length)
                where+=", '"+i+"'";
            if(i==num.length){
                System.out.println("This is last number"+i);
            where+=", '"+i+"')";
            }
        }
        System.out.println(where);  
    }
}

Answer 11

尝试使用此代码

 String ids[] = {"182","160","183"};
            StringBuilder builder = new StringBuilder();

            for( int i = 0 ; i < ids.length; i++ ) {
                builder.append("?,");
            }

            String sql = "delete from emp where id in ("+builder.deleteCharAt( builder.length() -1 ).toString()+")";

            PreparedStatement pstmt = connection.prepareStatement(sql);

            for (int i = 1; i <= ids.length; i++) {
                pstmt.setInt(i, Integer.parseInt(ids[i-1]));
            }
            int count = pstmt.executeUpdate();

Answer 12

许多数据库都有临时表的概念，即使假设您没有临时表，您也可以始终生成一个具有唯一名称的临时表，并在完成后将其删除。虽然创建和删除表的开销很大，但这对于非常大的操作或将数据库用作本地文件或内存中的数据库（SQLite）的情况可能是合理的。

我正在处理的一个示例（使用 Java/SqlLite）：

String tmptable = "tmp" + UUID.randomUUID();

sql = "create table " + tmptable + "(pagelist text not null)";
cnn.createStatement().execute(sql);

cnn.setAutoCommit(false);
stmt = cnn.prepareStatement("insert into "+tmptable+" values(?);");
for(Object o : rmList){
    Path path = (Path)o;
    stmt.setString(1, path.toString());
    stmt.execute();
}
cnn.commit();
cnn.setAutoCommit(true);

stmt = cnn.prepareStatement(sql);
stmt.execute("delete from filelist where path + page in (select * from "+tmptable+");");
stmt.execute("drop table "+tmptable+");");

请注意，我的表使用的字段是动态创建的。

如果您能够重用该表，这会更加有效。

Answer 13

Using Java 8 APIs, 

    List<Long> empNoList = Arrays.asList(1234, 7678, 2432, 9756556, 3354646);

    List<String> parameters = new ArrayList<>();
    empNoList.forEach(empNo -> parameters.add("?"));   //Use forEach to add required no. of '?'
    String commaSepParameters = String.join(",", parameters); //Use String to join '?' with ','

StringBuilder selectQuery = new StringBuilder().append("SELECT COUNT(EMP_ID) FROM EMPLOYEE WHERE EMP_ID IN (").append(commaSepParameters).append(")");

Answer 14

公共静态无效主（字符串arg []）{

    Connection connection = ConnectionManager.getConnection(); 
    PreparedStatement pstmt = null;
          //if the field values are in ArrayList
        List<String> fieldList = new ArrayList();

    try {

        StringBuffer sb = new StringBuffer();  

        sb.append("  SELECT *            \n");
        sb.append("   FROM TEST          \n");
        sb.append("  WHERE FIELD IN (    \n");

        for(int i = 0; i < fieldList.size(); i++) {
            if(i == 0) {
                sb.append("    '"+fieldList.get(i)+"'   \n");
            } else {
                sb.append("   ,'"+fieldList.get(i)+"'   \n");
            }
        }
        sb.append("             )     \n");

        pstmt = connection.prepareStatement(sb.toString());
        pstmt.executeQuery();

    } catch (SQLException se) {
        se.printStackTrace();
    }

}