Deleting multiple rows in a one statement

Question

I have some trivial table in database (let say Oracle10g) and I need to implement at DAO ability to delete multiple records. The method remove() receives as a parameter an array of ids (integers).

For now I have a query string "DELETE FROM news WHERE id = ?" which I use at PreparedStatement. I simply add batch for every id from array and then perform execute on PreparedStatement.

I wonder if there any ability to perform it through one query statement, something like "DELETE FROM news WHERE id IN ?". But I cannot find how to properly set an array of integers instead of '?'.

The same question applies to Hibernate and JPA. If there any constructions to solve this ? Because now I use batch-like-way: add Query to Session on every id from array and commit transaction.

See this answer:http://stackoverflow.com/a/189399/851432. It points to a [link](http://www.javaranch.com/journal/200510/Journal200510.jsp#a2) which may be useful in this context. — Jomoos, Dec 07 '11 at 05:52

score 4 · Accepted Answer · edited May 23 '17 at 12:14

The best I've seen done is to dynamically build the String used by the PreparedStatement, inserting the proper # of ?, sequences, then use a for loop to call setInt as appropriate for each row - each row to be deleted in your case.

JPA provides a special syntax for this (can accept a Collection to populate the list of arguments), since it has to create the SQL anyway - and likely does so very similar to how I just described. Specifics as to the API calls (for both JPA and HQL) are available at Hibernate HQL Query : How to set a Collection as a named parameter of a Query? .

Thanks, that was very helpful. – Dmitry Kankalovich Dec 07 '11 at 06:10 — Dmitry Kankalovich, Dec 07 '11 at 06:10

Deleting multiple rows in a one statement

1 Answers1