How to execute multi batch delete in JdbcTemplate?

Question

I want to delete multiple database entries at once. Each entry should only be deleted if 3 fields match (here: name, email, age).

If I'd just wanted to delete by a single property, I'd go for:

String sql = "DELETE FROM persons WHERE (email) IN (?)";

JdbcTemplate template;
template.execute(sql, Arrays.asList(emails...));

But what if my condition is formed by multiple fields?

String sql = "DELETE FROM persons WHERE (name, email, age) IN (?, ?, ?)";

JdbcTemplate template;
template.execute(sql, ...); ???

The condition should always match all 3 fields (AND)!

Answer 1

Use the batchUpdate(sql, batchArgs, argTypes) method.

String sql = "DELETE FROM persons WHERE name = ? AND email = ? AND age = ?";
int[] argTypes = { Types.VARCHAR, Types.VARCHAR, Types.INTEGER };

List<Object[]> batchArgs = new ArrayList<>();
batchArgs.add(new Object[] { "John Doe", "[email protected]", 42 });
batchArgs.add(new Object[] { "Jane Smith", "[email protected]", 47 });
. . .

JdbcTemplate template = ...;
int[] rowCounts = template.batchUpdate(sql, batchArgs, argTypes);