2009년 01월 29일
JNDI for Oracle JDBC Connection Pooling
How to Setup Global JNDI Mapping
for Oracle JDBC Connection Pooling
with Tomcat
| by Gregg Lagnese, MicroDeveloper, Inc. |
| May 16, 2005 v1.3 |
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="simpleVal ue" 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
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();
}
Making a User Dependent Connection
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();
}
# by | 2009/01/29 23:35 | 웹개발 | 트랙백 | 덧글(0)
