Building Internet Applications with Java Example: Java Stored Procedures Hotel November 1999

The Hotel applications enable end-users to check hotel availabilty and reserve rooms. The developers of this version implemented the business logic as Java methods and published them to Oracle8i as stored procedures. You can download the source code and supporting files for this application and a Java application that provides a GUI. The example illustrates the following concepts, features, and techniques:

• About Java Stored Procedures
• Building a Java Stored Procedure
  • Writing the Java Code
  • Loading into Oracle8i
  • Publishing to SQL
• Calling a Java Stored Procedure

About Java Stored Procedures

A stored procedure is a set of statements grouped into an executable unit, adding business logic to database data. Because they are stored and executed in the database instead of on the client, stored procedures reduce network traffic and improve application performance, scalability, and security. The term "stored procedure" refers to any of the following:

• Functions and procedures--named blocks that encapsulate a sequence of statements to add business logic to SQL data. Procedures perform actions, functions return values.
• Database triggers--routines associated with specific tables or views. A trigger fires when a DML operation modifies the corresponding table or view.
• Object-relational methods--code that manipulates user-defined composite data types. Well-behaved programs do not access such values directly. Instead, they call methods to get and set the values.

Prior to Oracle8i, developers wrote stored procedures in SQL and PL/SQL. Now, you can use Java. Except for graphical-user-interface (GUI) methods, any public static Java method can run in the RDBMS as a stored procedure.

Before you can call Java stored procedures, you must load them into the Oracle RDBMS and publish them to SQL (loading and publishing are separate tasks). Then, an application can execute a Java stored procedure as if it were a PL/SQL stored procedure.

To invoke a stored procedure, a client interacts with the SQL engine via JDBC calls. Then the SQL engine uses call specs to determine which Java method to execute. For example, the following figure shows what happens in the sample application when client-side code calls a Java stored procedure to reserve a hotel room. The call goes to the SQL engine which searches for a specification that maps the procedure name reserve_room to the Java method hotel.reserveRoom. The method executes in the Oracle8i Java VM, then client-side code continues.

Building a Java Stored Procedure

Building a Java stored procedure is a three-step process:

1. Write Java code. Any Java class can be deployed to Oracle8i, and the public static methods of the class can be published to SQL as stored procedures. Start by writing business logic in a Java class file.
2. Load it into Oracle8i. Load the Java source, class, and resource files into the Oracle RDBMS.
3. Publish it to SQL. For each Java method that is callable from SQL, write a call spec to expose the method's top-level entry point to Oracle. Many Java classes, referenced only by other Java classes, are never published to SQL.

You can do these steps separately using tools provided with Oracle8i, or you can use Oracle JDeveloper. JDeveloper provides a Java IDE for developing Java stored procedures and deploying them to Oracle8i. Use the JDeveloper source editor to create Java methods that will become Java stored procedures. Use the Deployment Profile Wizard to create a deployment profile, create a JAR file, and deploy (load and publish) the Java stored procedure to Oracle8i.

You can also use your favorite Java IDE to write the procedures, or reuse existing procedures that meet your needs. Oracle's Java facilities support many Java development tools and client-side programmatic interfaces.

Writing the Java Code

For this version of the Hotel application, the Java methods that will become stored procedures reside in the hotel.java file. The code below implements the getRoomDetails method (later, a call spec will publish it as a procedure named get_room_details). Note the following points:

• The method is declared public static so it can be published to SQL.
• Because it returns void, this method will be published as a procedure. Methods that return values are published as functions.
• The first two arguments (HotelId and RoomType) represent in-parameters, so they are declared as you would expect for a typical Java method.
• The third and fourth arguments (numRoomsAvailable and standardRoomRate) represent out-parameters (their values are changed, in this case by getInt and getFloat statements). When a Java argument corresponds to an OUT or an IN OUT parameter in SQL or PL/SQL, it must be a one-element array.

 public static void getRoomDetails(String HotelId, String RoomType,
                                   int[] numRoomsAvailable, float[] standardRoomRate) {
    Connection l_connection; // Database Connection Object
    try {
      l_connection = new OracleDriver().defaultConnection();
      if (RoomType.equals("ORCL")) RoomType = "OTHR";
      PreparedStatement l_stmt = l_connection.prepareStatement("SELECT STANDARD_RATE " +
      " FROM AVAILABLE_ROOM_TYPES WHERE HOT_ID = TO_NUMBER(?) AND ROOM_TYPE = ? ");

      l_stmt.setString(1, HotelId); // Bind the HotelId Input parameter
      l_stmt.setString(2, RoomType); // Bind the RoomType Input Parameter
      ResultSet l_rset = l_stmt.executeQuery();
      while (l_rset.next()) {
        standardRoomRate[0] = l_rset.getFloat(1); // Fetch Standard Room Rate

      }
      l_rset.close();
      l_stmt.close();

      l_stmt = l_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(?) )" );

      l_stmt.setString(1, HotelId); // Bind Input HotelId Parameter
      l_stmt.setString(2, HotelId); // Bind Input HotelId Parameter
      l_rset = l_stmt.executeQuery();
      while (l_rset.next()) {
        numRoomsAvailable[0] = l_rset.getInt(1); // Get Number of Rooms Available
      }
      l_stmt.close();
      l_rset.close();

    } catch (SQLException ex)  { // Trap SQL Errors
       System.err.println(ex.toString());
   }
 }

