/*
   * @author  Rajnish
   * @version 1.0
   *
   * Development Environment        :  Oracle9i JDeveloper
   * Name of the Application        :  ProgUpdateSample.java
   * Creation/Modification History  :
   *
   * rksingh.in       7-Dec-1999      Created
   * Reghu            15-May-2002     Certified on Oracle9i JDeveloper.
   * Savitha          06-Feb-2003     Certified on Linux.
   *                                  To update the newly inserted rows,  
   *                                  the table has to be requeried.
   *                                  To implement this, insertRecords() method 
   * 
                                 is modified to call dispatch event 
   *                                  'SELECT' again to recreate the 
   *                                  updatableResultSet.
   */
  package oracle.otnsamples.jdbc.progupdate;

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

  // Java SQL classes
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;

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

  // Oracle JDBC class
  import oracle.jdbc.pool.OracleDataSource;


  /**
   * Programmatic updates provide an ability to make database updates using the
   * JDBC 2.0 APIs instead of using SQL statements. This application illustrates
   * how to insert, update and delete records from the otn_hotels table using
   * JDBC 2.0 APIs. The method 'selectRecords()' will obtain an updatable result
   * set. An updatable result set is the ResultSet object which can be
   * updated(have its rows modified, inserted, or deleted) programmatically,
   * when its concurrency  type is CONCUR_UPDATABLE. Using this updatable result
   * set, methods like 'insertRecords(..)', 'updateRecords(..)' and
   * 'deleteRecords()' do the other operations on the  database. The gui for
   * this sample is handled in ProgUpdateFrame.java The sample uses the
   * JDBC-Thin driver.
   */
  public class ProgUpdateSample {
    // gui handler for this sample.
    private ProgUpdateFrame gui;

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

    // PreparedStatement for getting a updatable resultSet.
    private PreparedStatement stmt = null;

    // The updatable resultSet.
    private ResultSet updatableRst = null;

    /**
     * Constructor , instantiates gui.
     */
    public ProgUpdateSample(  ) {
      gui = new ProgUpdateFrame( this ); // Instantiate gui.
      gui.setVisible( true );
    }

    /**
     * Main entry point for the class. Instantiates the ProgUpdateSample class,
     * sets up the database connection and selectRecords() method is called to
     * display the existing records in OTN_HOTELS table.
     */

    public static void main( String[] args ) {
      // Instantiate ProgUpdateSample class.
      ProgUpdateSample progUpdate = new ProgUpdateSample(  );

      // Set up the connection and assign the value to connection.
      progUpdate.dbConnection(  );

      if ( progUpdate.connection != null ) {
        // Populates the table with the records existing in the OTN_HOTELS table.
        progUpdate.selectRecords( "%", "%", "%", "%", "%", "%", "%" );
      }
    }

    /**
     * Dispatches the gui events to the appropriate method, which performs the
     * required JDBC and other operations. This method is invoked when event
     * occurs in the gui (like table Selection, Button clicks etc.). This method
     * is invoked from the setupListeners method of ProgUpdateFrame.java
     */
    public void dispatchEvent( String eventName ) {
      // Get the user input values for id, name, address, phone, fax, url, 
      // airport details of the hotel.
      String name = gui.nameTextField.getText(  );
      String address = gui.addressTextField.getText(  );
      String id = gui.idTextField.getText(  );
      String phone = gui.phoneTextField.getText(  );
      String fax = gui.faxTextField.getText(  );
      String hotelUrl = gui.hotelUrlTextField.getText(  );
      String airport = gui.airportTextField.getText(  );

      // Various dispatching Events.
      if ( eventName.equals( "SELECT" ) ) { 
        // if the user presses SELECT button.
        this.selectRecords( name, address, id, phone, fax, hotelUrl, airport );
      }

      else if ( eventName.equals( "UPDATE" ) ) { 
        // if the user presses UPDATE button.
        // check if mandatory column values are null.
        if ( id.equals( "" ) || name.equals( "" ) || address.equals( "" ) ||
                   phone.equals( "" ) ) {
          gui.putStatus( " id, name, address, phone cannot be null" );
        }
        else 
{
          this.updateRecord( name, address, id, phone, fax, hotelUrl, airport );
        }
      }

      else if ( eventName.equals( "INSERT" ) ) { 
        // if the user presses INSERT button.
        // check if mandatory column values are null.    
        if ( id.equals( "" ) || name.equals( "" ) || address.equals( "" ) ||
                   phone.equals( "" ) ) {
          gui.putStatus( " id, name, address, phone cannot be null" );
        }
        else 

{
          this.insertRecord( name, address, id, phone, fax, hotelUrl, airport );
        }
      }

      else if ( eventName.equals( "DELETE" ) ) { 
        // if the user presses DELETE button.
        this.deleteRecord(  );
      }

      else if ( eventName.equals( "EXIT" ) ) { 
        // if the user presses EXIT button.
        this.exitApplication(  );
      }
    }

    /**
     * This method reads the properties file: Connection.properties which is
     * passed as a parameter. The properties are loaded into java Properties
     * object and the same is returned.
     */
    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 database 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(  ) );
      }
    }

    /**
     * Method to select records from the otn_hotels table. Here, updatableRst is
     * assigned a set of rows that satisfies the select query. This ResultSet
     * will be used to handle insert, update and delete operations using
     * programmatic  update APIs.
     */

    private void selectRecords( String name, String address, String id,
                                  String phone, String fax, String hotelUrl,
                                  String airport ) {
      // The SQL query for fetching rows from the otn_hotels table.
      String query = "SELECT id,name,address,phone,fax," +
                       "hotel_url,hotel_airport FROM otn_hotels h " +
                       "WHERE UPPER(name) like ? AND UPPER(address) " +
                       "LIKE ? AND id LIKE ? " +
                       "AND phone LIKE ? AND 
UPPER(NVL(fax, '%')) LIKE ? " +
                       "AND UPPER(NVL(hotel_url, '%')) LIKE ? " +
                       "AND UPPER(NVL(hotel_airport, '%')) LIKE ?";

      try {
        // Create a PreparedStatement using the query.
        // To get an updatable and sensitive resultSet here,
        // appropriate arguments have been used.
        stmt = connection.prepareStatement( query,
                                              ResultSet.TYPE_SCROLL_SENSITIVE,
                                              ResultSet.CONCUR_UPDATABLE );

        // If no query condition has been entered, change the bind variables
        // to select all records. Query is made case insensitive by converting 
        // String values to UPPER CASE.
        if ( name.equals( "" ) ) {
          name = "%";
        }
        else {
          name = "%" + name.toUpperCase(  ) + "%";
        }

        if ( address.equals( "" ) ) {
          address = "%";
        }
        else {
          address = "%" + address.toUpperCase(  ) + "%";
        }

        if ( id.equals( "" ) ) {
          id = "%";
        }
        else {
          id = "%" + id + "%";
        }

        if ( phone.equals( "" ) ) {
          phone = "%";
        }
        else {
          phone = "%" + phone + "%";
        }

        if ( fax.equals( "" ) ) {
          fax = "%";
        }
        else {
          fax = "%" + fax.toUpperCase(  ) + "%";
        }

        if ( hotelUrl.equals( "" ) ) {
          hotelUrl = "%";
        }
        else {
          hotelUrl = "%" + hotelUrl.toUpperCase(  ) + "%";
        }

        if ( airport.equals( "" ) ) {
          airport = "%";
        }
        else {
          airport = "%" + airport.toUpperCase(  ) + "%";
        }

        // Bind the PreparedStatement with corresponding values.
        stmt.setString( 1, name );
        stmt.setString( 2, address );
        stmt.setString( 3, id );
        stmt.setString( 4, phone );
        stmt.setString( 5, fax );
        stmt.setString( 6, hotelUrl );
        stmt.setString( 7, airport );

        // Execute the PreparedStatement to get the updatable result set.
        updatableRst = stmt.executeQuery(  );


        gui.tmodel.clearTable(  ); // Clear JTable in gui.

        while ( updatableRst.next(  ) ) { // Point result set to next row.

          // Retrieve column values for this row.
          String localname = updatableRst.getString( "name" );
          String localaddress = updatableRst.getString( "address" );
          String localid = String.valueOf( updatableRst.getInt( "id" ) );
          String localphone = updatableRst.getString( "phone" );
          String localfax = updatableRst.getString( "fax" );
          String localhotelUrl = updatableRst.getString( "hotel_url" );
          String localairport = updatableRst.getString( "hotel_airport" );

          // Update Jtable.
          gui.addToJTable( localname, localaddress, localid, localphone,
                             localfax, localhotelUrl, localairport );
        }

        gui.putStatus( gui.tmodel.getRowCount(  ) +
                         " Records selected from database." );
      }
       catch ( SQLException ex ) { // Trap SQL errors.
         gui.putStatus( "Error in selecting from the database " +
                         ex.toString(  ) );
      }
    }

    /**
     * This method demonstrates how to insert a new row into the database using
     * programmatic updates. To make this possible, the JDBC 2.0 API defines the
     * concept of an insert row. This is a special row, associated with the
     * result set but not part of it. It serves as a staging area for building
     * the row that is to be inserted. To access the insert row, we have to call
     * the ResultSet method moveToInsertRow(), which positions the cursor on the
     * insert row. Then appropriate updateXXX()  methods are called to add
     * column values to the insert row. Method insertRow() will insert that row 
     * in the database. Any inserted rows will not be visible in the ResultSet.
     * To view the newly inserted row, the table has to be requeried.
     */
    private void insertRecord( String name, String address, String id,
                                 String phone, String fax, String hotelUrl,
                                 String airport ) {
      try {
        updatableRst.moveToInsertRow(  ); // Move the cursor to the insert row.

        try {
          updatableRst.updateInt( "id", Integer.parseInt( id ) ); 
          updatableRst.updateString( "name", name ); 
          updatableRst.updateString( "address", address ); 
          updatableRst.updateString( "phone", phone ); 
          updatableRst.updateString( "fax", fax ); 
          updatableRst.updateString( "hotel_url", hotelUrl ); 
          updatableRst.updateString( "hotel_airport", airport ); 

          // Insert the contents of the insert row into the result set and 
          // database table.
          updatableRst.insertRow(  );

          // Clear all fields and call 'SELECT' dispatch event so that the  
          // resultset is repopulated.
          gui.clearfields(  );
          dispatchEvent( "SELECT" );

          // Update gui to reflect new record.
          int rowIndex = gui.hotelTable.getSelectedRow(  );
          gui.putStatus( "The Record is inserted into the database" );
        }
         catch ( NumberFormatException nfEx ) {
           gui.putStatus( "id has to be a number" );
        }
      }
       catch ( Exception ex ) {
         // NullPointerException will be thrown if id is null.
         // SQLException 
will be thrown when any of the fields(id, name, address)
         // are null because these variables have to follow NOT NULL constraint
         // in the database table.
         gui.putStatus( "Error while inserting record in the database. "+
                        "Error message is: \n" + ex.toString(  ) );
      }
    }

    /**
     * Method to update the selected row with new values using programmatic
     * updates. Here, we have to first position the cursor at the row to be
     * updated, then update the columns one by one using updatexxx(..) method.
     * Finally a call to updateRow() will make the updation to be reflected in 
     * the updatableRst.
     */

    private void updateRecord( String name, String address, String id,
                                 String phone, String fax, String hotelUrl,
                                 String airport ) {
      try {
        // Get the index of row to be updated from the JTable.
        int rowIndex = gui.hotelTable.getSelectedRow(  );

        // Position the cursor of Updatable ResultSet to the row to be updated.
        updatableRst.absolute( rowIndex + 1 );

        // Update the different columns.
        updatableRst.updateInt( "id", Integer.parseInt( id ) ); // set Hotel ID
        updatableRst.updateString( "name", name ); // Sets Hotel name
        updatableRst.updateString( "address", address ); // Sets Hotel address
        updatableRst.updateString( "phone", phone ); // Sets Phone Number
        updatableRst.updateString( 
"fax", fax ); // Sets Fax Number
        updatableRst.updateString( "hotel_url", hotelUrl ); // Sets Hotel URL
        updatableRst.updateString( "hotel_airport", airport ); // Sets Airport

        // Update the ResultSet and the underlying database table.
        updatableRst.updateRow(  );

        // Update the gui to reflect the changed record.
        gui.updateJTable( name, address, id, phone, fax, hotelUrl, airport );

        gui.putStatus( "Record is successfully updated " );
      }
       catch ( Exception ex ) {
         // NullPointerException will be thrown if id is null.
         // SQLException will be thrown when any of the fields(id, name, address)
         // are null because these variables have to follow NOT NULL constraint
         // in the database table.
         gui.putStatus( "Error while inserting in the database : " +
                         ex.toString(  ) );
      }
    }

    /**
     * Method to delete the selected row using the programmatic updates Here,
     * first we have to point the cursor at the row to be deleted, and then call
     * the deleteRow method available in ResultSet.
     */
    private void deleteRecord(  ) {
      try {
        // Get the index of row to be deleted from the JTable.
        int rowIndex = gui.hotelTable.getSelectedRow(  );

        // Set the cursor of the updatable ResultSet to the row to be deleted.
        updatableRst.absolute( rowIndex + 1 );

        // Delete the current row from the result set and the underlying 
        // database table row.
        updatableRst.deleteRow(  );

        // Delete the row from the JTable.
        gui.deleteFromJTable(  );

        // Display the message.
        gui.putStatus( "Record is successfully deleted" );
      }
       catch ( Exception ex ) { // Trap the exceptions.
         gui.putStatus( "Error in deleting the row " + '\n' + 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 PreparedStatement
          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
    }
  }