How to Setup Global JNDI Mapping
for Oracle JDBC Connection Pooling
with Tomcat

by Gregg Lagnese, MicroDeveloper, Inc.
May 16, 2005 v1.3
Multiple Page Variant
 
 
Preface

This document was originally created to outline the implementation of JNDI for Oracle connections. After working with both 5.0 and 5.5 configurations extensively, and with JDK 1.4.2 versus 1.5, I have come to realize that there are really two ways of implementing the global database JNDI configuration. The only difference I found was that the 5.0.x installation did not always work without a modification to the context web.xml file (Step 3). In 5.5 this was not necessary.

While there are apparently several suggested ways to accomplish global JNDI connectivity, this way seems to work consistently on a variety of platforms. This how-to assumes that you want the JNDI sources available to all contexts.

There are two possible ways to perform JNDI mapping; one uses a fixed logon, the other a dynamic one.  In the later case you will be required to add the necessary code to collect the proper username and password.  However, in doing this, configuration files may be used that can contain encrypted passwords thereby securing the database in the event that the Tomcat server is compromised.

 
Nomenclature:
<name> = where you substitute the necessary value
[name] = an optional element or parameter


Note
: All examples are given in Unix path notation, use Windows notation where applicable.
 

Section Index

Configuration

Testing the changes
Troubleshooting
Making a User Dependent Connection
 

Configuration

 
Pre-requisite setup:

To begin with, you need the following Jars (not zips) in your $CATALINA_BASE/<5.0.x>/common/lib directory.

Note: You only need naming-common.jar with 5.0 implementations, do not include it with 5.5 (or Tomcat will not start) because it is already bundled in the other 5.5 version of the factory Jar. You also don't need JSTL (JavaServer tags) unless you intend to use that as well.

/usr/local/tomcat/5.5.7/common/lib

commons-collections-3.1.jar
commons-dbcp-1.2.1.jar
commons-el.jar
commons-pool-1.2.jar jasper-compiler-jdt.jar
jasper-compiler.jar
jasper-runtime.jar
jsp-api.jar jstl.jar naming-java.jar naming-factory.jar naming-factory-dbcp.jar naming-resources.jar
ojdbc14.jar servlet-api.jar

For 5.0.x add this Jar:
naming-common.jar
You can get these files here if necessary:
Windows ZIP package
Unix GZIP package
 
Steps to Implement:
1) Modify the server.xml file
In <CATALINA_HOME>/conf/server.xml between <GlobalNamingResources> and </GlobalNamingResources> add the following
<Resource name="jdbc/<alias>"
   auth="Container"
   type="oracle.jdbc.pool.OracleDataSource"
   driverClassName="oracle.jdbc.driver.OracleDriver"
   factory="oracle.jdbc.pool.OracleDataSourceFactory"
   url="jdbc:oracle:thin:@<host>:<port>:<sid>"
   [user=<user>]
   [password=<password>]
   maxActive="20"
   maxIdle="10"
   maxWait="-1" />
    
Example
<!-- Global JNDI resources -->
 <GlobalNamingResources>
 <!-- Test entry for demonstration purposes -->
 <Environment name="simpleValue" type="java.lang.Integer" value="30"/>
 <!-- Editable user database that can also be used by
   UserDatabaseRealm to authenticate users -->
 <Resource name="UserDatabase" auth="Container"
   type="org.apache.catalina.UserDatabase"
   description="User database that can be updated and saved"
   factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
   pathname="conf/tomcat-users.xml" />
   
 <!-- Every connection to 'db1' uses the same user -->
 <Resource name="jdbc/db1"
   auth="Container"
   type="oracle.jdbc.pool.OracleDataSource"
   driverClassName="oracle.jdbc.driver.OracleDriver"
   factory="oracle.jdbc.pool.OracleDataSourceFactory"
   url="jdbc:oracle:thin:@oracle.microdeveloper.com:1521:db1"
   user="scott"
   password="tiger"
   maxActive="20"
   maxIdle="10"
   maxWait="-1" />
   
 <!-- Every connection to 'db2' must provide a username and password --> 
 <Resource name="jdbc/db2"
   auth="Container"
   type="oracle.jdbc.pool.OracleDataSource"
   driverClassName="oracle.jdbc.driver.OracleDriver"
   factory="oracle.jdbc.pool.OracleDataSourceFactory"
   url="jdbc:oracle:thin:@oracle.microdeveloper.com:1521:db2"
   maxActive="20"
   maxIdle="10"
   maxWait="-1" /> 
   
 </GlobalNamingResources>
 
