ConnectionPooling Sample Application

Table Of Contents 

• Overview of the Sample Application
• Required Software
• Notation Used
• Application Set-up and Configuration
• Database Set-up
• Running the Application 
• From Oracle9i JDeveloper
• From Stand alone OC4J:
  • On Windows
  • On Linux
• Description of Sample Files

Connection pooling is a mechanism whereby when an application closes a connection, that connection is recycled rather than being destroyed. Since establishing a connection is an expensive operation, reusing connections can improve performance dramatically.

This ConnectionPooling Sample demonstrates how connection pooling mechanism can be used in the middle tier. Here, a servlet will create a Connection Pool object when it is first invoked. Later, this connection pool object will be used to get a connection object for all the users. The Servlet will get a connection object from the connection pool when it needs to connect to the database and return it to the pool immediately after its use.

Working of the Sample application

The scenario used by this sample application described below, will demonstrate Connection Pooling mechanism usage in an application.

The sample application uses two database tables viz.. OTN_HOTELS and OTN_HOTEL_BOOKINGS. Reservations and cancellation can be done on any existing hotel in the database. When the application is invoked first, a connection pool is created and connections from this pool are used in the entire life of the application.

The application connects to the database and fetches all records available in the OTN_HOTELS and is displayed. The user can select any hotel from the 'Hotels' Table and opt for reservation or cancellation using Reserve or Cancel button.

Here is the code sample for using Connection Pooling APIs. You can find more details of the code in ConnectionPooling.java file under src/oracle/otnsamples/jdbc/pool folder. Look into Description of Sample Files section for folder and file details.

  /**
   * 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");

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

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

      // Sets the password
      connectionPoolDS.setPassword((String)prop.get("Password"));


    } catch(SQLException ex) { // Catch SQL errors
        ..........
    } catch(IOException ex) { // Catch IO errors
        ..........
    }
  }

  /**
   * 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. The method uses the 
   * connection from the available pool for retrieving room types.
   */
  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 
      // the 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());
    } 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());
          }
      }
    }
    return roomType;
  }

  /**
   * 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 Properties object.
   */
  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;
  }

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or JDK1.2.x or above This can be downloaded from here.
• Oracle9i Database or higher running SQL*Net TCP/IP listener. This can be downloaded from here.
• Oracle9i JDBC Driver. This can be downloaded from here.
• Oracle9i Containers for J2EE - OC4J Release 9.0.2. This can be downloaded from here.
• Ant project build tool. This can be downloaded from here.

• Unjar the provided ConnectionPooling.jar using the following command. 

> jar xvf ConnectionPooling.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path. 
(JDK_HOME is the root directory of the JDKx.x installation). This creates a folder ConnectionPooling with all the source files.

• Edit ConnectionPooling/Connection.properties file in your favorite editor. Provide these values by changing the HostName,Port,SID,UserName and Password to connect to your own database instance.

From a SQL*Plus Client, connect to the database using the credentials used above. Run the SQL Script file ConnectionPooling.sql to create the database tables and records required by the application. Look into Description of Sample Files section for folder and file details of the SQL file.
For example: SQL>@D:\ConnectionPooling\config\ConnectionPooling.sql

This sample application can be run in 2 different ways listed below.

• From Oracle9i JDeveloper
• From Stand alone OC4J
  • On Windows
  • On Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the ConnectionPooling.jws from the ConnectionPooling directory.
• Next, select Project/ConnectionPooling.jpr from main menu.
• Now, select Run/Run ConnectionPooling.jpr from main menu to run the application.

From Stand alone OC4J:

The application can be deployed and run from stand alone OC4J:

• On Windows NT
• On Linux

On Windows NT:

This section will describe steps to run the application from Windows NT. The sample can be run either manually or using a script file.

Deploy and run application using batch file: run.bat provided:

By setting few environment variables, the sample application could be deployed by just executing the batch file: run.bat from the command prompt, from ConnectionPooling directory. Environmental variables like JAVA_HOME, OC4J_HOME, HOST_NAME, ADMIN_PASSWORD have to be set before running run.bat file. Look at Notations section for more details on these variables.

Note: Make sure that OC4J is already running.

Example:

D:\ConnectionPooling> set OC4J_HOME=d:\oc4j
D:\ConnectionPooling> set ANT_HOME=d:\ant\jarkarta-ant-1.4.1
D:\ConnectionPooling> set JAVA_HOME=d:\jdk1.3.1
D:\ConnectionPooling> set HOST_NAME=incq207a.idc.oracle.com
D:\ConnectionPooling> set ADMIN_PASSWORD=welcome
D:\ConnectionPooling> run

Access the application using the following url from any browser:
http://<HOST_NAME>:<PORT>/pool/ConnectionPoolingServlet

Example: http://incq207a.idc.oracle.com:8888/pool/ConnectionPoolingServlet

Run application manually from Windows :

