JDBC Tutorial

Batch Processing

In traditional jdbc programming, to perform any transaction (insert, update and delete) we must send a separate request to database. If there is 'n' number of transactions then we must make 'n' number of request to the database and finally it leads to poor performance.

Disadvantages of NON-BATCH PROCESSING applications:

  1. There is a possibility of leading the database result in inconsistent in the case of interrelated transactions.
  2. Number of Network Round Trips or to and fro calls are more between frontend and backend application.

To avoid the above disadvantages we must use batch processing. Batch processing is the process of grouping 'n' number of interrelated transactions in a single unit and processing at a same time.

Advantages of BATCH PROCESSING:

  1. Batch processing always leads consistency of the database.
  2. Number of network round trips or to and fro calls will be reduced.

Steps for developing BATCH PROCESSING application:

  1. Every batch processing application must contain only DML (insert, delete and update) statements.
  2. Set the auto commit as false. Since, in jdbc environment by default auto commit is true. In order to make auto commit as false we must use the following method:
  3. Set auto commit

    For example:

    con.setAutoCommit (false);
    
  4. Prepare set of SQL DML statements and add to batch.
  5. add Batch

    For example:

    st.addBatch ("insert into dept values (10,"abc","hyd")");
    
  6. Execute batch of DML statements by using the following method:
  7. Execute Batch

    For example:

    int res []=st.executeBatch ();
    

    This method returns individual values of DML statements.

  8. After completion of batch of statements, commit the database by using the following method:
  9. Batch commit

    For example:

    con.commit ();
    
  10. If a single batch statement is not executing then we must rollback the database if required to its original state by using the following method:
  11. Batch rollback

    For example:

    con.rollback ();
    

Write a java program which illustrates the concept of Batch processing?

Answer:

import java.sql.*;

class BatchProConcept {

    public static void main(String[] args) throws Exception {
        Class.forName("Sun.jdbc.odbc.JdbcOdbcDriver");
        System.out.println("DRIVERS LOADED...");
        Connection con = DriverManager.getConnection("jdbc:odbc:oradsn", "scott", "tiger");
        System.out.println("CONNECTION  ESTABLISHED...");
        con.setAutoCommit(false);
        Statement st = con.createStatement();
        st.addBatch("insert into student values (3, 'j2ee')");
        st.addBatch("delete from student where sno=1");
        st.addBatch("update student set sname='java' where sno=2");
        int res[] = st.executeBatch();
        for (int i = 0; i < res.length; i++) {
            System.out.println("NUMBER OF ROWS EFFECTED : " + res[i]);
        }
        con.commit();
        con.rollback();
        con.close();
    }// main
};// BatchProConcept

With batch processing we can obtain effective performance to the jdbc applications by executing group of SQL DML statements.

Write a java program to create a table through frontend application?

Answer:

import java.sql.*;

class CreateTable {

    public static void main(String[] args) {

        try {

            Class.forName("Sun.jdbc.odbc.JdbcOdbcDriver");
            System.out.println("DRIVERS LOADED...");
            Connection con = DriverManager.getConnection("jdbc:odbc:oradsn", "scott", "tiger");
            System.out.println("CONNECTION  ESTABLISHED...");
            Statement st = con.createStatement();
            int i = st.executeUpdate("create table kalyan (eno number (4), ename varchar2 (15))");
            System.out.println("TABLE CREATED...");
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }// main
};// CreateTable