Tomcat - Oracle configurationb0

this is obsolete doc -- see http://doc.nethence.com/ instead

Tomcat - Oracle configuration

JDBC driver

Download the relevant Oracle JDBC driver (http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html) and install it,

cd /usr/local/tomcat6024/lib

mv ~/ojdbc6.jar .

chown root:root ojdbc6.jar

Tomcat resource

Configure the Tomcat resource,

cd /usr/local/tomcat6024/conf

vi server.xml

add (into GlobalNamingResources),

<Resource name="jdbc/base0"

auth="Container"

type="oracle.jdbc.pool.OracleDataSource"

driverClassName="oracle.jdbc.driver.OracleDriver"

factory="oracle.jdbc.pool.OracleDataSourceFactory"

url="jdbc:oracle:thin:@127.0.0.1:1521:base0"

user="USERNAME"

password="PASSWORD"

maxActive="20"

maxIdle="10"

maxWait="-1" />

Configure the resource link,

cd /usr/local/tomcat6024/conf

vi context.xml

add (into some Context),

<ResourceLink global="jdbc/base0"

name="jdbc/base0"

type="oracle.jdbc.pool.OracleDataSource" />

Reference the resource into your application,

cd /usr/local/tomcat6024/webapps/lala/WEB-INF

vi web.xml

add (into web-app),

<resource-ref>

   <description>base0 db</description>

   <res-ref-name>jdbc/base0</res-ref-name>

   <res-type>oracle.jdbc.pool.OracleDataSource</res-type>

   <res-auth>Container</res-auth>

</resource-ref>

Restart Tomcat to apply the change,

cd /usr/local/tomcat6024/bin

./shutdown.sh

./startup.sh

Test page

Create the connection class,

cd /usr/local/tomcat6024/webapps/lala/WEB-INF/classes

mkdir -p com/microdeveloper/db/jndi

cd com/microdeveloper/db/jndi

cat > ConnectionPool.java <<EOF9

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/base0");

     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;}

EOF9

note. change the connection alias accordingly on this line,

      OracleDataSource ds = (OracleDataSource) envContext.lookup("jdbc/base0");

and compile it,

javac -classpath /usr/local/tomcat6024/lib/ojdbc6.jar ConnectionPool.java

Create a JSP to test with,

cd /usr/local/tomcat6024/webapps/lala

vi connect.jsp

like,

<%@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>

Ready to go,

http: //SERVER_IP/lala/connect.jsp

References

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

http://www.microdeveloper.com/html/JNDI_Orcl_Tomcat1p.html

http://www.orafaq.com/wiki/JDBC

http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/htdocs/templates.htm#ThinDriverConnection