0

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

coder
  • 231
  • 3
  • 11
  • You are using `NamedParameterJdbcTemplate` but are not using named parameters. Another thing which is important is which database are you using? Finally could you be more clear on **what** isn't working? No batch update? No updates at all? – M. Deinum Jul 13 '23 at 09:16

1 Answers1

0

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));
M. Deinum
  • 115,695
  • 22
  • 220
  • 224
  • HI, Thank for your quick response. Which one is the fastest, i will be updating around 8k records – coder Jul 13 '23 at 09:32
  • Measure I suspect the plain `JdbcTemplate` one as that doesn't involve String parsing to replace the placeholder with question marks. The `IN` one is out the the question then (as those are limited generally to somewhere around 1000 entries). If you have that many you want to use an `InterruptibleBatchPreparedStatementSetter` to determine the optimal batch size (maybe that is 8K but it could be smaller as well). – M. Deinum Jul 13 '23 at 09:37
  • But that basically is another question, this one was about the fact it wasn't working which was due to the wrong usage. Which this answer does actually explain and cover. – M. Deinum Jul 13 '23 at 09:37
  • String updateQuery = "update product set status = 'P' where id in (:ids)"; namedParameterJDBCTemplate.update(updateQuery, Map.of("ids", ids)); -> I am getting ORA-01795: maximum number of expressions in a list is 1000 – coder Jul 13 '23 at 14:50
  • As I stated **"as those are limited generally to somewhere around 1000 entries:** and I also stated that with 8K items you cannot use the `IN` clause. – M. Deinum Jul 13 '23 at 18:23