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.