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

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