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