victorio victorio - 4 years ago 90
SQL Question

How to delete rows with updateBatch if I had a list and a single value for parameters?

I have a list, which contains some name data:

List<String> names; //not null, already with values


And I have a single integer, which contains the ID of the created names:

Integer creatorId = 47;


And finally, I have the delete sql, which is like:

String deleteSql = "DELETE FROM persons WHERE person_name = ? and creator_id = ?";


The point here, is to use the
updateBatch
method from Spring, and DO NOT USE
in
clause in the delete sql for person_name-s, and avoid
for
loops.

Is there a way, to use some batch methods here?
Thank you!

Answer Source

The solution is:

public void delete(List<String> names, Integer creatorId) {
    List<Object[]> batchArgs = new ArrayList<>();
    for (String name : names) {
        batchArgs.add(new Object[] { name, creatorId });
    }
    jdbcTemplate.batchUpdate("DELETE FROM persons WHERE person_name = ? and creator_id = ?", batchArgs);
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download