Thursday, October 05, 2006


Tuning Datasource for Oracle 10g in TomCat 5.5 on Linux



It's a brief instruction how to tune files:



./WEB-INF/web.xml
./META-INF/context.xml



for Web application supposed to be deployed to TomCat 5.5.17
and use Datasource (JNDI API) to connect to Oracle 10gR2 database


Create following directory structure and and put file "ojdbc14.jar" (Oracle JDBC Driver) into ./WEB-INF/lib

Tune files ./WEB-INF/web.xml & ./META-INF/context.xml as follows.



$ ls -CR 
.:
OrclData.jsp errorpg.jsp SqlQuery.html  index.jsp  META-INF  WEB-INF

./META-INF:
context.xml

./WEB-INF:
lib
web.xml

./WEB-INF/lib
ojdbc14.jar



*****************************
File ./META-INF/context.xml
*****************************



<Context debug="0" docBase="OraDataSrc" path="/OraDataSrc" reloadable="true">
<Resource name="jdbc/oracle10g"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="oracle.jdbc.driver.OracleDriver"
    url="jdbc:oracle:thin:@ServerRHL.informatics.dstu.net:1521:jdvs"
    username="hr"
    password="hr"
    maxActive="100"
    maxIdle="10"/>
</Context>



*************************
File ./WEB-INF/web.xml
*************************



<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">

<resource-ref>
 <description>Oracle Datasource example</description>
 <res-ref-name>jdbc/oracle10g</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
</resource-ref>
</web-app>



********************
File SqlQuery.html
********************


<html>
<head>
<title>Help page</title>
</head>
<body>
<h2>Please submit SQL Query </h2>
<form method="get" action="/OraDataSrc/OrclData.jsp">
<table border="1">
<tr> <td valign="top">
<input type="text" name="sqlquery" size="100">
</td></tr>
</table></form>
<p>
<input type="submit" value="Submit Query">
</body>
</html>



*******************
File OrclData.jsp
*******************



<html>
<head>
<%@ page errorPage="errorpg.jsp"
import="java.sql.*,
       javax.naming.Context,
       javax.naming.InitialContext,
       javax.naming.NamingException,
       javax.sql.*,
       javax.servlet.*,
       javax.servlet.http.*" %>
</head>
<body>
<%! 
DataSource datasrc ;
public void  _jspInit() { 
 try {
   Context initCtx = new InitialContext();
   Context envCtx = (Context) initCtx.lookup("java:comp/env");
   datasrc = (DataSource) envCtx.lookup ("jdbc/oracle10g");
   } catch (NamingException ne) {ne.getMessage();} 
} 
%> 

<%
   Connection  conn = datasrc.getConnection();
   Statement   stmt = conn.createStatement();
   String sqlquery = request.getParameter("sqlquery");
   ResultSet   rs   = stmt.executeQuery(sqlquery);
   ResultSetMetaData  rsm  = rs.getMetaData();
   int colCount = rsm.getColumnCount();
%>
<table border = \"1\"> <tr>
<% for (int i = 1; i <= colCount; ++i) { %>
<th> <%= rsm.getColumnName(i) %> </th>
<% } %>
</tr>
<tr>
<%  while (rs.next()) {   
    for (int j = 1; j <= colCount; ++j) { %>
<td> <%= rs.getString(j) %> </td>
<% } %>
</tr>
<% } 
stmt.close();
conn.close();
%>
</table>
</body>
</html>



Goto directory containing build.xml and modify it as needed.
OraDataSrc directory should be a subdirectory of previous one.
Run:-



$ ant -f build.xml
$ ant -f build.xml deploy



Application OraDataSrc may be started and tested from TomCat's
Manager Utility.

Posted by at