JDBC - Batching with Statement Object

Here is a typical sequence of steps to use Batch Processing with Statement Object −

Create a Statement object using either createStatement() methods.
Set auto-commit to false using setAutoCommit().
Add as many as SQL statements you like into batch using addBatch() method on created statement object.
Execute all the SQL statements using executeBatch() method on created statement object.
Finally, commit all the changes using commit() method.

This sample code has been written based on the environment and database setup done in the previous chapters.

Executing SQL statements in Batch with AutoCommit as False in JDBC Example

In this example, we've three static strings containing a dababase connection url, username, password. We've defined a method printResultSet() which takes the resultset as argument, iterates it and print all the records of the resultset. Once all changes are commited, printResultSet() method is called to print all the records.

Now using DriverManager.getConnection() method, we've prepared a database connection. Using setAutoCommit(false), we've set the auto commit as false which is by default true. Once connection is prepared, we've created a Statement object using connection.createStatement() method. While creating Statement Object, we've used ResultSet types as TYPE_SCROLL_INSENSITIVE and CONCUR_UPDATABLE. Then using statement.executeQuery(), all records are fetched and printed using printResultSet() method.

Now we've prepared a SQL insert statement as string and added this command to statement object using addBatch() method. Similarly one insert and an update statement is added to the statement object using addBatch() method. Then using executeBatch() method, we've executed all the statements in one go and commit the changes using commit() method. Now using printResultSet(), we're printing all the records available in Employees table.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";

   public static void printResultSet(ResultSet rs) throws SQLException{
      // Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         // Display values
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Age: " + rs.getInt("age"));
         System.out.print(", First: " + rs.getString("first"));
         System.out.println(", Last: " + rs.getString("last"));
      }
      System.out.println();
   }

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE)
            ) {		      
         conn.setAutoCommit(false);	    	  

         ResultSet rs = stmt.executeQuery("Select * from Employees");
         printResultSet(rs);

         // Create SQL statement
         String SQL = "INSERT INTO Employees (first, last, age) " + 
            "VALUES('Zia', 'Ali', 30)";
         // Add above SQL statement in the batch.
         stmt.addBatch(SQL);

         // Create one more SQL statement
         SQL = "INSERT INTO Employees (first, last, age) " +
            "VALUES('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 = 7";
         // 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();

         rs = stmt.executeQuery("Select * from Employees");
         printResultSet(rs);	  

         stmt.close();
         rs.close();

      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

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
ID: 1, Age: 23, First: Zara, Last: Ali
ID: 2, Age: 30, First: Mahnaz, Last: Fatma
ID: 3, Age: 35, First: Zaid, Last: Khan
ID: 4, Age: 33, First: Sumit, Last: Mittal
ID: 5, Age: 40, First: John, Last: Paul
ID: 7, Age: 20, First: Sita, Last: Singh
ID: 8, Age: 20, First: Rita, Last: Tez
ID: 9, Age: 20, First: Sita, Last: Singh

ID: 1, Age: 23, First: Zara, Last: Ali
ID: 2, Age: 30, First: Mahnaz, Last: Fatma
ID: 3, Age: 35, First: Zaid, Last: Khan
ID: 4, Age: 33, First: Sumit, Last: Mittal
ID: 5, Age: 40, First: John, Last: Paul
ID: 7, Age: 35, First: Sita, Last: Singh
ID: 8, Age: 20, First: Rita, Last: Tez
ID: 9, Age: 20, First: Sita, Last: Singh
ID: 10, Age: 30, First: Zia, Last: Ali
ID: 11, Age: 35, First: Raj, Last: Kumar
C:\>

Executing SQL statements in Batch with AutoCommit as true in JDBC Example

Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a Statement object using connection.createStatement() method. While creating Statement Object, we've used ResultSet types as TYPE_SCROLL_INSENSITIVE and CONCUR_UPDATABLE. Then using statement.executeQuery(), all records are fetched and printed using printResultSet() method.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";

   public static void printResultSet(ResultSet rs) throws SQLException{
      // Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         // Display values
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Age: " + rs.getInt("age"));
         System.out.print(", First: " + rs.getString("first"));
         System.out.println(", Last: " + rs.getString("last"));
      }
      System.out.println();
   }

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE)
            ) {		      
         ResultSet rs = stmt.executeQuery("Select * from Employees");
         System.out.println("Displaying records before batch processing.");
         System.out.println("--------------------------------------------");
         printResultSet(rs);

         // Create SQL statement
         String SQL = "INSERT INTO Employees (first, last, age) " + 
            "VALUES('Ahmed', 'Ali', 45)";
         // Add above SQL statement in the batch.
         stmt.addBatch(SQL);

         // Create one more SQL statement
         SQL = "INSERT INTO Employees (first, last, age) " +
            "VALUES('Raksha', 'Agarwal', 31)";
         // Add above SQL statement in the batch.
         stmt.addBatch(SQL);

         // Create one more SQL statement
         SQL = "UPDATE Employees SET age = 35 " +
         "WHERE id = 26";
         // Add above SQL statement in the batch.
         stmt.addBatch(SQL);

         // Create an int[] to hold returned values
         int[] count = stmt.executeBatch();
         System.out.println("Successfully executed batch.");

         rs = stmt.executeQuery("Select * from Employees");
         printResultSet(rs);	  
         System.out.println("-------------------------------------");
         stmt.close();
         rs.close();

      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

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
Displaying records before batch processing.
--------------------------------------------
ID: 1, Age: 18, First: Zara, Last: Ali
ID: 2, Age: 25, First: Mahnaz, Last: Fatma
ID: 3, Age: 30, First: Zaid, Last: Khan
ID: 4, Age: 28, First: Sumit, Last: Mittal
ID: 7, Age: 20, First: Rita, Last: Tez
ID: 8, Age: 20, First: Sita, Last: Singh
ID: 21, Age: 35, First: Jeevan, Last: Rao
ID: 22, Age: 40, First: Aditya, Last: Chaube
ID: 25, Age: 35, First: Jeevan, Last: Rao
ID: 26, Age: 35, First: Aditya, Last: Chaube
----------------------------------------
Successfully executed batch.
ID: 1, Age: 18, First: Zara, Last: Ali
ID: 2, Age: 25, First: Mahnaz, Last: Fatma
ID: 3, Age: 30, First: Zaid, Last: Khan
ID: 4, Age: 28, First: Sumit, Last: Mittal
ID: 7, Age: 20, First: Rita, Last: Tez
ID: 8, Age: 20, First: Sita, Last: Singh
ID: 21, Age: 35, First: Jeevan, Last: Rao
ID: 22, Age: 40, First: Aditya, Last: Chaube
ID: 25, Age: 35, First: Jeevan, Last: Rao
ID: 26, Age: 35, First: Aditya, Last: Chaube
ID: 34, Age: 45, First: Ahmed, Last: Ali
ID: 35, Age: 31, First: Raksha, Last: Agarwal
-------------------------------------

C:\>

jdbc-batch-processing.htm

Print Page