Executing a Batch of SQL Statements in a Database - Java JDBC

Java examples for JDBC:Batch SQL

  1. HOME
  2. Java
  3. JDBC
  4. Batch SQL

Description

Executing a Batch of SQL Statements in a Database

Demo Code


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

public class Main {
  
  public static void main(String[] argv) throws Exception {
    Connection connection = null;
    try {/*from w w w  .  j a  v  a2  s. co m*/
      // Disable auto-commit
    
      connection.setAutoCommit(false);

      String sql = "INSERT INTO my_table VALUES(?)";
      PreparedStatement pstmt = connection.prepareStatement(sql);

      // Insert 10 rows of data
      for (int i = 0; i < 10; i++) {
        pstmt.setString(1, "" + i);
        pstmt.addBatch();
      }

      // Execute the batch
      int[] updateCounts = pstmt.executeBatch();

      // updateCounts contains one element for each batched statement.
      // updateCounts[i] contains the number of rows affected by that statement.
      processUpdateCounts(updateCounts);

      // Since there were no errors, commit
      connection.commit();
    } catch (BatchUpdateException e) {
      // Not all of the statements were successfully executed
      int[] updateCounts = e.getUpdateCounts();

      processUpdateCounts(updateCounts);

      // Either commit the successfully executed statements or rollback the
      // entire batch
      connection.rollback();
    } catch (SQLException e) {
    }
  }

  public static void processUpdateCounts(int[] updateCounts) {
    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
      }
    }
  }
}

Related Tutorials