Oracle JDBC Connection Tomcat 5.5

Mon, Oct 6, 2008

How to Setup or Configure Oracle JDBC Connection with Tomcat 5.5

Below are 3 Steps to Create a JDBC Connection for Tomcat using Oracle driver

Step 1: Copy The Necessary Jars

To begin with You need to copy the Jars namely classes12.jar and classes111.jar from your Oracle Installation to your Tomcat 5.5 Common Lib Folder.

Oracle 9i Path to the Jars

Tomcat 5.5 Path to where the Jars need to be copied

Step 2: Modify the Server.xml file

<Resource name="UserDatabase" auth="Container"
type="org.apache.catalina.UserDatabase"
description="User database that can be updated and saved"
factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
pathname="conf/tomcat-users.xml" />

Add the Below peice of code in your Server.xml file just after the closing Tag of the above shown Section;

<p style="text-align: left;"><Resource name="jdbc/bob" auth="Container"
type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@127.0.0.1:1521:bob"
username="scott" password="tiger" maxActive="20" maxIdle="10"
maxWait="-1"/>

In the URL, “jdbc:oracle:thin:@127.0.0.1:1521:bob” (url=”jdbc:oracle:thin:@<host>:<port>:<sid>”);

127.0.0.1 is the hostname
1521 is the Port
and bob is the SID

Your Server.xml should look like this

Step 3: Test the Connection Class with the Servlet Code Example as Shown Below.

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class TestConnection extends HttpServlet {

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter out = response.getWriter();
try {
Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:bob", "scott", "tiger");
// @machineName:port:SID,   userid,  password

out.println("<HTML>");
out.println("<HEAD>");
out.println("<TITLE>Test JDBC Connection</TITLE>");
out.println("</HEAD>");
out.println("<BODY>");
out.println("<H1>Connection Established Successfully</H1>");
out.println("</BODY>");
out.println("</HTML>");

} catch (ClassNotFoundException e1) {
out.println("Connection Failed " + e1.toString());
} catch (SQLException e2) {
out.println("Connection Failed  " + e2.toString());
} catch (Exception e3) {
out.println("Connection Failed  " + e3.toString());
}
}

}

If all is well the Output should look like this;