如何动态创建预准备语句 - 并重复使用查询 [英] How to create a prepared statement dynamically - and re-use the query

问题描述

我一直在尝试创建一个类来处理来自不同类的查询，例如创建不同的对象。

 类员工，类客户，类销售
  

我想通过构造函数

例如，从两个不同的类中：

 新数据库（SELECT PRODUCT FROM SALES WHERE DATE = YESTERDAY）; 
 
 new数据库（SELECT FULLNAMES FROM CUSTOMER WHERE ADDRESS = NEWYORK）; 
  

我面临的问题是当动态创建以下项目（PreparedStatement参数）：

  stmt.setString（2，NEWYORK）; 
  

以使sql在？可以填充：

  String sql =SELECT FULLNAMES FROM CUSTOMER WHERE ADDRESS =？ 
  

在我的项目中，可能有一个语句将值传递给参数，更多的参数，意味着更多的语句，因此上面的不能重复使用。

任何人都可以有想法如何生成stmt.setString（2，NEWYORK）; 。动态地，使我可以动态生成它，并根据传递的参数的数量。所以我可以例如：

  stmt.setString（1，NEWYORK）; 
 stmt.setString（2，FULLNAMES）; 
 stmt.setString（3，EMPLOYEE）; 
  

注意：重点是重用数据库类。

<假设您已经能够动态创建SQL字符串（根据需要在正确的位置插入？）， ，我建议使用 Map 传递参数，其中键将是参数顺序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; 
} 
 
 / * 
 *我假设你已经有一个这样的方法
 * / 
 public List processQuery（）{
 List results = new ArrayList（）; 
 / * 
 *建立连接
 * / 
 PreparedStatement preparedStatement = connection.prepareStatement（this._sqlString）; 
 
 if（_parameters！= null）{
 / * 
 *在地图上迭代以设置参数
 * / 
 for（整数键：_parameters .keySet（））{
 preparedStatement.setObject（key，_parameters.get（key））; 
} 
} 
 
 ResultSet rs = preparedStatement.executeQuery（）; 
 / * 
 *处理ResultSet 
 * / 
返回结果; 
} 
 
} 
  

最后，您可以使用 Database 如下：

  String sql =SELECT FULLNAMES FROM CUSTOMER WHERE ADDRESS =？ OR ADDRESS =？; 
 
映射< Integer，Object> param = new HashMap<>（）; 
 param.put（1，NEW YORK）; 
 param.put（2，CHICAGO）; 
 
数据库db = new数据库（sql）; 
 db.setParameters（param）; 
 List customers = db.processQuery（）; 
  

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").

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):

stmt.setString(2, NEWYORK);

so that "sql" at "?" can be populated:

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(1, NEWYORK);
stmt.setString(2, FULLNAMES);
stmt.setString(3, EMPLOYEE);

NOTE: The whole point is to reuse the database class.

解决方案

Assuming you already are able to create SQL string dynamically (inserting the ? in the right position as needed), I would suggest use a Map to pass in parameters where the key would be the parameter order (1,2,3 and so on). Something like this:

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 Database as follows:

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

这篇关于如何动态创建预准备语句 - 并重复使用查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！