/*
   * @author  vkumar
   * @version 1.0
   *
   * Development Environment        :  Oracle 9i JDeveloper
   * Name of the Application        :  ConnectionPoolingServlet.java
   * Creation/Modification History  :
   *
   * Reghu             15-May-2002    Re-certified on Oracle9i JDeveloper
   * Elangovan         28-Nov-2002    modified  Connection Pooling
   * Savitha           03-Feb-2003    Certified on Linux
   */

  package oracle.otnsamples.jdbc.pooling;


  // Java IO classes
  import java.io.IOException;
  import java.io.PrintWriter;

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

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

  // Servlet specific classes
  import javax.servlet.ServletConfig;
  import javax.servlet.ServletContext;
  import javax.servlet.ServletException;
  import javax.servlet.http.HttpServlet;
  import javax.servlet.http.HttpServletRequest;
  import javax.servlet.http.HttpServletResponse;
  import javax.sql.PooledConnection;

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



  /**
   * Connection pooling is a mechanism where the server connection is recycled
   * rather than being destroyed during an application shutdown.  This class is
   * a servlet client for the 'Hotel Reservation System' which  uses Connection
   * Pooling mechanism to reuse the connections. In this application the method
   * 'createConnectionPool()' will create a connection pool object, this pool
   * will be used by the other methods to get a connection. This application
   * displays all the hotels to user. The user can choose any Hotel and either
   * press Reserve Room for Reserving Room in the Hotel or press Cancel Room for
   * cancelling the reservation.
   */
  public class ConnectionPoolingServlet extends HttpServlet {
    private ServletContext context = null; // ServletContext object to log errors.

    // Database Connection pool Object.
    private OracleConnectionPoolDataSource connectionPoolDS = null;
    private String servletPath = null; // Servlet path

    /**
     * Initializes the servlet. The method is called once, automatically by the
     * Java Web Server when it loads the servlet. The init() method should save
     * the ServletConfig object so that it can be returned by the
     * getServletConfig() method. Here we will get a pooled connection object.
     */
    public void init( ServletConfig config ) throws ServletException {
      super.init( config );
      // Call to create a connection pooled object.
      this.createConnectionPool(  ); 
      context = config.getServletContext(  ); // get ServletContext.
    }

    /**
     * This method is called whenever a servlet is invoked. In this method we
     * check for the value of parameter 'REQ_TYPE' and depending on its value
     * call the different methods which return HTML page in form of a String.
     */
    public void service( HttpServletRequest req, HttpServletResponse res )
                   throws ServletException, IOException {
      // Set the content type of the response.
      res.setContentType( "text/html" ); 


      // Get the output stream.
      PrintWriter out = new PrintWriter( res.getOutputStream(  ) );

      // Get the value of REQ_TYPE.
      String reqType = req.getParameter( "REQ_TYPE" );

      if ( reqType == null ) {
        servletPath = req.getRequestURI(  ); // Get the Path of the Servlet.

        // Get the main Page.
        out.println( ConnectionPoolingServletHTML.generateMainPageHTML( servletPath ) );
      }
      else if ( reqType.equals( "ALL_HOTELS" ) ) {
        // Get the frame having the table of 'HOTEL DETAILS'.
        Vector hotels = populateHotels(  );

        out.println( ConnectionPoolingServletHTML.generateTableFrameHTML(
                                                                hotels,
                                                                servletPath ) );
      }
      else if ( reqType.equals( "RESERVE_ROOM" ) ) { 
        // Request to get the reservation page.
        int hotelId = Integer.parseInt( req.getParameter( "HOTEL_ID" ) );

        // Get the room types.
        Vector roomTypes = retrieveRoomTypes( hotelId );
        out.println( ConnectionPoolingServletHTML.generateReservePageHTML(
                                                                hotelId,
                                                                roomTypes,
                                                                servletPath ) );
      }
      // get the reservation details from request object and 
      // invoke 'reserveRoom()'.
      else if ( reqType.equals( "DO_RESERVE" 
) ) {
        // Get the Hotel Id.
        int hotelId = Integer.parseInt( req.getParameter( "HOTEL_ID" ) );

        // Do the reservation.
        String status = reserveRoom( hotelId, req.getParameter( "ARR_DATE" ),
                                       req.getParameter( "BY" ),
                                       req.getParameter( "ROOM_TYPE" ),
                                       req.getParameter( "NUM_NIGHTS" ),
                                       req.getParameter( "NUM_ROOMS" ) );
        String page = null;

        if ( status == null ) { // if reservation is successful.
          page = ConnectionPoolingServletHTML.generateMessagePage(
                                             "The Room is reserved for <br>" +
                                             req.getParameter( "NUM_NIGHTS" ) +
                                             " day(s) from " +
                                             req.getParameter( "ARR_DATE" ) );
        }
        else {
          page = ConnectionPoolingServletHTML.generateMessagePage( status );
        }


        out.println( page );
      }
      // Get the reservation Details so that reservation can be selected for 
      // cancellation.
      else if ( reqType.equals( "CANCEL_ROOM" ) ) {
        // Get the Hotel Id.
        int hotelId = Integer.parseInt( req.getParameter( "HOTEL_ID" ) );

        Vector reservations = this.getRoomBookings( hotelId );
        out.println( ConnectionPoolingServletHTML.
                            generateReservationDetailsPageHTML( hotelId,
                                                                reservations,
                                                                servletPath ) );
      }
      else if ( reqType.equals( "DO_CANCEL" ) ) { 
        // Request to cancel a reservation.
        // Get the BookingId which identifies the reservation.
        int bookingId 
= Integer.parseInt( req.getParameter( "RESV_SELECT" ) );
        String status = roomCancellation( bookingId ); // Do the cancellation.

        if ( status != null ) {
          out.println( ConnectionPoolingServletHTML.generateMessagePage( 
                                        "Error in canceling the reservation " +
                                                                    status ) );
        }
        else {
          out.println( ConnectionPoolingServletHTML.generateMessagePage( 
                                       "Selected booking has been canceled" ) );
        }
      }
      else if ( reqType.equals( "BUTTON" ) ) {
 
       // Get the frame displaying the various buttons on main page.
        out.println( ConnectionPoolingServletHTML.
                                       generateButtonFrameHTML( servletPath ) );
      }
      else if ( reqType.equals( "PICTURE" ) ) {
        // Get the frame having the 'PICTURE'.
        out.println( ConnectionPoolingServletHTML.generateImageFrameHTML(  ) );
      }

      out.close(  ); // Close the output Stream.
    }

    /**
     * Creates a database connection pool object using JDBC 2.0. Please
     * substitute the database connection parameters with appropriate values in
     * Connection.properties file.
     */
    private void createConnectionPool(  ) {
      try {
        // Load the properties file to get the connection information.
        Properties prop = this.loadParams( "Connection" );

        // Create a OracleConnectionPoolDataSource instance.
        connectionPoolDS = new OracleConnectionPoolDataSource(  );

        // Set connection parameters.
        String url = "jdbc:oracle:thin:@" + prop.get( "HostName" ) + ":" +
                       prop.get( "Port" ) + ":" + prop.get( "SID" );


        // Set the connection URL.
        connectionPoolDS.setURL( url );

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

        // Set the password.
        connectionPoolDS.setPassword( (String) prop.get( "Password" ) );
      }
       catch ( SQLException ex ) { // Catch SQL errors.
        context.log( ex.toString(  ) ); // log errors.
      }
      catch ( IOException ex ) { // Catch IO errors.
        context.log( ex.toString(  ) ); // log errors.
      }
    }

    /**
     * This method reads a properties file which is passed as the parameter to 
     * it and loads it into a java Properties  object and returns the same.
     */
    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;
    }

    /**
     * This method retrieves all available room types for the selected hotel, 
     * and returns a vector containing all room types. Supporting method for 
     * Room Reservation functionality.
     */
    private Vector retrieveRoomTypes( int hotId ) {
      Vector roomType = new Vector(  );

      PooledConnection pooledconn = null;
      Connection connection = null;


      try {
        // Get a connection from the connection pool.
        pooledconn = connectionPoolDS.getPooledConnection(  );

        connection = pooledconn.getConnection(  );

        // Create a PreparedStatement Query to select all available room types
        // for the selected hotel.
        PreparedStatement pst = connection.prepareStatement(
                             "SELECT room_type FROM otn_available_room_types " +
                             "WHERE hot_id = ?" );

        // Bind hotel ID into SQL query.
        pst.setInt( 1, hotId );

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

        // Loop through the resultSet, retrieve room types and add to vector.
        while ( resultSet.next(  ) )

          // Retrieve column values for this row
          roomType.addElement( resultSet.getString( 1 ) );

        // Close the PreparedStatement object.
        pst.close(  );
      }
       catch ( SQLException ex ) { // Catch SQL errors.
        context.log( ex.toString(  ) ); // log errors.
      }
       finally { // return  connection object to the pool.

        if ( connection != null ) {
          try {
            connection.close(  ); // close the pooled connection.
            pooledconn.close(  );
          }
           catch ( SQLException e ) {
            context.log( e.toString(  ) ); // log errors.
          }
        }
      }

      return roomType;
    }

    /**
     * This method gets all data from the HOTEL RESERVATION FORM and do the
     * reservation. This method gets connection from connectionPool.
     */
    private String reserveRoom( int hotelId, String arrDate, String name,
                                  String roomType, String numNights,
                                  String numRooms ) {
      PooledConnection pooledconn = null;
      Connection connection = null;


      try {
        // Get a connection from the connection pool.
        pooledconn = connectionPoolDS.getPooledConnection(  );

        connection = pooledconn.getConnection(  );

        // Create a Statement object to execute the Query.
        Statement stmt = connection.createStatement(  );

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

        int bookingId = 0;

        // Retrieve the booking ID.
        if ( resultSet.next(  ) ) {
          bookingId = resultSet.getInt( 1 ); // Hold the value of booking_id.
        }

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

        // Create a Statement object to execute the Query.
        Statement st = connection.createStatement(  );

        // Close the Statement object.
        st.close(  );

        // Create a Prepared Statement insert a record to OTN_HOTEL_BOOKINGS 
        // table.
        PreparedStatement pst = connection.prepareStatement(
              "INSERT INTO otn_hotel_bookings VALUES (?,?,?,?,?,?,?,sysdate)" );

        // Bind the column values into the PreparedStatement: pst
        pst.setInt( 1, bookingId ); // Booking ID
        pst.setInt( 2, hotelId ); // HOTEL ID
        pst.setString( 3, arrDate ); // Arrival Date
        pst.setInt( 4, Integer.parseInt( numNights ) ); // Number of nights
        pst.setInt( 5, Integer.parseInt( numRooms ) ); // 
Number of rooms
        pst.setString( 6, roomType ); // Room Type
        pst.setString( 7, name ); // Reserved By

        // Execute the PreparedStatement.
        pst.execute(  );


        // Close the PreparedStatement object.
        pst.close(  );

        // Commit the changes explicitly.
        connection.commit(  );

        return ( null );
      }
       catch ( Exception ex ) { // Catch errors

        if ( ex.toString(  ).indexOf( "ORA-00001" ) == -1 ) {
          return " Error in Creating the Reservation: " + ex.toString(  );
        }
        else {
          return " ERROR! The hotel room has been reserved already for the " +
                   " specified date";
        }
      }
       finally { // return  connection object to the pool.

        if ( connection != null ) {
          try {
            connection.close(  ); // close the pooled connection
            pooledconn.close(  );
          }
           catch ( SQLException e ) { // Catch SQL errors
            context.log( e.toString(  ) ); // log errors.
          }
        }
      }
    }

    /**
     * This method returns the booking details of a hotel based on the value of
     * Hotel Id. It is a supporting method for Room Cancellation Functionality.
     * This method gets connection from connectionPool.
     */
    private Vector getRoomBookings( int hotId ) {
      Vector reservations = new Vector(  );
      PooledConnection pooledconn = null;
      Connection connection = null;

      try {
        // Get a connection from the connection pool.
        pooledconn = connectionPoolDS.getPooledConnection(  );

        connection = pooledconn.getConnection(  );

        // Create a PreparedStatement to select room type.
        PreparedStatement pst = connection.prepareStatement( 
                      "SELECT booking_id,TO_CHAR(arrival_date,'DD-Mon-YYYY')" +
                      ",no_of_nights,no_of_rooms,room_type, reserved_by, "+
                      " TO_CHAR(reserved_date,'DD-Mon-YYYY')" +
                      " FROM otn_hotel_bookings WHERE hot_id = ? " );

        // Bind the column values into the PreparedStatement: pst
        pst.setInt( 1, hotId );

        // Execute the PreparedStatement.
        ResultSet resultSet = pst.executeQuery(  );

        String name = "";
        String reserveDate = "";

        // Populate the Result set, retrieve rows.
        while ( resultSet.next(  ) ) { // Point result set to next row


          Vector tempData = new Vector(  );

          // Retrieve room type values for this row and insert into the Vector.
          tempData.addElement( resultSet.getString( 1 ) ); // booking id
          tempData.addElement( resultSet.getString( 5 ) ); // room type
          tempData.addElement( resultSet.getString( 3 ) ); // number of Nights
          tempData.addElement( resultSet.getString( 4 ) ); // numRooms
          tempData.addElement( resultSet.getString( 
2 ) ); // arrival date
          tempData.addElement( resultSet.getString( 6 ) ); // reserved by
          tempData.addElement( resultSet.getString( 7 ) ); // reserved date
          reservations.addElement( tempData );
        }

        // Close the PreparedStatement.
        pst.close(  );
      }
       catch ( SQLException ex ) { // Catch SQL errors.
        context.log( ex.toString(  ) ); // log errors.
      }
       finally { // return  connection object to the pool.

        if ( connection != null ) {
          try {
            connection.close(  ); // close the pooled connection.
            pooledconn.close(  );
          }
           catch ( SQLException e ) { // Catch SQL errors.
            context.log( e.toString(  ) ); // log errors.
          }
        }
      }

      return reservations;
    }

    /**
     * This method deletes the records from the OTN_HOTEL_BOOKINGS table based 
     * on Booking Id. This method gets connection from connectionPool.
     */
    private String roomCancellation( int bookId ) {
      PooledConnection pooledconn = null;
      Connection connection = null;

      try {
        // Get a connection from the connection pool.
        pooledconn = connectionPoolDS.getPooledConnection(  );

        connection = pooledconn.getConnection(  );

        // Create a PreparedStatement Object to execute the query.
        PreparedStatement pstmt = connection.prepareStatement(
                        "DELETE FROM otn_hotel_bookings WHERE booking_id = ?" );

        // Bind the column values into the PreparedStatement: pstmt
        pstmt.setInt( 1, bookId );

        // Execute the Query and gets number of rows deleted.
        int numb = pstmt.executeUpdate(  );

        // Commit the execution.
        connection.commit(  );

        // Close the Statement object.
        pstmt.close(  );
      }
       catch ( SQLException ex ) { // Catch SQL errors.


        return "Error in deleting row " + ex.toString(  );
      }
       finally { // return  connection object to the pool.

        if ( connection != null ) {
          try {
            connection.close(  ); // close the pooled connection.
            pooledconn.close(  );
          }
           catch ( SQLException e ) { // Catch SQL errors.
            context.log( e.toString(  ) ); // log errors.
          }
        }
      }

      return null;
    }

    /**
     * This method retrieves all rows from the OTN_HOTELS table and populates 
     * the Vector with all the hotel details. This Vector will be used to 
     * display main HTML page. This method gets connection from connectionPool.
     */
    private Vector populateHotels(  ) {
      Vector hotelDetails = new Vector(  );
      PooledConnection pooledconn = null;
      Connection connection = null;

      try {
        // Get a connection from the connection pool.
        pooledconn = connectionPoolDS.getPooledConnection(  );

        connection = pooledconn.getConnection(  );

        // Statement object to execute SQL query.
        Statement statement = connection.createStatement(  );

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

        // Loop through the result-set, obtain column values and add to 
        // a Vector.
        while ( resultSet.next(  ) ) {
          Vector hotel = new Vector(  );
          hotel.addElement( resultSet.getString( 1 ) ); // Hotel ID
          hotel.addElement( resultSet.getString( 2 ) ); // Hotel Name
          hotel.addElement( resultSet.getString( 3 ) ); // Hotel Address
          hotel.addElement( resultSet.getString( 4 ) ); // City Name
          hotelDetails.addElement( hotel );
        }

        // Close the statement object.
        statement.close(  );
      }
       catch ( SQLException ex ) { // Catch SQL errors.
        context.log( ex.toString(  ) ); // log errors.
      }
       finally { // return  connection object to the pool.

        if ( connection != null ) {
          try {
            connection.close(  ); // close the pooled connection.
            pooledconn.close(  );
          }
           catch ( SQLException e ) { // Catch SQL errors.
            context.log( e.toString(  ) ); // log errors.
          }
        }
      }

      return hotelDetails;
    }

    /**
     * Override the getServletInfo() method which is supposed to return
     * information about the Servlet, e.g. the Servlet name, version, author and
     * copyright. notice.
     */
    public String getServletInfo(  ) {
      return "Hotel Servlet Information";
    }


    /**
     * Destroy the servlet. This method is called once when the servlet is
     * unloaded.
     */
    public void destroy(  ) {
      super.destroy(  );

      if ( connectionPoolDS != null ) {
        connectionPoolDS = null;
      }
    }
  }