Loading into Oracle8i

To make Java files available to the Oracle 8i JVM, load them into the RDBMS. To load Java stored procedures automatically, use the command-line utility loadjava. Using a built-in package named LOADLOBS, loadjava uploads Java files into a BLOB column in the database table CREATE$JAVA$LOB$TABLE, which the utility creates in the logon schema. Then, it uses the SQL CREATE JAVA statement to load the Java files into the RDBMS as schema objects.

On the command line, you can enter the names of Java source, class, and resource files, SQLJ input files (.sqlj files), and uncompressed JARs and ZIP archives in any order. Here is the syntax:

loadjava {-user | -u} username/password[@database]
   [-option_name -option_name ...] filename filename ...

where option_name stands for the following syntax:

{ {andresolve | a}
   | debug
   | {definer | d}
   | {encoding | e} encoding_scheme_name
   | {force | f}
   | {grant | g} {username | role_name}[,{username | role_name}]...
   | {oci8 | o}
   | oracleresolver
   | {resolve | r}
   | {resolver | R} "resolver_spec"
   | {schema | S} schema_name
   | {synonym | s}

   | {thin | t}
   | {verbose | v} }

In the following example, loadjava connects to the default database using the default OCI JDBC driver, loads files from a JAR into schema BLAKE, then resolves them:

> loadjava -user scott/tiger -resolve -schema BLAKE serverObjs.jar

In the next example, loadjava connects using the thin JDBC driver, loads a class and a resource file, then resolves them:

> loadjava -u scott/tiger@dbhost:5521:orcl -t -r Agent.class images.dat

In the final example, loadjava adds NILES and FORD to the list of users who can execute Manager.class:

> loadjava -thin -user scott/tiger@localhost:5521:orcl -grant NILES,FORD Manager.class

You can also use CREATE JAVA statements to load Java stored procedures manually. For example, in SQL*Plus you can use the CREATE JAVA CLASS statement to load Java class files from local BFILEs and LOB columns into the RDBMS. For more information, see the Oracle8i SQL Reference.

Note: With JDeveloper, you can do both the loading step and the publishing step (described in the next section) through the Deployment Profile Wizard.

Publishing to SQL

To publish Java methods, write call specifications (call specs for short) that map Java method names, parameter types, and return types to their SQL counterparts. Unlike a wrapper, which adds another layer of execution, a call spec simply declares the existence of a Java method.

Use the SQL CREATE FUNCTION or CREATE PROCEDURE statement to declare a function or procedure call spec for a given Java method. Inside a PL/SQL package or SQL object type, use similar declarations. Publish value-returning Java methods as functions, and void Java methods as procedures. The function or procedure body contains the LANGUAGE JAVA clause. This clause records information about the Java method including its full name, its parameter types, and its return type. Mismatches are detected only at run time. Applications call the Java method through its call spec, that is, by referencing the call spec name. The run-time system looks up the call spec definition in the Oracle data dictionary, then executes the corresponding Java method.

For example, here is a simple Java class and a call spec that exposes the rowCount method as a function named row_count.

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

public class RowCounter {
  public static int rowCount (String tabName) throws SQLException {
    Connection conn = new OracleDriver().defaultConnection();
    String sql = "SELECT COUNT(*) FROM " + tabName;
    int rows = 0;
    try {
      Statement stmt = conn.createStatement();
      ResultSet rset = stmt.executeQuery(sql);

      while (rset.next()) {rows = rset.getInt(1);}
      rset.close();
      stmt.close();
    } catch (SQLException e) {System.err.println(e.getMessage());}
    return rows;
  }
}

