How to Setup Oracle Connection Pooling with Apache Tomcat using JNDI/JDBC

Posted at Tuesday, February 28, 2006

In order to create truly scalable web applications that leverage a RDBMS, the use of connection-pooling is critical. Setting this up can be a daunting task since every application server/container combination is configured using slightly different methods. Current J2EE standards don't require truly universal server configuration files, deployment, etc. Throw into the mix JNDI (so that you don't have to hard-code authentication information, etc.); JDBC; Oracle, etc. and you can have a real head-scratcher on your hands. Adequate, comprehensive documentation that illustrates how to set this up from end-to-end is virtually non-existent - the vendors really do a poor job. The following example focuses on Apache/Tomcat against Oracle 8i or higher.

Assumptions:

  • You have an existing Oracle database and listener (in my case, a local database named repo.colestock.com)

  • You are using an adequately powered machine with a supported Windows O/S

Download and install a Java 2 SDK (Software Development Kit - not just the runtime!) compatible with your desired Apache/Tomcat release. In my example, I will be installing Apache/Tomcat 5.0.28, therefore I will be using Java SDK 1.4.2.11 - the most recent 1.4.X release at the time of this post:

http://java.sun.com/j2se/1.4.2/download.html

Choose the 'Windows Offline Installation' option, downloading the relevant *.exe.

Launch the installer *.exe and follow the prompts to install the SDK (annotate the install location - this will later become your %JAVA_HOME%)

Set the %JAVA_HOME% system environment variable by navigating to:
Start > Control Panel > System > Advanced (Environment Variables) and creating a new system variable named 'JAVA_HOME' and provide the path of the SDK location as its value (Example: 'C:\j2sdk1.4.2_11'). After words, add %JAVA_HOME%\bin to the front of the system's %PATH% variable. Apache/Tomcat will now be able to use your Java installation.

Download, unzip, and install the desired Apache/Tomcat version from http://tomcat.apache.org/download-55.cgi (I chose the *.zip associated with the 5.0.28 release). When installing, I normally perform a custom install and select the 'Service' option under the 'Tomcat' heading. Annotate the directory location in which you installed the software (i.e. %CATALINA_HOME%) as well as the password you provided for 'Administrator Login'.

After you have installed Tomcat, ensure that it is running by visiting the default page at: http://localhost:8080/. If Tomcat is not running, start the service via the command-line: net start "Apache Tomcat" (or via any other method you prefer).

The next step is to place the required *.jar files that include the Oracle JDBC drivers and other database libraries into the %CATALINA_HOME%\common\lib directory. Doing this makes the libraries globally available to all application as well as the server itself. Copy the following libraries from your %ORACLE_HOME%\jdbc\lib directory to %CATALINA_HOME%\common\lib:

  • ojdbc14.jar

After doing so, cycle Tomcat via your preferred method.

I created a test application using Ant - called first_j2ee - that can be used to validate your set-up (Note: Right-click 'Save Target As'):

Source ¦ first_j2ee.war

Save the *.war file to your local machine (renaming as necessary back to first_j2ee.war). The source is provided, just in case you want to get a closer look and/or if you wish to use it as the basis for your own test application - remember to edit the path information in the build.xml file, should you choose to use this code tree.

Tomcat includes a handy function, that automatically deploys any *.war file dropped in its %CATALINA_HOME%\webapps directory - this is how we will deploy our test application.

Copy first_j2ee.war to your %CATALINA_HOME%\webapps directory (Example: C:\Tomcat5.0\webapps).

Test that the application was deployed via http://localhost:8080/first_j2ee/test.html. A successful deployment will return the following String in the body: "The first_j2ee application has been deployed successfully!". Nothing fancy, granted, but you get the picture.

Now comes the tough part. We need to define in the application's context a connection pool resource and a resource reference to it. This will allow our test Servlet to lookup and obtain an Oracle database connection from an Application Server-managed connection pool. I have packaged along with the application's *.war file, the *.xml file that you need to move and edit in order to accomplish this.

Start by moving the %CATALINA_HOME%\webapps\WEB-INF\first_j2ee.xml file to the %CATALINA_HOME%\conf\Catalina\localhost directory. This is the *.xml file that defines the connection pool resource, etc. Modify the following elements/attributes for your environment:

  • url - to reflect your database service
  • username - to reflect the username you want to connect with (must have select on v$instance for this example to work)

  • password - to reflect the password for the aforementioned username

  • docBase attribute of Context - to reflect the directory path considering your %CATALINA_HOME%

Once the aforementioned modificaions have been made, test via: http://localhost:8080/first_j2ee/TestJNDI. If successful, the page will return the value of the instance and host name for the database service you specified in first_j2ee.xml.

If you have been successful thusfar, then you can work on tweaking/testing other connection pool directives as desired - the example connection pool does not have settings intended for a production environment.

Belatedly, I found an excellent article on the topic.

Labels: ,