How to update a column of table for bulk records using namedParameterJDBCTemplate batchUpdate?

Question

I want update a column of table for bulk records. How can i do that using NamedParameterJdbcTemplate

List<Long> ids = new ArrayList();
ids.add(1);
ids.add(2);

String updateQuery = "update product set status = 'P' where id = ?";
SqlParameterSource  batch = SqlParameterSourceUtils.createBatch(ids.toArray());

namedParameterJDBCTemplate.batchUpdate(updateQuery, batch)

However, the above is not working. Please advise

Answer 1

You are using NamedParameterJdbcTemplate but are not using names in your query. You should have something like this.

Map<String, Long> row1 = Map.of("id", 1L);
Map<String, Long> row2 = Map.of("id", 2L);

SqlParameterSource  batch = SqlParameterSourceUtils.createBatch(List.of(row1, row2);

String updateQuery = "update product set status = 'P' where id = :id";
namedParameterJDBCTemplate.batchUpdate(updateQuery, batch)

or don't use NamedParameterJdbcTemplate but just a regular JdbcTemplate.

List<Object[]> ids = List.of(new Object[] {1L}, new Object[] {2L});
String updateQuery = "update product set status = 'P' where id = ?";
jdbcTemplate.batchUpdate(updateQuery, ids)

or, if the list of ids isn't too large use NamedParameterJdbcTemplate with an IN clause instead of a batch update.

List<Long> ids = List.of(1L, 2L);

String updateQuery = "update product set status = 'P' where id in (:ids)";
namedParameterJDBCTemplate.update(updateQuery, Map.of("ids", ids));