1 <?xml version="1.0" encoding="UTF-8"?>
3 Licensed to the Apache Software Foundation (ASF) under one or more
4 contributor license agreements. See the NOTICE file distributed with
5 this work for additional information regarding copyright ownership.
6 The ASF licenses this file to You under the Apache License, Version 2.0
7 (the "License"); you may not use this file except in compliance with
8 the License. You may obtain a copy of the License at
10 http://www.apache.org/licenses/LICENSE-2.0
12 Unless required by applicable law or agreed to in writing, software
13 distributed under the License is distributed on an "AS IS" BASIS,
14 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
15 See the License for the specific language governing permissions and
16 limitations under the License.
19 <!ENTITY project SYSTEM "@TOMCAT_PROJECT_DEST@">
21 <document url="jdbc-pool.html">
26 <author email="fhanik@apache.org">Filip Hanik</author>
27 <title>The Tomcat JDBC Connection Pool</title>
32 <section name="Table of Contents">
36 <section name="Introduction">
38 <p>The <strong>JDBC Connection Pool <code>org.apache.tomcat.jdbc.pool</code></strong>
39 is a replacement or an alternative to the <a href="http://commons.apache.org/dbcp/">Apache Commons DBCP</a>
42 <p>So why do we need a new connection pool?</p>
44 <p>Here are a few of the reasons:</p>
46 <li>Commons DBCP 1.x is single threaded. In order to be thread safe
47 Commons locks the entire pool for short periods during both object
48 allocation and object return. Note that this does not apply to
49 Commons DBCP 2.x.</li>
50 <li>Commons DBCP 1.x can be slow. As the number of logical CPUs grows and
51 the number of concurrent threads attempting to borrow or return
52 objects increases, the performance suffers. For highly concurrent
53 systems the impact can be significant. Note that this does not apply
54 to Commons DBCP 2.x.</li>
55 <li>Commons DBCP is over 60 classes. tomcat-jdbc-pool core is 8 classes,
56 hence modifications for future requirement will require much less
57 changes. This is all you need to run the connection pool itself, the
59 <li>Commons DBCP uses static interfaces. This means you have to use the
60 right version for a given JRE version or you may see
61 <code>NoSuchMethodException</code> exceptions.</li>
62 <li>It's not worth rewriting over 60 classes, when a connection pool can
63 be accomplished with a much simpler implementation.</li>
64 <li>Tomcat jdbc pool implements the ability retrieve a connection
65 asynchronously, without adding additional threads to the library
67 <li>Tomcat jdbc pool is a Tomcat module, it depends on Tomcat JULI, a
68 simplified logging framework used in Tomcat.</li>
69 <li>Retrieve the underlying connection using the
70 <code>javax.sql.PooledConnection</code> interface.</li>
71 <li>Starvation proof. If a pool is empty, and threads are waiting for a
72 connection, when a connection is returned, the pool will awake the
73 correct thread waiting. Most pools will simply starve.</li>
76 <p>Features added over other connection pool implementations</p>
78 <li>Support for highly concurrent environments and multi core/cpu systems.</li>
79 <li>Dynamic implementation of interface, will support <code>java.sql</code> and <code>javax.sql</code> interfaces for
80 your runtime environment (as long as your JDBC driver does the same), even when compiled with a lower version of the JDK.</li>
81 <li>Validation intervals - we don't have to validate every single time we use the connection, we can do this
82 when we borrow or return the connection, just not more frequent than an interval we can configure.</li>
83 <li>Run-Once query, a configurable query that will be run only once, when the connection to the database is established.
84 Very useful to setup session settings, that you want to exist during the entire time the connection is established.</li>
85 <li>Ability to configure custom interceptors.
86 This allows you to write custom interceptors to enhance the functionality. You can use interceptors to gather query stats,
87 cache session states, reconnect the connection upon failures, retry queries, cache query results, and so on.
88 Your options are endless and the interceptors are dynamic, not tied to a JDK version of a
89 <code>java.sql</code>/<code>javax.sql</code> interface.</li>
90 <li>High performance - we will show some differences in performance later on</li>
91 <li>Extremely simple, due to the very simplified implementation, the line count and source file count are very low, compare with c3p0
92 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
93 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>
94 <li>Asynchronous connection retrieval - you can queue your request for a connection and receive a <code>Future<Connection></code> back.</li>
95 <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>
96 <li>You can decide at what moment connections are considered abandoned, is it when the pool is full, or directly at a timeout
97 by specifying a pool usage threshold.
99 <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.
100 This is achieved using the <code>ResetAbandonedTimer</code>
102 <li>Close connections after they have been connected for a certain time. Age based close upon return to the pool.
104 <li>Get JMX notifications and log entries when connections are suspected for being abandoned. This is similar to
105 the <code>removeAbandonedTimeout</code> but it doesn't take any action, only reports the information.
106 This is achieved using the <code>suspectTimeout</code> attribute.</li>
107 <li>Connections can be retrieved from a <code>java.sql.Driver</code>, <code>javax.sql.DataSource</code> or <code>javax.sql.XADataSource</code>
108 This is achieved using the <code>dataSource</code> and <code>dataSourceJNDI</code> attributes.</li>
109 <li>XA connection support</li>
114 <section name="How to use">
116 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
117 transition will be very simple. Moving from other connection pools is also fairly straight forward.
119 <subsection name="Additional features">
120 <p>The Tomcat connection pool offers a few additional features over what most other pools let you do:</p>
122 <li><code>initSQL</code> - the ability to run a SQL statement exactly once, when the connection is created</li>
123 <li><code>validationInterval</code> - in addition to running validations on connections, avoid running them too frequently.</li>
124 <li><code>jdbcInterceptors</code> - flexible and pluggable interceptors to create any customizations around the pool,
125 the query execution and the result set handling. More on this in the advanced section.</li>
126 <li><code>fairQueue</code> - Set the fair flag to true to achieve thread fairness or to use asynchronous connection retrieval</li>
129 <subsection name="Inside the Apache Tomcat Container">
131 The Tomcat Connection pool is configured as a resource described in <a href="http://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html" target="_blank">The Tomcat JDBC documentation</a>
132 With the only difference being that you have to specify the <code>factory</code> attribute and set the value to
133 <code>org.apache.tomcat.jdbc.pool.DataSourceFactory</code>
136 <subsection name="Standalone">
138 The connection pool only has another dependency, and that is on tomcat-juli.jar.
139 To configure the pool in a stand alone project using bean instantiation, the bean to instantiate is
140 <code>org.apache.tomcat.jdbc.pool.DataSource</code>. The same attributes (documented below) as you use to configure a connection
141 pool as a JNDI resource, are used to configure a data source as a bean.
144 <subsection name="JMX">
146 The connection pool object exposes an MBean that can be registered.
147 In order for the connection pool object to create the MBean, the flag <code>jmxEnabled</code> has to be set to true.
148 This doesn't imply that the pool will be registered with an MBean server, merely that the MBean is created.
149 In a container like Tomcat, Tomcat itself registers the DataSource with the MBean server, the
150 <code>org.apache.tomcat.jdbc.pool.DataSource</code> object will then register the actual
151 connection pool MBean.
152 If you're running outside of a container, you can register the DataSource yourself under any object name you specify,
153 and it propagates the registration to the underlying pool. To do this you would call <code>mBeanServer.registerMBean(dataSource.getPool().getJmxPool(),objectname)</code>.
154 Prior to this call, ensure that the pool has been created by calling <code>dataSource.createPool()</code>.
159 <section name="Attributes">
160 <p>To provide a very simple switch to and from commons-dbcp and tomcat-jdbc-pool,
161 Most attributes are the same and have the same meaning.</p>
162 <subsection name="JNDI Factory and Type">
164 <attribute name="factory" required="true">
165 <p>factory is required, and the value should be <code>org.apache.tomcat.jdbc.pool.DataSourceFactory</code></p>
167 <attribute name="type" required="true">
168 <p>Type should always be <code>javax.sql.DataSource</code> or <code>javax.sql.XADataSource</code></p>
169 <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>
174 <subsection name="System Properties">
175 <p>System properties are JVM wide, affect all pools created in the JVM</p>
177 <attribute name="org.apache.tomcat.jdbc.pool.onlyAttemptCurrentClassLoader" required="false">
178 <p>(boolean) Controls classloading of dynamic classes, such as
179 JDBC drivers, interceptors and validators. If set to
180 <code>false</code>, default value, the pool will first attempt
181 to load using the current loader (i.e. the class loader that
182 loaded the pool classes) and if class loading fails attempt to
183 load using the thread context loader. Set this value to
184 <code>true</code>, if you wish to remain backwards compatible
185 with Apache Tomcat 8.0.8 and earlier, and only attempt the
187 If not set then the default value is <code>false</code>.
193 <subsection name="Common Attributes">
194 <p>These attributes are shared between commons-dbcp and tomcat-jdbc-pool, in some cases default values are different.</p>
197 <attribute name="defaultAutoCommit" required="false">
198 <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>
201 <attribute name="defaultReadOnly" required="false">
202 <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>
205 <attribute name="defaultTransactionIsolation" required="false">
206 <p>(String) The default TransactionIsolation state of connections created by this pool. One of the following: (see javadoc )</p>
208 <li><code>NONE</code></li>
209 <li><code>READ_COMMITTED</code></li>
210 <li><code>READ_UNCOMMITTED</code></li>
211 <li><code>REPEATABLE_READ</code></li>
212 <li><code>SERIALIZABLE</code></li>
214 <p>If not set, the method will not be called and it defaults to the JDBC driver.</p>
217 <attribute name="defaultCatalog" required="false">
218 <p>(String) The default catalog of connections created by this pool.</p>
221 <attribute name="driverClassName" required="true">
222 <p>(String) The fully qualified Java class name of the JDBC driver to be used. The driver has to be accessible
223 from the same classloader as tomcat-jdbc.jar
227 <attribute name="username" required="true">
228 <p>(String) The connection username to be passed to our JDBC driver to establish a connection.
229 Note that method <code>DataSource.getConnection(username,password)</code>
230 by default will not use credentials passed into the method,
231 but will use the ones configured here. See <code>alternateUsernameAllowed</code>
232 property for more details.
236 <attribute name="password" required="true">
237 <p>(String) The connection password to be passed to our JDBC driver to establish a connection.
238 Note that method <code>DataSource.getConnection(username,password)</code>
239 by default will not use credentials passed into the method,
240 but will use the ones configured here. See <code>alternateUsernameAllowed</code>
241 property for more details.
245 <attribute name="maxActive" required="false">
246 <p>(int) The maximum number of active connections that can be allocated from this pool at the same time.
247 The default value is <code>100</code></p>
250 <attribute name="maxIdle" required="false">
251 <p>(int) The maximum number of connections that should be kept in the pool at all times.
252 Default value is <code>maxActive</code>:<code>100</code>
253 Idle connections are checked periodically (if enabled) and
254 connections that been idle for longer than <code>minEvictableIdleTimeMillis</code>
255 will be released. (also see <code>testWhileIdle</code>)</p>
258 <attribute name="minIdle" required="false">
260 (int) The minimum number of established connections that should be kept in the pool at all times.
261 The connection pool can shrink below this number if validation queries fail.
262 Default value is derived from <code>initialSize</code>:<code>10</code> (also see <code>testWhileIdle</code>)
266 <attribute name="initialSize" required="false">
267 <p>(int)The initial number of connections that are created when the pool is started.
268 Default value is <code>10</code></p>
271 <attribute name="maxWait" required="false">
272 <p>(int) The maximum number of milliseconds that the pool will wait (when there are no available connections)
273 for a connection to be returned before throwing an exception.
274 Default value is <code>30000</code> (30 seconds)</p>
277 <attribute name="testOnBorrow" required="false">
278 <p>(boolean) The indication of whether objects will be validated before being borrowed from the pool.
279 If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another.
280 NOTE - for a true value to have any effect, the <code>validationQuery</code>
281 or <code>validatorClassName</code> parameter must be set to a non-null string.
282 In order to have a more efficient validation, see <code>validationInterval</code>.
283 Default value is <code>false</code>
287 <attribute name="testOnConnect" required="false">
288 <p>(boolean) The indication of whether objects will be validated when a connection is first created.
289 If an object fails to validate, it will be throw <code>SQLException</code>.
290 NOTE - for a true value to have any effect, the <code>validationQuery</code>, <code>initSQL</code>
291 or <code>validatorClassName</code> parameter must be set to a non-null string.
292 Default value is <code>false</code>
296 <attribute name="testOnReturn" required="false">
297 <p>(boolean) The indication of whether objects will be validated before being returned to the pool.
298 NOTE - for a true value to have any effect, the <code>validationQuery</code>
299 or <code>validatorClassName</code> parameter must be set to a non-null string.
300 The default value is <code>false</code>.
304 <attribute name="testWhileIdle" required="false">
305 <p>(boolean) The indication of whether objects will be validated by the idle object evictor (if any).
306 If an object fails to validate, it will be dropped from the pool.
307 NOTE - for a true value to have any effect, the <code>validationQuery</code>
308 or <code>validatorClassName</code> parameter must be set to a non-null string.
309 The default value is <code>false</code> and this property has to be set in order for the
310 pool cleaner/test thread is to run (also see <code>timeBetweenEvictionRunsMillis</code>)
314 <attribute name="validationQuery" required="false">
315 <p>(String) The SQL query that will be used to validate connections from this pool before returning them to the caller.
316 If specified, this query does not have to return any data, it just can't throw a <code>SQLException</code>.
317 The default value is <code>null</code>.
318 Example values are <code>SELECT 1</code>(mysql), <code>select 1 from dual</code>(oracle), <code>SELECT 1</code>(MS Sql Server)
322 <attribute name="validationQueryTimeout" required="false">
323 <p>(int) The timeout in seconds before a connection validation queries fail. This works by calling
324 <code>java.sql.Statement.setQueryTimeout(seconds)</code> on the statement that executes the <code>validationQuery</code>.
325 The pool itself doesn't timeout the query, it is still up to the JDBC driver to enforce query timeouts.
326 A value less than or equal to zero will disable this feature.
327 The default value is <code>-1</code>.
331 <attribute name="validatorClassName" required="false">
332 <p>(String) The name of a class which implements the
333 <code>org.apache.tomcat.jdbc.pool.Validator</code> interface and
334 provides a no-arg constructor (may be implicit). If specified, the
335 class will be used to create a Validator instance which is then used
336 instead of any validation query to validate connections. The default
337 value is <code>null</code>. An example value is
338 <code>com.mycompany.project.SimpleValidator</code>.
342 <attribute name="timeBetweenEvictionRunsMillis" required="false">
343 <p>(int) The number of milliseconds to sleep between runs of the idle connection validation/cleaner thread.
344 This value should not be set under 1 second. It dictates how often we check for idle, abandoned connections, and how often
345 we validate idle connections.
346 The default value is <code>5000</code> (5 seconds). <br/>
350 <attribute name="numTestsPerEvictionRun" required="false">
351 <p>(int) Property not used in tomcat-jdbc-pool.</p>
354 <attribute name="minEvictableIdleTimeMillis" required="false">
355 <p>(int) The minimum amount of time an object may sit idle in the pool before it is eligible for eviction.
356 The default value is <code>60000</code> (60 seconds).</p>
359 <attribute name="accessToUnderlyingConnectionAllowed" required="false">
360 <p>(boolean) Property not used. Access can be achieved by calling <code>unwrap</code> on the pooled connection.
361 see <code>javax.sql.DataSource</code> interface, or call <code>getConnection</code> through reflection or
362 cast the object as <code>javax.sql.PooledConnection</code></p>
365 <attribute name="removeAbandoned" required="false">
366 <p>(boolean) Flag to remove abandoned connections if they exceed the <code>removeAbandonedTimeout</code>.
367 If set to true a connection is considered abandoned and eligible for removal if it has been in use
368 longer than the <code>removeAbandonedTimeout</code> Setting this to <code>true</code> can recover db connections from
369 applications that fail to close a connection. See also <code>logAbandoned</code>
370 The default value is <code>false</code>.</p>
373 <attribute name="removeAbandonedTimeout" required="false">
374 <p>(int) Timeout in seconds before an abandoned(in use) connection can be removed.
375 The default value is <code>60</code> (60 seconds). The value should be set to the longest running query your applications
379 <attribute name="logAbandoned" required="false">
380 <p>(boolean) Flag to log stack traces for application code which abandoned a Connection.
381 Logging of abandoned Connections adds overhead for every Connection borrow because a stack trace has to be generated.
382 The default value is <code>false</code>.</p>
385 <attribute name="connectionProperties" required="false">
386 <p>(String) The connection properties that will be sent to our JDBC driver when establishing new connections.
387 Format of the string must be [propertyName=property;]*
388 NOTE - The "user" and "password" properties will be passed explicitly, so they do not need to be included here.
389 The default value is <code>null</code>.</p>
392 <attribute name="poolPreparedStatements" required="false">
393 <p>(boolean) Property not used.</p>
396 <attribute name="maxOpenPreparedStatements" required="false">
397 <p>(int) Property not used.</p>
404 <subsection name="Tomcat JDBC Enhanced Attributes">
408 <attribute name="initSQL" required="false">
409 <p>(String) A custom query to be run when a connection is first created.
410 The default value is <code>null</code>.</p>
413 <attribute name="jdbcInterceptors" required="false">
414 <p>(String) A semicolon separated list of classnames extending
415 <code>org.apache.tomcat.jdbc.pool.JdbcInterceptor</code> class.
416 See <a href="#Configuring_JDBC_interceptors">Configuring JDBC interceptors</a>
417 below for more detailed description of syntaz and examples.
420 These interceptors will be inserted as an interceptor into the chain
421 of operations on a <code>java.sql.Connection</code> object.
422 The default value is <code>null</code>.
425 Predefined interceptors:<br/>
426 <code>org.apache.tomcat.jdbc.pool.interceptor.<br />ConnectionState</code>
427 - keeps track of auto commit, read only, catalog and transaction isolation level.<br/>
428 <code>org.apache.tomcat.jdbc.pool.interceptor.<br />StatementFinalizer</code>
429 - keeps track of opened statements, and closes them when the connection is returned to the pool.
432 More predefined interceptors are described in detail in the
433 <a href="#JDBC_interceptors">JDBC Interceptors section</a>.
437 <attribute name="validationInterval" required="false">
438 <p>(long) avoid excess validation, only run validation at most at this frequency - time in milliseconds.
439 If a connection is due for validation, but has been validated previously within this interval, it will not be validated again.
440 The default value is <code>3000</code> (3 seconds).</p>
443 <attribute name="jmxEnabled" required="false">
444 <p>(boolean) Register the pool with JMX or not.
445 The default value is <code>true</code>.</p>
448 <attribute name="fairQueue" required="false">
449 <p>(boolean) Set to true if you wish that calls to getConnection should be treated
450 fairly in a true FIFO fashion. This uses the <code>org.apache.tomcat.jdbc.pool.FairBlockingQueue</code>
451 implementation for the list of the idle connections. The default value is <code>true</code>.
452 This flag is required when you want to use asynchronous connection retrieval.<br/>
453 Setting this flag ensures that threads receive connections in the order they arrive.<br/>
454 During performance tests, there is a very large difference in how locks
455 and lock waiting is implemented. When <code>fairQueue=true</code>
456 there is a decision making process based on what operating system the system is running.
457 If the system is running on Linux (property <code>os.name=Linux</code>.
458 To disable this Linux specific behavior and still use the fair queue, simply add the property
459 <code>org.apache.tomcat.jdbc.pool.FairBlockingQueue.ignoreOS=true</code> to your system properties
460 before the connection pool classes are loaded.
464 <attribute name="abandonWhenPercentageFull" required="false">
465 <p>(int) Connections that have been abandoned (timed out) wont get closed and reported up unless
466 the number of connections in use are above the percentage defined by <code>abandonWhenPercentageFull</code>.
467 The value should be between 0-100.
468 The default value is <code>0</code>, which implies that connections are eligible for closure as soon
469 as <code>removeAbandonedTimeout</code> has been reached.</p>
472 <attribute name="maxAge" required="false">
473 <p>(long) Time in milliseconds to keep this connection. This attribute
474 works both when returning connection and when borrowing connection.
475 When a connection is borrowed from the pool, the pool will check to see
476 if the <code>now - time-when-connected > maxAge</code> has been reached
477 , and if so, it reconnects before borrow it. When a connection is
478 returned to the pool, the pool will check to see if the
479 <code>now - time-when-connected > maxAge</code> has been reached, and
480 if so, it closes the connection rather than returning it to the pool.
481 The default value is <code>0</code>, which implies that connections
482 will be left open and no age check will be done upon borrowing from the
483 pool and returning the connection to the pool.</p>
486 <attribute name="useEquals" required="false">
487 <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>
488 when you wish to use <code>==</code> when comparing method names. This property does not apply to added interceptors as those are configured individually.
489 The default value is <code>true</code>.
492 <attribute name="suspectTimeout" required="false">
493 <p>(int) Timeout value in seconds. Default value is <code>0</code>.<br/>
494 Similar to to the <code>removeAbandonedTimeout</code> value but instead of treating the connection
495 as abandoned, and potentially closing the connection, this simply logs the warning if
496 <code>logAbandoned</code> is set to true. If this value is equal or less than 0, no suspect
497 checking will be performed. Suspect checking only takes place if the timeout value is larger than 0 and
498 the connection was not abandoned or if abandon check is disabled. If a connection is suspect a WARN message gets
499 logged and a JMX notification gets sent once.
502 <attribute name="rollbackOnReturn" required="false">
503 <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
504 Default value is <code>false</code>.<br/>
507 <attribute name="commitOnReturn" required="false">
508 <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
509 If <code>rollbackOnReturn==true</code> then this attribute is ignored.
510 Default value is <code>false</code>.<br/>
513 <attribute name="alternateUsernameAllowed" required="false">
514 <p>(boolean) By default, the jdbc-pool will ignore the
515 <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>
516 call, and simply return a previously pooled connection under the globally configured properties <code>username</code> and <code>password</code>, for performance reasons.
519 The pool can however be configured to allow use of different credentials
520 each time a connection is requested. To enable the functionality described in the
521 <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>
522 call, simply set the property <code>alternateUsernameAllowed</code>
523 to <code>true</code>.<br />
524 Should you request a connection with the credentials user1/password1 and the connection
525 was previously connected using different user2/password2, the connection will be closed,
526 and reopened with the requested credentials. This way, the pool size is still managed
527 on a global level, and not on a per schema level. <br/>
528 The default value is <code>false</code>.<br/>
529 This property was added as an enhancement to <a href="https://bz.apache.org/bugzilla/show_bug.cgi?id=50025">bug 50025</a>.
532 <attribute name="dataSource" required="false">
533 <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.
534 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>
537 <attribute name="dataSourceJNDI" required="false">
538 <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>
541 <attribute name="useDisposableConnectionFacade" required="false">
542 <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
543 reference of a connection it has already called closed on, to execute queries on it. Default value is <code>true</code>.
546 <attribute name="logValidationErrors" required="false">
547 <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.
550 <attribute name="propagateInterruptState" required="false">
551 <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.
554 <attribute name="ignoreExceptionOnPreLoad" required="false">
555 <p>(boolean) Flag whether ignore error of connection creation while initializing the pool.
556 Set to true if you want to ignore error of connection creation while initializing the pool.
557 Set to false if you want to fail the initialization of the pool by throwing exception.
558 The default value is <code>false</code>.
565 <section name="Advanced usage">
566 <subsection name="JDBC interceptors">
567 <p>To see an example of how to use an interceptor, take a look at
568 <code>org.apache.tomcat.jdbc.pool.interceptor.ConnectionState</code>.
569 This simple interceptor is a cache of three attributes, transaction isolation level, auto commit and read only state,
570 in order for the system to avoid not needed roundtrips to the database.
572 <p>Further interceptors will be added to the core of the pool as the need arises. Contributions are always welcome!</p>
573 <p>Interceptors are of course not limited to just <code>java.sql.Connection</code> but can be used to wrap any
574 of the results from a method invokation as well. You could build query performance analyzer that provides JMX notifications when a
575 query is running longer than the expected time.</p>
577 <subsection name="Configuring JDBC interceptors">
578 <p>Configuring JDBC interceptors is done using the <b>jdbcInterceptors</b> property.
579 The property contains a list of semicolon separated class names. If the
580 classname is not fully qualified it will be prefixed with the
581 <code>org.apache.tomcat.jdbc.pool.interceptor.</code> prefix.
585 jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
586 org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
591 <code> jdbcInterceptors="ConnectionState;StatementFinalizer"</code>
594 Interceptors can have properties as well. Properties for an interceptor
595 are specified within parentheses after the class name. Several properties
596 are separated by commas.
600 jdbcInterceptors="ConnectionState;StatementFinalizer(useEquals=true)"
604 Extra whitespace characters around class names, property names and values
608 <subsection name="org.apache.tomcat.jdbc.pool.JdbcInterceptor">
609 <p>Abstract base class for all interceptors, cannot be instantiated.</p>
611 <attribute name="useEquals" required="false">
612 <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>
613 when you wish to use <code>==</code> when comparing method names.
614 The default value is <code>true</code>.
619 <subsection name="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState">
620 <p>Caches the connection for the following attributes <code>autoCommit</code>, <code>readOnly</code>,
621 <code>transactionIsolation</code> and <code>catalog</code>.
622 It is a performance enhancement to avoid roundtrip to the database when getters are called or setters are called with an already set value.
627 <subsection name="org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer">
628 <p>Keeps track of all statements created using <code>createStatement</code>, <code>prepareStatement</code> or <code>prepareCall</code>
629 and closes these statements when the connection is returned to the pool.
632 <attribute name="trace" required="false">
633 <p>(boolean as String) Enable tracing of unclosed statements.
634 When enabled and a connection is closed, and statements are not closed,
635 the interceptor will log all stack traces.
636 The default value is <code>false</code>.
641 <subsection name="org.apache.tomcat.jdbc.pool.interceptor.StatementCache">
642 <p>Caches <code>PreparedStatement</code> and/or <code>CallableStatement</code>
643 instances on a connection.
645 <p>The statements are cached per connection.
646 The count limit is counted globally for all connections that belong to
647 the same pool. Once the count reaches <code>max</code>, subsequent
648 statements are not returned to the cache and are closed immediately.
651 <attribute name="prepared" required="false">
652 <p>(boolean as String) Enable caching of <code>PreparedStatement</code>
653 instances created using <code>prepareStatement</code> calls.
654 The default value is <code>true</code>.
657 <attribute name="callable" required="false">
658 <p>(boolean as String) Enable caching of <code>CallableStatement</code>
659 instances created using <code>prepareCall</code> calls.
660 The default value is <code>false</code>.
663 <attribute name="max" required="false">
664 <p>(int as String) Limit on the count of cached statements across
666 The default value is <code>50</code>.
671 <subsection name="org.apache.tomcat.jdbc.pool.interceptor.StatementDecoratorInterceptor">
672 <p>See <bug>48392</bug>. Interceptor to wrap statements and result sets in order to prevent access to the actual connection
673 using the methods <code>ResultSet.getStatement().getConnection()</code> and <code>Statement.getConnection()</code>
678 <subsection name="org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor">
679 <p>Automatically calls <code>java.sql.Statement.setQueryTimeout(seconds)</code> when a new statement is created.
680 The pool itself doesn't timeout the query, it is still up to the JDBC driver to enforce query timeouts.
683 <attribute name="queryTimeout" required="true">
684 <p>(int as String) The number of seconds to set for the query timeout.
685 A value less than or equal to zero will disable this feature.
686 The default value is <code>1</code> seconds.
691 <subsection name="org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport">
692 <p>Keeps track of query performance and issues log entries when queries exceed a time threshold of fail.
693 The log level used is <code>WARN</code>
696 <attribute name="threshold" required="false">
697 <p>(int as String) The number of milliseconds a query has to exceed before issuing a log alert.
698 The default value is <code>1000</code> milliseconds.
701 <attribute name="maxQueries" required="false">
702 <p>(int as String) The maximum number of queries to keep track of in order to preserve memory space.
703 A value less than or equal to 0 will disable this feature.
704 The default value is <code>1000</code>.
707 <attribute name="logSlow" required="false">
708 <p>(boolean as String) Set to <code>true</code> if you wish to log slow queries.
709 The default value is <code>true</code>.
712 <attribute name="logFailed" required="false">
713 <p>(boolean as String) Set to <code>true</code> if you wish to log failed queries.
714 The default value is <code>false</code>.
719 <subsection name="org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx">
720 <p>Extends the <code>SlowQueryReport</code> and in addition to log entries it issues JMX notification
721 for monitoring tools to react to. Inherits all the attributes from its parent class.
722 This class uses Tomcat's JMX engine so it wont work outside of the Tomcat container.
723 By default, JMX notifications are sent through the ConnectionPool mbean if it is enabled.
724 The <code>SlowQueryReportJmx</code> can also register an MBean if <code>notifyPool=false</code>
727 <attribute name="notifyPool" required="false">
728 <p>(boolean as String) Set to false if you want JMX notifications to go to the <code>SlowQueryReportJmx</code> MBean
729 The default value is <code>true</code>.
732 <attribute name="objectName" required="false">
733 <p>(String) Define a valid <code>javax.management.ObjectName</code> string that will be used to register this object with the platform mbean server
734 The default value is <code>null</code> and the object will be registered using
735 tomcat.jdbc:type=org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx,name=the-name-of-the-pool
740 <subsection name="org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer">
742 The abandoned timer starts when a connection is checked out from the pool.
743 This means if you have a 30second timeout and run 10x10second queries using the connection
744 it will be marked abandoned and potentially reclaimed depending on the <code>abandonWhenPercentageFull</code>
746 Using this interceptor it will reset the checkout timer every time you perform an operation on the connection or execute a
754 <section name="Code Example">
755 <p>Other examples of Tomcat configuration for JDBC usage can be found <a href="http://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html">in the Tomcat documentation</a>. </p>
756 <subsection name="Plain Ol' Java">
757 <p>Here is a simple example of how to create and use a data source.</p>
758 <source><![CDATA[ import java.sql.Connection;
759 import java.sql.ResultSet;
760 import java.sql.Statement;
762 import org.apache.tomcat.jdbc.pool.DataSource;
763 import org.apache.tomcat.jdbc.pool.PoolProperties;
765 public class SimplePOJOExample {
767 public static void main(String[] args) throws Exception {
768 PoolProperties p = new PoolProperties();
769 p.setUrl("jdbc:mysql://localhost:3306/mysql");
770 p.setDriverClassName("com.mysql.jdbc.Driver");
771 p.setUsername("root");
772 p.setPassword("password");
773 p.setJmxEnabled(true);
774 p.setTestWhileIdle(false);
775 p.setTestOnBorrow(true);
776 p.setValidationQuery("SELECT 1");
777 p.setTestOnReturn(false);
778 p.setValidationInterval(30000);
779 p.setTimeBetweenEvictionRunsMillis(30000);
781 p.setInitialSize(10);
783 p.setRemoveAbandonedTimeout(60);
784 p.setMinEvictableIdleTimeMillis(30000);
786 p.setLogAbandoned(true);
787 p.setRemoveAbandoned(true);
788 p.setJdbcInterceptors(
789 "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
790 "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
791 DataSource datasource = new DataSource();
792 datasource.setPoolProperties(p);
794 Connection con = null;
796 con = datasource.getConnection();
797 Statement st = con.createStatement();
798 ResultSet rs = st.executeQuery("select * from user");
801 System.out.println((cnt++)+". Host:" +rs.getString("Host")+
802 " User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
807 if (con!=null) try {con.close();}catch (Exception ignore) {}
813 <subsection name="As a Resource">
814 <p>And here is an example on how to configure a resource for JNDI lookups</p>
815 <source><![CDATA[<Resource name="jdbc/TestDB"
817 type="javax.sql.DataSource"
818 factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
822 validationQuery="SELECT 1"
823 validationInterval="30000"
824 timeBetweenEvictionRunsMillis="30000"
829 removeAbandonedTimeout="60"
830 removeAbandoned="true"
832 minEvictableIdleTimeMillis="30000"
834 jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;
835 org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
838 driverClassName="com.mysql.jdbc.Driver"
839 url="jdbc:mysql://localhost:3306/mysql"/>]]></source>
842 <subsection name="Asynchronous Connection Retrieval">
843 <p> The Tomcat JDBC connection pool supports asynchronous connection retrieval without adding additional threads to the
844 pool library. It does this by adding a method to the data source called <code>Future<Connection> getConnectionAsync()</code>.
845 In order to use the async retrieval, two conditions must be met:
848 <li>You must configure the <code>fairQueue</code> property to be <code>true</code>.</li>
849 <li>You will have to cast the data source to <code>org.apache.tomcat.jdbc.pool.DataSource</code></li>
851 An example of using the async feature is show below.
852 <source><![CDATA[ Connection con = null;
854 Future<Connection> future = datasource.getConnectionAsync();
855 while (!future.isDone()) {
856 System.out.println("Connection is not yet available. Do some background work");
858 Thread.sleep(100); //simulate work
859 }catch (InterruptedException x) {
860 Thread.currentThread().interrupt();
863 con = future.get(); //should return instantly
864 Statement st = con.createStatement();
865 ResultSet rs = st.executeQuery("select * from user");]]></source>
868 <subsection name="Interceptors">
869 <p>Interceptors are a powerful way to enable, disable or modify functionality on a specific connection or its sub components.
870 There are many different use cases for when interceptors are useful. By default, and for performance reasons, the connection pool is stateless.
871 The only state the pool itself inserts are <code>defaultAutoCommit</code>, <code>defaultReadOnly</code>, <code>defaultTransactionIsolation</code>, <code>defaultCatalog</code> if
872 these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection,
873 the pool itself will not reset them.</p>
874 <p>An interceptor has to extend the <code>org.apache.tomcat.jdbc.pool.JdbcInterceptor</code> class. This class is fairly simple,
875 You will need to have a no arg constructor</p>
876 <source><![CDATA[ public JdbcInterceptor() {
879 When a connection is borrowed from the pool, the interceptor can initialize or in some other way react to the event by implementing the
881 <source><![CDATA[ public abstract void reset(ConnectionPool parent, PooledConnection con);]]></source>
883 method. This method gets called with two parameters, a reference to the connection pool itself <code>ConnectionPool parent</code>
884 and a reference to the underlying connection <code>PooledConnection con</code>.
887 When a method on the <code>java.sql.Connection</code> object is invoked, it will cause the
889 <source><![CDATA[ public Object invoke(Object proxy, Method method, Object[] args) throws Throwable]]></source>
891 method to get invoked. The <code>Method method</code> is the actual method invoked, and <code>Object[] args</code> are the arguments.
892 To look at a very simple example, where we demonstrate how to make the invokation to <code>java.sql.Connection.close()</code> a noop
893 if the connection has been closed
895 <source><![CDATA[ if (CLOSE_VAL==method.getName()) {
896 if (isClosed()) return null; //noop for already closed.
898 return super.invoke(proxy,method,args);]]></source>
900 There is an observation being made. It is the comparison of the method name. One way to do this would be to do
901 <code>"close".equals(method.getName())</code>.
902 Above we see a direct reference comparison between the method name and <code>static final String</code> reference.
903 According to the JVM spec, method names and static final String end up in a shared constant pool, so the reference comparison should work.
904 One could of course do this as well:
906 <source><![CDATA[ if (compare(CLOSE_VAL,method)) {
907 if (isClosed()) return null; //noop for already closed.
909 return super.invoke(proxy,method,args);]]></source>
911 The <code>compare(String,Method)</code> will use the <code>useEquals</code> flag on an interceptor and do either reference comparison or
912 a string value comparison when the <code>useEquals=true</code> flag is set.
914 <p>Pool start/stop<br/>
915 When the connection pool is started or closed, you can be notifed. You will only be notified once per interceptor class
916 even though it is an instance method. and you will be notified using an interceptor currently not attached to a pool.
918 <source><![CDATA[ public void poolStarted(ConnectionPool pool) {
921 public void poolClosed(ConnectionPool pool) {
924 When overriding these methods, don't forget to call super if you are extending a class other than <code>JdbcInterceptor</code>
926 <p>Configuring interceptors<br/>
927 Interceptors are configured using the <code>jdbcInterceptors</code> property or the <code>setJdbcInterceptors</code> method.
928 An interceptor can have properties, and would be configured like this
930 <source><![CDATA[ String jdbcInterceptors=
931 "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState(useEquals=true,fast=yes)"]]></source>
933 <p>Interceptor properties<br/>
934 Since interceptors can have properties, you need to be able to read the values of these properties within your
935 interceptor. Taking an example like the one above, you can override the <code>setProperties</code> method.
937 <source><![CDATA[ public void setProperties(Map<String, InterceptorProperty> properties) {
938 super.setProperties(properties);
939 final String myprop = "myprop";
940 InterceptorProperty p1 = properties.get(myprop);
942 setMyprop(Long.parseLong(p1.getValue()));
947 <subsection name="Getting the actual JDBC connection">
948 <p>Connection pools create wrappers around the actual connection in order to properly pool them.
949 We also create interceptors in these wrappers to be able to perform certain functions.
950 If there is a need to retrieve the actual connection, one can do so using the <code>javax.sql.PooledConnection</code>
953 <source><![CDATA[ Connection con = datasource.getConnection();
954 Connection actual = ((javax.sql.PooledConnection)con).getConnection();]]></source>
960 <section name="Building">
961 <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>
962 <p>Other examples of Tomcat configuration for JDBC usage can be found <a href="http://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html">in the Tomcat documentation</a>. </p>
963 <subsection name="Building from source">
964 <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>
965 <source><![CDATA[ javac -classpath tomcat-juli.jar \
967 org/apache/tomcat/jdbc/pool/*.java \
968 org/apache/tomcat/jdbc/pool/interceptor/*.java \
969 org/apache/tomcat/jdbc/pool/jmx/*.java]]></source>
971 A build file can be found in the Tomcat <a href="http://svn.apache.org/viewvc/tomcat/trunk/modules/jdbc-pool/">source repository</a>.
974 As a convenience, a build file is also included where a simple build command will generate all files needed.
976 <source> ant download (downloads dependencies)
977 ant build (compiles and generates .jar files)
978 ant dist (creates a release package)
979 ant test (runs tests, expects a test database to be setup)</source>
982 The system is structured for a Maven build, but does generate release artifacts. Just the library itself.