Configuring data source on tomcat 5

0. Introduction

Oracle requires minimal changes from the MySQL configuration except for the usual gotchas :-)

Drivers for older Oracle versions may be distributed as *.zip files rather than *.jar files. 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.

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 heirarchy and rejar.

For Oracle 9i onwards you should use oracle.jdbc.OracleDriver rather than oracle.jdbc.driver.OracleDriver as Oracle have stated that oracle.jdbc.driver.OracleDriver is deprecated and support for this driver class will be discontinued in the next major release.

1. server.xml configuration

In a similar manner to the mysql config above, 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 sid called mysid. (Note: with the thin driver this sid is not the same as the tnsname). The schema used will be the default schema for the user scott.

Use of the OCI driver should simply involve a changing thin to oci in the URL string.

	type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@127.0.0.1:1521:mysid" username="scott" password="tiger" maxActive="20" maxIdle="10" maxWait="-1"/>

2. web.xml configuration

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

	Oracle Datasource example jdbc/myoracle javax.sql.DataSource Container

3. Code example

You can use the same example application as above (asuming 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(); //etc.

Tomcat looks for the required jar files in the folders
\netbeans-5.5\enterprise3\apache-tomcat-5.5.17\common\lib and
\netbeans-5.5\enterprise3\apache-tomcat-5.5.17\server\lib

These can be found in your oracle folder:
ora92\jdbc\lib