Java 在 IN 子句中带有参数列表的 PreparedStatement
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3107044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PreparedStatement with list of parameters in a IN clause
提问by Harish
How to set value for in clause in a preparedStatement in JDBC while executing a query.
如何在执行查询时为 JDBC 中的 PreparedStatement 中的 in 子句设置值。
Example:
例子：
connection.prepareStatement("Select * from test where field in (?)");
If this in-clause can hold multiple values how can I do it. Sometimes I know the list of parameters beforehand or sometimes I don't know beforehand. How to handle this case?
如果这个子句可以包含多个值，我该怎么做。有时我事先知道参数列表，有时我事先不知道。这个案子怎么处理？
采纳答案by OscarRyz
What I do is to add a "?" for each possible value.
我要做的是添加一个“？” 对于每个可能的值。
For instance:
例如：
List possibleValues = ...
StringBuilder builder = new StringBuilder();
for( int i = 0 ; i < possibleValue.size(); i++ ) {
builder.append("?,");
}
String stmt = "select * from test where field in ("
+ builder.deleteCharAt( builder.length() -1 ).toString() + ")";
PreparedStatement pstmt = ...
And then happily set the params
然后愉快地设置参数
int index = 1;
for( Object o : possibleValue ) {
pstmt.setObject( index++, o ); // or whatever it applies
}
回答by ryanprayogo
You might want to check this link:
您可能想查看此链接：
http://www.javaranch.com/journal/200510/Journal200510.jsp#a2
http://www.javaranch.com/journal/200510/Journal200510.jsp#a2
It explains the pros and cons of different methods of creating PreparedStatementwith inclause.
它解释了创建PreparedStatementwithin子句的不同方法的优缺点。
EDIT:
编辑：
An obvious approach is to dynamically generate the '?' part at runtime, but I don't want to merely suggest just this approach because depending on the way you use it, it might be inefficient (since the PreparedStatementwill need to be 'compiled' every time it gets used)
一个明显的方法是动态生成“？” 在运行时部分，但我不想仅仅建议这种方法，因为根据您使用它的方式，它可能效率低下（因为PreparedStatement每次使用时都需要“编译”）
回答by Asaph
You can't replace ?in your query with an arbitrary number of values. Each ?is a placeholder for a single value only. To support an arbitrary number of values, you'll have to dynamically build a string containing ?, ?, ?, ... , ?with the number of question marks being the same as the number of values you want in your inclause.
您不能用?任意数量的值替换您的查询。每个?都是单个值的占位符。要支持任意数量的值，您必须动态构建一个字符串，其中?, ?, ?, ... , ?问号的数量与您想要的in子句中的值的数量相同。
回答by rfk
What you can do is dynamically build the select string (the 'IN (?)' part) by a simple for loop as soon as you know how many values you need to put inside the IN clause. You can then instantiate the PreparedStatement.
只要您知道需要在 IN 子句中放入多少个值，您就可以通过简单的 for 循环动态构建选择字符串（“IN (?)”部分）。然后您可以实例化 PreparedStatement。
回答by Jefferey Cave
Many DBs have a concept of a temporary table, even assuming you don't have a temporary table you can always generate one with a unique name and drop it when you are done. While the overhead of creating and dropping a table is large, this may be reasonable for very large operations, or in cases where you are using the database as a local file or in memory (SQLite).
许多数据库都有临时表的概念，即使假设您没有临时表，您也可以始终生成一个具有唯一名称的表，并在完成后将其删除。虽然创建和删除表的开销很大，但对于非常大的操作，或者在将数据库用作本地文件或内存 (SQLite) 的情况下，这可能是合理的。
An example from something I am in the middle of (using Java/SqlLite):
我正在做的事情的一个例子（使用 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+");");
Note that the fields used by my table are created dynamically.
请注意，我的表使用的字段是动态创建的。
This would be even more efficient if you are able to reuse the table.
如果您能够重用该表，这将更加有效。
回答by user3870246
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);
}
}
回答by A Kunin
Currently, MySQL doesn't allow to set multiple values in one method call. So you have to have it under your own control. I usually create one prepared statement for predefined number of parameters, then I add as many batches as I need.
目前，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)
}
If you don't like to set NULL when no more parameters left, you can modify code to build two queries and two prepared statements. First one is the same, but second statement for the remainder (modulus). In this particular example that would be one query for 10 params and one for 8 params. You will have to add 3 batches for the first query (first 30 params) then one batch for the second query (8 params).
如果您不想在没有更多参数时设置 NULL，您可以修改代码以构建两个查询和两个准备好的语句。第一个是相同的，但余数（模数）的第二个语句。在这个特定的例子中，一个查询 10 个参数，一个查询 8 个参数。您必须为第一个查询添加 3 个批次（前 30 个参数），然后为第二个查询添加一个批次（8 个参数）。
回答by Patrick Cornelissen
You need jdbc4 then you can use setArray!
你需要 jdbc4 然后你可以使用 setArray！
In my case it didn't worked, as the UUID Datatype in postgres seems to still have its weak spots, but for the usual types it works.
就我而言，它没有用，因为 postgres 中的 UUID 数据类型似乎仍然有其弱点，但对于通常的类型它有效。
ps.setArray(1, connection.createArrayOf("$VALUETYPE",myValuesAsArray));
Of course replace $VALUETYPE and myValuesAsArray with the correct values.
当然，用正确的值替换 $VALUETYPE 和 myValuesAsArray。
Remark following Marks comment:
备注以下标记评论：
Your database and the driver needs to support this! I tried Postgres 9.4 but I think this has been introduced earlier. You need a jdbc 4 driver, otherwise setArray won't be available. I used the postgresql 9.4-1201-jdbc41 driver that ships with spring boot
您的数据库和驱动程序需要支持这一点！我尝试过 Postgres 9.4，但我认为这已经在前面介绍过了。您需要 jdbc 4 驱动程序，否则 setArray 将不可用。我使用了 spring boot 附带的 postgresql 9.4-1201-jdbc41 驱动程序
回答by Yarnie Park
public static void main(String 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();
}
}
回答by madx
You could use setArraymethod as mentioned in the javadoc below:
您可以使用setArray下面的 javadoc 中提到的方法：
Code:
代码：
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();
