Recent Pos

JDBC Batch Processing (Batch insert, update and delete)

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmail

Batch Updates:

⇒ The Batch updates is a concept that supports grouping a set of SQL statements (only insert, update and delete) submitting at once to the database server.

⇒ This can save the transportation cost (n/w load) on the database server and improve the performance of the overall system.

⇒ The JDBC 2.0 introduces the support for working with Batch update.
 

Working Batch Updates using Statement:

⇒ The following steps are involved in using Statement to work with Batch Updates.

Step 1: Create the Statement object.

Step 2: Add the SQL to the Statement using addBatch() method of Statement, repeat this for every statement to add into the Batch.

Step 3: Execute the Batch.
 

Creating the Statement:

⇒ We know that the createStatement() method of Connection object is used to create a Statement.

Adding the SQL to the Statement:

⇒ The following method of Statement object is used to add the SQL:

 
⇒ This method will add the given SQL to the batch managed by this statement object.
 
Note: Calling this method will not perform a database call. This will just store the SQL given in the memory of Statement object (i.e. in the client).
 

Executing the Batch:

⇒ After we add all the SQL queries into the batch by calling the addBatch(String sql) method for multiple times, we can now execute the batch using the following method of Statement:

 
⇒ When this method is called the JDBC submits all the SQL queries added earlier to this batch for execution. This returns the update counts for the SQL queries requested for execution.
 
Notes:
1. The JDBC submits the SQL queries into the database as same order as they are added to the batch.
 
2. The DB server executes the statement in the Batch as the same order they appear. Meaning the statements are executed in the same order as they are added to the batch.
 
jdbc batch processing statment
 

Q. What happens when a Statement in the batch fails to execute?

⇒ The proceedings from the when Statement in the batch fails will differ from database to database. The following are the two possible ways the failure will processed by the database:

1. The database will stop continuing execution further statements in the batch (Example Oracle).

2. The database continues executing the later statements in the batch. (Example MySQL).
 
 

Q. How will Java program identify that batch is failed to execute?

⇒ In case of failure in executing batch statement(s) in the executeBatch() method, JDBC throws BatchUpdateException.
 

Q. How to find the Batch terminated in between or continued to end?

⇒ The BatchUpdateException supports the following method to get the update counts of the executed statements in the batch.

 
⇒ In case if this array length is equals to the batch size: The Database has continued executing the batch till end even on error.

⇒ In case if this array length is less than batch size the DB has terminated the batch on first error.

⇒ In this case it is easy to find which statement in the batch is failed. That is the length of this Array will describe the index of the statement failed in the batch. That is length+1 is the index of the statement failed.
 

Q. In case of database continuing till end of the batch even on failure, how we can find which statement in the batch are failed?

⇒ We can read the update counts array from the BatchUpdateException to find this.

⇒ The value of the update count if equals to Statement.EXECUTE_FAILED (i.e. -3) then the Statement at the respective index is found to be failed.
 
jdbc batch processing preparedstatement
 
Example:

 
 

Working with Batch updates using PreparedStatement:

⇒ The following steps are involved in using the PreparedStatement for Batch update:

Step 1: Create the PreparedStatement.

Step 2: Set the values to the parameter.

Step 3: Add the values to the batch(Repeat the 2 and 3 for multiple times)

Step 4: Execute the batch.

⇒ The Step 1 and Step 2 are known to us, discussed earlier under PreparedStatement topic.

⇒ Adding the values to the batch:

⇒ After we set the values we can call the following method of PreparedStatement to add the values to the Batch:

 
Executing the Batch:

⇒ The following method is used to execute the current batch managed by this PreparedStatement:

 
Example:

 
 
Download this example: JDBCBatchProcessing.zip
 
 
<-Previous: Using JDBC To Access Excel Sheet CSV and Text file | | Next->Accessing Database Meta Data Using JDBC DatabaseMetaData

No Comments

Post a Comment