How to pass multiple parameters to a PreparedStatement in java?

Question

I am using the below code to retrive the order data from db2 and it works fine when i am passing only the BranchNumber and used the getWildcards() function since sometime i am passing multiple branch numbers .

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

Now i need to pass the startDate and endDate inside the function to retrieve the data but the preparedstatement is not formatting the select query with the passed value .

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

Can someone please explain me what i am doing wrong here and how i can get the expected preparedstatement,below is the formatted query coming in my log and error message recorded,

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)

You did not set value for the question mark in preparedstatement of this line: sb.append("AND ORDERDATE ? and ? WITH UR");. This may lead to all your prepraredstatement become wrong — TuyenNTA
– TuyenNTA, Commented Jul 21, 2017 at 22:48

c0der · Accepted Answer · 2020-05-25 06:34:38Z

3

Each ? in the PrepareStatement should be assigned a value. Here is an example adopted from 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 `?`

edited May 25, 2020 at 6:34

answered Jul 22, 2017 at 7:36

c0der

18.9k6 gold badges38 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chimbu Over a year ago

Thanks c0der, i have updated the code part like below and it works fine when i search the result for branch number , but its not working when i pass multiple branches for(int i=0 ; i<BranchNumber.size() ;i++) { statement.setInt(i+1,BranchNumber.get(i).getBranch()); } statement.setString (2,startDate); statement.setString (3,endDate); ResultSet resultSet = statement.executeQuery(); { error: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-313, SQLSTATE=07001, SQLERRMC=null, DRIVER=3.63.75

chimbu Over a year ago

updated the code like below and its now worked as expected for(int i=0 ; i<BranchNumber.size() ;i++) { statement.setInt(i+1,BranchNumber.get(i).getBranch()); } statement.setString (BranchNumber.size()+1, startDate); statement.setString (BranchNumber.size()+2, endDate); ResultSet resultSet = statement.executeQuery(); System.out.println(statement); {

Collectives™ on Stack Overflow

How to pass multiple parameters to a PreparedStatement in java?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related