blob: 9d2744532828cadf74baf98fe33018a9aa63fdd8 [file] [log] [blame]
刘洪青6266f992017-05-15 21:21:03 +08001<html><head><META http-equiv="Content-Type" content="text/html; charset=iso-8859-1"><title>Apache Tomcat 7 (7.0.77) - The Tomcat JDBC Connection Pool</title><meta name="author" content="Filip Hanik"><style type="text/css" media="print">
2 .noPrint {display: none;}
3 td#mainBody {width: 100%;}
4</style><style type="text/css">
5code {background-color:rgb(224,255,255);padding:0 0.1em;}
6code.attributeName, code.propertyName {background-color:transparent;}
7
8
9table {
10 border-collapse: collapse;
11 text-align: left;
12}
13table *:not(table) {
14 /* Prevent border-collapsing for table child elements like <div> */
15 border-collapse: separate;
16}
17
18th {
19 text-align: left;
20}
21
22
23div.codeBox pre code, code.attributeName, code.propertyName, code.noHighlight, .noHighlight code {
24 background-color: transparent;
25}
26div.codeBox {
27 overflow: auto;
28 margin: 1em 0;
29}
30div.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
45div.codeBox pre.wrap {
46 white-space: pre-wrap;
47}
48
49
50table.defaultTable tr, table.detail-table tr {
51 border: 1px solid #CCC;
52}
53
54table.defaultTable tr:nth-child(even), table.detail-table tr:nth-child(even) {
55 background-color: #FAFBFF;
56}
57
58table.defaultTable tr:nth-child(odd), table.detail-table tr:nth-child(odd) {
59 background-color: #EEEFFF;
60}
61
62table.defaultTable th, table.detail-table th {
63 background-color: #88b;
64 color: #fff;
65}
66
67table.defaultTable th, table.defaultTable td, table.detail-table th, table.detail-table td {
68 padding: 5px 8px;
69}
70
71
72p.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="
80 The Apache Tomcat Servlet/JSP Container
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>The Tomcat JDBC Connection Pool</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="#How_to_use">How to use</a><ol><li><a href="#Additional_features">Additional features</a></li><li><a href="#Inside_the_Apache_Tomcat_Container">Inside the Apache Tomcat Container</a></li><li><a href="#Standalone">Standalone</a></li><li><a href="#JMX">JMX</a></li></ol></li><li><a href="#Attributes">Attributes</a><ol><li><a href="#JNDI_Factory_and_Type">JNDI Factory and Type</a></li><li><a href="#Common_Attributes">Common Attributes</a></li><li><a href="#Tomcat_JDBC_Enhanced_Attributes">Tomcat JDBC Enhanced Attributes</a></li></ol></li><li><a href="#Advanced_usage">Advanced usage</a><ol><li><a href="#JDBC_interceptors">JDBC interceptors</a></li><li><a href="#Configuring_JDBC_interceptors">Configuring JDBC interceptors</a></li><li><a href="#org.apache.tomcat.jdbc.pool.JdbcInterceptor">org.apache.tomcat.jdbc.pool.JdbcInterceptor</a></li><li><a href="#org.apache.tomcat.jdbc.pool.interceptor.ConnectionState">org.apache.tomcat.jdbc.pool.interceptor.ConnectionState</a></li><li><a href="#org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer">org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer</a></li><li><a href="#org.apache.tomcat.jdbc.pool.interceptor.StatementCache">org.apache.tomcat.jdbc.pool.interceptor.StatementCache</a></li><li><a href="#org.apache.tomcat.jdbc.pool.interceptor.StatementDecoratorInterceptor">org.apache.tomcat.jdbc.pool.interceptor.StatementDecoratorInterceptor</a></li><li><a href="#org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor">org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor</a></li><li><a href="#org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport">org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport</a></li><li><a href="#org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx">org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx</a></li><li><a href="#org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer">org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer</a></li></ol></li><li><a href="#Code_Example">Code Example</a><ol><li><a href="#Plain_Ol'_Java">Plain Ol' Java</a></li><li><a href="#As_a_Resource">As a Resource</a></li><li><a href="#Asynchronous_Connection_Retrieval">Asynchronous Connection Retrieval</a></li><li><a href="#Interceptors">Interceptors</a></li><li><a href="#Getting_the_actual_JDBC_connection">Getting the actual JDBC connection</a></li></ol></li><li><a href="#Building">Building</a><ol><li><a href="#Building_from_source">Building from source</a></li></ol></li></ul>
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>The <strong>JDBC Connection Pool <code>org.apache.tomcat.jdbc.pool</code></strong>
86 is a replacement or an alternative to the <a href="http://commons.apache.org/dbcp/">Apache Commons DBCP</a>
87 connection pool.</p>
88
89 <p>So why do we need a new connection pool?</p>
90
91 <p>Here are a few of the reasons:</p>
92 <ol>
93 <li>Commons DBCP 1.x is single threaded. In order to be thread safe
94 Commons locks the entire pool for short periods during both object
95 allocation and object return. Note that this does not apply to
96 Commons DBCP 2.x.</li>
97 <li>Commons DBCP 1.x can be slow. As the number of logical CPUs grows and
98 the number of concurrent threads attempting to borrow or return
99 objects increases, the performance suffers. For highly concurrent
100 systems the impact can be significant. Note that this does not apply
101 to Commons DBCP 2.x.</li>
102 <li>Commons DBCP is over 60 classes. tomcat-jdbc-pool core is 8 classes,
103 hence modifications for future requirement will require much less
104 changes. This is all you need to run the connection pool itself, the
105 rest is gravy.</li>
106 <li>Commons DBCP uses static interfaces. This means you have to use the
107 right version for a given JRE version or you may see
108 <code>NoSuchMethodException</code> exceptions.</li>
109 <li>It's not worth rewriting over 60 classes, when a connection pool can
110 be accomplished with a much simpler implementation.</li>
111 <li>Tomcat jdbc pool implements the ability retrieve a connection
112 asynchronously, without adding additional threads to the library
113 itself.</li>
114 <li>Tomcat jdbc pool is a Tomcat module, it depends on Tomcat JULI, a
115 simplified logging framework used in Tomcat.</li>
116 <li>Retrieve the underlying connection using the
117 <code>javax.sql.PooledConnection</code> interface.</li>
118 <li>Starvation proof. If a pool is empty, and threads are waiting for a
119 connection, when a connection is returned, the pool will awake the
120 correct thread waiting. Most pools will simply starve.</li>
121 </ol>
122
123 <p>Features added over other connection pool implementations</p>
124 <ol>
125 <li>Support for highly concurrent environments and multi core/cpu systems.</li>
126 <li>Dynamic implementation of interface, will support <code>java.sql</code> and <code>javax.sql</code> interfaces for
127 your runtime environment (as long as your JDBC driver does the same), even when compiled with a lower version of the JDK.</li>
128 <li>Validation intervals - we don't have to validate every single time we use the connection, we can do this
129 when we borrow or return the connection, just not more frequent than an interval we can configure.</li>
130 <li>Run-Once query, a configurable query that will be run only once, when the connection to the database is established.
131 Very useful to setup session settings, that you want to exist during the entire time the connection is established.</li>
132 <li>Ability to configure custom interceptors.
133 This allows you to write custom interceptors to enhance the functionality. You can use interceptors to gather query stats,
134 cache session states, reconnect the connection upon failures, retry queries, cache query results, and so on.
135 Your options are endless and the interceptors are dynamic, not tied to a JDK version of a
136 <code>java.sql</code>/<code>javax.sql</code> interface.</li>
137 <li>High performance - we will show some differences in performance later on</li>
138 <li>Extremely simple, due to the very simplified implementation, the line count and source file count are very low, compare with c3p0
139 that has over 200 source files(last time we checked), Tomcat jdbc has a core of 8 files, the connection pool itself is about half
140 that. As bugs may occur, they will be faster to track down, and easier to fix. Complexity reduction has been a focus from inception.</li>
141 <li>Asynchronous connection retrieval - you can queue your request for a connection and receive a <code>Future&lt;Connection&gt;</code> back.</li>
142 <li>Better idle connection handling. Instead of closing connections directly, it can still pool connections and sizes the idle pool with a smarter algorithm.</li>
143 <li>You can decide at what moment connections are considered abandoned, is it when the pool is full, or directly at a timeout
144 by specifying a pool usage threshold.
145 </li>
146 <li>The abandon connection timer will reset upon a statement/query activity. Allowing a connections that is in use for a long time to not timeout.
147 This is achieved using the <code>ResetAbandonedTimer</code>
148 </li>
149 <li>Close connections after they have been connected for a certain time. Age based close upon return to the pool.
150 </li>
151 <li>Get JMX notifications and log entries when connections are suspected for being abandoned. This is similar to
152 the <code>removeAbandonedTimeout</code> but it doesn't take any action, only reports the information.
153 This is achieved using the <code>suspectTimeout</code> attribute.</li>
154 <li>Connections can be retrieved from a <code>java.sql.Driver</code>, <code>javax.sql.DataSource</code> or <code>javax.sql.XADataSource</code>
155 This is achieved using the <code>dataSource</code> and <code>dataSourceJNDI</code> attributes.</li>
156 <li>XA connection support</li>
157 </ol>
158
159
160</blockquote></td></tr></table><table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#525D76"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="How to use"><!--()--></a><a name="How_to_use"><strong>How to use</strong></a></font></td></tr><tr><td><blockquote>
161 <p>
162 Usage of the Tomcat connection pool has been made to be as simple as possible, for those of you that are familiar with commons-dbcp, the
163 transition will be very simple. Moving from other connection pools is also fairly straight forward.
164 </p>
165 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Additional features"><!--()--></a><a name="Additional_features"><strong>Additional features</strong></a></font></td></tr><tr><td><blockquote>
166 <p>The Tomcat connection pool offers a few additional features over what most other pools let you do:</p>
167 <ul>
168 <li><code>initSQL</code> - the ability to run a SQL statement exactly once, when the connection is created</li>
169 <li><code>validationInterval</code> - in addition to running validations on connections, avoid running them too frequently.</li>
170 <li><code>jdbcInterceptors</code> - flexible and pluggable interceptors to create any customizations around the pool,
171 the query execution and the result set handling. More on this in the advanced section.</li>
172 <li><code>fairQueue</code> - Set the fair flag to true to achieve thread fairness or to use asynchronous connection retrieval</li>
173 </ul>
174 </blockquote></td></tr></table>
175 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Inside the Apache Tomcat Container"><!--()--></a><a name="Inside_the_Apache_Tomcat_Container"><strong>Inside the Apache Tomcat Container</strong></a></font></td></tr><tr><td><blockquote>
176 <p>
177 The Tomcat Connection pool is configured as a resource described in <a href="http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html" target="_blank">The Tomcat JDBC documentation</a>
178 With the only difference being that you have to specify the <code>factory</code> attribute and set the value to
179 <code>org.apache.tomcat.jdbc.pool.DataSourceFactory</code>
180 </p>
181 </blockquote></td></tr></table>
182 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Standalone"><strong>Standalone</strong></a></font></td></tr><tr><td><blockquote>
183 <p>
184 The connection pool only has another dependency, and that is on tomcat-juli.jar.
185 To configure the pool in a stand alone project using bean instantiation, the bean to instantiate is
186 <code>org.apache.tomcat.jdbc.pool.DataSource</code>. The same attributes (documented below) as you use to configure a connection
187 pool as a JNDI resource, are used to configure a data source as a bean.
188 </p>
189 </blockquote></td></tr></table>
190 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="JMX"><strong>JMX</strong></a></font></td></tr><tr><td><blockquote>
191 <p>
192 The connection pool object exposes an MBean that can be registered.
193 In order for the connection pool object to create the MBean, the flag <code>jmxEnabled</code> has to be set to true.
194 This doesn't imply that the pool will be registered with an MBean server, merely that the MBean is created.
195 In a container like Tomcat, Tomcat itself registers the DataSource with the MBean server, the
196 <code>org.apache.tomcat.jdbc.pool.DataSource</code> object will then register the actual
197 connection pool MBean.
198 If you're running outside of a container, you can register the DataSource yourself under any object name you specify,
199 and it propagates the registration to the underlying pool. To do this you would call <code>mBeanServer.registerMBean(dataSource.getPool().getJmxPool(),objectname)</code>.
200 Prior to this call, ensure that the pool has been created by calling <code>dataSource.createPool()</code>.
201 </p>
202 </blockquote></td></tr></table>
203
204</blockquote></td></tr></table><table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#525D76"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Attributes"><strong>Attributes</strong></a></font></td></tr><tr><td><blockquote>
205 <p>To provide a very simple switch to and from commons-dbcp and tomcat-jdbc-pool,
206 Most attributes are the same and have the same meaning.</p>
207 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="JNDI Factory and Type"><!--()--></a><a name="JNDI_Factory_and_Type"><strong>JNDI Factory and Type</strong></a></font></td></tr><tr><td><blockquote>
208 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr><tr><td align="left" valign="center"><strong><code class="attributeName">factory</code></strong></td><td align="left" valign="center">
209 <p>factory is required, and the value should be <code>org.apache.tomcat.jdbc.pool.DataSourceFactory</code></p>
210 </td></tr><tr><td align="left" valign="center"><strong><code class="attributeName">type</code></strong></td><td align="left" valign="center">
211 <p>Type should always be <code>javax.sql.DataSource</code> or <code>javax.sql.XADataSource</code></p>
212 <p>Depending on the type a <code>org.apache.tomcat.jdbc.pool.DataSource</code> or a <code>org.apache.tomcat.jdbc.pool.XADataSource</code> will be created.</p>
213 </td></tr></table>
214 </blockquote></td></tr></table>
215
216 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Common Attributes"><!--()--></a><a name="Common_Attributes"><strong>Common Attributes</strong></a></font></td></tr><tr><td><blockquote>
217 <p>These attributes are shared between commons-dbcp and tomcat-jdbc-pool, in some cases default values are different.</p>
218 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr><tr><td align="left" valign="center"><code class="attributeName">defaultAutoCommit</code></td><td align="left" valign="center">
219 <p>(boolean) The default auto-commit state of connections created by this pool. If not set, default is JDBC driver default (If not set then the <code>setAutoCommit</code> method will not be called.)</p>
220 </td></tr><tr><td align="left" valign="center"><code class="attributeName">defaultReadOnly</code></td><td align="left" valign="center">
221 <p>(boolean) The default read-only state of connections created by this pool. If not set then the <code>setReadOnly</code> method will not be called. (Some drivers don't support read only mode, ex: Informix)</p>
222 </td></tr><tr><td align="left" valign="center"><code class="attributeName">defaultTransactionIsolation</code></td><td align="left" valign="center">
223 <p>(String) The default TransactionIsolation state of connections created by this pool. One of the following: (see javadoc )</p>
224 <ul>
225 <li><code>NONE</code></li>
226 <li><code>READ_COMMITTED</code></li>
227 <li><code>READ_UNCOMMITTED</code></li>
228 <li><code>REPEATABLE_READ</code></li>
229 <li><code>SERIALIZABLE</code></li>
230 </ul>
231 <p>If not set, the method will not be called and it defaults to the JDBC driver.</p>
232 </td></tr><tr><td align="left" valign="center"><code class="attributeName">defaultCatalog</code></td><td align="left" valign="center">
233 <p>(String) The default catalog of connections created by this pool.</p>
234 </td></tr><tr><td align="left" valign="center"><strong><code class="attributeName">driverClassName</code></strong></td><td align="left" valign="center">
235 <p>(String) The fully qualified Java class name of the JDBC driver to be used. The driver has to be accessible
236 from the same classloader as tomcat-jdbc.jar
237 </p>
238 </td></tr><tr><td align="left" valign="center"><strong><code class="attributeName">username</code></strong></td><td align="left" valign="center">
239 <p>(String) The connection username to be passed to our JDBC driver to establish a connection.
240 Note that method <code>DataSource.getConnection(username,password)</code>
241 by default will not use credentials passed into the method,
242 but will use the ones configured here. See <code>alternateUsernameAllowed</code>
243 property for more details.
244 </p>
245 </td></tr><tr><td align="left" valign="center"><strong><code class="attributeName">password</code></strong></td><td align="left" valign="center">
246 <p>(String) The connection password to be passed to our JDBC driver to establish a connection.
247 Note that method <code>DataSource.getConnection(username,password)</code>
248 by default will not use credentials passed into the method,
249 but will use the ones configured here. See <code>alternateUsernameAllowed</code>
250 property for more details.
251 </p>
252 </td></tr><tr><td align="left" valign="center"><code class="attributeName">maxActive</code></td><td align="left" valign="center">
253 <p>(int) The maximum number of active connections that can be allocated from this pool at the same time.
254 The default value is <code>100</code></p>
255 </td></tr><tr><td align="left" valign="center"><code class="attributeName">maxIdle</code></td><td align="left" valign="center">
256 <p>(int) The maximum number of connections that should be kept in the pool at all times.
257 Default value is <code>maxActive</code>:<code>100</code>
258 Idle connections are checked periodically (if enabled) and
259 connections that been idle for longer than <code>minEvictableIdleTimeMillis</code>
260 will be released. (also see <code>testWhileIdle</code>)</p>
261 </td></tr><tr><td align="left" valign="center"><code class="attributeName">minIdle</code></td><td align="left" valign="center">
262 <p>
263 (int) The minimum number of established connections that should be kept in the pool at all times.
264 The connection pool can shrink below this number if validation queries fail.
265 Default value is derived from <code>initialSize</code>:<code>10</code> (also see <code>testWhileIdle</code>)
266 </p>
267 </td></tr><tr><td align="left" valign="center"><code class="attributeName">initialSize</code></td><td align="left" valign="center">
268 <p>(int)The initial number of connections that are created when the pool is started.
269 Default value is <code>10</code></p>
270 </td></tr><tr><td align="left" valign="center"><code class="attributeName">maxWait</code></td><td align="left" valign="center">
271 <p>(int) The maximum number of milliseconds that the pool will wait (when there are no available connections)
272 for a connection to be returned before throwing an exception.
273 Default value is <code>30000</code> (30 seconds)</p>
274 </td></tr><tr><td align="left" valign="center"><code class="attributeName">testOnBorrow</code></td><td align="left" valign="center">
275 <p>(boolean) The indication of whether objects will be validated before being borrowed from the pool.
276 If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another.
277 In order to have a more efficient validation, see <code>validationInterval</code>.
278 Default value is <code>false</code>
279 </p>
280 </td></tr><tr><td align="left" valign="center"><code class="attributeName">testOnConnect</code></td><td align="left" valign="center">
281 <p>(boolean) The indication of whether objects will be validated when a connection is first created.
282 If an object fails to validate, it will be throw <code>SQLException</code>.
283 Default value is <code>false</code>
284 </p>
285 </td></tr><tr><td align="left" valign="center"><code class="attributeName">testOnReturn</code></td><td align="left" valign="center">
286 <p>(boolean) The indication of whether objects will be validated before being returned to the pool.
287 The default value is <code>false</code>.
288 </p>
289 </td></tr><tr><td align="left" valign="center"><code class="attributeName">testWhileIdle</code></td><td align="left" valign="center">
290 <p>(boolean) The indication of whether objects will be validated by the idle object evictor (if any).
291 If an object fails to validate, it will be dropped from the pool.
292 The default value is <code>false</code> and this property has to be set in order for the
293 pool cleaner/test thread is to run (also see <code>timeBetweenEvictionRunsMillis</code>)
294 </p>
295 </td></tr><tr><td align="left" valign="center"><code class="attributeName">validationQuery</code></td><td align="left" valign="center">
296 <p>(String) The SQL query that will be used to validate connections from this pool before returning them to the caller.
297 If specified, this query does not have to return any data, it just can't throw a <code>SQLException</code>.
298 The default value is <code>null</code>.
299 If not specified, connections will be validation by the isValid() method.
300 Example values are <code>SELECT 1</code>(mysql), <code>select 1 from dual</code>(oracle), <code>SELECT 1</code>(MS Sql Server)
301 </p>
302 </td></tr><tr><td align="left" valign="center"><code class="attributeName">validationQueryTimeout</code></td><td align="left" valign="center">
303 <p>(int) The timeout in seconds before a connection validation queries fail. This works by calling
304 <code>java.sql.Statement.setQueryTimeout(seconds)</code> on the statement that executes the <code>validationQuery</code>.
305 The pool itself doesn't timeout the query, it is still up to the JDBC driver to enforce query timeouts.
306 A value less than or equal to zero will disable this feature.
307 The default value is <code>-1</code>.
308 </p>
309 </td></tr><tr><td align="left" valign="center"><code class="attributeName">validatorClassName</code></td><td align="left" valign="center">
310 <p>(String) The name of a class which implements the
311 <code>org.apache.tomcat.jdbc.pool.Validator</code> interface and
312 provides a no-arg constructor (may be implicit). If specified, the
313 class will be used to create a Validator instance which is then used
314 instead of any validation query to validate connections. The default
315 value is <code>null</code>. An example value is
316 <code>com.mycompany.project.SimpleValidator</code>.
317 </p>
318 </td></tr><tr><td align="left" valign="center"><code class="attributeName">timeBetweenEvictionRunsMillis</code></td><td align="left" valign="center">
319 <p>(int) The number of milliseconds to sleep between runs of the idle connection validation/cleaner thread.
320 This value should not be set under 1 second. It dictates how often we check for idle, abandoned connections, and how often
321 we validate idle connections.
322 The default value is <code>5000</code> (5 seconds). <br>
323 </p>
324 </td></tr><tr><td align="left" valign="center"><code class="attributeName">numTestsPerEvictionRun</code></td><td align="left" valign="center">
325 <p>(int) Property not used in tomcat-jdbc-pool.</p>
326 </td></tr><tr><td align="left" valign="center"><code class="attributeName">minEvictableIdleTimeMillis</code></td><td align="left" valign="center">
327 <p>(int) The minimum amount of time an object may sit idle in the pool before it is eligible for eviction.
328 The default value is <code>60000</code> (60 seconds).</p>
329 </td></tr><tr><td align="left" valign="center"><code class="attributeName">accessToUnderlyingConnectionAllowed</code></td><td align="left" valign="center">
330 <p>(boolean) Property not used. Access can be achieved by calling <code>unwrap</code> on the pooled connection.
331 see <code>javax.sql.DataSource</code> interface, or call <code>getConnection</code> through reflection or
332 cast the object as <code>javax.sql.PooledConnection</code></p>
333 </td></tr><tr><td align="left" valign="center"><code class="attributeName">removeAbandoned</code></td><td align="left" valign="center">
334 <p>(boolean) Flag to remove abandoned connections if they exceed the <code>removeAbandonedTimeout</code>.
335 If set to true a connection is considered abandoned and eligible for removal if it has been in use
336 longer than the <code>removeAbandonedTimeout</code> Setting this to <code>true</code> can recover db connections from
337 applications that fail to close a connection. See also <code>logAbandoned</code>
338 The default value is <code>false</code>.</p>
339 </td></tr><tr><td align="left" valign="center"><code class="attributeName">removeAbandonedTimeout</code></td><td align="left" valign="center">
340 <p>(int) Timeout in seconds before an abandoned(in use) connection can be removed.
341 The default value is <code>60</code> (60 seconds). The value should be set to the longest running query your applications
342 might have.</p>
343 </td></tr><tr><td align="left" valign="center"><code class="attributeName">logAbandoned</code></td><td align="left" valign="center">
344 <p>(boolean) Flag to log stack traces for application code which abandoned a Connection.
345 Logging of abandoned Connections adds overhead for every Connection borrow because a stack trace has to be generated.
346 The default value is <code>false</code>.</p>
347 </td></tr><tr><td align="left" valign="center"><code class="attributeName">connectionProperties</code></td><td align="left" valign="center">
348 <p>(String) The connection properties that will be sent to our JDBC driver when establishing new connections.
349 Format of the string must be [propertyName=property;]*
350 NOTE - The "user" and "password" properties will be passed explicitly, so they do not need to be included here.
351 The default value is <code>null</code>.</p>
352 </td></tr><tr><td align="left" valign="center"><code class="attributeName">poolPreparedStatements</code></td><td align="left" valign="center">
353 <p>(boolean) Property not used.</p>
354 </td></tr><tr><td align="left" valign="center"><code class="attributeName">maxOpenPreparedStatements</code></td><td align="left" valign="center">
355 <p>(int) Property not used.</p>
356 </td></tr></table>
357
358 </blockquote></td></tr></table>
359
360 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Tomcat JDBC Enhanced Attributes"><!--()--></a><a name="Tomcat_JDBC_Enhanced_Attributes"><strong>Tomcat JDBC Enhanced Attributes</strong></a></font></td></tr><tr><td><blockquote>
361
362 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr><tr><td align="left" valign="center"><code class="attributeName">initSQL</code></td><td align="left" valign="center">
363 <p>(String) A custom query to be run when a connection is first created.
364 The default value is <code>null</code>.</p>
365 </td></tr><tr><td align="left" valign="center"><code class="attributeName">jdbcInterceptors</code></td><td align="left" valign="center">
366 <p>(String) A semicolon separated list of classnames extending
367 <code>org.apache.tomcat.jdbc.pool.JdbcInterceptor</code> class.
368 See <a href="#Configuring_JDBC_interceptors">Configuring JDBC interceptors</a>
369 below for more detailed description of syntax and examples.
370 </p>
371 <p>
372 These interceptors will be inserted as an interceptor into the chain
373 of operations on a <code>java.sql.Connection</code> object.
374 The default value is <code>null</code>.
375 </p>
376 <p>
377 Predefined interceptors:<br>
378 <code>org.apache.tomcat.jdbc.pool.interceptor.<br>ConnectionState</code>
379 - keeps track of auto commit, read only, catalog and transaction isolation level.<br>
380 <code>org.apache.tomcat.jdbc.pool.interceptor.<br>StatementFinalizer</code>
381 - keeps track of opened statements, and closes them when the connection is returned to the pool.
382 </p>
383 <p>
384 More predefined interceptors are described in detail in the
385 <a href="#JDBC_interceptors">JDBC Interceptors section</a>.
386 </p>
387 </td></tr><tr><td align="left" valign="center"><code class="attributeName">validationInterval</code></td><td align="left" valign="center">
388 <p>(long) avoid excess validation, only run validation at most at this frequency - time in milliseconds.
389 If a connection is due for validation, but has been validated previously within this interval, it will not be validated again.
390 The default value is <code>3000</code> (3 seconds).</p>
391 </td></tr><tr><td align="left" valign="center"><code class="attributeName">jmxEnabled</code></td><td align="left" valign="center">
392 <p>(boolean) Register the pool with JMX or not.
393 The default value is <code>true</code>.</p>
394 </td></tr><tr><td align="left" valign="center"><code class="attributeName">fairQueue</code></td><td align="left" valign="center">
395 <p>(boolean) Set to true if you wish that calls to getConnection should be treated
396 fairly in a true FIFO fashion. This uses the <code>org.apache.tomcat.jdbc.pool.FairBlockingQueue</code>
397 implementation for the list of the idle connections. The default value is <code>true</code>.
398 This flag is required when you want to use asynchronous connection retrieval.<br>
399 Setting this flag ensures that threads receive connections in the order they arrive.<br>
400 During performance tests, there is a very large difference in how locks
401 and lock waiting is implemented. When <code>fairQueue=true</code>
402 there is a decision making process based on what operating system the system is running.
403 If the system is running on Linux (property <code>os.name=Linux</code>.
404 To disable this Linux specific behavior and still use the fair queue, simply add the property
405 <code>org.apache.tomcat.jdbc.pool.FairBlockingQueue.ignoreOS=true</code> to your system properties
406 before the connection pool classes are loaded.
407 </p>
408 </td></tr><tr><td align="left" valign="center"><code class="attributeName">abandonWhenPercentageFull</code></td><td align="left" valign="center">
409 <p>(int) Connections that have been abandoned (timed out) wont get closed and reported up unless
410 the number of connections in use are above the percentage defined by <code>abandonWhenPercentageFull</code>.
411 The value should be between 0-100.
412 The default value is <code>0</code>, which implies that connections are eligible for closure as soon
413 as <code>removeAbandonedTimeout</code> has been reached.</p>
414 </td></tr><tr><td align="left" valign="center"><code class="attributeName">maxAge</code></td><td align="left" valign="center">
415 <p>(long) Time in milliseconds to keep this connection. When a connection is returned to the pool,
416 the pool will check to see if the <code>now - time-when-connected &gt; maxAge</code> has been reached,
417 and if so, it closes the connection rather than returning it to the pool.
418 The default value is <code>0</code>, which implies that connections will be left open and no age check
419 will be done upon returning the connection to the pool.</p>
420 </td></tr><tr><td align="left" valign="center"><code class="attributeName">useEquals</code></td><td align="left" valign="center">
421 <p>(boolean) Set to true if you wish the <code>ProxyConnection</code> class to use <code>String.equals</code> and set to <code>false</code>
422 when you wish to use <code>==</code> when comparing method names. This property does not apply to added interceptors as those are configured individually.
423 The default value is <code>true</code>.
424 </p>
425 </td></tr><tr><td align="left" valign="center"><code class="attributeName">suspectTimeout</code></td><td align="left" valign="center">
426 <p>(int) Timeout value in seconds. Default value is <code>0</code>.<br>
427 Similar to to the <code>removeAbandonedTimeout</code> value but instead of treating the connection
428 as abandoned, and potentially closing the connection, this simply logs the warning if
429 <code>logAbandoned</code> is set to true. If this value is equal or less than 0, no suspect
430 checking will be performed. Suspect checking only takes place if the timeout value is larger than 0 and
431 the connection was not abandoned or if abandon check is disabled. If a connection is suspect a WARN message gets
432 logged and a JMX notification gets sent once.
433 </p>
434 </td></tr><tr><td align="left" valign="center"><code class="attributeName">rollbackOnReturn</code></td><td align="left" valign="center">
435 <p>(boolean) If <code>autoCommit==false</code> then the pool can terminate the transaction by calling rollback on the connection as it is returned to the pool
436 Default value is <code>false</code>.<br>
437 </p>
438 </td></tr><tr><td align="left" valign="center"><code class="attributeName">commitOnReturn</code></td><td align="left" valign="center">
439 <p>(boolean) If <code>autoCommit==false</code> then the pool can complete the transaction by calling commit on the connection as it is returned to the pool
440 If <code>rollbackOnReturn==true</code> then this attribute is ignored.
441 Default value is <code>false</code>.<br>
442 </p>
443 </td></tr><tr><td align="left" valign="center"><code class="attributeName">alternateUsernameAllowed</code></td><td align="left" valign="center">
444 <p>(boolean) By default, the jdbc-pool will ignore the
445 <a href="http://docs.oracle.com/javase/6/docs/api/javax/sql/DataSource.html#getConnection(java.lang.String,%20java.lang.String)"><code>DataSource.getConnection(username,password)</code></a>
446 call, and simply return a previously pooled connection under the globally configured properties <code>username</code> and <code>password</code>, for performance reasons.
447 </p>
448 <p>
449 The pool can however be configured to allow use of different credentials
450 each time a connection is requested. To enable the functionality described in the
451 <a href="http://docs.oracle.com/javase/6/docs/api/javax/sql/DataSource.html#getConnection(java.lang.String,%20java.lang.String)"><code>DataSource.getConnection(username,password)</code></a>
452 call, simply set the property <code>alternateUsernameAllowed</code>
453 to <code>true</code>.<br>
454 Should you request a connection with the credentials user1/password1 and the connection
455 was previously connected using different user2/password2, the connection will be closed,
456 and reopened with the requested credentials. This way, the pool size is still managed
457 on a global level, and not on a per schema level. <br>
458 The default value is <code>false</code>.<br>
459 This property was added as an enhancement to <a href="https://bz.apache.org/bugzilla/show_bug.cgi?id=50025">bug 50025</a>.
460 </p>
461 </td></tr><tr><td align="left" valign="center"><code class="attributeName">dataSource</code></td><td align="left" valign="center">
462 <p>(javax.sql.DataSource) Inject a data source to the connection pool, and the pool will use the data source to retrieve connections instead of establishing them using the <code>java.sql.Driver</code> interface.
463 This is useful when you wish to pool XA connections or connections established using a data source instead of a connection string. Default value is <code>null</code>
464 </p>
465 </td></tr><tr><td align="left" valign="center"><code class="attributeName">dataSourceJNDI</code></td><td align="left" valign="center">
466 <p>(String) The JNDI name for a data source to be looked up in JNDI and then used to establish connections to the database. See the <code>dataSource</code> attribute. Default value is <code>null</code>
467 </p>
468 </td></tr><tr><td align="left" valign="center"><code class="attributeName">useDisposableConnectionFacade</code></td><td align="left" valign="center">
469 <p>(boolean) Set this to true if you wish to put a facade on your connection so that it cannot be reused after it has been closed. This prevents a thread holding on to a
470 reference of a connection it has already called closed on, to execute queries on it. Default value is <code>true</code>.
471 </p>
472 </td></tr><tr><td align="left" valign="center"><code class="attributeName">logValidationErrors</code></td><td align="left" valign="center">
473 <p>(boolean) Set this to true to log errors during the validation phase to the log file. If set to true, errors will be logged as SEVERE. Default value is <code>false</code> for backwards compatibility.
474 </p>
475 </td></tr><tr><td align="left" valign="center"><code class="attributeName">propagateInterruptState</code></td><td align="left" valign="center">
476 <p>(boolean) Set this to true to propagate the interrupt state for a thread that has been interrupted (not clearing the interrupt state). Default value is <code>false</code> for backwards compatibility.
477 </p>
478 </td></tr><tr><td align="left" valign="center"><code class="attributeName">ignoreExceptionOnPreLoad</code></td><td align="left" valign="center">
479 <p>(boolean) Flag whether ignore error of connection creation while initializing the pool.
480 Set to true if you want to ignore error of connection creation while initializing the pool.
481 Set to false if you want to fail the initialization of the pool by throwing exception.
482 The default value is <code>false</code>.
483 </p>
484 </td></tr><tr><td align="left" valign="center"><code class="attributeName">useStatementFacade</code></td><td align="left" valign="center">
485 <p>(boolean) Set this to true if you wish to wrap statements in order to
486 enable <code>equals()</code> and <code>hashCode()</code> methods to be
487 called on the closed statements if any statement proxy is set.
488 Default value is <code>true</code>.
489 </p>
490 </td></tr></table>
491 </blockquote></td></tr></table>
492</blockquote></td></tr></table><table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#525D76"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Advanced usage"><!--()--></a><a name="Advanced_usage"><strong>Advanced usage</strong></a></font></td></tr><tr><td><blockquote>
493 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="JDBC interceptors"><!--()--></a><a name="JDBC_interceptors"><strong>JDBC interceptors</strong></a></font></td></tr><tr><td><blockquote>
494 <p>To see an example of how to use an interceptor, take a look at
495 <code>org.apache.tomcat.jdbc.pool.interceptor.ConnectionState</code>.
496 This simple interceptor is a cache of three attributes, transaction isolation level, auto commit and read only state,
497 in order for the system to avoid not needed roundtrips to the database.
498 </p>
499 <p>Further interceptors will be added to the core of the pool as the need arises. Contributions are always welcome!</p>
500 <p>Interceptors are of course not limited to just <code>java.sql.Connection</code> but can be used to wrap any
501 of the results from a method invocation as well. You could build query performance analyzer that provides JMX notifications when a
502 query is running longer than the expected time.</p>
503 </blockquote></td></tr></table>
504 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Configuring JDBC interceptors"><!--()--></a><a name="Configuring_JDBC_interceptors"><strong>Configuring JDBC interceptors</strong></a></font></td></tr><tr><td><blockquote>
505 <p>Configuring JDBC interceptors is done using the <b>jdbcInterceptors</b> property.
506 The property contains a list of semicolon separated class names. If the
507 classname is not fully qualified it will be prefixed with the
508 <code>org.apache.tomcat.jdbc.pool.interceptor.</code> prefix.
509 </p>
510 <p>Example:<br>
511 <code>
512 jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
513 org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
514 </code>
515 <br>
516 is the same as
517 <br>
518 <code> jdbcInterceptors="ConnectionState;StatementFinalizer"</code>
519 </p>
520 <p>
521 Interceptors can have properties as well. Properties for an interceptor
522 are specified within parentheses after the class name. Several properties
523 are separated by commas.
524 </p>
525 <p>Example:<br>
526 <code>
527 jdbcInterceptors="ConnectionState;StatementFinalizer(useEquals=true)"
528 </code>
529 </p>
530 <p>
531 Extra whitespace characters around class names, property names and values
532 are ignored.
533 </p>
534 </blockquote></td></tr></table>
535 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="org.apache.tomcat.jdbc.pool.JdbcInterceptor"><strong>org.apache.tomcat.jdbc.pool.JdbcInterceptor</strong></a></font></td></tr><tr><td><blockquote>
536 <p>Abstract base class for all interceptors, can not be instantiated.</p>
537 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr><tr><td align="left" valign="center"><code class="attributeName">useEquals</code></td><td align="left" valign="center">
538 <p>(boolean) Set to true if you wish the <code>ProxyConnection</code> class to use <code>String.equals</code> and set to <code>false</code>
539 when you wish to use <code>==</code> when comparing method names.
540 The default value is <code>true</code>.
541 </p>
542 </td></tr></table>
543 </blockquote></td></tr></table>
544 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState"><strong>org.apache.tomcat.jdbc.pool.interceptor.ConnectionState</strong></a></font></td></tr><tr><td><blockquote>
545 <p>Caches the connection for the following attributes <code>autoCommit</code>, <code>readOnly</code>,
546 <code>transactionIsolation</code> and <code>catalog</code>.
547 It is a performance enhancement to avoid roundtrip to the database when getters are called or setters are called with an already set value.
548 </p>
549 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr></table>
550 </blockquote></td></tr></table>
551 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"><strong>org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer</strong></a></font></td></tr><tr><td><blockquote>
552 <p>Keeps track of all statements created using <code>createStatement</code>, <code>prepareStatement</code> or <code>prepareCall</code>
553 and closes these statements when the connection is returned to the pool.
554 </p>
555 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr></table>
556 </blockquote></td></tr></table>
557 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="org.apache.tomcat.jdbc.pool.interceptor.StatementCache"><strong>org.apache.tomcat.jdbc.pool.interceptor.StatementCache</strong></a></font></td></tr><tr><td><blockquote>
558 <p>Caches <code>PreparedStatement</code> and/or <code>CallableStatement</code>
559 instances on a connection.
560 </p>
561 <p>The statements are cached per connection.
562 The count limit is counted globally for all connections that belong to
563 the same pool. Once the count reaches <code>max</code>, subsequent
564 statements are not returned to the cache and are closed immediately.
565 </p>
566 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr><tr><td align="left" valign="center"><code class="attributeName">prepared</code></td><td align="left" valign="center">
567 <p>(boolean as String) Enable caching of <code>PreparedStatement</code>
568 instances created using <code>prepareStatement</code> calls.
569 The default value is <code>true</code>.
570 </p>
571 </td></tr><tr><td align="left" valign="center"><code class="attributeName">callable</code></td><td align="left" valign="center">
572 <p>(boolean as String) Enable caching of <code>CallableStatement</code>
573 instances created using <code>prepareCall</code> calls.
574 The default value is <code>false</code>.
575 </p>
576 </td></tr><tr><td align="left" valign="center"><code class="attributeName">max</code></td><td align="left" valign="center">
577 <p>(int as String) Limit on the count of cached statements across
578 the connection pool.
579 The default value is <code>50</code>.
580 </p>
581 </td></tr></table>
582 </blockquote></td></tr></table>
583 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="org.apache.tomcat.jdbc.pool.interceptor.StatementDecoratorInterceptor"><strong>org.apache.tomcat.jdbc.pool.interceptor.StatementDecoratorInterceptor</strong></a></font></td></tr><tr><td><blockquote>
584 <p>See <a href="http://bz.apache.org/bugzilla/show_bug.cgi?id=48392">48392</a>. Interceptor to wrap statements and result sets in order to prevent access to the actual connection
585 using the methods <code>ResultSet.getStatement().getConnection()</code> and <code>Statement.getConnection()</code>
586 </p>
587 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr></table>
588 </blockquote></td></tr></table>
589 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor"><strong>org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor</strong></a></font></td></tr><tr><td><blockquote>
590 <p>Automatically calls <code>java.sql.Statement.setQueryTimeout(seconds)</code> when a new statement is created.
591 The pool itself doesn't timeout the query, it is still up to the JDBC driver to enforce query timeouts.
592 </p>
593 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr><tr><td align="left" valign="center"><strong><code class="attributeName">queryTimeout</code></strong></td><td align="left" valign="center">
594 <p>(int as String) The number of seconds to set for the query timeout.
595 A value less than or equal to zero will disable this feature.
596 The default value is <code>1</code> seconds.
597 </p>
598 </td></tr></table>
599 </blockquote></td></tr></table>
600 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport"><strong>org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport</strong></a></font></td></tr><tr><td><blockquote>
601 <p>Keeps track of query performance and issues log entries when queries exceed a time threshold of fail.
602 The log level used is <code>WARN</code>
603 </p>
604 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr><tr><td align="left" valign="center"><code class="attributeName">threshold</code></td><td align="left" valign="center">
605 <p>(int as String) The number of milliseconds a query has to exceed before issuing a log alert.
606 The default value is <code>1000</code> milliseconds.
607 </p>
608 </td></tr><tr><td align="left" valign="center"><code class="attributeName">maxQueries</code></td><td align="left" valign="center">
609 <p>(int as String) The maximum number of queries to keep track of in order to preserve memory space.
610 A value less than or equal to 0 will disable this feature.
611 The default value is <code>1000</code>.
612 </p>
613 </td></tr></table>
614 </blockquote></td></tr></table>
615 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx"><strong>org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx</strong></a></font></td></tr><tr><td><blockquote>
616 <p>Extends the <code>SlowQueryReport</code> and in addition to log entries it issues JMX notification
617 for monitoring tools to react to. Inherits all the attributes from its parent class.
618 This class uses Tomcat's JMX engine so it wont work outside of the Tomcat container.
619 By default, JMX notifications are sent through the ConnectionPool mbean if it is enabled.
620 The <code>SlowQueryReportJmx</code> can also register an MBean if <code>notifyPool=false</code>
621 </p>
622 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr><tr><td align="left" valign="center"><code class="attributeName">notifyPool</code></td><td align="left" valign="center">
623 <p>(boolean as String) Set to false if you want JMX notifications to go to the <code>SlowQueryReportJmx</code> MBean
624 The default value is <code>true</code>.
625 </p>
626 </td></tr><tr><td align="left" valign="center"><code class="attributeName">objectName</code></td><td align="left" valign="center">
627 <p>(String) Define a valid <code>javax.management.ObjectName</code> string that will be used to register this object with the platform mbean server
628 The default value is <code>null</code> and the object will be registered using
629 tomcat.jdbc:type=org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx,name=the-name-of-the-pool
630 </p>
631 </td></tr></table>
632 </blockquote></td></tr></table>
633 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer"><strong>org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer</strong></a></font></td></tr><tr><td><blockquote>
634 <p>
635 The abandoned timer starts when a connection is checked out from the pool.
636 This means if you have a 30second timeout and run 10x10second queries using the connection
637 it will be marked abandoned and potentially reclaimed depending on the <code>abandonWhenPercentageFull</code>
638 attribute.
639 Using this interceptor it will reset the checkout timer every time you perform an operation on the connection or execute a
640 query successfully.
641 </p>
642 <table border="1" cellpadding="5"><tr><th width="15%" bgcolor="#023264"><font color="#ffffff">Attribute</font></th><th width="85%" bgcolor="#023264"><font color="#ffffff">Description</font></th></tr></table>
643 </blockquote></td></tr></table>
644</blockquote></td></tr></table><table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#525D76"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Code Example"><!--()--></a><a name="Code_Example"><strong>Code Example</strong></a></font></td></tr><tr><td><blockquote>
645 <p>Other examples of Tomcat configuration for JDBC usage can be found <a href="http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html">in the Tomcat documentation</a>. </p>
646 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Plain Ol' Java"><!--()--></a><a name="Plain_Ol'_Java"><strong>Plain Ol' Java</strong></a></font></td></tr><tr><td><blockquote>
647 <p>Here is a simple example of how to create and use a data source.</p>
648<div class="codeBox"><pre><code> import java.sql.Connection;
649 import java.sql.ResultSet;
650 import java.sql.Statement;
651
652 import org.apache.tomcat.jdbc.pool.DataSource;
653 import org.apache.tomcat.jdbc.pool.PoolProperties;
654
655 public class SimplePOJOExample {
656
657 public static void main(String[] args) throws Exception {
658 PoolProperties p = new PoolProperties();
659 p.setUrl("jdbc:mysql://localhost:3306/mysql");
660 p.setDriverClassName("com.mysql.jdbc.Driver");
661 p.setUsername("root");
662 p.setPassword("password");
663 p.setJmxEnabled(true);
664 p.setTestWhileIdle(false);
665 p.setTestOnBorrow(true);
666 p.setValidationQuery("SELECT 1");
667 p.setTestOnReturn(false);
668 p.setValidationInterval(30000);
669 p.setTimeBetweenEvictionRunsMillis(30000);
670 p.setMaxActive(100);
671 p.setInitialSize(10);
672 p.setMaxWait(10000);
673 p.setRemoveAbandonedTimeout(60);
674 p.setMinEvictableIdleTimeMillis(30000);
675 p.setMinIdle(10);
676 p.setLogAbandoned(true);
677 p.setRemoveAbandoned(true);
678 p.setJdbcInterceptors(
679 "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
680 "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
681 DataSource datasource = new DataSource();
682 datasource.setPoolProperties(p);
683
684 Connection con = null;
685 try {
686 con = datasource.getConnection();
687 Statement st = con.createStatement();
688 ResultSet rs = st.executeQuery("select * from user");
689 int cnt = 1;
690 while (rs.next()) {
691 System.out.println((cnt++)+". Host:" +rs.getString("Host")+
692 " User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
693 }
694 rs.close();
695 st.close();
696 } finally {
697 if (con!=null) try {con.close();}catch (Exception ignore) {}
698 }
699 }
700
701 }</code></pre></div>
702 </blockquote></td></tr></table>
703 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="As a Resource"><!--()--></a><a name="As_a_Resource"><strong>As a Resource</strong></a></font></td></tr><tr><td><blockquote>
704 <p>And here is an example on how to configure a resource for JNDI lookups</p>
705<div class="codeBox"><pre><code>&lt;Resource name="jdbc/TestDB"
706 auth="Container"
707 type="javax.sql.DataSource"
708 factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
709 testWhileIdle="true"
710 testOnBorrow="true"
711 testOnReturn="false"
712 validationQuery="SELECT 1"
713 validationInterval="30000"
714 timeBetweenEvictionRunsMillis="30000"
715 maxActive="100"
716 minIdle="10"
717 maxWait="10000"
718 initialSize="10"
719 removeAbandonedTimeout="60"
720 removeAbandoned="true"
721 logAbandoned="true"
722 minEvictableIdleTimeMillis="30000"
723 jmxEnabled="true"
724 jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
725 org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
726 username="root"
727 password="password"
728 driverClassName="com.mysql.jdbc.Driver"
729 url="jdbc:mysql://localhost:3306/mysql"/&gt;</code></pre></div>
730
731 </blockquote></td></tr></table>
732 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Asynchronous Connection Retrieval"><!--()--></a><a name="Asynchronous_Connection_Retrieval"><strong>Asynchronous Connection Retrieval</strong></a></font></td></tr><tr><td><blockquote>
733 <p> The Tomcat JDBC connection pool supports asynchronous connection retrieval without adding additional threads to the
734 pool library. It does this by adding a method to the data source called <code>Future&lt;Connection&gt; getConnectionAsync()</code>.
735 In order to use the async retrieval, two conditions must be met:
736 </p>
737 <ol>
738 <li>You must configure the <code>fairQueue</code> property to be <code>true</code>.</li>
739 <li>You will have to cast the data source to <code>org.apache.tomcat.jdbc.pool.DataSource</code></li>
740 </ol>
741 An example of using the async feature is show below.
742<div class="codeBox"><pre><code> Connection con = null;
743 try {
744 Future&lt;Connection&gt; future = datasource.getConnectionAsync();
745 while (!future.isDone()) {
746 System.out.println("Connection is not yet available. Do some background work");
747 try {
748 Thread.sleep(100); //simulate work
749 }catch (InterruptedException x) {
750 Thread.currentThread().interrupt();
751 }
752 }
753 con = future.get(); //should return instantly
754 Statement st = con.createStatement();
755 ResultSet rs = st.executeQuery("select * from user");</code></pre></div>
756
757 </blockquote></td></tr></table>
758 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Interceptors"><strong>Interceptors</strong></a></font></td></tr><tr><td><blockquote>
759 <p>Interceptors are a powerful way to enable, disable or modify functionality on a specific connection or its sub components.
760 There are many different use cases for when interceptors are useful. By default, and for performance reasons, the connection pool is stateless.
761 The only state the pool itself inserts are <code>defaultAutoCommit</code>, <code>defaultReadOnly</code>, <code>defaultTransactionIsolation</code>, <code>defaultCatalog</code> if
762 these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection,
763 the pool itself will not reset them.</p>
764 <p>An interceptor has to extend the <code>org.apache.tomcat.jdbc.pool.JdbcInterceptor</code> class. This class is fairly simple,
765 You will need to have a no arg constructor</p>
766<div class="codeBox"><pre><code> public JdbcInterceptor() {
767 }</code></pre></div>
768 <p>
769 When a connection is borrowed from the pool, the interceptor can initialize or in some other way react to the event by implementing the
770 </p>
771<div class="codeBox"><pre><code> public abstract void reset(ConnectionPool parent, PooledConnection con);</code></pre></div>
772 <p>
773 method. This method gets called with two parameters, a reference to the connection pool itself <code>ConnectionPool parent</code>
774 and a reference to the underlying connection <code>PooledConnection con</code>.
775 </p>
776 <p>
777 When a method on the <code>java.sql.Connection</code> object is invoked, it will cause the
778 </p>
779<div class="codeBox"><pre><code> public Object invoke(Object proxy, Method method, Object[] args) throws Throwable</code></pre></div>
780 <p>
781 method to get invoked. The <code>Method method</code> is the actual method invoked, and <code>Object[] args</code> are the arguments.
782 To look at a very simple example, where we demonstrate how to make the invocation to <code>java.sql.Connection.close()</code> a noop
783 if the connection has been closed
784 </p>
785<div class="codeBox"><pre><code> if (CLOSE_VAL==method.getName()) {
786 if (isClosed()) return null; //noop for already closed.
787 }
788 return super.invoke(proxy,method,args);</code></pre></div>
789 <p>
790 There is an observation being made. It is the comparison of the method name. One way to do this would be to do
791 <code>"close".equals(method.getName())</code>.
792 Above we see a direct reference comparison between the method name and <code>static final String</code> reference.
793 According to the JVM spec, method names and static final String end up in a shared constant pool, so the reference comparison should work.
794 One could of course do this as well:
795 </p>
796<div class="codeBox"><pre><code> if (compare(CLOSE_VAL,method)) {
797 if (isClosed()) return null; //noop for already closed.
798 }
799 return super.invoke(proxy,method,args);</code></pre></div>
800 <p>
801 The <code>compare(String,Method)</code> will use the <code>useEquals</code> flag on an interceptor and do either reference comparison or
802 a string value comparison when the <code>useEquals=true</code> flag is set.
803 </p>
804 <p>Pool start/stop<br>
805 When the connection pool is started or closed, you can be notifed. You will only be notified once per interceptor class
806 even though it is an instance method. and you will be notified using an interceptor currently not attached to a pool.
807 </p>
808<div class="codeBox"><pre><code> public void poolStarted(ConnectionPool pool) {
809 }
810
811 public void poolClosed(ConnectionPool pool) {
812 }</code></pre></div>
813 <p>
814 When overriding these methods, don't forget to call super if you are extending a class other than <code>JdbcInterceptor</code>
815 </p>
816 <p>Configuring interceptors<br>
817 Interceptors are configured using the <code>jdbcInterceptors</code> property or the <code>setJdbcInterceptors</code> method.
818 An interceptor can have properties, and would be configured like this
819 </p>
820<div class="codeBox"><pre><code> String jdbcInterceptors=
821 "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState(useEquals=true,fast=yes)"</code></pre></div>
822
823 <p>Interceptor properties<br>
824 Since interceptors can have properties, you need to be able to read the values of these properties within your
825 interceptor. Taking an example like the one above, you can override the <code>setProperties</code> method.
826 </p>
827<div class="codeBox"><pre><code> public void setProperties(Map&lt;String, InterceptorProperty&gt; properties) {
828 super.setProperties(properties);
829 final String myprop = "myprop";
830 InterceptorProperty p1 = properties.get(myprop);
831 if (p1!=null) {
832 setMyprop(Long.parseLong(p1.getValue()));
833 }
834 }</code></pre></div>
835
836 </blockquote></td></tr></table>
837 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Getting the actual JDBC connection"><!--()--></a><a name="Getting_the_actual_JDBC_connection"><strong>Getting the actual JDBC connection</strong></a></font></td></tr><tr><td><blockquote>
838 <p>Connection pools create wrappers around the actual connection in order to properly pool them.
839 We also create interceptors in these wrappers to be able to perform certain functions.
840 If there is a need to retrieve the actual connection, one can do so using the <code>javax.sql.PooledConnection</code>
841 interface.
842 </p>
843<div class="codeBox"><pre><code> Connection con = datasource.getConnection();
844 Connection actual = ((javax.sql.PooledConnection)con).getConnection();</code></pre></div>
845
846 </blockquote></td></tr></table>
847
848</blockquote></td></tr></table><table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#525D76"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Building"><strong>Building</strong></a></font></td></tr><tr><td><blockquote>
849 <p>We build the JDBC pool code with 1.6, but it is backwards compatible down to 1.5 for runtime environment. For unit test, we use 1.6 and higher</p>
850 <p>Other examples of Tomcat configuration for JDBC usage can be found <a href="http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html">in the Tomcat documentation</a>. </p>
851 <table border="0" cellspacing="0" cellpadding="2"><tr><td bgcolor="#828DA6"><font color="#ffffff" face="arial,helvetica.sanserif"><a name="Building from source"><!--()--></a><a name="Building_from_source"><strong>Building from source</strong></a></font></td></tr><tr><td><blockquote>
852 <p>Building is pretty simple. The pool has a dependency on <code>tomcat-juli.jar</code> and in case you want the <code>SlowQueryReportJmx</code></p>
853<div class="codeBox"><pre><code> javac -classpath tomcat-juli.jar \
854 -d . \
855 org/apache/tomcat/jdbc/pool/*.java \
856 org/apache/tomcat/jdbc/pool/interceptor/*.java \
857 org/apache/tomcat/jdbc/pool/jmx/*.java</code></pre></div>
858 <p>
859 A build file can be found in the Tomcat <a href="http://svn.apache.org/viewvc/tomcat/trunk/modules/jdbc-pool/">source repository</a>.
860 </p>
861 <p>
862 As a convenience, a build file is also included where a simple build command will generate all files needed.
863 </p>
864<div class="codeBox"><pre><code> ant download (downloads dependencies)
865 ant build (compiles and generates .jar files)
866 ant dist (creates a release package)
867 ant test (runs tests, expects a test database to be setup)</code></pre></div>
868
869 <p>
870 The system is structured for a Maven build, but does generate release artifacts. Just the library itself.
871 </p>
872 </blockquote></td></tr></table>
873</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
874 on improving documentation for Apache Tomcat.<br><br>
875 If you have trouble and need help, read
876 <a href="http://tomcat.apache.org/findhelp.html">Find Help</a> page
877 and ask your question on the tomcat-users
878 <a href="http://tomcat.apache.org/lists.html">mailing list</a>.
879 Do not ask such questions here. This is not a Q&amp;A section.<br><br>
880 The Apache Comments System is explained <a href="./comments.html">here</a>.
881 Comments may be removed by our moderators if they are either
882 implemented or considered invalid/off-topic.</p><script type="text/javascript"><!--//--><![CDATA[//><!--
883 var comments_shortname = 'tomcat';
884 var comments_identifier = 'http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html';
885 (function(w, d) {
886 if (w.location.hostname.toLowerCase() == "tomcat.apache.org") {
887 d.write('<div id="comments_thread"><\/div>');
888 var s = d.createElement('script');
889 s.type = 'text/javascript';
890 s.async = true;
891 s.src = 'https://comments.apache.org/show_comments.lua?site=' + comments_shortname + '&page=' + comments_identifier;
892 (d.getElementsByTagName('head')[0] || d.getElementsByTagName('body')[0]).appendChild(s);
893 }
894 else {
895 d.write('<div id="comments_thread"><strong>Comments are disabled for this page at the moment.<\/strong><\/div>');
896 }
897 })(window, document);
898 //--><!]]></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>
899 Copyright &copy; 1999-2017, Apache Software Foundation
900 </em></font></div></td></tr></table></body></html>