import java.io.*;
import java.sql.*;
import java.text.*;
import oracle.jdbc.driver.*;

public class TestOracleBatching {
  Connection conn;

  public TestOracleBatching() {
    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      conn = DriverManager.getConnection(
       "jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
      e.printStackTrace();
    }
  }

  public static void main(String[] args) 
   throws Exception, IOException {
    new TestOracleBatching().process(args[0]);
  }

  public void process(String iterations) throws IOException, SQLException {
    int               rows    = 0;
    int               last    = new Integer(iterations).intValue();
    long              start   = 0;
    long              end     = 0;
    Statement         stmt    = null;
    PreparedStatement pstmt   = null;
    String            text    = 
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890" +
     "12345678901234567890123456789012345678901234567890";

    // Set autocommit off
    try {
      conn.setAutoCommit(false);
      conn.commit();
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
    }

    // One statement at a time, awfully slow!
    try {
      start = System.currentTimeMillis();
      stmt = conn.createStatement();
      for (int i=0;i < last;i++) {
        rows = stmt.executeUpdate(
         "insert into test_batch " +
         "( test_batch_id, text ) " + 
         "values " + 
         "( test_batch_id.nextval, '" + text + "' )");
      }
      end = System.currentTimeMillis();
      stmt.close();
      stmt = null;
      conn.commit();
      System.out.println(
       last + " inserts using statement:                   " + 
       (end - start) + " milliseconds");
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
    }
    finally {
      if (stmt != null) 
        try { stmt.close(); } catch (SQLException ignore) { }
    }

    // One prepared statement at a time, better!
    try {
      pstmt = conn.prepareStatement(
       "insert into test_batch " +
       "( test_batch_id, text ) " + 
       "values " + 
       "( test_batch_id.nextval, ? )");
      start = System.currentTimeMillis();
      for (int i=0;i < last;i++) {
        pstmt.setString( 1, text );
        rows = pstmt.executeUpdate();
      }
      end = System.currentTimeMillis();
      pstmt.close();
      pstmt = null;
      conn.commit();
      System.out.println(
       last + " inserts using prepared statement:          " + 
       (end - start) + " milliseconds");
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
    }
    finally {
      if (pstmt != null) 
        try { pstmt.close(); } catch (SQLException ignore) { }
    }

    // But now using Oracle batching: wow!!!
    try {
      pstmt = conn.prepareStatement(
       "insert into test_batch " +
       "( test_batch_id, text ) " + 
       "values " + 
       "( test_batch_id.nextval, ? )");
      if (last < 30) {
       ((OraclePreparedStatement)pstmt).setExecuteBatch(last);
      }
      else {
       ((OraclePreparedStatement)pstmt).setExecuteBatch(30);
      }
      start = System.currentTimeMillis();
      for (int i=0;i < last;i++) {
        pstmt.setString( 1, text );
        rows = pstmt.executeUpdate();
      }
      end = System.currentTimeMillis();
      pstmt.close();
      pstmt = null;
      conn.commit();
      System.out.println(
       last + " inserts using prepared statement batching: " + 
       (end - start) + " milliseconds");
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
    }
    finally {
      if (pstmt != null) 
        try { pstmt.close(); } catch (SQLException ignore) { }
    }

    // Clean up
    try {
      stmt = conn.createStatement();
      rows = stmt.executeUpdate("delete test_batch");
      conn.commit();
      stmt.close();
      stmt = null;
      System.out.println(rows + " rows deleted");
      System.out.println("");
    }
    catch (SQLException e) {
      System.err.println(e.getMessage());
    }
    finally {
      if (stmt != null) 
        try { stmt.close(); } catch (SQLException ignore) { }
    }
  }

  protected void finalize() 
   throws Throwable {
    if (conn != null) 
      try { conn.close(); } catch (SQLException ignore) { }
    super.finalize();
  }
}