java - 如何在java中向PreparedStatement传递多个参数？

Question

我使用下面的代码从 db2 检索订单数据，当我仅传递 BranchNumber 并使用 getWildcards() 函数时，它工作正常，因为有时我传递多个分支编号。

public List<Order> getallorders(List<Branch> BranchNumber) throws SQLException {
        List<Order> orders = new ArrayList<Order>();        
        try {               
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ORDER_NUMBER as ordernumber,SERVICE_TYPE as service"
                    + "FROM ORDER WHERE "
                    + "BRANCH IN(");            
            sb.append(getWildCards(BranchNumber.size())).append(")").append(" WITH UR");
            String query = sb.toString();
            PreparedStatement statement = connection.prepareStatement(query);           
            for(int i=0 ; i<BranchNumber.size() ;i++)
            {
            statement.setInt(i+1,BranchNumber.get(i).getBranch()); 
            }
            ResultSet resultSet  = statement.executeQuery();
            {
            while (resultSet .next()) {
                Order order1 = new Order();
                order1.setOrdernumber(resultSet.getInt("ordernumber")); 
                orders.add(order1);                
            }            
            }            
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return orders;
}


    private String getWildCards(int size) {
        // TODO Auto-generated method stub
        StringBuilder sb = new StringBuilder();
        for(int i =0 ; i<size ; i++)

        {
             sb = (i == 0) ? sb.append("?") 
                     : sb.append(",").append("?");

        }
        return sb.toString();
    }

现在我需要在函数内传递 startDate 和 endDate 来检索数据，但准备好的语句不会使用传递的值格式化选择查询。

public List<Order> getallorders(List<Branch> BranchNumber,String startDate,String endDate) throws SQLException {
        List<Order> orders = new ArrayList<Order>();        
        try {               
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ORDER_NUMBER as ordernumber,SERVICE as service"
                    + "FROM ORDER WHERE "
                    + "BRANCH IN(");            
            sb.append(getWildCards(BranchNumber.size())).append(")");
            sb.append("AND ORDERDATE BETWEEN ? and ?  WITH UR");
            String query = sb.toString();
            PreparedStatement statement = 
 connection.prepareStatement(query);            
            for(int i=0 ; i<BranchNumber.size() ;i++)
            {
            statement.setInt(i+1,BranchNumber.get(i).getBranch()); 
            }
            ResultSet resultSet  = statement.executeQuery();
            {
            while (resultSet .next()) {
                Order order1 = new Order();
                order1.setOrdernumber(resultSet.getInt("ordernumber")); 
                orders.add(order1);                
            }            
            }            
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        return orders;
}

有人可以解释一下我在这里做错了什么以及如何获得预期的准备好的语句，下面是我的日志中出现的格式化查询和记录的错误消息，

SELECT ORDER_NUMBER as ordernumber,SERVICE_TYPE as service FROM .ORDER WHERE 
BRANCH_NUMBER IN(?) + AND ORDERDATE BETWEEN ? AND ? WITH UR

com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, 
SQLSTATE=42601, SQLERRMC=ORDER DATE BETWEEN ? AND;H_NUMBER IN(?) + AND;
<order_siblings_by>, DRIVER=3.63.75
at com.ibm.db2.jcc.am.fd.a(fd.java:679)

Answer 1

每个?在 PrepareStatement应该被赋值。这是从 here 中采用的示例:

   String updateString =
        "update " + dbName + ".COFFEES " +
        "set SALES = ? where COF_NAME = ?";

   PreparedStatement updateSales = con.prepareStatement(updateString);
   updateSales.setInt(1, 500); //set value to first `?`
   updateSales.setString(2, "roasted"); //set value to second `?`