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 |
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 |
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.jarNote: 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.
- 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.
The application can be deployed and run from stand alone OC4J:
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
- PATH pointing to:
- 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.
- Step 4: From the directory where sample is extracted (example: D:\ConnectionPooling ), deploy the ear files.
Example: D:\oc4j\j2ee\home> java -jar oc4j.jar
- 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
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
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.shAccess the application using the following url from any browser:
http://<HOST_NAME>:<PORT>/pool/ConnectionPoolingServletExample: 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
- PATH pointing to:
- 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.
- Step 4: From the directory where sample is extracted (Example, /home1/jdbc/ConnectionPooling), deploy the ear files.
Example: $ java -jar oc4j.jar
- 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
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
Description of Sample Files | Back To Top |
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. |
'Dev... > 플밍 관련 자료' 카테고리의 다른 글
[펌] [Jakarta Commons] Using a Configuration Factory (0) | 2005.02.11 |
---|---|
[펌] [jeus]dataSource 설정. (0) | 2005.02.11 |
[펌] 싱글톤 구현 (0) | 2005.02.11 |
[펌] JTS 이해하기 - 장막 뒤의 마법 (0) | 2005.02.11 |
[펌] JTS 이해하기 - 트랜잭션에 대한 소개 (0) | 2005.02.11 |