JDBC Batch Batch statement executebatch detailed explanation

JDBC It provides a database batch capable of processing, in bulk data Services (extras, deletions, etc.). ), you can significantly improve system performance. I have a link to a project. In the absence of batch therapy, delete 5 about half an hour of data, the transformation, the use of batch processing method. Deleting 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 a batch update. You should usually 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 (). Will throw SQLException. 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 is performed in the order in which the batch is joined.

ExecuteBatch () returns an array of update counts for the running command. Each command in the array corresponding to the batch includes an entry. 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 join). 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.

Assume that a command in a batch does not run correctly. Then ExecuteBatch () will throw 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 their order of accession 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 all interfaces that do not have an actual implementation method. 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. This array is a description of 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. Simply put, 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 run or never run both.

* 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 batch processing when the Batchupdateexception exception is thrown after the run 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
*/
Assume that the database access exception or the driver does not support batch commands. Or, suppose a command fails or tries to send to the database to get the result set
Fails, an exception is thrown batchupdateexception This is the SqlException subclass.

JDBC Batch Batch statement executebatch detailed explanation