/*
   * @author  vkumar
   * @version 1.0
   *
   * Development Environment        :  Oracle9i JDeveloper
   * Name of the Application        :  ScrollableResSetSample.java
   * Creation/Modification History  :
   *
   * vkumar.in       09-Dec-1999      Created
   * Reghu           15-May-2002      Certified on Oracle9i JDeveloper.
   * Savitha         03-Feb-2003      Certified on Linux.
   *
   */

  package oracle.otnsamples.jdbc.scrollrset;

  // Java IO APIs
  import java.io.IOException;

  // Java SQL Classes
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;

  // JDBC class
  import java.sql.Statement;

  // Java utility classes
  import java.util.Enumeration;
  import java.util.Properties;
  import java.util.ResourceBundle;

  // To get a database connection
  import oracle.jdbc.pool.OracleDataSource;



  /**
   * Scrollable ResultSet provides the ability to move the cursor forward and
   * backward to a specified position or to a position relative to the current
   * position. This sample shows how JDBC 2.0 API can be used to browse the
   * result sets. The sample uses the JDBC-Thin driver. JDBC-OCI driver can be
   * used after modifying the database URL in the dbConnection method.
   */
  public class ScrollableResSetSample {
    // GUI handler for this sample.
    private ScrollableResSetFrame gui;

    // Database Connection Object.
    private Connection connection = null;

    // Scrollable ResultSet Object
    private ResultSet resultSet = null;

    // Statement for getting a scrollable ResultSet.
    private Statement stmt = null;
    private int currentPosition = -1; //current position of cursor in ResultSet.
    private int resSetsize;

    /**
     * Constructor.  Instantiates gui.
     */
    public ScrollableResSetSample(  ) {
      gui = new ScrollableResSetFrame( this );
      gui.setVisible( true );
    }

    /**
     * Main entry point for the class. Instantiates root class and  sets up the
     * database connection.
     */

    public static void main( String[] argv ) {
      // Instantiate application class.
      ScrollableResSetSample srollSet = new ScrollableResSetSample(  );

      // Setup the database connection.
      srollSet.dbConnection(  );

      if ( srollSet.connection != null ) {
        // fetch hotels information from the database.
        srollSet.fetchHotelInfo(  );

        // Populate JTable with Hotel Records.
        srollSet.populateHotels(  );
      }
    }

    /**
     * Dispatches the gui events to the appropriate method, which perform the
     * required JDBC operations. This method is invoked when events occur in the
     * gui (like table Selection, Button clicks etc.).
     */
    public void dispatchEvent( String eventName ) {
      // Dispatch SELECTION event.
      if ( eventName.equals( "SELECT" ) ) {
        this.setToSelectedRow(  );
      }
      else if ( eventName.equals( "NEXT" ) ) { // Dispatch NEXT event.

        if ( currentPosition == -1 ) {
          gui.putStatus("Please select any row before using 'Show Next' button");

          return;
        }

        // check for the END of the resultset.
        if ( resSetsize == currentPosition ) {
          gui.putStatus( "Currently you are at the end of the ResultSet." +
                           "  Press 'Show Previous' button " );


          return;
        }

        // Set the ResutlSet cursor to next row and update the Hotel Info table
        this.moveToNextRow(  );
      }
      else if ( eventName.equals( "PREVIOUS" ) ) { // Dispatch PREVIOUS event

        if ( currentPosition == -1 ) {
          gui.putStatus( "Please select any row before using 'Show Previous'"+
                         " button." );

          return;
        }
        else if ( currentPosition == 1 ) { 
          // check for the beginning of the result set.
          gui.putStatus( "Currently you are at the beginning of the result Set."
                        +"  Press 'Show Next' button " );

          return;
        }

        // Set the ResutlSet cursor to previous row and update the Hotel Info
        // table.
        this.moveToPreviousRow(  );
      }
      else if ( eventName.equals( "EXIT" ) ) { // Dispatch EXIT event.
        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(  );

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

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

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

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

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

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


        // Create a connection  object
        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 IO errors
        
gui.putStatus( "Error in reading the properties file " + '\n' +
                         ex.toString(  ) );
      }
    }

    /**
     * This method fetches all hotel records into a global ReasultSet.
     */
    private void fetchHotelInfo(  ) {
      try {
        gui.putStatus( "Populating ResultSet." );

        // create a statement object.
        Statement stm1 = connection.createStatement(  );

        // Get the number of hotels.
        ResultSet res = stm1.executeQuery( "SELECT COUNT(*) FROM otn_hotels" );

        if ( res.next(  ) ) {
          resSetsize = res.getInt( 1 );
        }

        stm1.close(  ); // close the statement object.

        // create a statement object which creates a Scrollable resultset.
        stmt = connection.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,
                                             ResultSet.CONCUR_READ_ONLY );

        // Database Query.
        String query = "SELECT id,name,address,phone,fax,hotel_url,"+
                       "hotel_airport FROM otn_hotels";

        resultSet = stmt.executeQuery( query ); // Executes the Query.
      }
       catch ( SQLException ex ) { // Trap SQL Errors.
        gui.putStatus( "Error " + ex.toString(  ) );
      }
    }


    /**
     * This method populates the Hotels JTable with the records ('Hotel Id',
     * 'Hotel Name' and 'Hotel Address').
     */
    private void populateHotels(  ) {
      if ( resultSet != null ) {
        gui.putStatus( "Populating Hotels Please wait ..." );

        try {
          while ( resultSet.next(  ) ) {
            // Get the values from the ResultSet.
            String hotelId = resultSet.getString( 1 );
            String hotelName = resultSet.getString( 2 );
            String hotelAddress = resultSet.getString( 3 );

            // Display the new row into Hotel table.
            gui.insertHotelRow( hotelId, hotelName, hotelAddress );
          }
        }
         catch ( SQLException ex ) { // Trap SQL Errors.
          gui.putStatus( "Error " + ex.toString(  ) );
        }

        gui.putStatus( "Please select a hotel from the above table." );
      }
      else {
        gui.putStatus( "ResultSet is null. Error in fetching records from "+
                       "database" );
      }
    }

    /**
     * This method is called when the user selects a row from the Hotels Jtable.
     * If the selection is for the  first time it uses 'ResultSet.absolute()' to
     * change the cursor position in the resultset to the first row. Otherwise
     * it uses 'ResultSet.relative()' to change the cursor position in the
     * resultset to the selected row.
     */
    private void setToSelectedRow(  ) {
      try {
        int selectedRow = gui.selectedRow;

        // If the selection is for the first time.
        if ( currentPosition == -1 ) {
          currentPosition = selectedRow + 1;

          // Using absolute method to change the current row in the resultset to
          // the selected row.
          resultSet.absolute( currentPosition );
        }
        else {
          int relativeIndex = ( selectedRow + 1 ) - currentPosition;
          currentPosition = selectedRow + 1;

          // Using relative method to change the current row in the resultset
          // to the selected row.
          resultSet.relative( relativeIndex );
        }

        // Get the values from the ResultSet.
        String phoneNumber = resultSet.getString( 4 );
        String faxNumber = resultSet.getString( 5 );
        String url = resultSet.getString( 6 );
        String hotelAirport = resultSet.getString( 7 );

        // Display the new row into Hotel Info JTable.
        gui.insertHotelInfoRow( phoneNumber, faxNumber, url, hotelAirport );

        // Update the ResultSet current position text field.
        gui.setCurrentPosition( currentPosition );


        gui.putStatus( "Corresponding Hotel information is displayed" );
      }
       catch ( SQLException ex ) { // Trap SQL Errors.
        gui.putStatus( "Error " + ex.toString(  ) );
      }
    }

    /**
     * This method is called when the user presses 'Show Next'  button.
     * Resultset.next() method has been used to change  the cursor position from
     * result set's current row to the next row.
     */
    private void moveToNextRow(  ) {
      try {
        // Pointing to the next row using ResultSet.next()
        currentPosition++;
        resultSet.next(  );

        // Get the values from the ResultSet.
        String phoneNumber = resultSet.getString( 4 );
        String faxNumber = resultSet.getString( 5 );
        String url = resultSet.getString( 6 );
        String hotelAirport = resultSet.getString( 7 );

        // Display the new row into Hotel Info table.
        gui.insertHotelInfoRow( phoneNumber, faxNumber, url, hotelAirport );

        // Update the ResultSet current position text field.
        gui.setCurrentPosition( currentPosition );

        // Change the selection in Hotels JTable.
        gui.selectRow( currentPosition - 1 );

        gui.putStatus( "Corresponding Hotel information is displayed" );
      }
       catch ( SQLException ex ) { // Trap SQL Errors
        gui.putStatus( "Error " + ex.toString(  ) );
      }
    }

    /**
     * This method is called when the user presses 'Show Previous'  button.
     * Resultset.previous() method has been used to change  cursor position from
     * result set's current row to the previous row.
     */
    private void moveToPreviousRow(  ) {
      try {
        // Pointing to the previous row using ResultSet.previous()
        currentPosition--;
        resultSet.previous(  );

        // Get the values from the ResultSet.
        String phoneNumber = resultSet.getString( 4 );
        String faxNumber = resultSet.getString( 5 );
        String url = resultSet.getString( 6 );
        String hotelAirport = resultSet.getString( 7 );

        // Display the new row into Hotel Info table.
        gui.insertHotelInfoRow( phoneNumber, faxNumber, url, hotelAirport );

        // Update the ResultSet current position text field.
        gui.setCurrentPosition( currentPosition );

        // Change the selection in Hotels Jtable.
        gui.selectRow( currentPosition - 1 );


        gui.putStatus( "Corresponding Hotel information is displayed" );
      }
       catch ( SQLException ex ) { // Trap SQL Errors.
        gui.putStatus( "Error " + ex.toString(  ) );
      }
    }

    /**
     * Close the database Connection and resultSet and then exit the 
     * application.
     */
    private void exitApplication(  ) {
      try {
        gui.putStatus( "Closing the connection....please wait....." );

        if ( stmt != null ) { // Close the Statement.
          stmt.close(  );
        }

        if ( connection != null ) {
          connection.close(  ); // Close the connection object.
        }
      }
       catch ( SQLException ex ) { // Trap SQL Errors
        gui.putStatus( "Error " + ex.toString(  ) );
      }

      System.exit( 0 ); // Exit the application.
    }
  }