Java 如何在准备好的语句上设置参数列表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1305240/
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
How to set list of parameters on prepared statement?
提问by Chris
i have a list of names e.g.:
我有一个名字列表，例如：
List<String> names = ...
names.add('charles');
...
and a statement:
和声明：
PreparedStatement stmt =
conn.prepareStatement('select * from person where name in ( ? )');
how to do the following:
如何执行以下操作：
stmt.setParameterList(1,names);
Is there a workaround? can someone explain why this method is missing?
有解决方法吗？有人可以解释为什么缺少这种方法吗？
using: java, postgresql, jdbc3
使用：java、postgresql、jdbc3
采纳答案by Nick Holt
There's no clean way to do this simply by setting a list on the PreparedStatementthat I know of.
没有简单的方法可以简单地通过在PreparedStatement我知道的列表上设置一个列表来做到这一点。
Write code that constructs the SQL statement (or better replaces a single ? or similar token) with the appropriate number of questions marks (the same number as in your list) and then iterate over your list setting the parameter for each.
编写使用适当数量的问号（与列表中的数字相同）构造 SQL 语句（或更好地替换单个 ? 或类似标记）的代码，然后迭代您的列表，为每个问题设置参数。
回答by dfa
this method is missing due to type erasurethe parameter type of the List is lost at runtime. Therefore the need to add several methods arires: setIntParameters, setLongParameters, setObjectParameters, etc
由于类型擦除，此方法丢失，List 的参数类型在运行时丢失。因此，需要补充多种方法arires： ，setIntParameters，setLongParameters，setObjectParameters等
回答by junior
For postgres 9 I have used this approach:
对于 postgres 9，我使用了这种方法：
jdbcTemplate.query(getEmployeeReport(), new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setTimestamp(1, new java.sql.Timestamp(from.getTime()));
ps.setTimestamp(2, new java.sql.Timestamp(to.getTime()));
StringBuilder ids = new StringBuilder();
for (int i = 0; i < branchIds.length; i++) {
ids.append(branchIds[i]);
if (i < branchIds.length - 1) {
ids.append(",");
}
}
// third param is inside IN clause
// Use Types.OTHER avoid type check while executing query
ps.setObject(3, ids.toString(), **Types.OTHER**);
}
}, new PersonalReportMapper());
回答by Ahmad Hamid
I was reviewing code this morning and one of my colleagues had a different approach, just pass the parameter using setString("name1','name2','name3").
今天早上我正在代码，我的一位同事采用了不同的方法，只需使用setString("name1','name2','name3").
Note:I skipped the single quote at the beginning and end because these are going to be added by the setString.
注意：我在开头和结尾跳过了单引号，因为它们将由setString.
回答by earcam
This question is very old, but nobody has suggested using setArray
这个问题很老了，但没有人建议使用setArray
This answer might help https://stackoverflow.com/a/10240302/573057
回答by calioppe15
Other method :
其他方法：
public void setValues(PreparedStatement ps) throws SQLException {
// first param inside IN clause with myList values
ps.setObject(1 , myList.toArray(), 2003); // 2003=array in java.sql.Types
}
回答by bnsk
After examining various solutions in different forums and not finding a good solution, I feel the below hack I came up with, is the easiest to follow and code. Note however that this doesn't use prepared query but gets the work done anyway:
在检查了不同论坛中的各种解决方案并没有找到好的解决方案后，我觉得我想出的以下 hack 是最容易遵循和编码的。但是请注意，这不使用准备好的查询，但无论如何都会完成工作：
Example: Suppose you have a list of parameters to pass in the 'IN' clause. Just put a dummy String inside the 'IN' clause, say, "PARAM" do denote the list of parameters that will be coming in the place of this dummy String.
示例：假设您有一个要在“IN”子句中传递的参数列表。只需在 'IN' 子句中放置一个虚拟字符串，例如，“PARAM”确实表示将代替此虚拟字符串出现的参数列表。
select * from TABLE_A where ATTR IN (PARAM);
You can collect all the parameters into a single String variable in your Java code. This can be done as follows:
您可以将所有参数收集到 Java 代码中的单个 String 变量中。这可以按如下方式完成：
String param1 = "X";
String param2 = "Y";
String param1 = param1.append(",").append(param2);
You can append all your parameters separated by commas into a single String variable, 'param1', in our case.
在我们的例子中，您可以将所有由逗号分隔的参数附加到单个字符串变量“param1”中。
After collecting all the parameters into a single String you can just replace the dummy text in your query, i.e., "PARAM" in this case, with the parameter String, i.e., param1. Here is what you need to do:
在将所有参数收集到单个字符串中后，您可以将查询中的虚拟文本（即本例中的“PARAM”）替换为参数字符串，即 param1。以下是您需要做的：
String query = query.replaceFirst("PARAM",param1); where we have the value of query as
query = "select * from TABLE_A where ATTR IN (PARAM)";
You can now execute your query using the executeQuery() method. Just make sure that you don't have the word "PARAM" in your query anywhere. You can use a combination of special characters and alphabets instead of the word "PARAM" in order to make sure that there is no possibility of such a word coming in the query. Hope you got the solution.
您现在可以使用 executeQuery() 方法执行查询。只要确保您的查询中的任何地方都没有“PARAM”一词。您可以使用特殊字符和字母的组合来代替单词“PARAM”，以确保查询中不可能出现这样的单词。希望你得到了解决方案。
回答by AlikElzin-kilaka
In case the questions' meaning is to set several params in a single call...
如果问题的含义是在一次调用中设置多个参数...
Because the type validation is already defined in a higher level, I think the only need is for setObject(...).
因为类型验证已经在更高级别定义了，我认为唯一需要的是setObject(...)。
Thus, a utility method can be used:
因此，可以使用实用方法：
public static void addParams(PreparedStatement preparedStatement, Object... params) throws SQLException {
for (int i = 0; i < params.length; i++) {
Object param = params[i];
preparedStatement.setObject(i+1, param);
}
}
Usage:
用法：
SqlUtils.addParams(preparedStatement, 1, '2', 3d);
Feel free converting this to a Java 8 lambda :)
随意将其转换为 Java 8 lambda :)
