print Printer View e-mail E-mail this page Bookmark Bookmark 
/*
 * @author  Elangovan

 * @version 1.0
 *
 * Development Environment        :  Oracle9i JDeveloper
 * Name of the Application        :  StmtCacheSample.java
 * Creation/Modification History  :
 *

 *    Elangovan       26-Feb-2002      Created
 *    Srikanth        24-Feb-2003      Certified on Linux
 *
 */

package oracle.otnsamples.oracle9ijdbc.stmtcache;


// JDBC imports
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.SQLException;

// Oracle's extensions
import oracle.jdbc.OracleConnection;

import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.OraclePreparedStatement;

// Java Utility Classes
import java.util.Properties;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.ResourceBundle;


import java.io.IOException;

/**
 *     This sample illustrates the benefit and use of two statement caching
 *  schemes in Oracle9i JDBC Drivers.
 *
 *     Statement caching prevents the overhead of repeated statement parsing and
 *  cursor creation by caching the statement state and meta data. There are
 *  two schemes available for statement caching
 *

 *     1) Implicit Statement Caching : This scheme automatically caches all
 *  Prepared and Callable statements. Each time a statement is created, the cache
 *  is searched with the SQL string and statement type( Prepared or Callable),
 *  if any match is found the statement is returned from the cache, else a new
 *  statement is created. When the user calls the close() method on the statement,
 *  it is logically closed and put in the cache(for further use). The whole statement
 *  caching process is completely transparent to the developer.
 *
 *    2) Explicit Statement Caching : In this scheme, a key(any Java String) is
 *  associated with each statement and this key is used to refer to the statement.
 *  When a Prepared or Callable statement is closed using stmt.closeWithKey(key),

 *  the statement is associated with this key and put in the cache. when the
 *  statement has to be created again, OracleConnection.getStatementWithKey(key)
 *  is used to retrieve the statement associated with key. Explicit caching has
 *  a performance edge over implicit caching since all the details of the statement
 *  are cached( statement data, state and meta data).
 *
 *       This sample illustrates the benefits and use of statement caching  with
 *  a "Employee Search Application". Consider an employee search application in
 *  which a user can search for employees based on First Name, Department and Salary.
 *  Since the skeleton of the statement remains the same except the parameters, it
 *  would be ideal to use statement caching( instead of preparing the statement each time).
 *

 *  To show the performance of the statement caching schemes, we run each search
 *  10 times.
 */
public class StmtCacheSample {

  // Handles the User Interface
  StmtCacheFrame   gui    = null;

  Connection       conn   = null;

  // Holds the time( in milli seconds) taken by each caching schemes
  public int[]    data   = null;


  // Department Id
  private String[] deptId = null;

  /**
   *  Constructor. Initializes the gui.
   */
  public StmtCacheSample() {

    gui  = new StmtCacheFrame(this);
    data = new int[3];

  }

  /**

   *   Main entry point for the class. Connects to the database and populates the
   *   Departments combo Box.
   */
  public static void main(String[] args) {

    StmtCacheSample sample = new StmtCacheSample();

    // Populate the Departments Combo Box
    sample.populateDept();

  }

  /**
   * This method reads a properties file which is passed as
   * the parameter to it and load it into a java Properties

   * object and returns it.
   */
  public static Properties loadParams( String file ) throws IOException {
    // Loads a ResourceBundle and creates Properties from it
    Properties prop = new Properties();
    ResourceBundle bundle = ResourceBundle.getBundle( file );
    Enumeration enum = bundle.getKeys();
    String key = null;
    while( enum.hasMoreElements() ) {
      key = (String)enum.nextElement();
      prop.put( key, bundle.getObject( key ) );
    }
    return prop;
  }

