ojdbc7 using addBatch method insert wrong data into table

Question

There is a link for oracle batch, I use it in a project, like below:

PreparedStatement pstmt = 
        conn.prepareStatement("INSERT INTO user VALUES(?, ?)");

pstmt.setInt(1, 2);
pstmt.setString(2, "a");
pstmt.addBatch();

pstmt.setInt(1, 3);
//pstmt.setString(2, "b");
pstmt.addBatch();

The table has two fields, id and name. I don't set name the second time, but in the table there are still two records and names are both 'a'.

ojdbc version

<dependency>
  <groupId>com.oracle.jdbc</groupId>
  <artifactId>ojdbc7</artifactId>
  <version>12.1.0.2</version>
  <scope>test</scope>
</dependency>

Is there anything wrong in my usage, or just ojdbc has a defect? Should I align the parameter before using the ojbdc batching?

Answer 1

a is still set from the first record. You could do

pstmt.clearParameters();

before setting values for the 2^nd record

Answer 2

This is the expected and correct behaviour. Parameters values that you don't set again between addBatch or execute methods retain their values, and will be used on the next addBatch or execute call.

This is specified in section 13.2.2 Setting Parameters of the JDBC 4.3 specification:

The values set for the parameter markers of a PreparedStatement object are not reset when it is executed. The method clearParameters can be called to explicitly clear the values that have been set. Setting a parameter with a different value will replace the previous value with the new one.

(This rule also applies for addBatch, though this is not explicitly specified. Be aware, there is an exception to this rule for InputStream- and Reader-based parameters defined later.)

As Reimeus says in their answer, if you don't want that, you'll need to call clearParameters() after adding to the batch (or executing).