Java JDBC How to - Execute multiple SQL statements with batch using addBatch and executeBatch commands

Question

We would like to know how to execute multiple SQL statements with batch using addBatch and executeBatch commands.

Answer

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/*from   w w w  . j  a v a  2  s .  com*/
public class Main {
  public static void main(String[] args) throws Exception {
    Class.forName("org.apache.derby.jdbc.ClientDriver");
    Connection con = DriverManager.getConnection(
        "jdbc:derby://localhost:1527/testDb", "name", "pass");
    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);
    String insertEmp1 = "insert into emp values(10,'A','trainee')";
    String insertEmp2 = "insert into emp values(11,'B','trainee')";
    String insertEmp3 = "insert into emp values(12,'C','trainee')";
    con.setAutoCommit(false);
    stmt.addBatch(insertEmp1);
    stmt.addBatch(insertEmp2);
    stmt.addBatch(insertEmp3);
    ResultSet rs = stmt.executeQuery("select * from emp");
    rs.last();
    System.out.println("rows before batch execution= " + rs.getRow());
    stmt.executeBatch();
    con.commit();
    System.out.println("Batch executed");
    rs = stmt.executeQuery("select * from emp");
    rs.last();
    System.out.println("rows after batch execution= " + rs.getRow());
  }
}