CODEWITHSUNDEEP
javahibernatejpasql-delete
Dmitry Kankalovich
Dmitry Kankalovich

Reputation: 623

Deleting multiple rows in a one statement

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.

Upvotes: 1

Views: 8220

Answers (1)

ziesemer
ziesemer

Reputation: 28707

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? .

Upvotes: 4

Related Questions