David Ghedini

Linux, Java, Oracle, and PostgreSQL


David Ghedini

Tuesday Mar 01, 2011

Tomcat Oracle JDBC

This post will cover connecting your Tomcat web application to Oracle via JDBC using Tomcat's JNDI emulation ability.

We'll be creating this example using Tomcat 6.0.29 on CentOS 5.5

In our example, we'll create a connection for an application in the Tomcat/webapps/ROOT directory.

We'll also do the same for an application in a directory other than ROOT (e.g. Tomcat/webapps/myapp).

In our examples below, I have installed Tomcat at /usr/share/apache-tomcat-6.0.29.

Adjust this to your Tomcat installation as required.

First, let's create a simple test table in Oracle.

CREATE TABLE  DEMO_CUSTOMERS 
   (    CUSTOMER_ID NUMBER, 
    CUST_FIRST_NAME VARCHAR2(20), 
    CUST_LAST_NAME VARCHAR2(20) 
    
   )


Now, let's populate it with some data.

INSERT INTO DEMO_CUSTOMERS VALUES (1, 'John ', 'Dulles');
INSERT INTO DEMO_CUSTOMERS VALUES (2, 'William ', 'Hartsfield');
INSERT INTO DEMO_CUSTOMERS VALUES (3, 'Edward', 'Logan');
INSERT INTO DEMO_CUSTOMERS VALUES (4, 'Edward "Butch"', 'OHAare');
INSERT INTO DEMO_CUSTOMERS VALUES (5, 'Fiorello', 'Lambert');
INSERT INTO DEMO_CUSTOMERS VALUES (6, 'Albert', 'Hartsfield');
INSERT INTO DEMO_CUSTOMERS VALUES (7, 'Eugene', 'Bradley');


1. Download the required drivers


You will need to download the required Oracle JDBC drivers from the OTN

Both ojdbc6.jar and ojdbc14.jar will work for this example.

Additionally, since I will be using JSP Standard Tag Library (or JSTL), I will also need to download standard.jar and jstl.jar, which you can download here.

2. Move the drivers above to: /usr/share/apache-tomcat-6.0.29/lib


[root@sv2 ~]# mv ojdbc14.jar /usr/share/apache-tomcat-6.0.29/lib/ojdbc14.jar
[root@sv2 ~]# mv standard.jar /usr/share/apache-tomcat-6.0.29/lib/standard.jar
[root@sv2 ~]# mv jstl.jar /usr/share/apache-tomcat-6.0.29/lib/jstl.jar


By placing the drivers in the TOMCAT_HOME/lib directory, we are making them available to all applications.

3. Create the JDBC JNDI resource for our default context 


Since we are using the ROOT directory, we will be using /usr/share/apache-tomcat-6.0.29/conf/context.xml.

Add the following:

<Resource name="jdbc/oradb"
          auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.OracleDriver"
          url="jdbc:oracle:thin:@host:1521:sid"
          username="username"
          password="password"
          maxActive="20"
          maxIdle="30"
          maxWait="-1"
/>


Replace the url, username, and password above with your own.

I've used 'oradb' as my resource name. You can use whatever you like.

For an explanation of maxActive, maxIdle, and maxWait, and other attributes, please see the Context Configuration section of the Tomcat JNDI Datasource HOW-TO

4. Add a Resource-Ref to our web.xml file. 


Again, we are using the ROOT directory so we'll add our resource-ref to /usr/share/apache-tomcat-6.0.29/webapps/ROOT/WEB-INF/web.xml


jdbc/oradb
javax.sql.DataSource
Container


5. Create a JSP page to test our set up. 


I'll call the file mytest.jsp and place it into the ROOT directory (/usr/share/apache-tomcat-6.0.29/webapps/ROOT)

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

  <html>
  <head>

    
       select * from DEMO_CUSTOMERS
    

  </head>
  <body>
    <h1>Testing JDBC JINDI Oracle</h1>
    <table width='500' border='1'>
      <tr>
        <th align='left'>Customer Id</th>
        <th align='left'>Customer First Name</th>
        <th align='left'>Customer Last Name</th>
      </tr>
      <c:forEach var="democusts" items="${democusts.rows}">
        <tr>
           <td> ${democusts.CUSTOMER_ID}</td>
           <td> ${democusts.CUST_FIRST_NAME} </td>
           <td> ${democusts.CUST_LAST_NAME} </td>
       </tr>
      </c:forEach>
    </table>
  </body>
  </html>


Now start (or restart) Tomcat to allow it to read the changes to our configuration files

[root@sv2 ~]# service tomcat start
Using CATALINA_BASE:   /usr/share/apache-tomcat-6.0.29
Using CATALINA_HOME:   /usr/share/apache-tomcat-6.0.29
Using CATALINA_TMPDIR: /usr/share/apache-tomcat-6.0.29/temp
Using JRE_HOME:        /usr/java/jdk1.6.0_23
Using CLASSPATH:       /usr/share/apache-tomcat-6.0.29/bin/bootstrap.jar 


We can now navigate to http://YourDomain.com:8080/mytest.jsp and we should see the following:



6. Locations when using directory outside of ROOT. 


If you web application does not live in the ROOT directory, you can apply the above to using the following locations within your application.

For an application called "MyApp", for example, you could use the following locations.

JDBC JNDI Resource: Webapps>MyApp>META-INF/context.xml

Resource-Ref: Webapps>MyApp>WEB-INF/web.xml

Drivers: Webapps>MyApp>WEB-INF/lib/{ojdbc14.jar, standard.jar, jstl.jar}

If any of the directories or files above do not exist, you will need to create them.

Again, you can leave the drivers under Tomcat/lib if you want them to be available to all applications.

For more information, see Apache Tomcat JNDI Datasource HOW-TO

Related Posts:

Install Tomcat 6 on CentOS

Tomcat Manager Password

Tomcat Custom 404 Page

Bookmark and Share



Comments:

Post a Comment:
  • HTML Syntax: Allowed

Main Menu

Built With

Search

Pages

LinkedIn

Technorati Profile

Add Technorati Favorite

Tag Cloud

Enciva Solutions

Navigation

Visitors

Sponsors

Feeds

Tag Cloud

VPS Hosting: 1Gbps Network