Use prepared statement to delete values from database

Question

How can I use a prepared statement to delete entries from a database? I have found that I must write the following code

String deleteSQL = "DELETE DBUSER WHERE USER_ID = ?

but I want to specify a clause with more than one variable. I have used the AND operator but it doesn't seem to work.

Add the query that you don't believe works. – Perception Feb 21 '13 at 13:17 — Perception, Feb 21 '13 at 13:17
Add the code to your query – juergen d Feb 21 '13 at 13:18 — juergen d, Feb 21 '13 at 13:18
how it is related to prepared statements? – Your Common Sense Feb 21 '13 at 13:21 — Your Common Sense, Feb 21 '13 at 13:21

Aliaksei Bulhak · Answer 1 · 2013-02-21T13:31:57.927

It's must work/ for example

Select from Employee e where e.ID < ? and e.ID >= ? order by e.ID

to set values use this:

    int id1 = 1;
    int id2 = 10;
    preparedStatement.setInt(2, id1);
    preparedStatement.setInt(1, id2);

for delete I use this code:

public synchronized boolean deleteNewsById(Integer[] idList)
        throws NewsManagerException {
    DatabaseConnection connection = pool.getConnection();
    StringBuffer buffer = new StringBuffer();
    buffer.append("(");
    buffer.append(idList[0]);
    for (int i = 1; i < idList.length; i++) {
        buffer.append(",");
        buffer.append(idList[i]);
    }
    buffer.append(")");
    PreparedStatement statement = connection
            .getPreparedStatement(DELETE_NEWS_BY_ID + buffer);
}

and sql query looks like this

private static final String DELETE_NEWS_BY_ID = "delete from NEWS where ID in ";

or simple write delete from NEWS where ID in (?,?,?) and set values like in first example

This is a select statement, not a delete like the OP requested. — Robert H, Feb 21 '13 at 13:20
It does for clarity. Adding an answer that is not immediately relevant to the question will turn people off of the answer, regardless if the correct syntax can be taken from the statement. — Robert H, Feb 21 '13 at 13:23

score 1 · Answer 2 · answered Feb 21 '13 at 13:21

Here is an example if your syntax is not correct..

DELETE DBUSER WHERE USER_ID = ? and USER_NAME = ?;

you can append more conditions in where clause by using more AND ... operators.

OR if you have more than one USER_IDs to delete in a single query..

DELETE DBUSER WHERE USER_ID in (?, ?, ?, ?);

score 0 · Answer 3 · edited May 23 '17 at 12:04

Are you looking for the IN operator which allows you to specify multiple values in the WHERE clause such as in my example.

String deleteSQL = "DELETE DBUSER WHERE USER_ID IN (?)"

Though in PreparedStatement IN clause alternatives there are some useful answers and links that you may want to take a look at such as Batch Statements in JDBC which discuss the pros and cons of different batching approaches. The IN approach I'm suggesting is part of that discussion. The end result is that you make just one trip to the database, rather than one per delete and that's better performing because of the reduced network activity required.

score 0 · Answer 4 · answered Feb 21 '13 at 13:52

I think the response from Aleksei Bulgak is correct, but to perhaps more straightforwardly word it...you can set your parameters like this:

String stmt = "DELETE DBUSER WHERE USER_ID = ? and (USER_NAME = ? or USER_NAME = ?)";
preparedStatement.setInt(1, firstParam);
preparedStatement.setString(2, secondParam);
preparedStatement.setString(3, thirdParam);

...and for however many parameters(question marks) in your SQL (no matter if you're using IN or whatever you want), you should set that many parameters here(using setInt for ints, setString for Strings, etc). This goes for select and delete queries.

Use prepared statement to delete values from database

4 Answers4