  /**

   * Creates a database connection object using DataSource object. Please
   * substitute the database connection parameters with appropriate values in
   * Connection.properties file
   */
  public void dbConnect() {

    try {
      gui.putStatus("Trying to connect to the Database");

      // Load the properties file to get the connection information
      Properties prop = StmtCacheSample.loadParams("Connection");

      // Create a OracleDataSource instance
      OracleDataSource ods = new OracleDataSource();

      // Sets the driver type
      ods.setDriverType("thin");


      // Sets the database server name
      ods.setServerName((String)prop.get("HostName"));

      // Sets the database name
      ods.setDatabaseName((String)prop.get("SID"));

      // Sets the port number
      ods.setPortNumber(new Integer((String)prop.get("Port")).intValue());

      // Sets the user name
      ods.setUser((String)prop.get("UserName"));

      // Sets the password
      ods.setPassword((String)prop.get("Password"));

      // Create a connection  object
      conn = (OracleConnection)ods.getConnection();


      gui.putStatus(" Connected to " + prop.get("SID") +
                    " Database as " + prop.get("UserName"));


    } catch(SQLException ex) { // Handle SQL errors
        System.out.println(
                     "Error in Connecting to the Database "+'\n'+ex.toString());
    } catch(IOException ex) { // Handle IO errors
        System.out.println(
                     "Error in reading the properties file "+'\n'+ex.toString());
    }
    if(conn == null) gui.goButton.setEnabled(false);

  }

  /**
   *   Dispatches the appropriate methods to handle the events generated
   *   in the gui. Those methods carry out the required JDBC operations.

   */
  public void dispatchEvent(String event) {

    // Search for employee
    if (event.equals("SEARCH")) {
      search();

    // Close the application
    } else if (event.equals("EXIT")) {
      exitApplication();
    }

  }

  /**
   *   This method searches the Employees table for any records matching user's criteria
   *   and catches performance metrics on statement caching schemes.
   */
  public void search() {


    int withoutCache = 0,expCache = 0,impCache = 0;

    // Search and display matching records in results JTable
    searchEmployee();

    // Run the search in all possible combinations to get performance metrics
    withoutCache  = searchWithoutCache();
    impCache      = searchWithImpCache();
    expCache      = searchWithExpCache();

    impCache     += searchWithImpCache();
    expCache     += searchWithExpCache();
    withoutCache += searchWithoutCache();

    expCache     += searchWithExpCache();
    withoutCache += searchWithoutCache();
    impCache     += searchWithImpCache();


    // Take the average of the 3 runs
    data[0] = withoutCache / 3;

    data[1] = impCache     / 3;
    data[2] = expCache     / 3;

  }


  /**
   *   Search employees and display matching records in Result's JTable.
   */
  public void searchEmployee() {

    // Connect to database
    this.dbConnect();

    OraclePreparedStatement opstmt = null;
    ResultSet               rset   = null;

    try {

      // Get search parameters
      String empName      = gui.searchText.getText();
      int    selectedDept = gui.cmbDepartment.getSelectedIndex();

      int    lowerLimit   = Integer.parseInt(gui.lowerLimit.getText());
      int    upperLimit   = Integer.parseInt(gui.upperLimit.getText());

      String mysql = new StringBuffer()
        .append(" SELECT Employee_Id,First_Name || ' ' || Last_Name,Salary ")
        .append(" FROM Employees ")
        .append(" WHERE UPPER(First_Name) like UPPER(?) AND ")
        .append(" Department_Id = ? AND ")
        .append(" SALARY BETWEEN ? AND ? ")
        .append(" ORDER BY First_Name,Last_Name ").toString();

      // Clear Result JTable
      gui.tableModel.clearTable();

      // Prepare the search query
      opstmt = (OraclePreparedStatement) conn.prepareStatement(mysql);

      // Set the parameters
      opstmt.setString(1, empName);
      opstmt.setInt(2, Integer.parseInt(deptId[selectedDept]));
      opstmt.setInt(3, lowerLimit);
      opstmt.setInt(4, upperLimit);


      // Execute the query and display results in JTable
      rset = opstmt.executeQuery();

      while (rset.next()) {
        gui.addToJTable(rset.getString(1), rset.getString(2),
                          rset.getString(3));
      }

      // Close the ResultSet and OraclePreparedStatement Objects
      rset.close();
      opstmt.close();

    } catch (SQLException sqlEx) {      // Handle SQL Errors
      gui.putStatus(" Error displaying results  : " + sqlEx.toString());
    }

    // Disconnect from database
    this.dbDisconnect();

  }


