Oracle: Add a number to an existing column value using spring jdbcTemplate

Question

I have an Oracle table as below:

Request_id(VARCHAR2) | batch_id(VARCHAR2) | total_records(NUMBER(3,0)) | updated_records(NUMBER(3,0))  
12345                |       1            |         3                  |           0

Now in my SpringBoot application whenever each record in my batch is completed processing, I am doing:

String query = "UPDATE BATCH_STATUS set UPDATED_RECORDS = (1 + UPDATED_RECORDS) WHERE REQUEST_ID = ? AND BATCH_ID = ?";  
jdbcTemplate.update(query, ps -> {  
  ps.setString(1, requestId);  
  ps.setString(2, batchId);  
  
  ps.executeUpdate();  
});

After this execution, the value of updated_records becomes 2.

But if I run the same query in SQL Developer, it is working fine.

Why is this happening and how to achieve what I am aiming for?

Answer 1

It seems that jdbcTemplate.update method internally does the addition twice and hence such output.

Instead, if I use the execute method, then it is working properly.

String query = "UPDATE BATCH_STATUS set UPDATED_RECORDS = (1 + UPDATED_RECORDS) WHERE REQUEST_ID = ? AND BATCH_ID = ?";  
jdbcTemplate.execute(query, (PreparedStatementCallback<Boolean>) ps -> {

    ps.setString(1, requestId);
    ps.setString(2, batchId));

    return ps.execute();
});