Java/Database SQL JDBC/Batch Update

Batch update: add Batch commands

 
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    ResultSet rs = null;
    Statement stmt = null;
    try {
        conn = getMySqlConnection();
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);
        conn.setAutoCommit(false);
        stmt.addBatch("INSERT INTO survey(id, name) VALUES("11", "Alex")");
        stmt.addBatch("INSERT INTO survey(id, name) VALUES("22", "Mary")");
        stmt.addBatch("INSERT INTO survey(id, name) VALUES("33", "Bob")");
        int[] updateCounts = stmt.executeBatch();
        System.out.println(updateCounts);
        conn.rumit();
        rs = stmt.executeQuery("SELECT * FROM survey");
        while (rs.next()) {
            String id = rs.getString("id");
            String name = rs.getString("name");
            System.out.println("id="+id +"  name="+name);
        }
    }
    catch(BatchUpdateException b) {
        System.err.println("SQLException: " + b.getMessage());
        System.err.println("SQLState: " + b.getSQLState());
        System.err.println("Message: " + b.getMessage());
        System.err.println("Vendor error code: " + b.getErrorCode());
        System.err.print("Update counts: ");
        int [] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + " ");
        }
    }
    catch(SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState: " + ex.getSQLState());
        System.err.println("Message: " + ex.getMessage());
        System.err.println("Vendor error code: " + ex.getErrorCode());
    }
    catch(Exception e) {
        e.printStackTrace();
        System.err.println("Exception: " + e.getMessage());
    }
    finally {
        rs.close();
        stmt.close();
        conn.close();
    }
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Batch Update Demo

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class MainClass {
  public static void main(String[] args) {
    Connection connection = null;
    Statement statement = null;
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      String url = "jdbc:mysql://localhost/chapter04_jdbc21";
      connection = DriverManager.getConnection(url, "username", "password");
      statement = connection.createStatement();
      String update1 = "UPDATE employees SET email = "a@a.ru" WHERE email = "a@b.ru"";
      statement.addBatch(update1);
      String update2 = "UPDATE employees SET email = "b@b.ru" WHERE email = "b@c.ru"";
      statement.addBatch(update2);
      String update3 = "UPDATE employees SET email = "c@c.ru" WHERE email = "c@d.ru"";
      statement.addBatch(update3);
      statement.executeBatch();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (statement != null) {
        try {
          statement.close();
        } catch (SQLException e) {
        } // nothing we can do
      }
      if (connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
        } // nothing we can do
      }
    }
  }
}

Batch update for MySQL

 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.BatchUpdateException;
public class TestBatchUpdate {
  public static Connection getConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/octopus";
    String username = "root";
    String password = "root";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void main(String args[]) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      conn.setAutoCommit(false);
      stmt.addBatch("INSERT INTO batch_table(id, name) VALUES("11", "A")");
      stmt.addBatch("INSERT INTO batch_table(id, name) VALUES("22", "B")");
      stmt.addBatch("INSERT INTO batch_table(id, name) VALUES("33", "C")");
      int[] updateCounts = stmt.executeBatch();
      conn.rumit();
      rs = stmt.executeQuery("SELECT * FROM batch_table");
      while (rs.next()) {
        String id = rs.getString("id");
        String name = rs.getString("name");
        System.out.println("id=" + id + "  name=" + name);
      }
    } catch (BatchUpdateException b) {
      System.err.println("SQLException: " + b.getMessage());
      System.err.println("SQLState: " + b.getSQLState());
      System.err.println("Message: " + b.getMessage());
      System.err.println("Vendor error code: " + b.getErrorCode());
      System.err.print("Update counts: ");
      int[] updateCounts = b.getUpdateCounts();
      for (int i = 0; i < updateCounts.length; i++) {
        System.err.print(updateCounts[i] + " ");
      }
    } catch (SQLException ex) {
      System.err.println("SQLException: " + ex.getMessage());
      System.err.println("SQLState: " + ex.getSQLState());
      System.err.println("Message: " + ex.getMessage());
      System.err.println("Vendor error code: " + ex.getErrorCode());
    } catch (Exception e) {
      System.err.println("Exception: " + e.getMessage());
    } finally {
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (Exception ignore) {
      }
    }
  }
}

