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