/*
* @author  srangan.in
* @version 1.2
*
* Development Environment        :  Oracle 9i JDeveloper
* Name of the Application        :  RowidSample.java
* Creation/Modification History  :
*
*    srangan.in       30-Dec-1998     Created
*    neshastr         22-May-2002     Re-certified on Oracle9i JDeveloper
*    Stephen Raj      22-Jan-2003     Certified on Linux
 *                                    Added method checkTables
*                                     Optimised the code
*/

package oracle.otnsamples.jdbc.rowid;

import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

// Packages containing classes which are Oracle Extensions to JDBC
import oracle.sql.ROWID;
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.jdbc.driver.OracleResultSet;

import oracle.jdbc.pool.OracleDataSource;

/**
 *
 * This application illustrates accessing the ROWID of a database row  using
 * JDBC. ROWIDs identify the location of the row in the datafiles  and hence
 * are unique for each row in a database table, and are also the fastest way
 * to access database rows.
 *
 * This sample first creates the table OTN_AIRLINES in the database it then
 * retrieves all rows from the OTN_AIRLINES table, and displays the
 * airline code and rowid for each row in a JTable. The user can now select a
 * particular row, and retrieve the NAME and PARTNER fields in two ways:
 * 1. using the ROWID of the selected row and
 * 2. using the code for the selected ROW.
 *
 * Both these accesses are timed and displayed in the corresponding fields.
 *
 * The gui for this sample is handle by the class RowidFrame. (RowidFrame.java)
 *
 * NOTE: The time taken for retrieval using CODE and ROWID may not be consistent
 * and vary. The reason is the small number of rows. If the OTN_AIRLINES table
 * has large number of rows, then ROWID retrieval will take lesser time
 * consistently.
 *
 */

public class RowidSample {

  private Connection connection; // The db connection object
  private RowidFrame gui; // gui handler for this sample

  /**
   * Constructor. Instantiates gui.
   */
  public RowidSample() {
    gui = new RowidFrame(this); // Instantiate gui
    gui.setVisible(true);
  }

  /**
   * Main entry point for the class. Instantiates class, sets up database
   * connection, and calls getAllRows to retrieve all existing records in
   * the OTN_AIRLINES table for display in gui.
   */
  public static void main(String[] args) {
    RowidSample rowid = new RowidSample(); // Instantiate root frame
    rowid.dbConnection(); // Connect to DB
    if(rowid.connection != null) {
      rowid.checkTables(); // Check if the DB table OTN_AIRLINES is present
      rowid.getAllRows(); // Retrieve all records and populate gui
      rowid.gui.enableButtons(); // Enable the selection buttons
    }
  }

  /**
   * Dispatches the gui events to the appropriate method, which performs
   * the required JDBC operations. This method is invoked from the setupListeners
   * section of RowidFrame.java
   */
  public void dispatchEvent(String eventName) {

    // Get the row selected from the JTable in the gui
    int selectedRow = gui.table.getSelectedRow();

    // Dispatch Event
    if(eventName.equals("SELECT WITH ROWID")) {

      // Retrieve ROWID for the row selected in the gui table
      ROWID rowid = (ROWID)gui.tableModel.getValueAt(selectedRow, 2);
      selectWithRowid(rowid);
    } else {
      if(eventName.equals("SELECT WITHOUT ROWID")) {

        // Retrieve CODE for the row selected in the gui table
        String code = (String)gui.tableModel.getValueAt(selectedRow, 0);
        selectWithoutRowid(code);
      } else {
        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 boolean 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"));
      connection=ods.getConnection();
      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());
    }

    // Return false if failed to obtain connection object
    if( connection != null )
        return true;

    return false;
  }

  /**
   * Retrieve the CODE and ROWID for all rows in the OTN_AIRLINES table,
   * and populate JTable with the rows
   */
  void getAllRows() {
    gui.tableModel.clearTable(); // Clear the JTable
    try {

      // Create a statement to retrieve the rows from a JTable
      Statement stmt = connection.createStatement();

      // Obtain the result-set for the query below
      ResultSet rset = stmt.executeQuery("select code,rowid,rowid from otn_airlines");
      while(rset.next()) { // Loop through the result-set

        // Retrieve the column values from the result-set record
        String code = rset.getString(1);

        // ROWIDs can be processed as a String and as a ROWID object.
        // This call retrieves the ROWID as a String. The String value
        // is used to display the ROWID value in the JTable
        String rowid = rset.getString(2);

        // Obtain the ROWID as a ROWID objects(which is an Oracle Extension
        // datatype. Since ROWID is an Oracle extension, the getROWID method
        // is not available in the ResultSet class. Hence ResultSet has to be
        // cast to OracleResultSet
        ROWID rowid1 = ((OracleResultSet)rset).getROWID(3);
        gui.addToJTable(code, rowid, rowid1);
      }
      stmt.close(); // Close the statement, which implicitly closes the resultset
    } catch(SQLException ex) { // Trap SQL errors
      gui.putStatus("Error Selecting Rows: ");
      gui.appendStatus(ex.toString());
    }
  }

