source: nutchez-0.1/tomcat/webapps/docs/jndi-datasource-examples-howto.html @ 186

Last change on this file since 186 was 66, checked in by waue, 16 years ago

NutchEz - an easy way to nutch
File size: 49.0 KB
Line 
1<html><head><META http-equiv="Content-Type" content="text/html; charset=iso-8859-1"><title>Apache Tomcat 6.0 - JNDI Datasource HOW-TO</title><meta value="Les Hughes" name="author"><meta value="leslie.hughes@rubus.com" name="email"><meta value="David Haraburda" name="author"><meta value="david-tomcat@haraburda.com" name="email"><meta value="Glenn Nielsen" name="author"><meta value="" name="email"><meta value="Yoav Shapira" name="author"><meta value="yoavs@apache.org" name="email"></head><body vlink="#525D76" alink="#525D76" link="#525D76" text="#000000" bgcolor="#ffffff"><table cellspacing="0" width="100%" border="0"><!--PAGE HEADER--><tr><td><!--PROJECT LOGO--><a href="http://tomcat.apache.org/"><img border="0" alt="
2      The Apache Tomcat Servlet/JSP Container
3    " align="right" src="./images/tomcat.gif"></a></td><td><font face="arial,helvetica,sanserif"><h1>Apache Tomcat 6.0</h1></font></td><td><!--APACHE LOGO--><a href="http://www.apache.org/"><img border="0" alt="Apache Logo" align="right" src="./images/asf-logo.gif"></a></td></tr></table><table cellspacing="4" width="100%" border="0"><!--HEADER SEPARATOR--><tr><td colspan="2"><hr size="1" noshade></td></tr><tr><!--LEFT SIDE NAVIGATION--><td nowrap="true" valign="top" width="20%"><p><strong>Links</strong></p><ul><li><a href="index.html">Docs Home</a></li><li><a href="http://wiki.apache.org/tomcat/FAQ">FAQ</a></li></ul><p><strong>User Guide</strong></p><ul><li><a href="introduction.html">1) Introduction</a></li><li><a href="setup.html">2) Setup</a></li><li><a href="appdev/index.html">3) First webapp</a></li><li><a href="deployer-howto.html">4) Deployer</a></li><li><a href="manager-howto.html">5) Manager</a></li><li><a href="realm-howto.html">6) Realms and AAA</a></li><li><a href="security-manager-howto.html">7) Security Manager</a></li><li><a href="jndi-resources-howto.html">8) JNDI Resources</a></li><li><a href="jndi-datasource-examples-howto.html">9) JDBC DataSources</a></li><li><a href="class-loader-howto.html">10) Classloading</a></li><li><a href="jasper-howto.html">11) JSPs</a></li><li><a href="ssl-howto.html">12) SSL</a></li><li><a href="ssi-howto.html">13) SSI</a></li><li><a href="cgi-howto.html">14) CGI</a></li><li><a href="proxy-howto.html">15) Proxy Support</a></li><li><a href="mbeans-descriptor-howto.html">16) MBean Descriptor</a></li><li><a href="default-servlet.html">17) Default Servlet</a></li><li><a href="cluster-howto.html">18) Clustering</a></li><li><a href="balancer-howto.html">19) Load Balancer</a></li><li><a href="connectors.html">20) Connectors</a></li><li><a href="monitoring.html">21) Monitoring and Management</a></li><li><a href="logging.html">22) Logging</a></li><li><a href="apr.html">23) APR/Native</a></li><li><a href="virtual-hosting-howto.html">24) Virtual Hosting</a></li><li><a href="aio.html">25) Advanced IO</a></li><li><a href="extras.html">26) Additional Components</a></li><li><a href="maven-jars.html">27) Mavenized</a></li></ul><p><strong>Reference</strong></p><ul><li><a href="RELEASE-NOTES.txt">Release Notes</a></li><li><a href="config/index.html">Configuration</a></li><li><a href="api/index.html">Javadocs</a></li><li><a href="http://tomcat.apache.org/connectors-doc/">JK 1.2 Documentation</a></li></ul><p><strong>Apache Tomcat Development</strong></p><ul><li><a href="building.html">Building</a></li><li><a href="changelog.html">Changelog</a></li><li><a href="http://wiki.apache.org/tomcat/TomcatVersions">Status</a></li><li><a href="developers.html">Developers</a></li><li><a href="architecture/index.html">Architecture</a></li><li><a href="funcspecs/index.html">Functional Specs.</a></li></ul></td><!--RIGHT SIDE MAIN BODY--><td align="left" valign="top" width="80%"><table cellspacing="4" width="100%" border="0"><tr><td valign="top" align="left"><h1>Apache Tomcat 6.0</h1><h2>JNDI Datasource HOW-TO</h2></td><td nowrap="true" valign="top" align="right"><small><a href="printer/jndi-datasource-examples-howto.html"><img alt="Printer Friendly Version" border="0" src="./images/printer.gif"><br>print-friendly<br>version
4                    </a></small></td></tr></table><table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#525D76"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Table of Contents"><strong>Table of Contents</strong></a></font></td></tr><tr><td><blockquote>
5<p>
6<a href="#Introduction">Introduction</a><br>
7<a href="#Database Connection Pool (DBCP) Configurations">
8Database Connection Pool (DBCP) Configurations</a><br>
9<a href="#Non DBCP Solutions">Non DBCP Solutions</a><br>
10<a href="#Oracle 8i with OCI client">Oracle 8i with OCI client</a><br>
11<a href="#Common Problems">Common Problems</a><br>
12</p>
13</blockquote></td></tr></table><table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#525D76"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Introduction"><strong>Introduction</strong></a></font></td></tr><tr><td><blockquote>
14
15<p>JNDI Datasource configuration is covered extensively in the
16JNDI-Resources-HOWTO.  However, feedback from <code>tomcat-user</code> has
17shown that specifics for individual configurations can be rather tricky.</p>
18
19<p>Here then are some example configurations that have been posted to
20tomcat-user for popular databases and some general tips for db useage.</p>
21
22<p>You should be aware that since these notes are derived from configuration
23and/or feedback posted to <code>tomcat-user</code> YMMV :-). Please let us
24know if you have any other tested configurations that you feel may be of use
25to the wider audience, or if you feel we can improve this section in anyway.</p>
26
27<p>
28<b>Please note that JNDI resource configuration changed somewhat between
29Tomcat 5.0.x and Tomcat 5.5.x.</b>  You will most likely need to modify older
30JNDI resource configurations to match the syntax in the example below in order
31to make them work in Tomcat 6.x.x.
32</p>
33
34<p>
35Also, please note that JNDI DataSource configuration in general, and this
36tutorial in particular, assumes that you have read and understood the
37<a href="config/context.html">Context</a> and
38<a href="config/host.html">Host</a> configuration references, including
39the section about Automatic Application Deployment in the latter reference.
40</p>
41</blockquote></td></tr></table><table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#525D76"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Database Connection Pool (DBCP) Configurations"><strong>Database Connection Pool (DBCP) Configurations</strong></a></font></td></tr><tr><td><blockquote>
42
43<p>DBCP provides support for JDBC 2.0.  On systems using a 1.4 JVM DBCP
44will support JDBC 3.0. Please let us know if you have used DBCP and its
45JDBC 3.0 features with a 1.4 JVM.
46</p>
47
48<p>See the <a href="http://jakarta.apache.org/commons/dbcp/configuration.html">
49DBCP documentation</a> for a complete list of configuration parameters.
50</p>
51
52<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Installation"><strong>Installation</strong></a></font></td></tr><tr><td><blockquote>
53<p>DBCP uses the Jakarta-Commons Database Connection Pool. It relies on
54number of Jakarta-Commons components:
55<ul>
56<li>Jakarta-Commons DBCP</li>
57<li>Jakarta-Commons Collections</li>
58<li>Jakarta-Commons Pool</li>
59</ul>
60These libraries are located in a single JAR at
61<code>$CATALINA_HOME/lib/tomcat-dbcp.jar</code>. However,
62only the classes needed for connection pooling have been included, and the
63packages have been renamed to avoid interfering with applications.
64</p>
65
66</blockquote></td></tr></table>
67
68<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Preventing dB connection pool leaks"><strong>Preventing dB connection pool leaks</strong></a></font></td></tr><tr><td><blockquote>
69
70<p>
71A database connection pool creates and manages a pool of connections
72to a database. Recycling and reusing already existing connections
73to a dB is more efficient than opening a new connection.
74</p>
75
76<p>
77There is one problem with connection pooling.  A web application has
78to explicetely close ResultSet's, Statement's, and Connection's.
79Failure of a web application to close these resources can result in
80them never being available again for reuse, a db connection pool "leak".
81This can eventually result in your web application db connections failing
82if there are no more available connections.</p>
83
84<p>
85There is a solution to this problem.  The Jakarta-Commons DBCP can be
86configured to track and recover these abandoned dB connections.  Not
87only can it recover them, but also generate a stack trace for the code
88which opened these resources and never closed them.</p>
89
90<p>
91To configure a DBCP DataSource so that abandoned dB connections are
92removed and recycled add the following attribute to the
93<code>Resource</code> configuration for your DBCP DataSource:
94<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
95            removeAbandoned="true"
96</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
97When available db connections run low DBCP will recover and recyle
98any abandoned dB connections it finds. The default is <code>false</code>.
99</p>
100
101<p>
102Use the <code>removeAbandonedTimeout</code> attribute to set the number
103of seconds a dB connection has been idle before it is considered abandoned.
104<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
105            removeAbandonedTimeout="60"
106</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
107The default timeout for removing abandoned connections is 300 seconds.
108</p>
109
110<p>
111The <code>logAbandoned</code> attribute can be set to <code>true</code>
112if you want DBCP to log a stack trace of the code which abandoned the
113dB connection resources.
114<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
115            logAbandoned="true"
116</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
117The default is <code>false</code>.
118</p>
119
120</blockquote></td></tr></table>
121
122<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="MySQL DBCP Example"><strong>MySQL DBCP Example</strong></a></font></td></tr><tr><td><blockquote>
123
124<h3>0. Introduction</h3>
125<p>Versions of <a href="http://www.mysql.com/products/mysql/index.html">MySQL</a> and JDBC drivers that have been reported to work:
126<ul>
127<li>MySQL 3.23.47, MySQL 3.23.47 using InnoDB,, MySQL 3.23.58,  MySQL 4.0.1alpha</li>
128<li><a href="http://www.mysql.com/products/connector-j">Connector/J</a> 3.0.11-stable (the official JDBC Driver)</li>
129<li><a href="http://mmmysql.sourceforge.net">mm.mysql</a> 2.0.14 (an old 3rd party JDBC Driver)</li>
130</ul>
131</p>
132
133<p>Before you proceed, don't forget to copy the JDBC Driver's jar into <code>$CATALINA_HOME/lib</code>.</p>
134
135<h3>1. MySQL configuration</h3>
136<p>
137Ensure that you follow these instructions as variations can cause problems.
138</p>
139
140<p>Create a new test user, a new database and a single test table.
141Your MySQL user <strong>must</strong> have a password assigned. The driver
142will fail if you try to connect with an empty password.
143<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
144mysql&gt; GRANT ALL PRIVILEGES ON *.* TO javauser@localhost
145    -&gt;   IDENTIFIED BY 'javadude' WITH GRANT OPTION;
146mysql&gt; create database javatest;
147mysql&gt; use javatest;
148mysql&gt; create table testdata (
149    -&gt;   id int not null auto_increment primary key,
150    -&gt;   foo varchar(25),
151    -&gt;   bar int);
152</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
153<blockquote>
154<strong>Note:</strong> the above user should be removed once testing is
155complete!
156</blockquote>
157</p>
158
159<p>Next insert some test data into the testdata table.
160<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
161mysql&gt; insert into testdata values(null, 'hello', 12345);
162Query OK, 1 row affected (0.00 sec)
163
164mysql&gt; select * from testdata;
165+----+-------+-------+
166| ID | FOO   | BAR   |
167+----+-------+-------+
168|  1 | hello | 12345 |
169+----+-------+-------+
1701 row in set (0.00 sec)
171
172mysql&gt;
173</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
174</p>
175
176<h3>2. Context configuration</h3>
177<p>Configure the JNDI DataSource in Tomcat by adding a declaration for your
178resource to your <a href="config/context.html">Context</a>.</p>
179<p>For example:
180
181<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
182&lt;Context path="/DBTest" docBase="DBTest"
183        debug="5" reloadable="true" crossContext="true"&gt;
184
185    &lt;!-- maxActive: Maximum number of dB connections in pool. Make sure you
186         configure your mysqld max_connections large enough to handle
187         all of your db connections. Set to -1 for no limit.
188         --&gt;
189
190    &lt;!-- maxIdle: Maximum number of idle dB connections to retain in pool.
191         Set to -1 for no limit.  See also the DBCP documentation on this
192         and the minEvictableIdleTimeMillis configuration parameter.
193         --&gt;
194
195    &lt;!-- maxWait: Maximum time to wait for a dB connection to become available
196         in ms, in this example 10 seconds. An Exception is thrown if
197         this timeout is exceeded.  Set to -1 to wait indefinitely.
198         --&gt;
199
200    &lt;!-- username and password: MySQL dB username and password for dB connections  --&gt;
201
202    &lt;!-- driverClassName: Class name for the old mm.mysql JDBC driver is
203         org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
204         Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
205         --&gt;
206   
207    &lt;!-- url: The JDBC connection url for connecting to your MySQL dB.
208         The autoReconnect=true argument to the url makes sure that the
209         mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
210         connection.  mysqld by default closes idle connections after 8 hours.
211         --&gt;
212
213  &lt;Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
214               maxActive="100" maxIdle="30" maxWait="10000"
215               username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
216               url="jdbc:mysql://localhost:3306/javatest?autoReconnect=true"/&gt;
217
218&lt;/Context&gt;
219</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
220</p>
221
222<h3>3. web.xml configuration</h3>
223
224<p>Now create a <code>WEB-INF/web.xml</code> for this test application.
225<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
226&lt;web-app xmlns="http://java.sun.com/xml/ns/j2ee"
227    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
228    xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
229http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
230    version="2.4"&gt;
231  &lt;description&gt;MySQL Test App&lt;/description&gt;
232  &lt;resource-ref&gt;
233      &lt;description&gt;DB Connection&lt;/description&gt;
234      &lt;res-ref-name&gt;jdbc/TestDB&lt;/res-ref-name&gt;
235      &lt;res-type&gt;javax.sql.DataSource&lt;/res-type&gt;
236      &lt;res-auth&gt;Container&lt;/res-auth&gt;
237  &lt;/resource-ref&gt;
238&lt;/web-app&gt;
239</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
240</p>
241
242<h3>4. Test code</h3>
243<p>Now create a simple <code>test.jsp</code> page for use later.
244<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
245&lt;%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %&gt;
246&lt;%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %&gt;
247
248&lt;sql:query var="rs" dataSource="jdbc/TestDB"&gt;
249select id, foo, bar from testdata
250&lt;/sql:query&gt;
251
252&lt;html&gt;
253  &lt;head&gt;
254    &lt;title&gt;DB Test&lt;/title&gt;
255  &lt;/head&gt;
256  &lt;body&gt;
257
258  &lt;h2&gt;Results&lt;/h2&gt;
259 
260&lt;c:forEach var="row" items="${rs.rows}"&gt;
261    Foo ${row.foo}&lt;br/&gt;
262    Bar ${row.bar}&lt;br/&gt;
263&lt;/c:forEach&gt;
264
265  &lt;/body&gt;
266&lt;/html&gt;
267</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
268</p>
269
270<p>That JSP page makes use of <a href="http://java.sun.com/products/jsp/jstl">JSTL</a>'s SQL and Core taglibs. You can get it from Sun's <a href="http://java.sun.com/webservices/downloads/webservicespack.html">Java Web Services Developer Pack</a> or <a href="http://jakarta.apache.org/taglibs/doc/standard-doc/intro.html">Jakarta Taglib Standard 1.1</a> project - just make sure you get a 1.1.x release. Once you have JSTL, copy <code>jstl.jar</code> and <code>standard.jar</code> to your web app's <code>WEB-INF/lib</code> directory.
271
272</p>
273
274<p>Finally deploy your web app into <code>$CATALINA_BASE/webapps</code> either
275as a warfile called <code>DBTest.war</code> or into a sub-directory called
276<code>DBTest</code></p>
277<p>Once deployed, point a browser at
278<code>http://localhost:8080/DBTest/test.jsp</code> to view the fruits of
279your hard work.</p>
280
281</blockquote></td></tr></table>
282
283<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Oracle 8i, 9i & 10g"><strong>Oracle 8i, 9i &amp; 10g</strong></a></font></td></tr><tr><td><blockquote>
284<h3>0.    Introduction</h3>
285
286<p>Oracle requires minimal changes from the MySQL configuration except for the
287usual gotchas :-)</p>
288<p>Drivers for older Oracle versions may be distributed as *.zip files rather
289than *.jar files. Tomcat will only use <code>*.jar</code> files installed in
290<code>$CATALINA_HOME/lib</code>. Therefore <code>classes111.zip</code>
291or <code>classes12.zip</code> will need to be renamed with a <code>.jar</code>
292extension. Since jarfiles are zipfiles, there is no need to unzip and jar these
293files - a simple rename will suffice.</p>
294
295<p>For Oracle 9i onwards you should use <code>oracle.jdbc.OracleDriver</code>
296rather than <code>oracle.jdbc.driver.OracleDriver</code> as Oracle have stated
297that <code>oracle.jdbc.driver.OracleDriver</code> is deprecated and support
298for this driver class will be discontinued in the next major release.
299</p>
300
301<h3>1. Context configuration</h3>
302<p>In a similar manner to the mysql config above, you will need to define your
303Datasource in your <a href="config/context.html">Context</a>. Here we define a
304Datasource called myoracle using the thin driver to connect as user scott,
305password tiger to the sid called mysid. (Note: with the thin driver this sid is
306not the same as the tnsname). The schema used will be the default schema for the
307user scott.</p>
308
309<p>Use of the OCI driver should simply involve a changing thin to oci in the URL string.
310<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
311&lt;Resource name="jdbc/myoracle" auth="Container"
312              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
313              url="jdbc:oracle:thin:@127.0.0.1:1521:mysid"
314              username="scott" password="tiger" maxActive="20" maxIdle="10"
315              maxWait="-1"/&gt; 
316</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
317</p>
318
319<h3>2.    web.xml configuration</h3>
320<p>You should ensure that you respect the element ordering defined by the DTD when you
321create you applications web.xml file.</p>
322<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
323&lt;resource-ref&gt;
324 &lt;description&gt;Oracle Datasource example&lt;/description&gt;
325 &lt;res-ref-name&gt;jdbc/myoracle&lt;/res-ref-name&gt;
326 &lt;res-type&gt;javax.sql.DataSource&lt;/res-type&gt;
327 &lt;res-auth&gt;Container&lt;/res-auth&gt;
328&lt;/resource-ref&gt;
329</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
330<h3>3.   Code example</h3>
331<p>You can use the same example application as above (asuming you create the required DB
332instance, tables etc.) replacing the Datasource code with something like</p>
333<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
334Context initContext = new InitialContext();
335Context envContext  = (Context)initContext.lookup("java:/comp/env");
336DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
337Connection conn = ds.getConnection();
338//etc.
339</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
340</blockquote></td></tr></table>
341
342
343<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="PostgreSQL"><strong>PostgreSQL</strong></a></font></td></tr><tr><td><blockquote>
344<h3>0.    Introduction</h3>
345<p>PostgreSQL is configured in a similar manner to Oracle.</p>
346
347<h3>1. Required files </h3>
348<p>
349Copy the Postgres JDBC jar to $CATALINA_HOME/lib. As with Oracle, the
350jars need to be in this directory in order for DBCP's Classloader to find
351them. This has to be done regardless of which configuration step you take next.
352</p>
353
354<h3>2. Resource configuration</h3>
355
356<p>
357You have two choices here: define a datasource that is shared across all Tomcat
358applications, or define a datasource specifically for one application.
359</p>
360
361<h4>2a. Shared resource configuration</h4>
362<p>
363Use this option if you wish to define a datasource that is shared across
364multiple Tomcat applications, or if you just prefer defining your datasource
365in this file.
366</p>
367<p><i>This author has not had success here, although others have reported so.
368Clarification would be appreciated here.</i></p>
369
370<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
371&lt;Resource name="jdbc/postgres" auth="Container"
372          type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
373          url="jdbc:postgresql://127.0.0.1:5432/mydb"
374          username="myuser" password="mypasswd" maxActive="20" maxIdle="10" maxWait="-1"/&gt;
375</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
376<h4>2b. Application-specific resource configuration</h4>
377
378<p>
379Use this option if you wish to define a datasource specific to your application,
380not visible to other Tomcat applications. This method is less invasive to your
381Tomcat installation.
382</p>
383
384<p>
385Create a resource definition for your <a href="config/context.html">Context</a>.
386The Context element should look something like the following.
387</p>
388
389<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
390&lt;Context path="/someApp" docBase="someApp"
391   crossContext="true" reloadable="true" debug="1"&gt;
392
393&lt;Resource name="jdbc/postgres" auth="Container"
394          type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
395          url="jdbc:postgresql://127.0.0.1:5432/mydb"
396          username="myuser" password="mypasswd" maxActive="20" maxIdle="10"
397maxWait="-1"/&gt;
398&lt;/Context&gt;
399</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
400
401<h3>3. web.xml configuration</h3>
402<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
403&lt;resource-ref&gt;
404 &lt;description&gt;postgreSQL Datasource example&lt;/description&gt;
405 &lt;res-ref-name&gt;jdbc/postgres&lt;/res-ref-name&gt;
406 &lt;res-type&gt;javax.sql.DataSource&lt;/res-type&gt;
407 &lt;res-auth&gt;Container&lt;/res-auth&gt;
408&lt;/resource-ref&gt;
409</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
410
411<h4>4. Accessing the datasource</h4>
412<p>
413When accessing the datasource programmatically, remember to prepend
414<code>java:/comp/env</code> to your JNDI lookup, as in the following snippet of
415code. Note also that "jdbc/postgres" can be replaced with any value you prefer, provided
416you change it in the above resource definition file as well.
417</p>
418
419<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
420InitialContext cxt = new InitialContext();
421if ( cxt == null ) {
422   throw new Exception("Uh oh -- no context!");
423}
424
425DataSource ds = (DataSource) cxt.lookup( "java:/comp/env/jdbc/postgres" );
426
427if ( ds == null ) {
428   throw new Exception("Data source not found!");
429}
430</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
431
432</blockquote></td></tr></table>
433</blockquote></td></tr></table><table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#525D76"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Non-DBCP Solutions"><strong>Non-DBCP Solutions</strong></a></font></td></tr><tr><td><blockquote>
434<p>
435These solutions either utilise a single connection to the database (not recommended for anything other
436than testing!) or some other pooling technology.
437</p>
438</blockquote></td></tr></table><table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#525D76"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Oracle 8i with OCI client"><strong>Oracle 8i with OCI client</strong></a></font></td></tr><tr><td><blockquote>
439<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Introduction"><strong>Introduction</strong></a></font></td></tr><tr><td><blockquote>
440<p>Whilst not strictly addressing the creation of a JNDI DataSource using the OCI client, these notes can be combined with the
441Oracle and DBCP solution above.</p>
442<p>
443In order to use OCI driver, you should have an Oracle client installed. You should have installed
444Oracle8i(8.1.7) client from cd,  and download the suitable JDBC/OCI
445driver(Oracle8i 8.1.7.1 JDBC/OCI Driver) from <a href="http://otn.oracle.com/">otn.oracle.com</a>.
446</p>
447<p>
448After renaming <code>classes12.zip</code> file to <code>classes12.jar</code>
449for Tomcat, copy it into <code>$CATALINA_HOME/lib</code>.
450You may also have to remove the <code>javax.sql.*</code> classes
451from this file depending upon the version of Tomcat and JDK you are using.
452</p>
453</blockquote></td></tr></table>
454
455<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Putting it all together"><strong>Putting it all together</strong></a></font></td></tr><tr><td><blockquote>
456<p>
457Ensure that you have the <code>ocijdbc8.dll</code> or <code>.so</code> in your <code>$PATH</code> or <code>LD_LIBRARY_PATH</code>
458 (possibly in <code>$ORAHOME\bin</code>) and also confirm that the native library can be loaded by a simple test program
459using <code>System.loadLibrary("ocijdbc8");</code>
460</p>
461<p>
462You should next create a simple test servlet or jsp that has these
463<strong>critical lines</strong>:
464</p>
465<div align="left"><table border="0" cellpadding="0" cellspacing="4"><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#ffffff"><pre>
466DriverManager.registerDriver(new
467oracle.jdbc.driver.OracleDriver());
468conn =
469DriverManager.getConnection("jdbc:oracle:oci8:@database","username","password");
470</pre></td><td width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr><tr><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td><td height="1" width="1" bgcolor="#023264"><img border="0" hspace="0" vspace="0" height="1" width="1" src="./images/void.gif"></td></tr></table></div>
471<p>
472where database is of the form <code>host:port:SID</code> Now if you try to access the URL of your
473test servlet/jsp and what you get is a
474<code>ServletException</code> with a root cause of <code>java.lang.UnsatisfiedLinkError:get_env_handle</code>.
475</p>
476<p>
477First, the <code>UnsatisfiedLinkError</code> indicates that you have
478<ul>
479<li>a mismatch between your JDBC classes file and
480your Oracle client version. The giveaway here is the message stating that a needed library file cannot be
481found. For example, you may be using a classes12.zip file from Oracle Version 8.1.6 with a Version 8.1.5
482Oracle client. The classeXXXs.zip file and Oracle client software versions must match.
483</li>
484<li>A <code>$PATH</code>, <code>LD_LIBRARY_PATH</code> problem.</li>
485<li>It has been reported that ignoring the driver you have downloded from otn and using
486the classes12.zip file from the directory <code>$ORAHOME\jdbc\lib</code> will also work.
487</li>
488</ul>
489</p>
490<p>
491Next you may experience the error <code>ORA-06401 NETCMN: invalid driver designator</code>
492</p>
493<p>
494The Oracle documentation says : "Cause: The login (connect) string contains an invalid
495driver designator. Action: Correct the string and re-submit."
496
497Change the database connect string (of the form <code>host:port:SID</code>) with this one:
498<code>(description=(address=(host=myhost)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))</code>
499</p>
500<p>
501<i>Ed. Hmm, I don't think this is really needed if you sort out your TNSNames - but I'm not an Oracle DBA :-)</i>
502</p>
503</blockquote></td></tr></table>
504</blockquote></td></tr></table><table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#525D76"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Common Problems"><strong>Common Problems</strong></a></font></td></tr><tr><td><blockquote>
505<p>Here are some common problems encountered with a web application which
506uses a database and tips for how to solve them.</p>
507
508<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Intermittent dB Connection Failures"><strong>Intermittent dB Connection Failures</strong></a></font></td></tr><tr><td><blockquote>
509<p>
510Tomcat runs within a JVM.  The JVM periodically performs garbage collection
511(GC) to remove java objects which are no longer being used.  When the JVM
512performs GC execution of code within Tomcat freezes. If the maximum time
513configured for establishment of a dB connection is less than the amount
514of time garbage collection took you can get a db conneciton failure.
515</p>
516
517<p>To collect data on how long garbage collection is taking add the
518<code>-verbose:gc</code> argument to your <code>CATALINA_OPTS</code>
519environment variable when starting Tomcat.  When verbose gc is enabled
520your <code>$CATALINA_BASE/logs/catalina.out</code> log file will include
521data for every garbage collection including how long it took.</p>
522
523<p>When your JVM is tuned correctly 99% of the time a GC will take less
524than one second.  The remainder will only take a few seconds.  Rarely,
525if ever should a GC take more than 10 seconds.</p>
526
527<p>Make sure that the db connection timeout is set to 10-15 seconds.
528For the DBCP you set this using the parameter <code>maxWait</code>.</p>
529
530</blockquote></td></tr></table>
531
532<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Random Connection Closed Exceptions"><strong>Random Connection Closed Exceptions</strong></a></font></td></tr><tr><td><blockquote>
533<p>
534These can occur when one request gets a db connection from the connection
535pool and closes it twice.  When using a connection pool, closing the
536connection just returns it to the pool for reuse by another request,
537it doesn't close the connection.  And Tomcat uses multiple threads to
538handle concurrent requests. Here is an example of the sequence
539of events which could cause this error in Tomcat:
540<pre>
541  Request 1 running in Thread 1 gets a db connection.
542
543  Request 1 closes the db connection.
544
545  The JVM switches the running thread to Thread 2
546
547  Request 2 running in Thread 2 gets a db connection
548  (the same db connection just closed by Request 1).
549
550  The JVM switches the running thread back to Thread 1
551
552  Request 1 closes the db connection a second time in a finally block.
553
554  The JVM switches the running thread back to Thread 2
555
556  Request 2 Thread 2 tries to use the db connection but fails
557  because Request 1 closed it.
558</pre>
559Here is an example of properly written code to use a db connection
560obtained from a connection pool:
561<pre>
562  Connection conn = null;
563  Statement stmt = null;  // Or PreparedStatement if needed
564  ResultSet rs = null;
565  try {
566    conn = ... get connection from connection pool ...
567    stmt = conn.createStatement("select ...");
568    rs = stmt.executeQuery();
569    ... iterate through the result set ...
570    rs.close();
571    rs = null;
572    stmt.close();
573    stmt = null;
574    conn.close(); // Return to connection pool
575    conn = null;  // Make sure we don't close it twice
576  } catch (SQLException e) {
577    ... deal with errors ...
578  } finally {
579    // Always make sure result sets and statements are closed,
580    // and the connection is returned to the pool
581    if (rs != null) {
582      try { rs.close(); } catch (SQLException e) { ; }
583      rs = null;
584    }
585    if (stmt != null) {
586      try { stmt.close(); } catch (SQLException e) { ; }
587      stmt = null;
588    }
589    if (conn != null) {
590      try { conn.close(); } catch (SQLException e) { ; }
591      conn = null;
592    }
593  }
594</pre>
595</p>
596
597</blockquote></td></tr></table>
598
599<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="Context versus GlobalNamingResources"><strong>Context versus GlobalNamingResources</strong></a></font></td></tr><tr><td><blockquote>
600<p>
601  Please note that although the above instructions place the JNDI declarations in a Context
602  element, it is possible and sometimes desirable to place these declarations in the
603  <a href="config/globalresources.html">GlobalNamingResources</a> section of the server
604  configuration file.  A resource placed in the GlobalNamingResources section will be shared
605  among the Contexts of the server.
606</p>
607</blockquote></td></tr></table>
608
609<table cellpadding="2" cellspacing="0" border="0"><tr><td bgcolor="#828DA6"><font face="arial,helvetica.sanserif" color="#ffffff"><a name="JNDI Resource Naming and Realm Interaction"><strong>JNDI Resource Naming and Realm Interaction</strong></a></font></td></tr><tr><td><blockquote>
610<p>
611  In order to get Realms to work, the realm must refer to the datasource as
612  defined in the &lt;GlobalNamingResources&gt; or &lt;Context&gt; section, not a datasource as renamed
613  using &lt;ResourceLink&gt;.
614</p>
615</blockquote></td></tr></table> 
616
617</blockquote></td></tr></table></td></tr><!--FOOTER SEPARATOR--><tr><td colspan="2"><hr size="1" noshade></td></tr><!--PAGE FOOTER--><tr><td colspan="2"><div align="center"><font size="-1" color="#525D76"><em>
618        Copyright &copy; 1999-2008, Apache Software Foundation
619        </em></font></div></td></tr></table></body></html>
Note: See TracBrowser for help on using the repository browser.