Batch Update Insert

 
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    ResultSet rs = null;
    Statement stmt = null;
    try {
        conn = getMySqlConnection();
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);
        conn.setAutoCommit(false);
        stmt.addBatch("INSERT INTO survey(id, name) VALUES("11", "Alex")");
        stmt.addBatch("INSERT INTO survey(id, name) VALUES("22", "Mary")");
        stmt.addBatch("INSERT INTO survey(id, name) VALUES("33", "Bob")");
        int[] updateCounts = stmt.executeBatch();
        System.out.println(updateCounts);
        conn.rumit();
        rs = stmt.executeQuery("SELECT * FROM survey");
        while (rs.next()) {
            String id = rs.getString("id");
            String name = rs.getString("name");
            System.out.println("id="+id +"  name="+name);
        }
    }
    catch(BatchUpdateException b) {
        System.err.println("SQLException: " + b.getMessage());
        System.err.println("SQLState: " + b.getSQLState());
        System.err.println("Message: " + b.getMessage());
        System.err.println("Vendor error code: " + b.getErrorCode());
        System.err.print("Update counts: ");
        int [] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + " ");
        }
    }
    catch(SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState: " + ex.getSQLState());
        System.err.println("Message: " + ex.getMessage());
        System.err.println("Vendor error code: " + ex.getErrorCode());
    }
    catch(Exception e) {
        e.printStackTrace();
        System.err.println("Exception: " + e.getMessage());
    }
    finally {
        rs.close();
        stmt.close();
        conn.close();
    }
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Check Batch Update Result

 
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    ResultSet rs = null;
    Statement stmt = null;
    try {
        conn = getMySqlConnection();
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                    ResultSet.CONCUR_UPDATABLE);
        conn.setAutoCommit(false);
        stmt.addBatch("INSERT INTO survey(id, name) VALUES("11", "Alex")");
        stmt.addBatch("INSERT INTO survey(id, name) VALUES("22", "Mary")");
        stmt.addBatch("INSERT INTO survey(id, name) VALUES("33", "Bob")");
        int[] updateCounts = stmt.executeBatch();
        System.out.println(updateCounts);
        conn.rumit();
        rs = stmt.executeQuery("SELECT * FROM survey");
        while (rs.next()) {
            String id = rs.getString("id");
            String name = rs.getString("name");
            System.out.println("id="+id +"  name="+name);
        }
    }
    catch(BatchUpdateException b) {
        System.err.println("SQLException: " + b.getMessage());
        System.err.println("SQLState: " + b.getSQLState());
        System.err.println("Message: " + b.getMessage());
        System.err.println("Vendor error code: " + b.getErrorCode());
        System.err.print("Update counts: ");
        int [] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + " ");
        }
    }
    catch(SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState: " + ex.getSQLState());
        System.err.println("Message: " + ex.getMessage());
        System.err.println("Vendor error code: " + ex.getErrorCode());
    }
    catch(Exception e) {
        e.printStackTrace();
        System.err.println("Exception: " + e.getMessage());
    }
    finally {
        rs.close();
        stmt.close();
        conn.close();
    }
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Create a batch update in JDBC

  
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = null;
    try {
      conn = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "root", "");
      conn.setAutoCommit(false);
      Statement st = conn.createStatement();
      st.addBatch("INSERT INTO products (id, name, quantity, price) VALUE ("1", "M", 10.99, 20)");
      st.addBatch("INSERT INTO products (id, name, quantity, price) VALUE ("2", "D", 15.99, 19)");
      st.addBatch("INSERT INTO products (id, name, quantity, price) VALUE ("3", "T", 5.99, 25)");
      st.addBatch("INSERT INTO products (id, name, quantity, price) VALUE ("4", "S", 8.99, 50)");
      int[] updateCounts = st.executeBatch();
      conn.rumit();
    } catch (SQLException e) {
      if (conn != null) {
        conn.rollback();
      }
      e.printStackTrace();
    } finally {
      if (conn!= null) {
        conn.close();
      }
    }
  }
}