  /**
   *   Search the Employees table without using statement cache. The query is

   *   prepared and executed 10 times to get performance metrics.
   */
  public int searchWithoutCache() {

    // Connect to database
    this.dbConnect();

    // Start and End time of query (in milliseconds)
    long                    start  = 0, end = 0;

    OraclePreparedStatement opstmt = null;
    ResultSet               rset   = null;

    try {

      // Get search parameters
      String empName      = gui.searchText.getText();
      int    selectedDept = gui.cmbDepartment.getSelectedIndex();
      int    lowerLimit   = Integer.parseInt(gui.lowerLimit.getText());
      int    upperLimit   = Integer.parseInt(gui.upperLimit.getText());

      String mysql = new StringBuffer()
        .append(" SELECT Employee_Id,First_Name || ' ' || Last_Name,Salary ")
        .append(" FROM Employees ")
        .append(" WHERE UPPER(First_Name) like UPPER(?) AND ")

        .append(" Department_Id = ? AND ")
        .append(" SALARY BETWEEN ? AND ? ")
        .append(" ORDER BY First_Name,Last_Name ").toString();

      // Set start time
      start = System.currentTimeMillis();

      // Loop, prepare and execute the query 10 times
      for (int i = 1; i <= 10; i++) {

        opstmt = (OraclePreparedStatement) conn.prepareStatement(mysql);

        opstmt.setString(1, empName);
        opstmt.setInt(2, Integer.parseInt(deptId[selectedDept]));
        opstmt.setInt(3, lowerLimit);
        opstmt.setInt(4, upperLimit);

        // Execute query
        rset = opstmt.executeQuery();

        // Do not process ResultSet

        // Close the ResultSet and OraclePreparedStatement Objects
        rset.close();
        opstmt.close();


      }

      // Set the End time
      end = System.currentTimeMillis();

    } catch (SQLException sqlEx) {      // Handle SQL Errors
      gui.putStatus(" Error searching without cache  : " + sqlEx.toString());
    }

    // Disconnect from database
    this.dbDisconnect();

    // Return the time taken to prepare and execute query
    return ((int) (end - start));

  }

  /**
   *   Search the Employees table, using Implicit scheme to cache Prepared
   *   statements. The query is prepared and executed 10 times to get
   *   performance metrics.
   */
  public int searchWithImpCache() {

    // Connect to database
    this.dbConnect();


    // Start and End time of query (in milliseconds)
    long                    start  = 0, end = 0;

    OraclePreparedStatement opstmt = null;
    ResultSet               rset   = null;

    try {

      // Get search parameters
      String empName      = gui.searchText.getText();
      int    selectedDept = gui.cmbDepartment.getSelectedIndex();
      int    lowerLimit   = Integer.parseInt(gui.lowerLimit.getText());
      int    upperLimit   = Integer.parseInt(gui.upperLimit.getText());

      String mysql = new StringBuffer()
        .append(" SELECT Employee_Id,First_Name || ' ' || Last_Name,Salary ")
        .append(" FROM Employees ")
        .append(" WHERE UPPER(First_Name) like UPPER(?) AND ")
        .append(" Department_Id = ? AND ")
        .append(" SALARY BETWEEN ? AND ? ")
        .append(" ORDER BY First_Name,Last_Name ").toString();

      // Set the Statement cache size to 5
      ((OracleConnection) conn).setStatementCacheSize(5);

      // Enable Implicit caching
      ((OracleConnection) conn).setImplicitCachingEnabled(true);


      // Set start time
      start = System.currentTimeMillis();

      // Loop, prepare and execute the query 10 times
      for (int i = 1; i <= 10; i++) {

        // The statement is searched in the cache, if a match is found, returns
        // the statement from cache else creates a new statement
        opstmt = (OraclePreparedStatement) conn.prepareStatement(mysql);

        opstmt.setString(1, empName);
        opstmt.setInt(2, Integer.parseInt(deptId[selectedDept]));
        opstmt.setInt(3, lowerLimit);
        opstmt.setInt(4, upperLimit);

        // Execute query
        rset = opstmt.executeQuery();

        // Do not process ResultSet

        // Close the ResultSet
        rset.close();

        // The statement is not closed physically, but put in the cache.
        opstmt.close();

      }

      // Set the End time

      end = System.currentTimeMillis();

    } catch (SQLException sqlEx) {      // Handle SQL Errors
      gui.putStatus(" Error using implicit cache  : " + sqlEx.toString());
    }

    // Disconnect from database
    this.dbDisconnect();

    // Return the time taken to prepare and execute query
    return ((int) (end - start));

  }