CREATE FUNCTION row_count (tab_name IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'RowCounter.rowCount(java.lang.String) return int';

public static void getRoomDetails(String HotelId, String RoomType,
                                   int[] numRoomsAvailable, float[] standardRoomRate)

CREATE OR REPLACE PROCEDURE get_room_details (
  hotel_id IN VARCHAR2,
  room_type IN VARCHAR2,
  num_rooms_available OUT NUMBER,
  standard_room_rate OUT NUMBER)
AS LANGUAGE JAVA
NAME 'hotel.getRoomDetails(java.lang.String, java.lang.String, int[], float[])';

Calling a Java Stored Procedure

In this section, methods from the sample application show techniques for calling a Java stored procedure:

• In the first example, a Java method calls a stored procedure directly.
• In the second example, a Java method alters the database, indirectly firing a procedure stored as a trigger.

Direct Call

In this example, JSPHotelSample.getRoomAvailabilityRates creates and initializes a CallableStatement, then executes it to invoke the Java stored procedure get_room_details, implemented in Java by hotel.getRoomDetails. The stored procedure assigns values to the out-parameters, which are later retrieved by calls to CallableStatement.getInt and CallableStatement.getFloat.

  /**
  * This Method calls the Java stored procedure get_room_details, to obtain the
  * room availability and rate details for a hotel.
  *
  * The method first obtains all the room types available in the hotel using a
  * JDBC resultset, and then calls the get_room_details java stored procedure
  * to obtain rate and availability.
  *
  * The body of get_room_details is implemented as the getRoomDetails() method in
  * hotel.java
  *
  * JSP feature used:
  *    Java Stored Procedure call using the "CALL" SQL statement
  **/
  public void  getRoomAvailabilityRates(int p_selectedHotelID){

    // Vector to store resulting table
    Vector l_resultTable= new Vector();


    // Call the JSP to fetch room details.
    // JDBC is used in the following block to call the JSP. The SQLJ code
    // equivalent to the block below will be:
    //
    // #sql iterator roomTypeIter { String room_type };
    //
    // #sql roomTypeIter = { Select distinct(room_type) from
    //         available_room_types where hot_id = :hotelID }
    //
    // while(roomTypeIter.next()) {
    //  #sql { CALL get_Room_Details(  :selectedHotelID, roomTypeIter.room_Type(),
    //		          :out availableRooms, :out standardRate) };
    // }

    try{
      m_GUI.putStatus("Fetching Room Details using Java Stored Procedures. ");
      // This query selects room types available in the selected hotel.
      Statement l_statement=m_connection.createStatement();
      String l_query="select room_type from  Available_room_types "+
                "where hot_id = " + p_selectedHotelID;
      ResultSet l_result=l_statement.executeQuery(l_query);

      // Create a callableStatement for calling 
      // Java Stored Procedure 'Get_Room_Details()'.
      CallableStatement l_callableStatement =
         m_connection.prepareCall("{ call get_Room_Details(?,?,?,?) }");


      // Set the type of output parameters
      l_callableStatement.registerOutParameter(3, Types.INTEGER);
      l_callableStatement.registerOutParameter(4, Types.FLOAT);

      // For each room type, a Java Stored Procedure is called
      // to fetch available rooms and standard room rates
      while (l_result.next()) {

        // Get the room type from the result set.
        String  l_roomType = l_result.getString(1);

        // Set the Input parameters for Java Stored Procedure
        l_callableStatement.setInt(1, p_selectedHotelID);
        l_callableStatement.setString(2, l_roomType);

        // Call Java Stored Procedure to fetch room details.
        l_callableStatement.execute();

        // Creating new output row with the result
        Vector l_newrow = new   Vector();
        l_newrow.addElement(l_roomType);
        l_newrow.addElement(new Float( l_callableStatement.getFloat(4) ));
        l_newrow.addElement(new Integer( l_callableStatement.getInt(3) ));

        //  Add the new row to the result table.
        l_resultTable.addElement(l_newrow);

      }

      // Close the result set, JDBC statement, and callableStatement.
      l_result.close();
      l_statement.close();
      l_callableStatement.close();
      m_GUI.appendStatus("Done ");

      // Display the result using Jtable
      m_GUI.displayRoomDetails(l_resultTable);
      m_GUI.clearStatus();

    } catch(SQLException ex){ // Trap SQL Errors
      m_GUI.putStatus(" Error: " + ex.toString());
    }
  }

Indirect Call Via a Trigger

In this example, JSPHotelSample.reserveRoom defines and executes a PreparedStatement. Because this statement represents an INSERT INTO query, it fires the stored trigger update_room_data_trig, defined using the SQL CREATE TRIGGER statement and implemented in Java as hotel.updateAvailableRoomData. The reserveRoom method also uses Statement objects to define and execute queries, but because these queries select and retrieve data without altering tables or views, no triggers are fired.

  public void reserveRoom(String p_arrDate, String p_name, String p_roomType,
                          int p_numNights, int p_numRooms) {
    try{
       m_GUI.putStatus("Saving room reservation ...");
       java.util.Date l_tempDate = new java.util.Date();  // Current Date
       String l_bookingNote = p_name + "##" + l_tempDate;
       double l_itnId = 9999999; // The default itinerary ID for the sample
       int l_hotId = m_GUI.getSelectedHotelID();

       // Query the next booking ID from the sequence
       Statement l_stmt = m_connection.createStatement();
       ResultSet l_resultSet = l_stmt.executeQuery(
                                 "select hotel_booking_id.nextval from dual");
       int l_bookingId =0 ;
       // Retrieve the booking ID
       if (l_resultSet.next())
          l_bookingId = l_resultSet.getInt(1); // Holds the value of booking_id
       l_resultSet.close();

       // Query the room rate and currency for the chosen room
       l_resultSet = l_stmt.executeQuery("select standard_rate,currency from "+
                                   "AVAILABLE_ROOM_TYPES where hot_id = "+l_hotId+
                                   "and room_type = '"+p_roomType+"'");
       // Initialize room rate and currency

       int l_roomRate = -1;
       String l_currency = "";
       // Retrieve room rate and currency
       if (l_resultSet.next()) {
         l_roomRate = l_resultSet.getInt(1);
         l_currency = l_resultSet.getString(2);
       }
       l_resultSet.close(); // Close the resultSet
       l_stmt.close(); // Close the Statement object

       // Create a SQL Statement context to insert a record to
       // hotel_bookings table
       PreparedStatement l_pst = m_connection.prepareStatement(
                    "insert into hotel_bookings values (?,?,?,?,?,?,?,?,?,?)");

       // Bind the column values into the PreparedStatement, l_pst
       l_pst.setInt(1,l_bookingId);   // Booking ID
       l_pst.setInt(2,l_hotId); // HOTEL ID
       l_pst.setString(3,p_arrDate); // Arrival Date
       l_pst.setInt(4,p_numNights); // Number of nights
       l_pst.setInt(5,p_numRooms); // Number of rooms
       l_pst.setInt(6,l_roomRate); // Room Rate
       l_pst.setString(7,p_roomType); // Room Type
       l_pst.setString(8,l_currency); // Currency of Room Rate
       l_pst.setDouble(9,l_itnId); // Itinerary ID
       l_pst.setString(10,l_bookingNote); // Booking Note

       l_pst.execute(); // Execute the PreparedStatement
       l_pst.close();   // Close the PreparedStatement object


       m_GUI.m_Pane.clearForm(); // Clear the Room Reservation Form
       m_GUI.putStatus("The Room is reserved for "+p_numNights+" days from "
                       +p_arrDate);
    } catch (Exception ex) { // Trap SQL errors
      m_GUI.putStatus("Error in reserving room... "+ex.toString());
      m_GUI.getReservationForm();
    }
  }

< Back to Introduction

Questions or comments? Post a message in OTN's Sample Code discussion forum or send email to the author.

Building Internet Applications with Java: Java Stored Procedures Hotel Example
Author: Robert Hall, Oracle Corporation
Date: November 1999

This document is provided for information purposes only and the information herein is subject to change without notice. Please report any errors herein to Oracle Corporation. Oracle Corporation does not provide any warranties covering and specifically disclaims any liability in connection with this document.
Oracle is a registered trademark and Enabling the Information Age is a trademark or registered trademark of Oracle Corporation. All other company and product names mentioned are used for identification purposes only and may be trademarks of their respective owners.

Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA94065
U.S.A.

Worldwide Inquiries:
+1.650.506.7200
Copyright © Oracle Corporation 1999, 2000
All Rights Reserved