print Printer View e-mail E-mail this page Bookmark Bookmark 
/*
* @author : Umesh Kulkarni (ukulkarn.in)

* @Version 1.0
*
* Development Environment     : Oracle9i JDeveloper 
*
* Name of the Application     : RoomsInformationProcedure.java
*

* Creation / Modification History
*    ukulkarn.in           Creation        18-Mar-1999
*    Jagriti               Modified        29-Aug-2002   removed Hotel.java file
*  
*/

package oracle.otnsamples.jsp.roomsinformationsample;


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

//Oracle Extensions to JDBC

import oracle.jdbc.driver.OracleDriver;


/**
 * The file consists of a Java Method that is  used to demonstrate
 * following Java Stored Procedure. The Java method "getRoomDetails" gets 
 * published to SQL and is stored in the Oracle database.
 */    
public class RoomsInformationProcedure {


 /**
  * This method finds out the Room Details namely 'Number of Rooms Available'
  * and 'Standard Room Rates' for a given Hotel and Room Type
  *
  * Note: 1) Input Parameters to this method are hotelId and roomType where as
  *          numRoomsAvailable and standardRoomRate are output parameters.
  *       2) Corresponding to IN OUT or OUT parameters of PL/SQL procedures,
  *          the Java Methods must have parameters that are a one-element array.
  */

  public static void getRoomDetails(String hotelId, String roomType,
                    int[] numRoomsAvailable, float[] standardRoomRate) {

    Connection connection = null; // Database connection object
    
    try {
      // Get a Default Database Connection using Server Side JDBC Driver. 
      // Note : This class will be loaded on the Database Server and hence use a 
      // Server Side JDBC Driver to get default Connection to Database
      connection = new OracleDriver().defaultConnection();


      if (roomType.equals("ORCL")) 
          roomType = "OTHR";

      // Query to find out Standard Room Rates for a given Hotel and Room Type
      PreparedStatement stmt = connection.prepareStatement
                                           ("SELECT standard_rate" +
                              " FROM available_room_types  WHERE " +
                       " hot_id = TO_NUMBER(?) AND room_type = ? ");

      stmt.setString(1,hotelId);   // Bind the Hotel ID Input parameter
      stmt.setString(2,roomType);  // Bind the roomType Input parameter


      ResultSet rset = stmt.executeQuery(); // Execute the query, get Resultset
      
      // Loop through the Resultset and fetch the results
      while (rset.next()) {
        standardRoomRate[0] = rset.getFloat(1); // Fetch Standard Room Rates
      }

      // Close the Result Set and Statement objects
      rset.close();
      stmt.close();

      // Query to find out total number of available rooms for a given Room Type
      // and Hotel ID

      stmt = connection.prepareStatement("SELECT TOTAL_"+ roomType +
                                         " FROM room_availability WHERE " +
                                         " hot_id = TO_NUMBER(?) AND  " +
                                         " booking_date = ( SELECT MAX(booking_date) " +
                                         " FROM room_availability  " +
                                         " WHERE hot_id = TO_NUMBER(?) )" );

      stmt.setString(1,hotelId); // Bind Input Hotel ID Parameter
      stmt.setString(2,hotelId); // Bind Input Hotel ID Parameter

      rset = stmt.executeQuery(); // Execute query and get ResultSet
      
      // Loop through the Resultset and fetch results
      while (rset.next()) {

        numRoomsAvailable[0] = rset.getInt(1); // Get number of Rooms available
      }

      // Close Resultset and Statement
      rset.close();
      stmt.close();
     
      
    } catch (SQLException ex) { // Trap SQL Errors
      ex.printStackTrace();
    } finally {
       try{
         if (connection != null || !connection.isClosed())
             connection.close();   // Close the database connection
         } catch(SQLException ex){ 

           ex.printStackTrace();
         }
    }
  }
}
E-mail this page
Printer View Printer View
Oracle Is The Information Company About Oracle | Oracle RSS Feeds | Careers | Contact Us | Site Maps | Legal Notices | Terms of Use | Privacy