JDBC - Batch Processing



Batch Processing enables you to gather related SQL proclamations into a cluster and submit them with one call to the database.

When you send a few SQL proclamations to the database immediately, you diminish the measure of correspondence overhead, accordingly improving performance.

  • JDBC drivers are not required to help this element. You should utilize the DatabaseMetaData.supportsBatchUpdates() strategy to decide whether the objective database bolsters group refresh handling. The strategy returns genuine if your JDBC driver underpins this feature.

  • The addBatch() technique for Statement, PreparedStatement, and CallableStatement is utilized to add singular explanations to the clump. The executeBatch() is utilized to begin the execution of the considerable number of explanations assembled together.

  • The executeBatch() restores a variety of whole numbers, and every component of the exhibit speaks to the refresh mean the individual refresh statement.

  • Just as you can add articulations to a clump for preparing, you can evacuate them with the clearBatch() strategy. This technique expels every one of the announcements you included with the addBatch() strategy. Be that as it may, you can't specifically pick which articulation to remove.

Batching with Statement Object

Here is a normal grouping of ventures to utilize Batch Processing with Statement Object −

  • Create a Statement object utilizing either createStatement() methods.

  • Set auto-focus on false utilizing setAutoCommit().

  • Add the same number of as SQL explanations you like into bunch utilizing addBatch() technique on made articulation object.

  • Execute all the SQL proclamations utilizing executeBatch() technique on made articulation object.

  • Finally, submit every one of the progressions utilizing commit() method.

Example

The following code bit gives a case of a group refresh utilizing Statement object −

//Create articulation object 

Statement stmt = conn.createStatement(); 

//Set auto-focus on false 

conn.setAutoCommit(false); 

//Create SQL statement 

String SQL = "INSERT INTO Employees (id, first, last, age) " + 

"VALUES(200,'Zia', 'Ali', 30)"; 

//Add above SQL articulation in the batch. 

stmt.addBatch(SQL);
    
    //Create one more SQL statement 

String SQL = "INSERT INTO Employees (id, first, last, age) " + 

"VALUES(201,'Raj', 'Kumar', 35)"; 

//Add above SQL articulation in the batch. 

stmt.addBatch(SQL); 

//Create one more SQL statement 

String SQL = "UPDATE Employees SET age = 35 " + 

"WHERE id = 100"; 

//Add above SQL articulation in the batch. 

stmt.addBatch(SQL); 

//Create an int[] to hold returned values 

int[] check = stmt.executeBatch(); 

//Explicitly submit articulations to apply changes 

conn.commit();

For a superior comprehension, let us ponder the Batching - Example Code.

Batching with PrepareStatement Object

Here is a run of the mill succession of ventures to utilize Batch Processing with PrepareStatement Object −

  1. Create SQL articulations with placeholders.

  2. Create PrepareStatement object utilizing either prepareStatement() methods.

  3. Set auto-focus on false utilizing setAutoCommit().

  4. Add the same number of as SQL proclamations you like into clump utilizing addBatch() strategy on made articulation object.

  5. Execute all the SQL proclamations utilizing executeBatch() strategy on made explanation object.

  6. Finally, submit every one of the progressions utilizing commit() method.

The following code scrap gives a case of a cluster refresh utilizing PrepareStatement object −

//Create SQL statement 

String SQL = "INSERT INTO Employees (id, first, last, age) " + 

"VALUES(?, ?, ?)"; 

//Create PrepareStatement object 

PreparedStatemen pstmt = conn.prepareStatement(SQL); 

//Set auto-focus on false 

conn.setAutoCommit(false); 

//Set the variables 

pstmt.setInt( 1, 400 ); 

pstmt.setString( 2, "Pappu" ); 

pstmt.setString( 3, "Singh" ); 

pstmt.setInt( 4, 33 ); 

//Add it to the batch 

pstmt.addBatch();
    
//Set the variables 

pstmt.setInt( 1, 401 ); 

pstmt.setString( 2, "Pawan" ); 

pstmt.setString( 3, "Singh" ); 

pstmt.setInt( 4, 31 ); 

//Add it to the batch 

pstmt.addBatch(); 

//include more batches 

. 

. 

. 

. 

//Create an int[] to hold returned values 

int[] tally = stmt.executeBatch();
//Explicitly submit explanations to apply changes 

conn.commit();





© Javacodegeeks © 2019 - 2019
All Right Reserved and you agree to have read and accepted our term and condition.

Python 3