Batch ProcessingBatch Processing allows you to group related SQL statements into a batch and submit them with one call to the database. When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance.
Here is a typical sequence of steps to use Batch Processing with Statment Object:
The following code snippet provides an example of a batch update using Statement object:
// Create statement object
Statement stmt = conn.createStatement(); // Set auto-commit to false conn.setAutoCommit(false); // Create SQL statement String SQL = "INSERT INTO Employees (id, first, last, age) " + "VALUES(200,'Zia', 'Ali', 30)"; // Add above SQL statement 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 statement in the batch. stmt.addBatch(SQL); // Create one more SQL statement String SQL = "UPDATE Employees SET age = 35 " + "WHERE id = 100"; // Add above SQL statement in the batch. stmt.addBatch(SQL); // Create an int[] to hold returned values int[] count = stmt.executeBatch(); //Explicitly commit statements to apply changes conn.commit(); For a better understanding, let us study the Batching - Example Code as discussed below. Batching - Example Code Here is a typical sequence of steps to use Batch Processing with Statement Object:
Copy and past the following example in JDBCExample.java, compile and run as follows:
// Import required packages
import java.sql.*; public class JDBCExample { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/EMP"; // Database credentials static final String USER = "username"; static final String PASS = "password"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ // Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); // Create statement System.out.println("Creating statement..."); stmt = conn.createStatement(); // Set auto-commit to false conn.setAutoCommit(false); // First, let us select all the records and display them. printRows( stmt ); // Create SQL statement String SQL = "INSERT INTO Employees (id, first, last, age) " + "VALUES(200,'Zia', 'Ali', 30)"; // Add above SQL statement in the batch. stmt.addBatch(SQL); // Create one more SQL statement SQL = "INSERT INTO Employees (id, first, last, age) " + "VALUES(201,'Raj', 'Kumar', 35)"; // Add above SQL statement in the batch. stmt.addBatch(SQL); // Create one more SQL statement SQL = "UPDATE Employees SET age = 35 " + "WHERE id = 100"; // Add above SQL statement in the batch. stmt.addBatch(SQL); // Create an int[] to hold returned values int[] count = stmt.executeBatch(); //Explicitly commit statements to apply changes conn.commit(); // Again, let us select all the records and display them. printRows( stmt ); // Clean-up environment stmt.close(); conn.close(); }catch(SQLException se){ //Handle errors for JDBC se.printStackTrace(); }catch(Exception e){ //Handle errors for Class.forName e.printStackTrace(); }finally{ //finally block used to close resources try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// nothing we can do try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); }//end finally try }//end try System.out.println("Goodbye!"); }//end main public static void printRows(Statement stmt) throws SQLException{ System.out.println("Displaying available rows..."); // Let us select all the records and display them. String sql = "SELECT id, first, last, age FROM Employees"; ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); //Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } System.out.println(); rs.close(); }//end printRows() }//end JDBCExample Now let us compile the above example as follows:
C:\>javac JDBCExample.java
C:\> When you run JDBCExample, it produces the following result:
C:\>java JDBCExample
Connecting to database... Creating statement... Displaying available rows... ID: 95, Age: 20, First: Sima, Last: Chug ID: 100, Age: 18, First: Zara, Last: Ali ID: 101, Age: 25, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 30, First: Sumit, Last: Mittal ID: 110, Age: 20, First: Sima, Last: Chug Displaying available rows... ID: 95, Age: 20, First: Sima, Last: Chug ID: 100, Age: 35, First: Zara, Last: Ali ID: 101, Age: 25, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 30, First: Sumit, Last: Mittal ID: 110, Age: 20, First: Sima, Last: Chug ID: 200, Age: 30, First: Zia, Last: Ali ID: 201, Age: 35, First: Raj, Last: Kumar Goodbye! C:\> Batching with PrepareStatement Object Here is a typical sequence of steps to use Batch Processing with PrepareStatement Object: 1. Create SQL statements with placeholders. 2. Create PrepareStatement object using either prepareStatement() methods. 3. Set auto-commit to false using setAutoCommit(). 4. Add as many as SQL statements you like into batch using addBatch() method on created statement object. 5. Execute all the SQL statements using executeBatch() method on created statement object. 6. Finally, commit all the changes using commit() method. The following code snippet provides an example of a batch update using 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-commit to 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(); //add more batches . . . . //Create an int[] to hold returned values int[] count = stmt.executeBatch(); //Explicitly commit statements to apply changes conn.commit(); For a better understanding, let us to study the Batching - Example Code with PrepareStatement object as discussed below. Batching - Example Code Here is a typical sequence of steps to use Batch Processing with PrepareStatement Object:
Copy and past the following example in JDBCExample.java, compile and run as follows:
// Import required packages
import java.sql.*; public class JDBCExample { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/EMP"; // Database credentials static final String USER = "username"; static final String PASS = "password"; public static void main(String[] args) { Connection conn = null; PreparedStatement stmt = null; try{ // Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); // Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); // Create SQL statement String SQL = "INSERT INTO Employees(id,first,last,age) " + "VALUES(?, ?, ?, ?)"; // Create preparedStatemen System.out.println("Creating statement..."); stmt = conn.prepareStatement(SQL); // Set auto-commit to false conn.setAutoCommit(false); // First, let us select all the records and display them. printRows( stmt ); // Set the variables stmt.setInt( 1, 400 ); stmt.setString( 2, "Pappu" ); stmt.setString( 3, "Singh" ); stmt.setInt( 4, 33 ); // Add it to the batch stmt.addBatch(); // Set the variables stmt.setInt( 1, 401 ); stmt.setString( 2, "Pawan" ); stmt.setString( 3, "Singh" ); stmt.setInt( 4, 31 ); // Add it to the batch stmt.addBatch(); // Create an int[] to hold returned values int[] count = stmt.executeBatch(); //Explicitly commit statements to apply changes conn.commit(); // Again, let us select all the records and display them. printRows( stmt ); // Clean-up environment stmt.close(); conn.close(); }catch(SQLException se){ //Handle errors for JDBC se.printStackTrace(); }catch(Exception e){ //Handle errors for Class.forName e.printStackTrace(); }finally{ //finally block used to close resources try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// nothing we can do try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); }//end finally try }//end try System.out.println("Goodbye!"); }//end main public static void printRows(Statement stmt) throws SQLException{ System.out.println("Displaying available rows..."); // Let us select all the records and display them. String sql = "SELECT id, first, last, age FROM Employees"; ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); //Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } System.out.println(); rs.close(); }//end printRows() }//end JDBCExample Now let us compile above example as follows:
C:\>javac JDBCExample.java
C:\> When you run JDBCExample, it produces following result:
C:\>java JDBCExample
Connecting to database... Creating statement... Displaying available rows... ID: 95, Age: 20, First: Sima, Last: Chug ID: 100, Age: 35, First: Zara, Last: Ali ID: 101, Age: 25, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 30, First: Sumit, Last: Mittal ID: 110, Age: 20, First: Sima, Last: Chug ID: 200, Age: 30, First: Zia, Last: Ali ID: 201, Age: 35, First: Raj, Last: Kumar Displaying available rows... ID: 95, Age: 20, First: Sima, Last: Chug ID: 100, Age: 35, First: Zara, Last: Ali ID: 101, Age: 25, First: Mahnaz, Last: Fatma ID: 102, Age: 30, First: Zaid, Last: Khan ID: 103, Age: 30, First: Sumit, Last: Mittal ID: 110, Age: 20, First: Sima, Last: Chug ID: 200, Age: 30, First: Zia, Last: Ali ID: 201, Age: 35, First: Raj, Last: Kumar ID: 400, Age: 33, First: Pappu, Last: Singh ID: 401, Age: 31, First: Pawan, Last: Singh Goodbye! C:\> |