0

I am using prepared statement for doing some operations on my database. I have table as below:

+--------------------------------------------------------+ |Field | Type | Size | Key | Text | +--------------------------------------------------------+ |X1DEL | Char | 1 | 2 | Status | |X1CMP | Zoned | 3 | 1 | Company No. | |X1ORD | Zoned | 9 | 2 | Order Number | |X1TYPE | Char | 1 | 1 | Transaction Type | |X1ORDT | Zoned | 8 | 0 | Order Date | |X1CUST | Char | 10 | 0 | Customer Number | |X1PO | Char | 20 | 0 | PO Number | |X1OTOT | Zoned | 11 | 2 | Order Total | +--------------------------------------------------------+

I am doing one delete query as delete from my_table where field=? for single parameter. I have created query for 2 parameters as delete from my_table where field=? and type=? Again now i want to execute query for 4 to 5 parameters. I am writing a query as delete from my_table where field=?,type=?,size=? and key=? I have tried for this query but not getting result.

Improve this question
6
  • 1
    It is unclear to me what you want to do. If you want to create a query with 4 conditions, then create one (and no, , is not a proper way to separate conditions, you need to use AND or OR). Commented Oct 8, 2013 at 15:03
  • will it work for more than two parameters? Commented Oct 8, 2013 at 15:09
  • I had a thought as it will not work for more than 2 parameters so i asked for the query Commented Oct 8, 2013 at 15:10
  • You can use as many parameters as your database or driver supports. I am unsure why that wouldn't be obvious, so I assume you actually want to do something else. Commented Oct 8, 2013 at 15:10
  • The query with more than two parameters in your question is simply wrong: it has syntax errors. It has nothing to do with the number of parameters. Commented Oct 8, 2013 at 15:12

2 Answers 2

Reset to default
3

By design, use prepared statements to build queries with any number of parameters.

final String sql = "delete from my_table where field=? and type=? and size=? and key=?";

If your parameters are in a collection you can use the advice of the linked tutorial and "make coding easier by using a for loop or a while loop to set values for input parameters". Depending on the type and number of the parameters you are supplying, you need to call the appropriate .set method for each of them.

 //assuming con (DB connection) is properly setup final PreparedStatment statement = con.prepareStatement(sql); statement.setString(1, "X1DEL"); statement.setString(2, "CHAR"); statement.setInt(3,1); statement.setInt(4, 2); //... etc

You have to remember that prepared statements are 1-based indexing.

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

2 Comments

why the String and prepared statement are final here
Mainly due to conventions from work experiences which have become habit.
1

Why did you do it that way when the 2 parameter example showed you the right thing to do?

delete from my_table where field = ? and type = ? and size = ? and key = ?
Improve this answer

1 Comment

You could have tried it yourself and answered easily in the 39 minutes that have passed since you posted this comment. It'll only fail if you code it badly.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.