JDBC - Batching with PreparedStatement Object

Previous
Next

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

  • Create SQL statements with placeholders.

  • Create PrepareStatement object using either prepareStatement() 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 PreparedStatement (INSERT) 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 PreparedStatement object using connection.createPreparedStatement() method while passing an INSERT query with placeholders. Then using statement.executeQuery(), all records are fetched and printed using printResultSet() method.

Now we've set values to the preparedStatement and then using addBatch() method, preparedStatement is added to the batch. Once added, we've again set the values to the preparedStatement object and using addBatch() method, preparedStatement is added to the batch. 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.*;

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);
           
         conn.setAutoCommit(false);	

         // Create SQL statement
         String SQL = "INSERT INTO Employees(id,first,last,age) " +
                   "VALUES(?, ?, ?, ?)";
         // Create preparedStatement
         System.out.println("Creating statement...");
         stmt = conn.prepareStatement(SQL);
         // print all the records
         ResultSet rs = stmt.executeQuery("Select * from Employees");
         printResultSet(rs);
		 
         // 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();
         System.out.print("Batch Executed.");
         //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();
      } 
   }
}

Now let us compile above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample

Creating statement...
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
Batch Executed.
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
C:\>

Executing PreparedStatement (UPDATE) 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 PreparedStatement object using connection.createPreparedStatement() method while passing an Update query with placeholder. Then using statement.executeQuery(), all records are fetched and printed using printResultSet() method.

Now we've set values to the preparedStatement and then using addBatch() method, preparedStatement is added to the batch. Once added, we've again set the values to the preparedStatement object and using addBatch() method, preparedStatement is added to the batch. 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.*;

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);
           
         conn.setAutoCommit(false);	

         // Create SQL statement
         String SQL = "update employees set age=50 where id=?";
         // Create preparedStatement
         System.out.println("Creating statement...");
         stmt = conn.prepareStatement(SQL);
         // print all the records
         ResultSet rs = stmt.executeQuery("Select * from Employees");
         printResultSet(rs);
		 
         stmt.setInt(1, 35);
         stmt.addBatch();
      
         stmt.setInt(1, 36);
         stmt.addBatch();
      
         stmt.setInt(1, 37);
         stmt.addBatch();  

         // Create an int[] to hold returned values
         int[] count = stmt.executeBatch();
         System.out.print("Batch Executed.");
         //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();
      } 
   }
}

Now let us compile above example as follows −

C:\>javac JDBCExample.java
C:\>

When you run JDBCExample, it produces the following result −

C:\>java JDBCExample

Creating statement...
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
ID: 36, Age: 35, First: Sankalp, Last: Hawladar
ID: 37, Age: 48, First: Anand, Last: Roy

Batch Executed.
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: 50, First: Raksha, Last: Agarwal
ID: 36, Age: 50, First: Sankalp, Last: Hawladar
ID: 37, Age: 50, First: Anand, Last: Roy

C:\>
jdbc-batch-processing.htm
Print Page
Previous
Next
Advertisements