Home » Core Java » sql » Batch statement execution example

About Byron Kiourtzoglou

Byron Kiourtzoglou
Byron is a master software engineer working in the IT and Telecom domains. He is an applications developer in a wide variety of applications/services. He is currently acting as the team leader and technical architect for a proprietary service creation and integration platform for both the IT and Telecom industries in addition to a in-house big data real-time analytics solution. He is always fascinated by SOA, middleware services and mobile development. Byron is co-founder and Executive Editor at Java Code Geeks.

Batch statement execution example

With this example we are going to demonstrate how to execute a batch statement in Java. In particular we are going to insert 10 rows of data to a table. In short, to execute a batch statement you should:

  • Load the JDBC driver, using the forName(String className) API method of the Class. In this example we use the MySQL JDBC driver.
  • Create a Connection to the database. Invoke the getConnection(String url, String user, String password) API method of the DriverManager to create the connection.
  • Disable auto-commit, with the setAutoCommit(boolean autoCommit) API method, so that SQL statements are grouped into transactions that are terminated by a call to either the method commit or the method rollback.
  • Create a PreparedStatement object, using the prepareStatement(String sql) API method of the Connection in order to send the parameterized SQL statement to the database.
  • For each one of the 10 rows invoke the setString(int parameterIndex, String x) and addBatch() API methods of the PreparedStatement, in order to set the parameters in the String value of each row and then add the row to the batch.
  • Invoke the executeBatch() in order to execute the batch, which will return an array of update counts.
  • If there are no errors commit, with the commit() API method.
  • If there is a BatchUpdateException, something is wrong with the updated rows. So for each one of them, check if the batch statement was executed successfully, but no count of the number of rows it affected is available ( SUCCESS_NO_INFO ). Check also if any error occured while executing a batch statement ( EXECUTE_FAILED ). If an error has occured, rollback, with the rollback() API method.

Let’s take a look at the code snippet that follows:

package com.javacodegeeks.snippets.core;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

public class BatchInsert {
 
  public static void main(String[] args) {

    Connection connection = null;
    try {

  // Load the MySQL JDBC driver

  String driverName = "com.mysql.jdbc.Driver";

  Class.forName(driverName);


  // Create a connection to the database

  String serverName = "localhost";

  String schema = "test";

  String url = "jdbc:mysql://" + serverName +  "/" + schema;

  String username = "username";

  String password = "password";

  connection = DriverManager.getConnection(url, username, password);

  

  System.out.println("Successfully Connected to the database!");

  
    } catch (ClassNotFoundException e) {

  System.out.println("Could not find the database driver " + e.getMessage());
    } catch (SQLException e) {

  System.out.println("Could not connect to the database " + e.getMessage());
    }

    try {

  // Disable auto-commit

  connection.setAutoCommit(false);


  // Create a prepared statement

  PreparedStatement statement = connection.prepareStatement("INSERT INTO test_table VALUES(?)");


  // Insert 10 rows of data

  for (int i=0; i<10; i++) {


statement.setString(1, "test_value_"+i);


statement.addBatch();

  }


  /*

   * Execute the batch and get an array containing the result code for every 

   * individual statement execution. For every successful statement execution 

   * the number of rows affected by the specific statement is returned. If the 

   * number of affected rows is not available a Statement.SUCCESS_NO_INFO is 

   * returned. If a statement execution fails then a BatchUpdateException is thrown.

   * Nevertheless some databases will continue to execute statements after one fails 

   * returning Statement.EXECUTE_FAILED for every failed statement execution. If so, 

   * updateCounts will contain results for all batched statements; else updateCounts 

   * will contain results for the statements that where executed up until the first one 

   * that failed

   */

  int[] updateCounts = statement.executeBatch();


  // Since there were no errors, commit

  connection.commit();


  System.out.println("Successfully added 10 rows to the database");

    } catch (BatchUpdateException e) {


  // Not all of the statements were successfully executed

  int[] updateCounts = e.getUpdateCounts();


  for (int i=0; i<updateCounts.length; i++) {


if (updateCounts[i] >= 0) {


    // Successfully executed; the number represents number of affected rows


} else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {


    // Successfully executed; number of affected rows not available


} else if (updateCounts[i] == Statement.EXECUTE_FAILED) {


    // Failed to execute


}

  }


  // We can commit the successfully executed statements or rollback the entire batch - here we rollback

  try {


connection.rollback();

  } catch (SQLException e1) {


System.out.println("Could not rollback transaction " + e1.getMessage());

  }

    } catch (SQLException e) {

  System.out.println("Could not execute statement " + e.getMessage());
    }

  }
}

Example Output:

Successfully Connected to the database!
Successfully added 10 rows to the database

 
This was an example of how to execute a batch statement in Java.

(No Ratings Yet)
Start the discussion Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar
  Subscribe  
Notify of