HOW-TO configure Oracle Datasource with Tomcat 4.1.x

JNDI Datasource HOW-TO
Database Connection Pool (DBCP) Configurations
DBCP provides support for JDBC 2.0. On systems using a 1.4 JVM DBCP will support JDBC 3.0.

DBCP uses the Jakarta-Commons Database Connection Pool. It relies on number of Jakarta-Commons componenets:

• Jakarta-Commons DBCP 1.0
  
• Jakarta-Commons Collections 2.0
  
• Jakarta-Commons Pool 1.0

These jar files along with your the jar file for your JDBC driver should be installed in $CATALINA_HOME/common/lib.

NOTE:Third Party drivers should be in jarfiles, not zipfiles. Tomcat only adds $CATALINA_HOME/common/lib/*.jar to the classpath.

NOTE: Do not install these jarfiles in your /WEB-INF/lib, or $JAVA_HOME/jre/lib/ext, or anywhere else. You will experience problems if you install them anyplace other than $CATALINA_HOME/common/lib.

Firstly by default, Tomcat will only use *.jar files installed in $CATALINA_HOME/common/lib therefore classes111.zip or classes12.zip will need to be renamed with a .jar extension. Since jarfiles are zipfiles, there is no need to unzip and jar these files - a simple rename will suffice. Also, you should be aware that some (early) versions of Tomcat 4.0 when used with JDK 1.4 will not load classes12.zip unless you unzip the file, remove the javax.sql.* class hierarchy and rejar.

1. server.xml configuration
You will need to define your Datasource in your server.xml file. Here we define a Datasource called myoracle using the thin driver to connect as user scott, password tiger to the schema called myschema in the sid called mysid. (Note: with the thin driver this sid is not the same as the tnsname)

<Resource name="jdbc/myoracle" auth="Container"
type="javax.sql.DataSource"/>

<ResourceParams name="jdbc/myoracle">
<parameter>

<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>

<value>oracle.jdbc.driver.OracleDriver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:oracle:thin:@[db's ip address]:1521:mysid</value>

</parameter>
<parameter>
<name>username</name>
<value>scott</value>
</parameter>

<parameter>
<name>password</name>
<value>tiger</value>
</parameter>
<parameter>

<name>maxActive</name>
<value>20</value>
</parameter>
<parameter>
<name>maxIdle</name>

<value>10</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>-1</value>

</parameter>
</ResourceParams>

2. web.xml configuration
You should ensure that you respect the element ordering defined by the DTD when you create you applications web.xml file.

<resource-ref>
<description>Oracle Datasource example</description>

<res-ref-name>jdbc/myoracle</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

3.Code example
You can use the same example application as above (assuming you create the required DB instance, tables etc.) replacing the Datasource code with something like


Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
Connection conn = ds.getConnection();



..Happy Valentine's Day ..