JNDI Datasource Tomcat Oracle

JNDI Datasource Tomcat Oracle

This tutorial will show you how to configure JNDI data source to tomcat server for oracle database and get connection through your program using configured JNDI name.

Before you proceed to configure JNDI Datasource Tomcat Oracle, don’t forget to copy the JDBC Driver’s jar into $CATALINA_HOME/lib.

Configuration JNDI Datasource Tomcat Oracle

Create a new test user, a new database and a single test table. Your Oracle user must have a password assigned. The driver will fail if you try to connect with an empty password.

mysql> GRANT ALL PRIVILEGES ON *.* TO javauser@localhost 
    ->   IDENTIFIED BY 'javadude' WITH GRANT OPTION;
mysql> create database javatest;
mysql> use javatest;
mysql> create table testdata (
    ->   id int not null auto_increment primary key,
    ->   foo varchar(25), 
    ->   bar int);

Note: the above user should be removed once testing is complete!

Next insert some test data into the testdata table.

mysql> insert into testdata values(null, 'hello', 12345);
Query OK, 1 row affected (0.00 sec)

mysql> select * from testdata;
+----+-------+-------+
| ID | FOO   | BAR   |
+----+-------+-------+
|  1 | hello | 12345 |
+----+-------+-------+
1 row in set (0.00 sec)

mysql>

Context configuration for  JNDI Datasource Tomcat Oracle

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.

<Resource name="jdbc/myoracle" auth="Container"
              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"/>

web.xml configuration for JNDI Datasource Tomcat Oracle

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>

Code example for JNDI Datasource Tomcat Oracle

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.

Finally deploy your web app into $CATALINA_BASE/webapps either as a warfile called DBTest.war

Once deployed, you should get information form data base.