how to correctly remove the list of records from the database?

Question

I do a simple web-app where user can select checkboxes of items to remove them from database(like mail manager).
So, is there a correct way to do this?

I think to do like this:(in DAO class)

void delete(List<Long> ids){
    .....
    statement = connection.prepareStatement("DELETE FROM table WHERE id=?");
    for (Long id: ids){
         statement.setInt(1, id);
         statement.executeUpdate();
    }
    ......

or this:(in servlet action class)

DAO dao = new DAO();
for (Long id: ids){
    dao.delete(id); // in DAO simple method void delete(long id);
    }

are not good. Сan you tell or explain how to do it right?
UPDATE: ok, can someone tell me how to delete records not one by one?

do you get errors? or the items do not actually delete from the database? what is the issue? — Randy, Feb 14 '12 at 14:50
@Randy, I have no errors, but the code is written so that in case of SQLException may cause serious problems — Ifozest, Feb 14 '12 at 14:56

score 1 · Accepted Answer · edited May 23 '17 at 09:59

That depends greatly on whether they're supposed to be deleted in a single transaction or not. Imagine that you need to delete 5 records upon a request and the deletion of 3rd record fails with an exception. What exactly should happen with the first two records and the last two records? That's a business requirement which you've to figure out yourself first.

At least, the DAO/service method which you're calling in the servlet should not care about this. It should be able to take a Set of IDs like so (no, not a List since it can contain duplicates):

Set<Long> ids = collectItSomehow();
someService.delete(ids);

Then, in the real JDBC implementation, you could use PreparedStatement#addBatch() to create a batch of statements and PreparedStatement#executeBatch() to execute it. You can find some concrete examples in this answer: Reusing a PreparedStatement multiple times.

score 0 · Answer 2 · answered Feb 14 '12 at 16:14

for (Long id: ids){
        populateArray;
    }

Create array (tempArray) from above loop and pass it here.

statement = connection.prepareStatement("DELETE FROM table WHERE id in("+ tempArray+")");

Note: This may cause sql injection if input is not properly validated.

score -1 · Answer 3 · answered Feb 14 '12 at 14:47

-1

You want to delete the records one by one or selecting more than one record at once?

answered Feb 14 '12 at 14:47

maufonfa

92
8

I do not know what to say.) I want to delete all selected records, and now I ask exactly how this is best done. I think to delete the records one by one (as in my code) is not good. – Ifozest Feb 14 '12 at 14:54
1

This is a comment and not an answer. – home Feb 14 '12 at 14:54

how to correctly remove the list of records from the database?

3 Answers3