/*
 * @author  Reghu
 * @version 1.2
 *
 * Development Environment        :  JDeveloper 2.0
 * Name of the Application        :  ColumnTypeSample.java
 * Creation/Modification History  :
 *
 *    Reghu       14-Jan-1999      Created
 *    Sujatha     14-May-2002      Certified on Oracle9i JDeveloper
 *    Stephen     10-Feb-2003      Certified on Linux
 *
 */
package oracle.otnsamples.jdbc.columntype;



import java.io.IOException;

// Package for JDBC Classes
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.Types;
import java.sql.ResultSet;
import oracle.jdbc.driver.OraclePreparedStatement; 
import oracle.jdbc.pool.OracleDataSource;

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

/**
 *
 * This sample illustrates the Column Type Specification performance extension
 * of Oracle JDBC drivers. Column Type Specification is a feature that improves
 * performance of selecting rows, by specifying the type of the column.
 * By specifying the column type a network roundtrip to obtain the column type
 * is avoided.
 *
 * This sample retrieves 10 rows from a table both by specifying and not
 * specifying the column type. Both types of accesses are timed.
 *
 * The GUI for this sample is handled in ColumnTypeFrame.java
 *
 */

public class ColumnTypeSample {
   // Database Connection Object
   Connection  connection = null;

   // GUI handler for this sample
   ColumnTypeFrame gui;

   int numFetchedRows ; // Number of rows fetched

  /**
   * Constructor, instantiates GUI.
   */
  public ColumnTypeSample() {
    gui = new ColumnTypeFrame(this); // Instantiate GUI
    gui.setVisible(true);
  }

  /**
   * Main entry point of the Class. Instantiates the root frame, then
   * establishes a Database connection and sets numTotalRows to the
   * total number of rows in PRODUCTS table
   */
  public static void main( String args[] ) {
    ColumnTypeSample colType = new ColumnTypeSample();// Instantiate root frame
    colType.dbConnection(); // Establishes the DB connection
    if( colType.connection != null ) {
      colType.gui.buttonStateChange(true);
    }   
  }


  /**
   * Dispatches the GUI events to the appropriate method, which performs
   * the required JDBC operations. This method is invoked when event occurs
   * in the GUI (like Button clicks). This method is invoked from
   * the setupListeners method and run() method of ColumnTypeFrame.java
   */
  public void dispatchEvent(String eventName) {
    // Dispatch Event
    if( eventName.equals("START SELECT") )
        selectRecords(gui.flag);
    if( eventName.equals("EXIT") )
        exitApplication();
  }

  /**
   * 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.
   */
  private 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
   */
  private void dbConnection() {
    try {
      gui.putStatus("Trying to connect to the Database");
 
      // Load the properties file to get the connection information
      Properties prop = this.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
      connection = ods.getConnection();
 
      // Sets the auto-commit property for the connection to be false.
      connection.setAutoCommit(false);
 
      gui.putStatus(" Connected to " + prop.get("SID") +
                    " Database as " + prop.get("UserName"));

 
    } catch(SQLException ex) { // Trap SQL errors
        gui.putStatus("Error in Connecting to the Database"+'\n'+ex.toString());
    } catch(IOException ex) { // Trap I/O errors
        gui.putStatus("Error in reading the properties file"+'\n'+ex.toString());
    }
  }

  /**
   * This method selects records from the database using column type specification
   * If specifyColumnType is true and without specifying column types if
   * specifyColumnType is false.
   */
  private void selectRecords( boolean specifyColumnType ) {
    long  timetaken; // Holds the execution time
    numFetchedRows = 0;

    PreparedStatement prepare =  null;

    // The SQL query for querying 10 rows from the table
    String query = "SELECT language_id FROM products WHERE ROWNUM < 11";
    try {
      gui.putStatus("Started selecting rows, Please wait..");

      // Create a PreparedStatement based on the query in query
      prepare =  connection.prepareStatement(query);

      // Casting the PreparedStatement to OraclePreparedStatement and
      // Specifying the Standard_rate Column type as FLOAT using
      // defineColumnType() method.
      if ( specifyColumnType )
        ((OraclePreparedStatement)prepare).defineColumnType(1,Types.VARCHAR);

      // Get time at start of query
      long initialTime = System.currentTimeMillis();

      // Execute the Query in query
      ResultSet rst = prepare.executeQuery();

      String temp = null;

      // Populate the ResultSet object
      while ( rst.next() ) {
        numFetchedRows++;
        temp = rst.getString(1);
       }

      // Get time at end of query
      long finalTime = System.currentTimeMillis();

      // Compute time taken
      timetaken = finalTime - initialTime;

      gui.displayResult( specifyColumnType, timetaken); //Display the results
      gui.putStatus( numFetchedRows + " Rows Selected" );
    } catch( SQLException ex ) { // Trap SQL Errors
      gui.putStatus(" Error in Selecting rows "+'\n'+ ex.toString());
    } finally {
      try {
        prepare.close(); // Close statement which also closes open result sets
      } catch(SQLException ex) {
        
      }
    }
  }


  /**
   * Closing the Database Connection Object and exit the application
   */
  private void exitApplication() {
    try {
      gui.putStatus("Closing the connection....please wait.....");
      if( connection != null )
        connection.close(); // Closing the connection object.
    } catch( SQLException ex ) {  // Trap SQL Errors
      gui.putStatus(ex.toString());
    }
    System.exit(0); // Exit the aplication
  }
}