• Step 1: Set the following environmental variables:
  • PATH pointing to:
    • the directory where JDK 1.2 or higher is installed.
    • the directory where the project build tool ant is installed.
      Example: D:\ConnectionPooling> set PATH=d:\jdk1.3.1\bin;d:\ant\jakarta-ant-1.4.1\bin;%PATH%
  • OC4J_HOME pointing to the directory where oc4j is installed. See Notations for more details.
    Example: D:\ConnectionPooling> set OC4J_HOME=d:\oc4j
    
• Step 2: From the directory where sample is extracted (example: D:\ConnectionPooling), run the ant utility.
  

Example: D:\ConnectionPooling> ant
This will create connectionpool.war and pool.ear files.

• Step 3: Start OC4J from<oc4j_home>\j2ee\home directory.
  

Example: D:\oc4j\j2ee\home> java -jar oc4j.jar

• Step 4: From the directory where sample is extracted (example: D:\ConnectionPooling ), deploy the ear files.

java -jar <OC4J_HOME>\j2ee\home\admin.jar ormi:\\<HOST_NAME> <UID> <ADMIN_PASSWORD> -deploy -file pool.ear -deploymentName pool

Example: D:\oc4j\j2ee\home> java -jar d:\oc4j\j2ee\home\admin.jar ormi://incq207a.idc.oracle.com admin welcome -deploy -file pool.ear -deploymentName pool

• Step 5: Bind the web application to a context root using from the same directory as above step 4.

  java -jar <OC4J_HOME>\j2ee\home\admin.jar ormi://<HOST_NAME> <UID><ADMIN_PASSWORD> -bindWebApp pool connectionpool http-web-site pool
  
  Example: D:\oc4j\j2ee\home>java -jar d:\oc4j\j2ee\home\admin.jar ormi://incq207a.idc.oracle.com admin welcome -bindWebApp pool connectionpool http-web-site pool

• Step 6: Access the application using the following url from any browser:

  http://<HOST_NAME>:<PORT>/pool/ConnectionPoolingServlet
  
  Example: http://incq207a.idc.oracle.com:8888/pool/ConnectionPoolingServlet

On Linux :

This section will describe steps to run the application from console using JDK on Red Hat Linux Advanced Server Release 2.1. The sample can be run either manually or using a script file.

Deploy and run application using script file: run.sh provided:

By setting few environment variables, the sample application could be deployed by just executing the script file: run.sh from the command prompt, from ConnectionPooling directory. User will be prompted to enter value for the environmental variables required to run the script.

Note: Make sure that OC4J is already running.

• Go to ConnectionPooling directory and from the $ prompt, use the command below to run the script file.
  $sh run.sh

  Access the application using the following url from any browser:
  http://<HOST_NAME>:<PORT>/pool/ConnectionPoolingServlet

  Example: http://incq207a.idc.oracle.com:8888/pool/ConnectionPoolingServlet
  

Running the application manually :

• Step 1: Set the following environmental variables:
  • PATH pointing to:
    • the directory where JDK 1.2 or higher is installed.
    • the directory where the project build tool ant is installed.
      Example: $ export PATH=/home1/java/jdk1.3.1/bin:/home1/ant/bin:$PATH
  • OC4J_HOME pointing to the directory where oc4j is installed. See Notations for more details.
    Example: $ export OC4J_HOME=/home1/oc4j
    
  • JAVA_HOME pointing to the directory where JDK 1.2 or higher is installed.
    Example: $ export JAVA_HOME=/home1/java/jdk1.3.1
• Step 2: From the directory where sample is extracted (Example, /home1/jdbc/ConnectionPooling), run the ant utility.
  

Example: $ ant
This will create connectionpool.war and pool.ear files.

• Step 3: Start OC4J from <oc4j_home>/j2ee/home directory.
  

Example: $ java -jar oc4j.jar

• Step 4: From the directory where sample is extracted (Example, /home1/jdbc/ConnectionPooling), deploy the ear files.

java -jar <OC4J_HOME>/j2ee/home/admin.jar ormi://<HOST_NAME> <UID> <ADMIN_PASSWORD> -deploy -file pool.ear -deploymentName pool

Example: $java -jar /home1/oc4j/j2ee/home/admin.jar ormi://incq207a.idc.oracle.com admin welcome -deploy -file pool.ear -deploymentName pool

• Step 5: Bind the web application to a context root using from the same directory as above step 4.

  java -jar <OC4J_HOME>/j2ee/home/admin.jar ormi://<HOST_NAME> <UID><ADMIN_PASSWORD> -bindWebApp pool connectionpool http-web-site pool
  
  Example: $java -jar /home1/oc4j/j2ee/home/admin.jar ormi://incq207a.idc.oracle.com admin welcome -bindWebApp pool connectionpool http-web-site pool

• Step 6: Access the application using the following url from any browser:

  http://<HOST_NAME>:<PORT>/pool/ConnectionPoolingServlet
  
  Example: http://incq207a.idc.oracle.com:8888/pool/ConnectionPoolingServlet