Beginner Q: deleting a list from jdbc using template

[ahmetaa]

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.

[Rod Johnson]

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.

[ahmetaa]

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

[trisberg]

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.



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.