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