BATCH UPDATES / BATCH PROCESSING

What is the need of Batch Updates

The BatchUpdates feature allows us to group SQL statements together and send to database server in one single trip.


Batch Updates using Statement

1. void addBatch(String sql) - Adds the given SQL command to the current list of commands for the statement object.
2. void clearBatch() - Empties this Statement objects current list of SQL commands.
3. int[] executeBatch() - Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Test24 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "rakesh");
Statement st = con.createStatement();
con.setAutoCommit(false);
st.addBatch("insert into dept values(69,'kkk','ddd')");
st.addBatch("delete from dept where deptno=66");
st.addBatch("update dept set loc='NY' where deptno=10");
int count[]=st.executeBatch();
con.commit();
}
}

Output -
 DEPTNO DNAME                LOC
-------          --------------           --------
     69         kkk                         ddd
     10         ACCOUNTING     NY
     20         RESEARCH          DALLAS
     30         SALES                   CHICAGO

     40         OPERATIONS       BOSTON


Batch Update using PreparedStatement

package Package1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test25 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "rakesh");
PreparedStatement ps = con.prepareStatement("insert into dept values(?,?,?)");
ps.setInt(1, 12);
ps.setString(2, "def");
ps.setString(3, "PAk");
ps.addBatch();
ps.setInt(1, 13);
ps.setString(2, "lmn");
ps.setString(3, "Russia");
ps.addBatch();
int count[] = ps.executeBatch();
for(int e:count)
{
System.out.println(e);
}
}

}

Output -
DEPTNO DNAME                  LOC
------           --------------              --------
    69           kkk                          ddd
    12           def                            PAk
    13           lmn                          Russia
    10           ACCOUNTING     NY
    20           RESEARCH          DALLAS
    30           SALES                    CHICAGO

    40           OPERATIONS      BOSTON