Batch Processing in JDBC
Instead of executing a single query, we can execute a batch (group) of queries. It makes the performance fast. It is because when one sends multiple statements of SQL at once to the database, the communication overhead is reduced significantly, as one is not communicating with the database frequently, which in turn results to fast performance.
The java.sql.Statement and java.sql.PreparedStatement interfaces provide methods for batch processing.
Advantage of Batch Processing
Fast Performance
Methods of Statement interface
The required methods for batch processing are given below:
| Method | Description |
|---|---|
| void addBatch(String query) | The addBatch(String query) method of the CallableStatement, PreparedStatement, and Statement is used to single statements to a batch. |
| int[] executeBatch() | The executeBatch() method begins the execution of all the grouped together statements. The method returns an integer array, and each of the element of the array represents the updated count for respective update statement. |
| boolean DatabaseMetaData.supportsBatchUpdates() throws SQLException | If the target database facilitates the batch update processing, then the method returns true. |
| void clearBatch() | The method removes all the statements that one has added using the addBatch() method. |
Example of batch processing in JDBC
Let’s see the simple example of batch processing in JDBC. It follows following steps:
- Load the driver class
- Create Connection
- Create Statement
- Add query in the batch
- Execute Batch
- Close Connection
FileName: FetchRecords.java
If you see the table user420, two records have been added.
Example of batch processing using PreparedStatement
FileName: BP.java
Output:
enter id 101 enter name Manoj Kumar enter salary 10000 Want to add more records y/n y enter id 101 enter name Harish Singh enter salary 15000 Want to add more records y/n y enter id 103 enter name Rohit Anuragi enter salary 30000 Want to add more records y/n y enter id 104 enter name Amrit Gautam enter salary 40000 Want to add more records y/n n record successfully saved
It will add the queries into the batch until user press n. Finally, it executes the batch. Thus, all the added queries will be fired.