Java 如何动态创建准备好的语句 - 并重用查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18466437/
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 create a prepared statement dynamically - and re-use the query
提问by ORey
I've been trying to create a class that handles queries from different Classes that create different objects, for example.
例如，我一直在尝试创建一个类来处理来自创建不同对象的不同类的查询。
Class Employees, Class Customers, Class Sales
I'd like to pass a SQL query via the constructor derived from JTextField values (to the query class, "Database").
我想通过从 JTextField 值派生的构造函数（到查询类“数据库”）传递 SQL 查询。
For example, from two different classes:
例如，来自两个不同的类：
new Database (SELECT PRODUCT FROM SALES WHERE DATE = YESTERDAY);
new Database (SELECT FULLNAMES FROM CUSTOMER WHERE ADDRESS = NEWYORK);
The problem I'm facing is when it comes to creating the following items dynamically (PreparedStatement Parameters):
我面临的问题是在动态创建以下项目时（PreparedStatement 参数）：
stmt.setString(2, NEWYORK);
so that "sql" at "?" can be populated:
所以“sql”在“？” 可以填充：
String sql = "SELECT FULLNAMES FROM CUSTOMER WHERE ADDRESS = ?";
In my project there could be one statement that passes values to the parameter just as above, or there could be more parameters, meaning more statements, hence the above can't be reused.
在我的项目中，可能有一个语句将值传递给参数，就像上面一样，或者可能有更多的参数，意味着更多的语句，因此不能重复使用上面的语句。
Could anyone have ideas on how to generate "stmt.setString(2, NEWYORK);" dynamically so that I could generate it dynamically and as per the number of parameters being passed. So that I could for example have:
任何人都可以对如何生成“stmt.setString(2, NEWYORK);”有想法吗？动态，以便我可以根据传递的参数数量动态生成它。这样我就可以例如：
stmt.setString(1, NEWYORK);
stmt.setString(2, FULLNAMES);
stmt.setString(3, EMPLOYEE);
NOTE: The whole point is to reuse the database class.
注意：重点是重用数据库类。
采纳答案by dic19
Assuming you already are able to create SQL string dynamically (inserting the ?in the right position as needed), I would suggest use a Mapto pass in parameters where the keywould be the parameter order (1,2,3 and so on). Something like this:
假设您已经能够动态创建 SQL 字符串（?根据需要将 插入到正确的位置），我建议使用 aMap传入参数，其中key将是参数顺序（1、2、3 等）。像这样的东西：
public class Database{
private String _sqlString;
private Map<Integer,Object> _parameters;
public Database(String sql){
_sqlstring = sql;
}
public void setParameters(Map<Integer,Object> param){
_parameters = param;
}
/*
* I'm assuming you already have a method like this
*/
public List processQuery(){
List results = new ArrayList();
/*
* establish connection here
*/
PreparedStatement preparedStatement = connection.prepareStatement(this._sqlString);
if(_parameters != null){
/*
* Iterate over the map to set parameters
*/
for(Integer key : _parameters.keySet()){
preparedStatement.setObject(key, _parameters.get(key));
}
}
ResultSet rs = preparedStatement.executeQuery();
/*
* process the ResultSet
*/
return results;
}
}
Finally you can use Databaseas follows:
最后你可以使用Database如下：
String sql = "SELECT FULLNAMES FROM CUSTOMER WHERE ADDRESS = ? OR ADDRESS = ?";
Map<Integer,Object> param = new HashMap<>();
param.put(1,"NEW YORK");
param.put(2,"CHICAGO");
Database db = new Database(sql);
db.setParameters(param);
List customers = db.processQuery();
