/*
   * @author  Stephen Raj
   * @version 1.0
   *
   * Development Environment        :  Oracle9i JDeveloper
   * Name of the Application        :  BatchUpdateSample.java
   * Creation/Modification History  :
   *
   * sarokias.in       10-Dec-1999    Created
   * Reghu             15-May-2002    Modified
   * Savitha           31-Jan-2003    Certified on Linux
   *
   */
  package oracle.otnsamples.jdbc.batchupdate;


  // Java IO Exception class
  import java.io.IOException;

  // Java SQL Date class
  import java.sql.Date;

  // JDBC related classes
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.sql.BatchUpdateException;
  import java.sql.Connection;

  // Exception classes
  import java.text.ParseException;

  // Java date formatting class
  import java.text.SimpleDateFormat;

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

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



  /**
   * A batch update is a set of multiple update statements that is submitted to
   * the database for processing as a batch. Sending multiple update statements
   * to the database together as a unit, in some situations, can be much more
   * efficient than sending each update statement separately. This ability to
   * send updates as a unit, referred to as the batch update facility, is one of
   * the features provided with the JDBC 2.0 API. In this application the method
   * 'addBatch()' is used to create a batch of  updates and finally the method
   * 'executeBatch()' is used to send the updates to the Database. When the
   * application invoked, it displays all the hotels from otn_hotels  table. The
   * user can choose any hotel and press either 'Reserve Room' for  reserving
   * room in the Hotel or press 'Cancel Room' for cancelling the Room.
   */
  public class BatchUpdateSample {
    // GUI handler for this sample.
    private BatchUpdateFrame gui;

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

    /**
     * This is a constructor to: Instantiate gui, make a database connection,
     * populate JTable with records of otn_hotels, and position the UI at the
     * center of the screen.
     */
    public BatchUpdateSample(  ) {
      gui = new BatchUpdateFrame( this ); // Instantiate gui.
      gui.setVisible( true );
    }

    /**
     * This is the main entry point for the class. It instantiates the
     * BatchUpdateSample class.
     */
    public static void main( String[] args ) {
      // Instantiate BatchUpdateSample class.
      BatchUpdateSample batch = new BatchUpdateSample(  );
      batch.dbConnection(  ); // Set up DB connection.


      if ( batch.connection != null ) {
        // Populate the JTable with records of otn_hotels table.
        batch.populateHotels(  );
      }
    }

    /**
     * This method dispatches the gui events to the appropriate methods that
     * perform the required JDBC operations. This method is invoked when an
     * event occurs in the gui (like table Selection, Button clicks etc.), and
     * is  invoked from the setupListeners section of BatchUpdateFrame.java.
     */
    public void dispatchEvent( String eventName ) {
      // Dispatch the Submit Cart event.
      if ( eventName.equals( "SUBMIT CART" ) ) {
        this.submitCart(  ); // The batch is submitted for update.

        // Dispatch the exit application event.
      }
      else if ( eventName.equals( "EXIT" ) ) {
        this.exitApplication(  ); // Exit the application.
      }

      // Dispatch the Show room bookings event.
      else if ( eventName.equals( "SHOW ROOM BOOKINGS" ) ) {
        this.showRoomBookings( gui.getSelectedHotelID(  ) );
      }
    }

    /**
     * This method reads the Connection.properties file which is passed as the
     * parameter to it and loads it into a java Properties object and returns
     * the properties object.
     */
    private static Properties loadParams( String file )
                                    throws IOException {
      // Load ResourceBundle and create 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" ) +
                         " \n Choose a Hotel to Reserve or Cancel." );
      }
       catch ( SQLException ex ) { // Catch SQL errors.
        gui.putStatus( "Error in Connecting to the Database " + '\n' 
+
                         ex.toString(  ) );
      }
      catch ( IOException ex ) { // Catch IO errors.
        gui.putStatus( "Error in reading the properties file " + '\n' +
                         ex.toString(  ) );
      }
    }

    /**
     * This method retrieves all rows from the otn_hotels table and populates the
     * gui Table with the records.
     */
    private void populateHotels(  ) {
      try {
        // Statement Object to execute SQL query.
        Statement stmt = connection.createStatement(  );

        // Retrieve all rows from the otn_hotels table into a ResultSet.
        ResultSet resultSet = stmt.executeQuery(
        "SELECT id,name,address,city FROM otn_hotels ORDER BY id" );

        // Loop through the result-set, obtain column values and add to JTable.
        while ( resultSet.next(  ) ) {
          Vector vec = new Vector(  ); // Vector for inserting a Row into the JTable.
          vec.addElement( resultSet.getString( 1 ) ); // Hotel ID
          vec.addElement( resultSet.getString( 2 ) ); // Hotel name
          vec.addElement( resultSet.getString( 3 ) ); // Hotel address
          vec.addElement( resultSet.getString( 4 ) ); // City

          gui.insertHotelRow( vec ); // Insert into the JTable.
        }

        stmt.close(  ); // Close the Statement.
      }
       catch ( SQLException ex ) { // Catch SQL Errors.
        gui.putStatus( "Error in Populating the otn_hotels Table. " +
                         ex.toString(  ) );
      }
    }

    /**
     * This method retrieves all available room types for the selected hotel, and
     * returns a vector containing all room types. This is a supporting method
     * for Room Reservation Functionality.
     */
    public Vector retrieveRoomTypes( int hotID ) {
      Vector roomTypes = new Vector(  ); // Vector for holding the Room Types.


      try {
        // Get the Room Types of selected Hotel.
        PreparedStatement stmt = connection.prepareStatement(
        "SELECT room_type FROM otn_available_room_types WHERE hot_id = ?" );

        stmt.setInt( 1, hotID ); // Bind the column value.

        // Execute the Query.
        ResultSet resultSet = stmt.executeQuery(  );

        // Populate the Vector with available Room Types from ResultSet object.
        while ( resultSet.next(  ) )
          roomTypes.addElement( resultSet.getString( 1 ) );

        stmt.close(  ); // Close the Statement.
      }
       catch ( SQLException ex ) { // Catch SQL Errors.
        gui.putStatus( "Error while Retrieving Room Type. " + ex.toString(  ) );
      }

      return roomTypes;
    }

    /**
     * This method displays the current booking details of the selected hotel.
     */
    private void showRoomBookings( int hotId ) {
      try {
        // Get current reservation details.
        PreparedStatement stmt = connection.prepareStatement(
         "SELECT booking_id, to_char(arrival_date, 'DD-Mon-YYYY'), room_type," +
         " no_of_rooms, no_of_nights, reserved_by FROM otn_hotel_bookings " +
         " WHERE hot_id = ?" );

        stmt.setInt( 1, hotId ); // Bind the column value.

        // Execute the Query.
        ResultSet resultSet = stmt.executeQuery(  );

        // String for getting Name.
        String name = new String(  );

        if ( resultSet.next(  ) ) {
          do { // Populate the JTable with the reservations.


            Vector newRow = new Vector(  ); // Vector for Populating the JTable.

            newRow.addElement( resultSet.getString( 1 ) ); // Booking ID
            newRow.addElement( resultSet.getString( 6 ) ); // Name
            newRow.addElement( resultSet.getString( 2 ) ); // Arrival Date
            newRow.addElement( resultSet.getString( 3 ) ); // Room Type
            newRow.addElement( resultSet.getString( 4 ) ); // No of Rooms
    
        newRow.addElement( resultSet.getString( 5 ) ); // No of Nights

            // insert row into the JTable.
            gui.cancelDialog.tablemodel.insertRow( newRow );
          }
           while ( resultSet.next(  ) );

          gui.displayRoomBookings(  );
        }
        else { // flag message if there are no reservations.
          gui.putStatus( "No Reservations in the selected hotel to cancel" );

          return;
        }
      }
       catch ( SQLException ex ) { // Catch SQL Errors.
        gui.putStatus( "Error while getting the Reservation Details: " +
                         ex.toString(  ) );
      }
    }

    /**
     * This function is used to get a Booking ID for Reservation from the
     * database sequence.
     */
    public int getBookingID(  ) {
      try {
        int bookingID = 0;

        // Create a Statement object.
        Statement stmt = connection.createStatement(  );

        // Execute the Query to get the Booking id.
        ResultSet resultSet = stmt.executeQuery(
         "SELECT otn_hotel_booking_id.nextval FROM dual" );

        if ( resultSet.next(  ) ) {
          bookingID = resultSet.getInt( 1 ); // Get the Booking Id.
        }

        stmt.close(  ); // Close the Statement.

        return bookingID;
      }
       catch ( SQLException ex ) { // Catch SQL Errors.
        gui.putStatus( "Error in Getting Booking ID " + ex.toString(  ) );


        return -1;
      }
    }

    /**
     * This is where the batch update is done and is invoked when 'Submit Cart'
     * button is clicked. This method creates two separate batches one for
     * reservation and the other for cancellation; it adds all the requested
     * reservation and cancellations from the Reservation Cart into the
     * respective batch. They are submitted for update in the Database as two
     * separate batches. When there is a batchUpdateException or SQLException it
     * rolls back to previous state.
     */
    private void submitCart(  ) {
      PreparedStatement cancelStmt = null; // PreparedStatement for cancellation.

      try {
        // Get the No. of Rows in the Reservation Cart to be Updated.
        int noOfRows = gui.hotelReservationModel.getRowCount(  );

        if ( noOfRows <= 0 ) {
          gui.putStatus( "No entry in the Reservation Cart for Submission" );
        }

        // Create a PreparedStatement for reservation.
        PreparedStatement reStmt = connection.prepareStatement(
          "insert into otn_hotel_bookings (booking_id, hot_id, arrival_date," +
          "room_type, no_of_rooms, no_of_nights, reserved_by) " +
          " VALUES(?,?,?,?,?,?,?)" );

        // Create a PreparedStatement for cancellation.
        cancelStmt = connection.prepareStatement( 
         "delete from otn_hotel_bookings where BOOKING_ID=?" );

        for ( int i = 0; i < noOfRows; i++ ) {
          // Get a row from the JTable.
          Vector newVect = gui.hotelReservationModel.getRow( i );

          // Get the Reservation/Cancellation ID.
          String resOrCan = newVect.elementAt( 7 ).toString(  );

          // Booking ID.
          int bookID = Integer.parseInt( newVect.elementAt( 1 ).toString(  ) );

          // Check Whether Reservation or Cancellation.
          if ( resOrCan.equals( "R" ) ) { // if it is Reservation

            int hotID = Integer.parseInt( newVect.elementAt( 0 ).toString(  ) );
            String name = newVect.elementAt( 2 ).toString(  ); // name
            String arrDate = newVect.elementAt( 3 ).toString(  ); // Arrival Date
            String roomType = newVect.elementAt( 4 ).toString(  ); // Room Type

            // No of Rooms
            int noRooms = Integer.parseInt( newVect.elementAt( 5 ).toString(  ) );

            // No of Nights
            int noNights = Integer.parseInt( newVect.elementAt( 6 ).toString(  ) );


            // check for arrival date to be later than current day.
            Date tempDate = null;

            try {
              // To parse dates from String, Ex: 05-SEP-1980.
              SimpleDateFormat formatter = new SimpleDateFormat( "dd-MMM-yyyy" );

              // Parse the date from user input string.
              tempDate = new Date( formatter.parse( arrDate ).getTime(  ) );
            }
             catch ( ParseException pex ) {
            }

            // Bind the column values
            reStmt.setInt( 1, bookID ); // Booking ID
            reStmt.setInt( 2, hotID ); // Hotel ID
            reStmt.setDate( 3, tempDate ); // Arrival Date
            reStmt.setString( 4, roomType ); // Room rate
            reStmt.setInt( 5, noRooms ); // No of Rooms
            reStmt.setInt( 6, noNights ); // No of Nights
            reStmt.setString( 7, name ); // Reserved by

            // Add the update to the reservation batch.
            reStmt.addBatch(  );
          }
          else if ( resOrCan.equals( "C" ) ) {
             // bind the column value to Booking ID.
            cancelStmt.setInt( 1, bookID );

            // Add the update to cancellation batch.
            cancelStmt.addBatch(  );
          }
        }

        // Execute the reservation batch.
        int[] upDateCounts = reStmt.executeBatch(  );

        // Close the Statement.
        reStmt.close(  );
      }
       catch ( BatchUpdateException b ) { // Catch BatchUpdate Exception.

        if ( b.toString(  ).indexOf( "ORA-00001" ) == -1 ) {
          gui.putStatus( " Error in Updating the Reservations: " +
                           b.toString(  ) );
        }
        else {
          gui.putStatus( " ERROR! There are date clashes for reservations. " +
                           " The hotel rooms have been reserved already." );
        }

        // Roll back to the previous state.
        try {
          connection.rollback(  );

          return;
        }
         catch ( SQLException e ) { // Catch SQL Errors.
          gui.appendStatus( "Error in rolling back :" + e.toString(  ) );
        }
      }
      catch ( SQLException ex ) { // Catch SQL Errors.
        gui.putStatus( "Error in Updating :" + ex.toString(  ) );

        // Roll back to the previous state.
        try {
          connection.rollback(  );

          return;
        }
         catch ( SQLException e ) { // Catch SQL Errors.
          gui.appendStatus( "Error in rolling back :" + e.toString(  ) );
        }
      }

      try {
        // Update the Cancellation Batch.
        int[] upDateCounts2 = cancelStmt.executeBatch(  );


        cancelStmt.close(  ); // Close the Statement (also closes the ResultSet).
        connection.commit(  ); // Commit the changes.

        // Clear the JTable
        gui.hotelReservationModel.clearTable(  );
        gui.submitCart.setEnabled( false );
      }
       catch ( BatchUpdateException b ) { // Catch Batch Update Exception.
        gui.putStatus( "Error in Updating the Cancellations: " );

        // Roll back to the previous state.
        try {
          connection.rollback(  );

          return;
        }
         catch ( SQLException e ) { // Catch SQL Errors.
          gui.appendStatus( "Error in rolling back :" + e.toString(  ) );
        }
      }
      catch ( SQLException ex ) { // Catch SQL Errors.
        gui.putStatus( "Error in Updating " + ex.toString(  ) );

        // Roll back to the previous state.
        try {
          connection.rollback(  );

          return;
        }
         catch ( SQLException e ) { // Catch SQL Error.
          gui.appendStatus( "Error in rolling back :" + e.toString(  ) );
        }
      }

      gui.putStatus( " Updated successfully!" );
    }

    /**
     * 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 ) { // Catch SQLException.
        gui.putStatus( "Error in Closing the connection :" + ex.toString(  ) );
      }

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