/*
 * @author  Chandar
 * @version 1.0
 *
 * Development Environment        :  Oracle9i JDeveloper

 * Name of the Application        :  RowPrefetchSample.sqlj
 * Creation/Modification History  :
 *
 *     Chandar       15-Oct-2001      Created
 *     Venky         03-Mar-2003      Certified on Linux platform
 *

 * Overview of Application        :
 * This sample illustrates the row-prefetch performance extension of Oracle
 * JDBC drivers. Row-Prefetch allows the user to specify the number of rows
 * to be fetched from the result-set in each trip to the database when
 * executing a SQL query. By default the row prefetch value is 10 .
 *
 * The sample executes a query to retrieve products information from database.

 * The sample GUI gives user the option to set the number of rows to be fetched
 * in each database trip. When user clicks 'Get Rows' button, the time taken to
 * fetch the all rows is displayed.
 * Methods Description in sample :
 * dbConnect()            : This method establishes the connection to database.
 * setRowPreFetchValue() : This method shows how to set the row prefetch size
 *                          for a query.
 *

 */

package oracle.otnsamples.sqlj.rowprefetch;

// SQLJ related imports
import sqlj.runtime.ExecutionContext;
import oracle.sqlj.runtime.Oracle;

// Create a Named Iterator to hold the product information retrieved from

// database
#sql iterator ProdIter (int product_id,String product_name,
                         String product_description,Float min_price);


public class RowPrefetchSample {
   // Define variable of ProdIter iterator.
   private static ProdIter iter;

   // Define object of frame class for the application.

   private static RowPrefetchFrame gui;

/**
  * Constructor definition. This method sets up the GUI and initializes the
  * database connection.
  */
  public RowPrefetchSample() {
    try{
      // Instatiate the object of frame class.
      gui =new  RowPrefetchFrame();


      // display frame window.
      gui.show();
      gui.setStatus("Connecting to database...");

      // connect to database
      dbConnect();
      gui.setStatus("Connected");
      gui.getRows.setEnabled(true);

    }catch(Exception e){
      // catch and show the exception in status bar.
      System.out.println("Error connecting to database "+e);

      System.exit(1);
    }
  }

/**
  * This method establishes the connection with the database.
  */
  private void dbConnect() throws Exception{

    // connect to database using 'Connection.properties' file. This file
    // containsdatabase information like host name,sid, username and
    // password.
    Oracle.connect(RowPrefetchSample.class,"../../../../Connection.properties");


  }

/**
  * This method sets the value for number of row to be fetched from database in
  * a database trip. It also calculates the total time taken to fetch all the
  * rows.
  */
 public static void setRowPrefetchValue(int value){
   // create an execution context object.
   ExecutionContext ectx=new ExecutionContext();

   try{
     // set the fetch size to the value given by the user using execution context

     // object.
     ectx.setFetchSize(value);

     // execute the sqlj statement using above execution context to get product
     // information.
     #sql [ectx] iter= {SELECT product_id,product_name,product_description,
                       min_price FROM product_information};

     gui.setStatus("fetching rows from database...");

     // variable to hold number of rows fetched
     int rowsFetched=0;

     // get the fetch start time
     long start=System.currentTimeMillis();


     // Loop through the iterator.
     while(iter.next()){
       rowsFetched =rowsFetched + 1;
     }
     // get the fetch end time.
     long end=System.currentTimeMillis();

     // show number of rows fetched in status bar.
     gui.setStatus(rowsFetched +" rows selected");

     // show the time taken to fetch the rows in JLabel
     gui.timeLabel.setText("Time Taken to Fetch Rows(ms) :" +(end-start));
   }
   catch(Exception e){
     // catch and display the exception in status bar.

     gui.setStatus(" " +e);
   }
   finally{
     try{
       iter.close();
       ectx=null;
     }     catch(Exception e){
       gui.setStatus(""+e);
     }
   }
 }

/**
  * This method defines the entry point of the application.
  */
  public static void main(String[] args){


   // Instantiate the application
  RowPrefetchSample app= new RowPrefetchSample();
  }

/**
  * This method is used to exit from the application.
  */
  public static void exitApplication(){
    try{
      Oracle.close();
    }
    catch(Exception e){
      e.printStackTrace();
    }
    System.exit(0);
  }
}