Setting up Tomcat 5.5, Oracle, JSTL, with connection pool
[nobr]Hi I'm trying to setup the above, going thru the Apache Tomcat 5.5 Servlet/JSP Container HOW-TO at:
http://tomcat.apache.org/tomcat-5.5-doc/printer/jndi-datasource-examples-howto.html
I have added this to the server.xml file:
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/javatest?autoReconnect=true"/>
I then added this to my applications web.xml file:
<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">
<description>MySQL Test App</description>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/TestDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
Using this simple test page:
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<sql:query var="rs" dataSource="jdbc/TestDB">
select id, foo, bar from testdata
</sql:query>
<html>
<head>
<title>DB Test</title>
</head>
<body>
<h2>Results</h2>
<c:forEach var="row" items="${rs.rows}">
Foo ${row.foo}<br/>
Bar ${row.bar}<br/>
</c:forEach>
</body>
</html>
I should be able to query my oracle database, I put in my own database values , of course.
My point of confusion is where I place the following code:
Context initContext =new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
Connection conn = ds.getConnection();
//etc.
It would seem this code should go into a servlet, but the way it's stated, it seems like it goes in one of the configuration files.
I am trying to follow the MVC design pattern using JSTL so I want to keep all code out of the JSP.
Thank you for your help!
Alan[/nobr]
The tutorial your following is a good one, you will get it working in the end....
I used mySQL, but will do my best.
Did you start the server.xml config with
<Context path="/DBTest" docBase="DBTest"
debug="5" reloadable="true" crossContext="true">
as was the case in the mySql tutorial?
If you look, the Oracle stuff on server.xml just covers the <resource> section, it still needs to be nested in a context.
Have you copied the ORACLE driver you need into the tomcat/common/lib folder?
If not this is a .jar file you will have to find and download, then just save it in above mentioned folder.
The code example is an ALTERNATIVE METHOD to the code beginning
<sql:query var="rs" dataSource="jdbc/TestDB">
The last code example is showing how to access JNDI using only Java.
Hope that makes sense!
I never had much luck putting this config into server.xml
But there is an alternative:
Put the config into a seperate "context.xml" file under [TOMCAT]/conf\Catalina\localhost, as documented here:
http://tomcat.apache.org/tomcat-5.5-doc/config/context.html
eg in [TOMCAT]/conf/Catalina/localhost/testJDBC.xml
<Context path="/testJDBC" docBase="C:\Java\Tomcat 5.0\webapps\testing">
<Resource name="jdbc/TestDB" auth="Container"
type="javax.sql.DataSource">
</Resource>
<ResourceParams name="jdbc/TestDB" >
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/javatest?autoReconnect=true</value>
</parameter>
<parameter>
<name>username</name>
<value>javauser</value>
</parameter>
<parameter>
<name>password</name>
<value>javadude</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>-10000</value>
</parameter>
</ResourceParams>
</Context>
Hope this helps,
evnafets
Its even better to put the params as attributes rather use the Resource params tag.
So do just as evnafets said, define your own context xml (the name of the xml file should be <your_contextname>.xml) and put this in <TOMCAT_INSTALL>/conf/CATALINA/localhost directory.
<Context path="/testJDBC" docBase="C:\Java\Tomcat 5.0\webapps\testing">
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/javatest?autoReconnect=true"/>
</Context>
The docBase attribute is the path to your web application on your desktop. You needn't put it in the webapps folder :). Plcae the app anywhere on the desktop and just give the path in the docBase attribute.
ram.
Thank you for your replies,
I was able to work on the solutions you have provided, I cannot get it working when I change the server.xml file, this method isn't recommended for tomcat 5 anyways according to the reference evnafets provided.
I then tried evnafets method but am getting an error "unable to get connection, DataSource invalid, no driver found" I have the driver in C:\Tomcat5516\common\lib and have also tried it in <myapp>\WEB-INF\lib
I do have the most recent oracle driver installed. Another question, do you still need a web.xml file in your applications WEB-INF folder using the method evnafets describes?
Thanks again for your help!
