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

小开

最佳答案

我所做的是为每个可能的值添加一个“ ?”。

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

可以选择使用 StringBuilder(这是10多年前的原始答案)

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
}

小开

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

小开

您可以动态地构建选择字符串(‘ IN (?))一旦您知道在 IN 子句中需要放入多少值，就可以通过简单的 for 循环来实现。然后可以实例化 PreparedStatement。

小开

许多 DBs 都有临时表的概念，即使假设您没有临时表，您也可以生成一个具有唯一名称的临时表，并在完成后删除它。虽然创建和删除表的开销很大，但对于非常大的操作，或者在将数据库用作本地文件或内存(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+");");

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

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

小开

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);
}
}

小开

目前，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个参数。您必须为第一个查询(前30个参数)添加3个批处理，然后为第二个查询(8个参数)添加一个批处理。

小开

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

在我的案例中，它没有工作，因为在 postgres 中的 UUID 数据类型似乎仍然有它的弱点，但对于通常的类型它工作。

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

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

以下是马克斯的评论:

您的数据库和驱动程序需要支持这一点！我尝试了 Postgres 9.4，但我认为这已经介绍了早些时候。您需要一个 jdbc4驱动程序，否则 setArray 将不可用。我使用了弹簧引导附带的 postgreql 9.4-1201-jdbc41驱动程序

小开

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();
}


}

小开

你可以使用下面 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();

小开

你不想使用带有 IN 子句的动态查询来使用 PreparedStatment，至少你可以确定你总是小于5个变量或者类似的小值，但是即使这样，我认为这是一个坏主意(不是很糟糕，但是很糟糕)。因为元素的数量很大，所以情况会更糟糕(而且很糟糕)。

在 IN 子句中想象成百上千种可能性:

这样做会适得其反，因为每次有新的请求时都会缓存，所以会丢失性能和内存，而 PreparedStatement 不仅仅用于 SQL 注入，它还与性能有关。在这种情况下，语句更好。
您的池有一个 PreparedStatement 的限制(默认值为 -1，但您必须对其进行限制) ，并且您将达到此限制！如果没有限制或者限制非常大，就会有内存泄漏的风险，在极端情况下会出现 OutofMemory 错误。所以如果你的个人小项目只有3个用户使用，这并不引人注目，但是如果你在一个大公司，你的应用程序被成千上万的用户使用，你不会希望这样的情况发生。

看看书。 IBM: 使用准备语句缓存时的内存利用考虑

小开

你可使用:

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();

小开

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

小开

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(")");

小开

试试这个代码

 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();