2) Modify the context.xml file
In <CATALINA_HOME>/conf/context.xml between <Context> and </Context> add the following for each entry in the JNDI resource list:
<ResourceLink global="jdbc/<alias>" name="jdbc/<alias>" type="oracle.jdbc.pool.OracleDataSource"/>
Example
<!-- The contents of this file will be loaded for each web application -->
 <Context>
 <!-- Default set of monitored resources -->
 <WatchedResource>WEB-INF/web.xml</WatchedResource>
 <WatchedResource>META-INF/context.xml</WatchedResource>
   
 <!-- Uncomment this to disable session persistence across Tomcat restarts -->
 <!--
 <Manager pathname="" />
   -->
 <ResourceLink global="jdbc/db1" name="jdbc/db1" type="oracle.jdbc.pool.OracleDataSource"/>
 <ResourceLink global="jdbc/db2" name="jdbc/db2" type="oracle.jdbc.pool.OracleDataSource"/>
 </Context>
 
3) Modify the context's web.xml file (5.0.x step only - not necessary for 5.5.x)
In the <CONTEXT>/WEB-INF/web.xml between <web-app> and </web-app> add the following:
<resource-ref>
   <description><Your Description></description>
   <res-ref-name>jdbc/<alias></res-ref-name>
   <res-type>oracle.jdbc.pool.OracleDataSource</res-type>
   <res-auth>Container</res-auth>
</resource-ref>
Example
<resource-ref>
   <description>Oracle Development Datasource</description>
   <res-ref-name>jdbc/db1</res-ref-name>
   <res-type>oracle.jdbc.pool.OracleDataSource</res-type>
   <res-auth>Container</res-auth>
</resource-ref>

<resource-ref>
   <description>Oracle Development Datasource</description>
   <res-ref-name>jdbc/db2</res-ref-name>
   <res-type>oracle.jdbc.pool.OracleDataSource</res-type>
   <res-auth>Container</res-auth>
</resource-ref>
 
4) Restart Tomcat
 
 

Testing the Changes

 
5) Create a connection class (in the example it will be called ConnectionPool.java)
 
package com.microdeveloper.db.jndi;
import oracle.jdbc.pool.OracleDataSource;

import javax.naming.Context;
import javax.naming.InitialContext;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement; import java.util.Properties;
public class ConnectionPool implements Serializable {
   String message = "Not Connected";
public void init() {
   Connection conn = null;
   ResultSet rst = null;
   Statement stmt = null;
   try {
      Context initContext = new InitialContext();
      Context envContext = (Context) initContext.lookup("java:/comp/env");
      OracleDataSource ds = (OracleDataSource) envContext.lookup("jdbc/db1");
       
     if (envContext == null) throw new Exception("Error: No Context");
     if (ds == null) throw new Exception("Error: No DataSource");
     if (ds != null) conn = ds.getConnection();
     if (conn != null) {
        message = "Got Connection " + conn.toString() + ", ";
        stmt = conn.createStatement();
        rst = stmt.executeQuery("SELECT 'Success obtaining connection' FROM DUAL");
     }
     if (rst.next()) message = rst.getString(1);

   rst.close();
   rst = null;
   stmt.close();
   stmt = null;
   conn.close(); // Return to connection pool
   conn = null; // Make sure we don't close it twice
 } catch (Exception e) {
   e.printStackTrace();
 } finally {
   // Always make sure result sets and statements are closed,
   // and the connection is returned to the pool
   if (rst != null) {
      try {
         rst.close();
      } catch (SQLException e) {;}
      rst = null;
   }

   if (stmt != null) {
      try {
         stmt.close();
      } catch (SQLException e) {;}
      stmt = null;
   }

   if (conn != null) {
      try {
         conn.close();
      } catch (SQLException e) {;}
      conn = null;
   }
 }
}
public String getMessage() {return message;}
}
 
 
6) Create a JSP page to test with:
 
<%@page contentType="text/html"%>
 <%@page pageEncoding="UTF-8"%>
<html>
   <head><title>JSP Page</title></head>
<body>
<% com.microdeveloper.db.jndi.ConnectionPool ocp = new com.microdeveloper.db.jndi.ConnectionPool();
   ocp.init(); %>