Deal with batch update exception and results

 
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class DemoBatchUpdateException {
  public static Connection getMySQLConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/databaseName";
    String username = "root";
    String password = "root";
    Class.forName(driver);
    return DriverManager.getConnection(url, username, password);
  }  
  public static void checkUpdateCounts(int[] updateCounts) {
    for (int i = 0; i < updateCounts.length; i++) {
      if (updateCounts[i] >= 0) {
        // Successfully executed; the number represents number of affected rows
        System.out.println("OK: updateCount=" + updateCounts[i]);
      } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
        // Successfully executed; number of affected rows not available
        System.out.println("OK: updateCount=Statement.SUCCESS_NO_INFO");
      } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
        System.out.println("updateCount=Statement.EXECUTE_FAILED");
      }
    }
  }
  public static void main(String[] args)throws Exception {
    Connection conn = getMySQLConnection();
    Statement stmt = null;
    try {
      conn.setAutoCommit(false);
      stmt = conn.createStatement();
      stmt.addBatch("DELETE FROM animalsTable");
      stmt.addBatch("INSERT INTO animalsTable(id, name) VALUES(1, "A")");
      stmt.addBatch("INSERT INTO animalsTable(id, name) VALUES(2, "B")");
      stmt.addBatch("INSERT INTO animalsTable(id, name) VALUES(3, "C")");
      int[] updateCounts = stmt.executeBatch();
      checkUpdateCounts(updateCounts);
      conn.rumit();
    } catch (BatchUpdateException e) {
      int[] updateCounts = e.getUpdateCounts();
      checkUpdateCounts(updateCounts);
      try {
        conn.rollback();
      } catch (Exception e2) {
        e.printStackTrace();
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      stmt.close();
      conn.close();
    }
  }
}

Demo Prepared Statement Add Batch MySQL

 
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class DemoPreparedStatementAddBatchMySQL {
  public static Connection getConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/databaseName";
    String username = "root";
    String password = "root";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void checkUpdateCounts(int[] updateCounts) {
    for (int i = 0; i < updateCounts.length; i++) {
      if (updateCounts[i] >= 0) {
        System.out.println("Successfully executed; updateCount=" + updateCounts[i]);
      } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
        System.out.println("Successfully executed; updateCount=Statement.SUCCESS_NO_INFO");
      } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
        System.out.println("Failed to execute; updateCount=Statement.EXECUTE_FAILED");
      }
    }
  }
  public static void main(String[] args)throws Exception {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      conn.setAutoCommit(false);
      String query = "insert into add_batch_table(stringCol, intCol) values(?, ?)";
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, "1");
      pstmt.setInt(2, 100);
      pstmt.addBatch();
      pstmt.setString(1, "2");
      pstmt.setInt(2, 200);
      pstmt.addBatch();
      pstmt.setString(1, "3");
      pstmt.setInt(2, 300);
      pstmt.addBatch();
      int[] updateCounts = pstmt.executeBatch();
      checkUpdateCounts(updateCounts);
      conn.rumit();
    } catch (BatchUpdateException e) {
      int[] updateCounts = e.getUpdateCounts();
      checkUpdateCounts(updateCounts);
      try {
        conn.rollback();
      } catch (Exception e2) {
        e.printStackTrace();
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      pstmt.close();
      conn.close();
    }
  }
}

Determining If a Database Supports Batching

  
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    DatabaseMetaData dmd = connection.getMetaData();
    if (dmd.supportsBatchUpdates()) {
      // Batching is supported
    } else {
      // Batching is not supported
    }
  }
}

Prepared Statement With Batch Update

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    con.setAutoCommit(false);
    String sql = "INSERT product VALUES(?,?)";
    PreparedStatement prest = con.prepareStatement(sql);
    prest.setString(1, "A");
    prest.setInt(2, 2002);
    prest.addBatch();
    prest.setString(1, "B");
    prest.setInt(2, 1998);
    prest.addBatch();
    prest.setString(1, "C");
    prest.setInt(2, 1980);
    prest.addBatch();
    prest.setString(1, "D");
    prest.setInt(2, 1975);
    prest.addBatch();
    int count[] = prest.executeBatch();
    con.rumit();
    con.close();
  }
}

Statement Batch Update

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial",
        "root", "root");
    con.setAutoCommit(false);
    String table1 = "INSERT emp_sal VALUES("v",1200)";
    String table2 = "DELETE FROM movies WHERE title = "r"";
    Statement st = con.createStatement();
    st.addBatch(table1);
    st.addBatch(table2);
    int count[] = st.executeBatch();
    con.rumit();
    con.close();
    System.out.println("Successfully!");
  }
}