Using the Batch Update Feature of the JDBC API

Description

Demo Code

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class Main {
  public static void main(String[] args) {
    Connection conn = null;//  ww  w . jav a  2  s.c  o m

    try {
      conn = JDBCUtil.getConnection();

      int[] personIds = { 801, 901 };
      String[] firstNames = { "A", "B" };
      String[] lastNames = { "C", "D" };
      String[] genders = { "M", "M" };
      String[] dobString = { "{d '1980-04-01'}", "{d '1982-03-01'}" };
      double[] incomes = { 12345.00, 12345.00 };

      insertPersonStatement(conn, personIds, firstNames, lastNames, genders,
          dobString, incomes);

      JDBCUtil.commit(conn);
    } catch (SQLException e) {
      System.out.println(e.getMessage());
      JDBCUtil.rollback(conn);
    } finally {
      JDBCUtil.closeConnection(conn);
    }
  }

  public static void insertPersonStatement(Connection conn, int[] personId,
      String[] firstName, String[] lastName, String[] gender, String[] dob,
      double[] income) throws SQLException {

    int[] updatedCount = null;
    Statement stmt = null;

    try {
      stmt = conn.createStatement();
      for (int i = 0; i < personId.length; i++) {
        String SQL = "insert into person "
            + "(person_id, first_name, last_name," + " gender, dob, income) "
            + "values " + "(" + personId[i] + ", " + "'" + firstName[i] + "'"
            + ", " + "'" + lastName[i] + "'" + ", " + "'" + gender[i] + "'"
            + ", " + dob[i] + ", " + income[i] + ")";

        stmt.addBatch(SQL);
      }
      updatedCount = stmt.executeBatch();
      System.out.println("Batch executed successfully.");
      printBatchResult(updatedCount);
    } catch (BatchUpdateException e) {
      updatedCount = e.getUpdateCounts();

      System.out.println("Batch failed.");
      int commandCount = personId.length;
      if (updatedCount.length == commandCount) {
        System.out.println("JDBC driver continues to execute all"
            + " commands in a batch after a failure.");
      } else {
        System.out.println("JDBC driver stops executing subsequent"
            + " commands in a batch after a failure.");
      }
      throw e;
    } finally {
      JDBCUtil.closeStatement(stmt);
    }
  }

  public static void insertPersonPreparedStatement(Connection conn,
      int[] personId, String[] firstName, String[] lastName, String[] gender,
      java.sql.Date[] dob, double[] income) throws SQLException {

    int[] updatedCount = null;
    String SQL = "insert into person "
        + "(person_id, first_name, last_name, gender, dob," + " income) "
        + " values " + "(?, ?, ?, ?, ?, ?)";

    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(SQL);

      for (int i = 0; i < personId.length; i++) {
        pstmt.setInt(1, personId[i]);
        pstmt.setString(2, firstName[i]);
        pstmt.setString(3, lastName[i]);
        pstmt.setString(4, gender[i]);
        if (dob[i] == null) {
          pstmt.setNull(5, Types.DATE);
        } else {
          pstmt.setDate(5, dob[i]);
        }

        pstmt.setDouble(6, income[i]);
        pstmt.addBatch();
      }
      updatedCount = pstmt.executeBatch();
      printBatchResult(updatedCount);
    } catch (BatchUpdateException e) {
      updatedCount = e.getUpdateCounts();
      System.out.println("Batch failed.");
      int commandCount = personId.length;
      if (updatedCount.length == commandCount) {
        System.out.println("JDBC driver continues to execute all"
            + "commands in a batch after a failure.");
      } else {
        System.out.println("JDBC driver stops executing subsequent"
            + "commands in a batch after a failure.");
      }
      throw e;
    } finally {
      JDBCUtil.closeStatement(pstmt);
    }
  }

  public static void printBatchResult(int[] updateCount) {
    for (int i = 0; i < updateCount.length; i++) {
      int value = updateCount[i];
      if (value >= 0) {
        System.out.println("Command #" + (i + 1) + ": Success. Update Count="
            + value);
      } else if (value >= Statement.SUCCESS_NO_INFO) {
        System.out.println("Command #" + (i + 1)
            + ": Success. Update Count=Unknown");
      } else if (value >= Statement.EXECUTE_FAILED) {
        System.out.println("Command #" + (i + 1) + ": Failed");
      }
    }
  }
}

class JDBCUtil {
  public static Connection getConnection() throws SQLException {
    // Register the Java DB embedded JDBC driver
    Driver derbyEmbeddedDriver = null;// new
                                      // org.apache.derby.jdbc.EmbeddedDriver();
    DriverManager.registerDriver(derbyEmbeddedDriver);

    // Construct the connection URL
    String dbURL = "jdbc:derby:beginningJavaDB;create=true;";
    String userId = "root";
    String password = "password";

    // Get a connection
    Connection conn = DriverManager.getConnection(dbURL, userId, password);

    // Set the auto-commit off
    conn.setAutoCommit(false);

    return conn;
  }

  public static void closeConnection(Connection conn) {
    try {
      if (conn != null) {
        conn.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeStatement(Statement stmt) {
    try {
      if (stmt != null) {
        stmt.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeResultSet(ResultSet rs) {
    try {
      if (rs != null) {
        rs.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void commit(Connection conn) {
    try {
      if (conn != null) {
        conn.commit();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void rollback(Connection conn) {
    try {
      if (conn != null) {
        conn.rollback();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection();
      System.out.println("Connetced to the database.");
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      closeConnection(conn);
    }
  }
}
Using the Batch Update Feature of the JDBC API - Java JDBC

Description

Demo Code

Related Tutorials