  /**
   * Retrieve the NAME and PARTNER values for the selected row using ROWID
   */
  void selectWithRowid(ROWID rowid) {
    String name = null, partner = null;

    // Note time before start of retrieval
    java.util.Date beforeDate = new java.util.Date();
    long before = beforeDate.getTime();
    try {

      // Create a PreparedStatement, to form the query
      PreparedStatement pstmt = connection.prepareStatement("SELECT name, partner FROM otn_airlines WHERE rowid = ?");

      // Bind rowid into prepared statement. Since ROWID type is an Oracle
      // Extension, it can only be bound to an OraclePreparedStatement. Hence
      // pstmt is being cast to OraclePreparedStatement
      ((OraclePreparedStatement)pstmt).setROWID(1, rowid);
      ResultSet rset = pstmt.executeQuery(); // Execute the prepared query

      if (rset.next()) {

        // Retrieve the columns from the result-set
        name    = rset.getString(1);    // Get name
        partner = rset.getString(2);    // Get partner
      }
      pstmt.close(); // Close the prepared statement, which implicitly closes resultset

      // Get time at completion
      java.util.Date afterDate = new java.util.Date();
      long after = afterDate.getTime();

      // Compute Time taken and copy to textfield
      Integer time = new Integer((int)(after - before));

      // Display time taken and also the name/partner values in gui
      gui.printRowidResults(time, name, partner);

      } catch(SQLException ex) { // Trap SQL errors
      gui.putStatus("Error retrieving row using rowid:");
      gui.appendStatus(ex.toString());
    }
  }

  /**
   * Retrieve the NAME and PARTNER values for the selected row using the
   * airline code
   */
  void selectWithoutRowid(String code) {
    String name = null, partner = null;

    // Note time before start of retrieval
    java.util.Date beforeDate = new java.util.Date();
    long before = beforeDate.getTime();
    try {

      // Create a PreparedStatement instance for the query
      PreparedStatement pstmt = connection.prepareStatement("SELECT name, partner FROM otn_airlines WHERE code = ?");

      // Bind code into prepared statement.
      pstmt.setString(1, code);
      ResultSet rset = pstmt.executeQuery(); // Execute the prepared query

      if (rset.next()) {

        // Retrieve the columns from the result-set
        name    = rset.getString(1); // Get name
        partner = rset.getString(2); // Get partner
      }
      pstmt.close(); // Close the prepared statement, which implicitly closes resultset

      //Get time at completion
      java.util.Date afterDate = new java.util.Date();
      long after = afterDate.getTime();

      // Compute Time taken and copy to textfield
      Integer time = new Integer((int)(after - before));

      // Display time taken and also the name/partner values in gui
      gui.printNoRowidResults(time, name, partner);

    } catch(SQLException ex) { // Trap SQL errors
      gui.putStatus("Error retrieving row without using rowid:");
      gui.appendStatus(ex.toString());
    }
  }

  /**
   * Close connection and exit application
   */
  private void exitApplication() {
    try {
      if(connection != null) {
        connection.close();
      }
    } catch(SQLException ex) { // Trap SQL errors
      gui.putStatus("Error closing connection");
      gui.appendStatus(ex.toString());
    }
    System.exit(0);
  }

  /**
   * Checks if the table ('OTN_AIRLINES') is present, else creates it.
   * Look into PopulateTable.java for more details
   */
  private void checkTables()   {
    Statement stmt = null;
    ResultSet rset = null;
    try     {
      stmt = connection.createStatement();

      // check from User_tables data dictionary table if the table is existing.
      rset = stmt.executeQuery(" SELECT Table_Name FROM User_Tables "+
                               " WHERE Table_Name = 'OTN_AIRLINES' ");

      // if the resultset of the above query does not have any record, it means
      // table is not existing. So the table is created.
      if (!rset.next()) {
        // call the class to create the table
        PopulateTable popTable = new PopulateTable(connection,gui);
        popTable.createSchemaTable();
        gui.appendStatus("OTN_AIRLINES Table created.");
      }
    } catch (SQLException sqlEx) {
       gui.putStatus("Could not create table OTN_AIRLINES : "+sqlEx.toString());
    } finally  {
        try {
          if( rset != null ) rset.close( );
          if( stmt != null ) stmt.close( );
        } catch(SQLException ex) { }
    }
  }
}