Beginner Q: deleting a list from jdbc using template

Hello,
i want to erase a list of records from a database using Spring JdbcTemplate, However i couldnt find the corect method for that. i know with hibernate it is possible, is there a way to erase a list of objects efficiently using JdbcTemplate? (i would appreciate a tiny example)
Thanks.

It sounds like you want to issue a SQL "DELETE...WHERE", which you can do via the various update methods of JdbcTemplate.

There is no way in Spring JDBC to delete all objects in a collection from the database. However, that would usually be too be inefficient, as it would involve n JDBC calls, rather than 1.

thanks a lot. i will use update methods. i think it is fine to me it in n iterations.

If you don't mind a few roundtrips to the database, then you could use the batchUpdate feature.

Here is an example where we pass in a JdbcTemplate and a List of Customer objects that should be deleted.

Code:

---

        private void deleteBatch(JdbcTemplate jt, final List customers) {
               
                int[] actualRowsAffected = jt.batchUpdate(
                        "delete from customer where id = ?",
                        new BatchPreparedStatementSetter() {
                                public void setValues(PreparedStatement ps, int i) throws SQLException {
                                        ps.setInt(1, ((Customer)customers.get(i)).getId());
                                }
                                public int getBatchSize() {
                                        return customers.size();
                                }
                        });
               
        }

---

You need to implement the BatchPrepareStatementSetter with two methods setValues and getBatchSize. The setValues methods gets a PreparedStatment and the index indicating the position in the batch. In our case this corresponds to the position in the List of Customer objects.