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

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:

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

Section Index

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

naming-common.jar

Steps to Implement:

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

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>

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

Testing the Changes

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

}

<%@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>

Results

Troubleshooting

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

 <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" /> 

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

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