About the JdbcTemplate batch update batchupdate () method returns a value of-2

Original:
Update Counts in the Oracle implementation of Standard batching
If A statement batch is processed successfully, then the integer array, or update counts array, returned by the statement ExecuteBatch call would always have one element for each operation in the batch. In the Oracle implementation of standard update batching, the values of the array elements are as follows:

For a prepared statement batch, it isn't possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of-2. According to the JDBC 2.0 specification, a value of-2 indicates this operation was successful but the number of rows Affected is unknown.

For a generic statement batch, the array contains the actual update counts indicating the number of rows affected by each Operation. The actual update counts can be provided to generic statements in the Oracle implementation of standard B Atching.

For a callable statement batch, the server always returns the value 1 as the update count, irrespective of the number rows Affected by each operation.

In your code, upon successful processing of a batch, you should is prepared to handle either-2, 1, or True update counts In the array elements. For a successful batch processing, the array contains either ALL-2, 1, or all positive integers.

Example 23-2 illustrates the use of standard update batching.

Example 23-2 Standard Update Batching

This example combines the sample fragments in the previous sections accomplishing the following:

Disabling Auto-commit mode, which you should always does when using either update batching model

Creating a prepared Statement object

Adding operations to the batch associated with the prepared statement object

Processing the Batch

Committing the operations from the batch

Conn.setautocommit (false);

PreparedStatement pstmt = 
          conn.preparestatement ("INSERT into employees VALUES (?,?)");

Pstmt.setint (1, Watts);
Pstmt.setstring (2, "Milo Mumford");
Pstmt.addbatch ();

Pstmt.setint (1, 3000);
Pstmt.setstring (2, "Sulu Simpson");
Pstmt.addbatch ();

int[] updatecounts = Pstmt.executebatch ();

Conn.commit ();

Pstmt.close ();
...

Can process the update counts array to determine if the batch processed successfully.

Error Handling in the Oracle implementation of Standard batching
If any one of the batched operations fails to complete successfully or attempts to return a result set during a Executeba TCH call, then the processing stops and a java.sql.BatchUpdateException is generated.

After a batch exception, the update counts array can is retrieved using the Getupdatecounts method of the Batchupdateexcep tion object. This is returns an int array of update counts, just as the ExecuteBatch method does. In the Oracle implementation of standard update batching, contents of the update counts array are as follows, after a BATC H is processed:

For a prepared statement batch, it isn't possible to know which operation. The array has one element for each operation in the batch, and each element has a value of-3. According to the JDBC 2.0 specification, a value of-3 indicates this a operation did not complete. In this case, it is presumably just one operation that actually failed, but because the JDBC driver does not know which O Peration that is, it labels all the batched operations as failures.

You are should always perform a ROLLBACK operation in this situation.

For a generic statement batch or callable statement batch, the update counts array are only a partial array containing the Actual update counts to the "point of the" error. The actual update counts can be provided because Oracle JDBC cannot with true batching for generic and callable statements In the Oracle implementation of standard update batching.

For example, if there were operations in the batch, the succeeded, and the 14th generated a exception, then T He update counts array would have elements, containing actual update counts of the successful operations.

can either commit or roll back to the successful operations in this situation, as to you prefer.

In your code, upon failed processing of a batch, you should is prepared to handle either-3 or True update counts in the A Rray elements When a exception occurs. For a failed batch processing, you'll have either a full array of-3 or a partial array of positive.

Intermixing batched Statements and nonbatched statements
You are cannot call executeupdate for regular, nonbatched processing of a operation if the statement object has a pending bat CH of operations.

However, can intermix batched operations and nonbatched operations in a single Statement object if your process NONBATC Hed operations either prior to adding any operations to the statement batch or after processing the batch. Essentially, you can call executeupdate for a statement the object only as it update batch is empty. If The batch is Non-empty, then a exception'll be generated.

For example, it's valid to have a sequence, such as the following:

Intermixing nonbatched operations on one statement object and batched operations on another statement object within your C Ode is permissible. Different statement objects are independent of each of the, and regard to update batching operations. A COMMIT request would affect all nonbatched operations and all successful operations in processed batches Fect any pending batches.

Translation:
Update count in the Oracle implementation of standard batch
If the statement batch succeeds, the integer array or update count array returned by the statement ExecuteBatch call will always provide an element for each operation in the batch.
In the Oracle implementation of the standard update batch, the values for the array elements are as follows:

For a prepared statement batch, it is not possible to know the number of rows affected in the database for each statement in the batch.
Therefore, the value of all array elements is-2.
According to the JDBC 2.0 specification, a value of 2 indicates that the operation was successful, but the number of rows affected is unknown.

For a generic statement batch, the array contains the actual update count, indicating the number of rows affected by each operation.
The actual update count can only be provided in the Oracle implementation of the standard batch.

For callable statement batches, the server always returns a value of 1 as the update count, regardless of the number of rows affected by each operation.

In your code, when you successfully process a batch, you should be prepared to handle the 2, 1, or True update count in the array element.
For a successful batch, the array contains all 2, 1, or all positive integers.

Example 23-2 demonstrates the use of standard update batches.

Example 23-2 Standard update batch

This example combines the sample fragments from the previous sections to complete the following steps:

Disable autocommit mode, which you should always do when using the update batch model

Create a prepared statement object

To add an action to a batch that is associated with a prepared statement object

Processing Batches

Submitting actions from a batch

Conn.setautocommit (false);

PreparedStatement pstmt = 
          conn.preparestatement ("INSERT into employees VALUES (?,?)");

Pstmt.setint (1, Watts);
Pstmt.setstring (2, "Milo Mumford");
Pstmt.addbatch ();

Pstmt.setint (1, 3000);
Pstmt.setstring (2, "Sulu Simpson");
Pstmt.addbatch ();

int[] updatecounts = Pstmt.executebatch ();

Conn.commit ();

Pstmt.close ();
...

You can process the update count array to determine whether the batch process was successfully processed.

error Handling in Oracle implementations of standard batch processing
If any batch operation fails, or if an attempt is made to return a result set during a executebatch call, the processing stops, a java.sql.
Batchupdateexception generation.

After the batch exception is processed, the update count array can be retrieved using the Getupdatec salvage method of the Batchupdateexception object.
This returns the update count of an int array, just as the ExecuteBatch method does.
In the Oracle implementation of the standard update batch, the contents of the update Count array are as follows, after processing batch batches:

For a prepared statement batch, it is not possible to know which operation failed.
The array has one element for each operation in the batch, and the value of each element is-3.
According to the JDBC 2.0 specification, the value of 3-3 indicates that the operation did not complete successfully.
In this case, there may be only one operation that fails, but because the JDBC driver does not know which operation it is, it marks all batch operations as failed.

In this case, you should always perform a rollback operation.

For a generic statement batch or callable statement batch, the update Count array is just a partial array containing the actual update count to the error point.
The actual update count can be provided, because Oracle JDBC cannot use real batches and callable statements in the Oracle implementation of the standard update batch.

For example, if there are 20 operations in the batch, the 13th succeeds, and the 14th generates an exception, the update Count array will contain 13 elements that contain the actual update count for the successful operation.

You can commit or rollback a successful operation in this case, as you would expect.

In your code, when a batch fails, you should be prepared to handle the 3 or True update count in an array element when an exception occurs.
For a failed batch, you will have a full 3 or a partial array of positive integers.

Mixed batch and non-batch statements
If the statement object has a pending operation, Executeupdate cannot be invoked for regular, non-batch operations.

However, you can mix batch and non batch operations in a single Statement object if you are working with a batch operation, or before adding an action to a statement batch or processing batch.
In essence, executeupdate can be invoked for a statement object only if its update batch is empty.
If the batch is Non-empty, an exception is generated.

For example, there is a sequence that is valid, such as the following:

...
PreparedStatement pstmt = 
          conn.preparestatement ("INSERT into employees VALUES (?,?)");

Pstmt.setint (1, Watts);
Pstmt.setstring (2, "Milo Mumford");

int scount = Pstmt.executeupdate ();   OK; No operations in pstmt batch

pstmt.setint (1, 3000);
Pstmt.setstring (2, "Sulu Simpson");
Pstmt.addbatch ();                    Now start a batch

pstmt.setint (1, 4000);
Pstmt.setstring (2, "Stan Leland");
Pstmt.addbatch ();

int[] bcounts = Pstmt.executebatch ();

Pstmt.setint (1, 5000);
Pstmt.setstring (2, "Amy Feiner");

int scount = Pstmt.executeupdate ();   OK; pstmt Batch was executed ...

Original address:
Https://docs.oracle.com/cd/B28359_01/java.111/b31224/oraperf.htm#CHDCBHEC