[펌] ConnectionPooling

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; }

Required Software

Back To Top

• 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.

Notations Used

Back To Top

Notation	Description
<OC4J_HOME>	points to the directory where oc4j is installed . For example, D:\oc4j
<JAVA_HOME>	points to the directory where JDK1.2 or higher is installed. For example, D:\jdk1.3.1
<HOST_NAME>	points to the hostname name where oc4j is running. For example, incq207a.idc.oracle.com
<UID>	is the oc4j admin username; default is: admin
<ADMIN_PASSWORD>	Password of oc4j admin user.
<PORT>	Port number where OC4J is running, default is: 8888

Application Set-up and Configuration

Back To Top

• 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.

HostName	=	incq212e.idc.oracle.com
SID	=	otn9i
Port	=	1521
UserName	=	scott
Password	=	tiger

Database Set-up

Back To Top

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

Running the Application

Back To Top

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
  

Description of Sample Files

Back To Top

The directory structure of the deliverable ConnectionPooling.jar will be as shown below. ConnectionPooling is the top level directory.

Directory	Files	Description
ConnectionPooling	ConnectionPooling.jws	The Oracle9i JDeveloper workspace file.
	ConnectionPooling.jpr	The Oracle9i JDeveloper project file.
	Connection.properties	This file has the details of the database connection parameters.
	build.xml	Project build file used by ANT tool to create pool.ear file which is deployed to stand alone OC4J.
	run.bat	The batch file to compile and deploy the sample in Windows environment.
	run.sh	The batch file(shell script) to compile and deploy the sample in Linux environment.
ConnectionPooling\doc	Readme.html	This file.
ConnectionPooling\config	ConnectionPooling.sql	This is the SQL script file to create the required tables in the database.
ConnectionPooling\images	logo.gif	This is the logo used in the sample application that is displayed in the left frame of the main page.
ConnectionPooling\META-INF	application.xml	Application level J2EE deployment descriptor.
ConnectionPooling\WEB-INF	web.xml	Deployment descriptor for the web application.
ConnectionPooling\src\oracle\otnsamples\jdbc\pool	ConnectionPoolingServlet.java	The servlet source file for the sample.
	ConnectionPoolingHTML.java	This class contains static methods, which generate HTML pages for the ConnectionPooling Sample.