JDBC Batch Batch processing statement ExecuteBatch specific explanation

JDBC provides the ability of database batch processing to significantly improve the performance of the system in the case of large volume operations (additions, deletions, etc.). I have contacted a project, in the absence of batch processing, delete 50,000 data about half an hour or so, and then the system to transform, using batch processing method, delete 50,000 data will not be more than 1 minutes. Look at the JDBC code:

Turn off your own active run
Con.setautocommit (FALSE);
Statement stmt = Con.createstatement ();

Stmt.addbatch ("INSERT into employees VALUES (+, ' Joe Jones ')");
Stmt.addbatch ("INSERT into departments VALUES (260, ' Shoe ')");
Stmt.addbatch ("INSERT into Emp_dept VALUES (1000, 260)");

Submit a batch of UPDATE commands to run
int[] updatecounts = Stmt.executebatch ();

In this example, you disable the active run mode, which prevents JDBC from running the transaction when calling Statement.executebatch (). Disabling your own active operation allows the application to decide whether to run transactions when errors occur and some commands in the batch do not run. Therefore, when you make batch updates, you should typically turn off your own active operation.

In JDBC 2.0, the Statement object can remember a list of commands that can be submitted together for execution. When you create a statement, the list of commands associated with it is empty. The Statement.addbatch () method adds an element to the command list of the calling statement. Suppose the batch includes a command that attempts to return a result set, when Statement is called. ExecuteBatch (), the SQLException is thrown. Only DDL and DML commands (which simply return a simple update count) can be run as part of the batch process. Suppose the application decides not to commit a statement that has already been
Command batch, you can call the method Statement.clearbatch () (not shown above) to set up batch processing again.

The Statement.executebatch () method will submit the command batch to the base DBMS to run. The run of the command will be performed in the order in which it was added in the batch. ExecuteBatch () returns an array of update counts for the running command. Each of the commands in the array corresponding to the batch includes an entry, and the elements in the array are sorted according to the order in which the commands are run (this is the same order as the Order's initial Entry). Calling ExecuteBatch () closes the current result set of the Statement object that emitted the call (assuming one result set is open). Once ExecuteBatch () returns, the statement's internal batch command list is set to empty again.

Assuming a command in the batch does not run correctly, ExecuteBatch () throws Batchupdateexception. The ability to call the Batchupdateexception.getupdatecounts () method to return an integer array of update counts for a command that runs successfully in a batch. Since the first command returns an error, Statement.executebatch () aborts, and the commands are run based on the order in which they were added in the batch. So suppose that the array returned by Batchupdateexception.getupdatecounts () includes n elements, which means that the first N commands in the batch are successfully run when ExecuteBatch () is called. Use PreparedStatement to write code like this:

Turn off your own active run
Con.setautocommit (FALSE);
PreparedStatement stmt = con.preparestatement ("INSERT into employees VALUES (?,?)");

Stmt.setint (1, 2000);
Stmt.setstring (2, "Kelly Kaufmann");
Stmt.addbatch ();

Submit the batch to run
int[] updatecounts = Stmt.executebatch ();

========================================

Preparestatement is also an interface
Preparestatement extends Statement
Preparestatement itself without int[] ExecuteBatch () throws SQLException method
Instead, they inherit the statement method, and they are not actually implemented by the interface, but statement
The ExecuteBatch () method is regulated by the interface
/**
* Submits a batch of commands to the database for execution and
* If all commands the execute successfully, returns an array of update counts.
Each time a batch of commands is submitted to the database to run, assuming that all the commands are running successfully, return a
Array, which indicates the number of rows affected by each command
* The <code>int</code> elements of the array that's returned is ordered
* to correspond to the commands in the batch, which is ordered
* According to the order in which they were added to the batch.
Each integer value in the returned array is ordered, and their order is consistent with the commands in the batch process.
The order of the commands is in accordance with the order in which they are added to the batch.
* The elements in the array returned by the method <code>executeBatch</code>
* May be one of the following:
The elements in the array returned by the ExecuteBatch method may be one of the following scenarios:
* <OL>
* <li>a number greater than or equal to zero – indicates that
* Command was processed successfully and are an update count giving the
* Number of rows in the database, were affected by the command ' s
* Execution
A number greater than or equal to zero, which simply means that when the command runs successfully, it returns the number of rows affected by it.
* <li>a Value of <code>SUCCESS_NO_INFO</code>-Indicates the command was
* Processed successfully but that's the number of rows affected is
* Unknown

* The constant indicating that a batch statement executed successfully
* But that's no count of the number of rows it affected is available.
int success_no_info =-2;
The constant success_no_info represents the value =-2, which means that the command ran successfully but the number of rows affected by the command
Cannot be counted, is unknown, can only return success_no_info to explain the command run condition.
* <P> * If One of the commands in a batch update fails to execute properly,
* This method throws a <code>batchupdateexception</code>, and a JDBC
* Driver may or could not continue to process the remaining commands in
* The batch.
Assuming that one of the commands fails during batch processing, an exception is thrown batchupdateexception
The JDBC driver may stop the remaining commands, or it may continue to run the remaining commands.
* However, the driver ' s behavior must be consistent with a
* Particular DBMS, either always continuing to process commands or never
* Continuing to process commands.
Anyway, what the driver does depends on the details of the database management system, always running or never running either.
* If the driver continues processing
* After a failure, the array returned by the method
* <code>BatchUpdateException.getUpdateCounts</code>
* would contain as many elements as there is commands in the batch, and
* At least one of the elements would be the following:
Assuming that the drive continues to run after a failure, return through the Batchupdateexception.getupdatecounts () method
Array should contain the results of those commands in the batch, and at least one of the element values is the following:
* <P>
* <li>a Value of <code>EXECUTE_FAILED</code>-Indicates that command FAILED
* To-execute successfully and occurs only if a driver continues to
* Process commands after a command fails
int execute_failed =-3;
Indicates that the command does not run successfully with a constant value of execute_failed, and only occurs if the drive continues to run after a command error.
Assuming that no longer runs after an error, the results returned do not have error messages but only those that have been successfully run.
* </OL>
* <P> * A driver is no required to implement this method.
* The possible implementations and return values has been modified in
* The Java 2 SDK, Standard Edition, version 1.3 to
* Accommodate the option of continuing to proccess commands in a batch
* Update after a <code>BatchUpdateException</code> obejct have been thrown.
The driver does not implement this method and may occur when the implementation and return values are in Java 2 Sdk,standard Edition,
Version 1.3 to accommodate the batch when the Batchupdateexception exception is thrown after the run is continued or
The option to terminate the run.

* @return An array of update counts containing one element for each
* command in the batch. The elements of the array is ordered according
* to the order in which commands were added to the batch.
Returns an array result with the same order as the join command
* @exception SQLException If a database access error occurs or the
* Driver does not support batch statements. Throws {@link Batchupdateexception}
* (a subclass of <code>SQLException</code>) if one of the commands sent to the
* Database fails to execute properly or attempts to return a result set.
* @since 1.3
*/
Suppose a database access exception or driver does not support batch commands, or if a command fails to send to the database or attempts to obtain a result set
Fails, it throws an exception batchupdateexception it is a subclass of SqlException.