<h2>Results</h2>
    Message: <%= ocp.getMessage() %>
</body>
</html>
 
7) Compile the class and deploy the context
Either manually or through your IDE, compile the class, then deploy the context to Tomcat. Now run the JSP page created in step 6. You should see the following:

Results

Message: Success obtaining connection
 
 

Troubleshooting

 
8) Check the database first
Verify database connectivity
a) TNSPing the database
b) Connect using the username and password in step 1
c) Verify the server, sid, and port
 
9) Error messages

Driver errors usually look like (Cannot create JDBC driver of class '' for connect URL 'null'):
Place the ojdbc14.jar file in the <CATALINA_HOME>\common\lib directory
Do NOT place the JAR in your <CONTEXT>/WEB-INF/lib directory (this can cause problems)
If used with an IDE that auto-deploys, exclude the JAR from the deployment

Javax Driver errors looking like "java.sql.SQLException: No suitable driver"
Usually means the JNDI lookup could not use the default javax implementation or Oracle driver (or both). Most people will tell you to use this construct:

<Resource name="jdbc/<alias>"
          auth="Container" 
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.driver.OracleDriver"
          ...

But I never found that to work reliably. This works every time (see step 1); note the inclusion of the factory:

<Resource name="jdbc/<alias>"
          auth="Container" 
          type="oracle.jdbc.pool.OracleDataSource"
          driverClassName="oracle.jdbc.driver.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
...

Ensure that the resource link (step 2) is in either:
the <CONTEXT>/META-INF/context.xml file (for context configurations)
- or -
the <CATALINA_HOME>/conf/context.xml (for global configurations)

Error messages that 'jdbc' is an unknown context:
Verify that step (3 for 5.0) is complete and accurate.
Verify that you have all the correct JARs in place. Specifically these:

naming-factory.jar
naming-factory-dbcp.jar
naming-resources.jar

IO Error Messages 'Io exception: The Network Adapter could not establish the connection'
This almost always means that the URL is improperly formed. For the Oracle thin driver it's jdbc:oracle:thin:@<host>:<port>:<sid>
Pay extra attention to the colon ':' after 'thin' and the '@' symbol. These tend to be left out often (for me anyway).

 

 
 

Making a User Dependent Connection

 
10) Setup the server.xml file without the username and password
Setup the server.xml file with an entry without the username and password specified as shown in the example below:
 
 <Resource name="jdbc/db2"
           auth="Container" 
           type="oracle.jdbc.pool.OracleDataSource"
           driverClassName="oracle.jdbc.driver.OracleDriver"
           factory="oracle.jdbc.pool.OracleDataSourceFactory"
           url="jdbc:oracle:thin:@oracle.microdeveloper.com:1521:db2"
           maxActive="20"
           maxIdle="10"
           maxWait="-1" /> 
 
11) Add code to the class to set the username and password
In the class, before the connection is formed, add a call to the datasource to set the username and password. This can be done in one of two ways as shown below:
 
try {
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
OracleDataSource ds = (OracleDataSource) envContext.lookup("jdbc/db2");
    if (envContext == null) throw new Exception("Error: No Context");
    if (ds == null) throw new Exception("Error: No DataSource");
    if (ds != null){
       ds.setUser("scott");
       ds.setPassword("tiger");
       conn = ds.getConnection();
    } catch (Exception e) {
   e.printStackTrace();
}
- or -

try {
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
OracleDataSource ds = (OracleDataSource) envContext.lookup("jdbc/db2");
    if (envContext == null) throw new Exception("Error: No Context");
    if (ds == null) throw new Exception("Error: No DataSource");
    if (ds != null){
       conn = ds.getConnection("scott","tiger");
    } catch (Exception e) {
   e.printStackTrace();
}
 
This method has been tested on Oracle 8.1.6 through 10.0.1 and Tomcat 5.0.28, 5.5.4, 5.5.7, and 5.5.9. It was tested using IntelliJ 4.5 and directly.
 
 
Revision History
05.16.05.01     glagnese     Defined differences between 5.0 and 5.5 implementations. Added additional troubleshooting tips.
03.08.05.01     glagnese     Corrected typo showing javax.sql.Factory incorrectly in syntax and incorrect JNDI parameter "username" that should be "user". Verified and tested.
01.21.05.01     glagnese     Created.

Gregg Lagnese is an senior executive and developer for MicroDeveloper, Inc. and has been developing software for Oracle databases for more than 14 years.