  /**
   *   Search the Employees table, using Explicit scheme to cache Prepared
   *   statements. The query is prepared and executed 10 times to get
   *   performance metrics.
   */
  public int searchWithExpCache() {

    // Connect to database
    this.dbConnect();

    // Start and End time of query (in milliseconds)
    long                    start  = 0, end = 0;

    OraclePreparedStatement opstmt = null;
    ResultSet               rset   = null;


    try {

      // Get search parameters
      String empName = gui.searchText.getText();
      int selectedDept = gui.cmbDepartment.getSelectedIndex();
      int lowerLimit   = Integer.parseInt(gui.lowerLimit.getText());
      int upperLimit   = Integer.parseInt(gui.upperLimit.getText());

      String mysql = new StringBuffer()
        .append(" SELECT Employee_Id,First_Name || ' ' || Last_Name,Salary ")
        .append(" FROM Employees ")
        .append(" WHERE UPPER(First_Name) like UPPER(?) AND ")
        .append(" Department_Id = ? AND ")
        .append(" SALARY BETWEEN ? AND ? ")
        .append(" ORDER BY First_Name,Last_Name ").toString();

      // Set Statement Cache size to 5
      ((OracleConnection) conn).setStatementCacheSize(5);

      // Enable Explicit caching
      ((OracleConnection) conn).setExplicitCachingEnabled(true);

      // Set start time
      start = System.currentTimeMillis();

      // Loop, prepare and execute the query 10 times
      for (int i = 1; i <= 10; i++) {

        // Retrieve the statement from Explicit cache using key.
        opstmt = (OraclePreparedStatement)
            ((OracleConnection) conn).getStatementWithKey("searchEmployee");


        // If statement not in Explicit cache, create one.
        if (opstmt == null) {

          // This step is reached only when the function is called for the first time
          // from the next time the statement would be retrieved from cache.
          opstmt = (OraclePreparedStatement) conn.prepareStatement(mysql);
        }

        opstmt.setString(1, empName);
        opstmt.setInt(2, Integer.parseInt(deptId[selectedDept]));
        opstmt.setInt(3, lowerLimit);
        opstmt.setInt(4, upperLimit);

        // Execute query
        rset = opstmt.executeQuery();

        // Do not process results

        // Close the ResultSet
        rset.close();

        // Close the statement with a key(any Java String), this key would be used
        // to search for this statement in the explicit cache.
        opstmt.closeWithKey("searchEmployee");

      }

      // Set the End time
      end = System.currentTimeMillis();

    } catch (SQLException sqlEx) {      // Handle SQL Errors

      gui.putStatus(" Error using explicit cache  : " + sqlEx.toString());
    }

    // Disconnect from database
    this.dbDisconnect();

    // Return the time taken to prepare and execute query
    return ((int) (end - start));

  }


  /**
   *   Populates the Department Combo Box with Department names from Departments
   *   table.
   */
  public void populateDept() {

    // Connect to database
    this.dbConnect();

    if(conn == null) return;

    Statement stmt   = null;
    ResultSet rset   = null;
    ArrayList deptList = new ArrayList();

    try {

      // Create a Statement object
      stmt = conn.createStatement();

      // Retrieve the department id and name

      rset = stmt.executeQuery(
        " SELECT Department_Id,Department_Name FROM Departments ");

      // Add the department id to a string array and department name to Combo Box
      while (rset.next()) {
        deptList.add(rset.getString(1));
        gui.cmbDepartment.addItem(rset.getString(2));
      }

      // Close ResultSet and Statement objects
      rset.close();
      stmt.close();


      deptId = (String[])deptList.toArray(new String[deptList.size()]);

    } catch (SQLException sqlEx) {      // Handle SQL Errors
      gui.putStatus(" Error populating Department details  : " +
                                                        sqlEx.toString());
    }

    // Disconnect from database
    this.dbDisconnect();

  }

  /**
   *   Disconnect from database.
   */
  public void dbDisconnect() {

    try {

      // If connected, close the connection

      if (conn != null)   conn.close();

    } catch (SQLException sqlEx) {
      gui.putStatus(" Error while disconnecting : " + sqlEx.toString());
    }

  }

  /**
   *   Closes the application.
   */
  public void exitApplication() {

      // Dereference gui
      gui = null;

      // Close application
      System.exit(0);

  }

}
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy