Return-Path:
The JDBC Connection Pool The JDBC Connection Pool org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer
)org.apache.tomcat.jdbc.pool
+ org.apache.tomcat.jdbc.pool
is a replacement or an alternative to the commons-dbcp
connection pool.synchronized
statement in Java 6, commons-dbcp still suffers in speed and concurrency.
Features added over other connection pool implementations
removeAbandonedTimeout
but it doesn't take any action, only reports the information.
- This is achieved using the suspectTimeout
attribute.java.sql.Driver
, javax.sql.DataSource
or javax.sql.XADataSource
+ the removeAbandonedTimeout
but it doesn't take any action, only reports the information.
+ This is achieved using the suspectTimeout
attribute.java.sql.Driver
, javax.sql.DataSource
or javax.sql.XADataSource
This is achieved using the dataSource
and dataSourceJNDI
attributes.- 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 - transition will be very simple. Moving from other connection pools is also fairly straight forward. + 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 + transition will be very simple. Moving from other connection pools is also fairly straight forward.
The Tomcat connection pool offers a few additional features over what most other pools let you do:
initSQL
- the ability to run a SQL statement exactly once, when the connection is createdvalidationInterval
- in addition to running validations on connections, avoid running them too frequently.jdbcInterceptors
- flexible and pluggable interceptors to create any customizations around the pool,
+ jdbcInterceptors
- flexible and pluggable interceptors to create any customizations around the pool,
the query execution and the result set handling. More on this in the advanced section.fairQueue
- Set the fair flag to true to achieve thread fairness or to use asynchronous connection retrieval
The Tomcat Connection pool is configured as a resource described in The Tomcat JDBC documentation
- With the only difference being that you have to specify the factory
attribute and set the value to
+ With the only difference being that you have to specify the factory
attribute and set the value to
org.apache.tomcat.jdbc.pool.DataSourceFactory
- The connection pool only has another dependency, and that is on tomcat-juli.jar.
- To configure the pool in a stand alone project using bean instantiation, the bean to instantiate is
+ The connection pool only has another dependency, and that is on tomcat-juli.jar.
+ To configure the pool in a stand alone project using bean instantiation, the bean to instantiate is
org.apache.tomcat.jdbc.pool.DataSource
. The same attributes (documented below) as you use to configure a connection
pool as a JNDI resource, are used to configure a data source as a bean.
- The connection pool object exposes an MBean that can be registered.
+ The connection pool object exposes an MBean that can be registered.
In order for the connection pool object to create the MBean, the flag jmxEnabled
has to be set to true.
This doesn't imply that the pool will be registered with an MBean server, merely that the MBean is created.
- In a container like Tomcat, Tomcat itself registers the DataSource with the MBean server, the
- org.apache.tomcat.jdbc.pool.DataSource
object will then register the actual
+ In a container like Tomcat, Tomcat itself registers the DataSource with the MBean server, the
+ org.apache.tomcat.jdbc.pool.DataSource
object will then register the actual
connection pool MBean.
If you're running outside of a container, you can register the DataSource yourself under any object name you specify,
and it propagates the registration to the underlying pool.
To provide a very simple switch to and from commons-dbcp and tomcat-jdbc-pool,
@@ -158,7 +158,7 @@
These attributes are shared between commons-dbcp and tomcat-jdbc-pool, in some cases default values are different. (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 setAutoCommit method will not be called.) (String) The fully qualified Java class name of the JDBC driver to be used. The driver has to be accessible
- from the same classloader as tomcat-jdbc.jar
+ from the same classloader as tomcat-jdbc.jar
(int) The maximum number of connections that should be kept in the pool at all times.
+ (int) The maximum number of connections that should be kept in the pool at all times.
Default value is (int) The maximum number of milliseconds that the pool will wait (when there are no available connections)
- for a connection to be returned before throwing an exception.
+ (int) The maximum number of milliseconds that the pool will wait (when there are no available connections)
+ for a connection to be returned before throwing an exception.
Default value is (boolean) The indication of whether objects will be validated before being borrowed from the pool.
- If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another.
- NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string.
+ (boolean) The indication of whether objects will be validated before being borrowed from the pool.
+ If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another.
+ NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string.
Default value is false
In order to have a more efficient validation, see (boolean) The indication of whether objects will be validated by the idle object evictor (if any).
+ (boolean) The indication of whether objects will be validated by the idle object evictor (if any).
If an object fails to validate, it will be dropped from the pool.
NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string.
- The default value is (String) The SQL query that will be used to validate connections from this pool before returning them to the caller.
- If specified, this query does not have to return any data, it just can't throw a SQLException.
+ (String) The SQL query that will be used to validate connections from this pool before returning them to the caller.
+ If specified, this query does not have to return any data, it just can't throw a SQLException.
The default value is (String) The name of a class which implements the
(int) The number of milliseconds to sleep between runs of the idle connection validation/cleaner thread.
- This value should not be set under 1 second. It dictates how often we check for idle, abandoned connections, and how often
+ (int) The number of milliseconds to sleep between runs of the idle connection validation/cleaner thread.
+ This value should not be set under 1 second. It dictates how often we check for idle, abandoned connections, and how often
we validate idle connections.
The default value is (int) The minimum amount of time an object may sit idle in the pool before it is eligible for eviction.
+ (int) The minimum amount of time an object may sit idle in the pool before it is eligible for eviction.
The default value is (boolean) Flag to remove abandoned connections if they exceed the (int) Timeout in seconds before an abandoned(in use) connection can be removed.
- The default value is (int) Timeout in seconds before an abandoned(in use) connection can be removed.
+ The default value is (String) The connection properties that will be sent to our JDBC driver when establishing new connections.
Format of the string must be [propertyName=property;]*
- NOTE - The "user" and "password" properties will be passed explicitly, so they do not need to be included here.
+ NOTE - The "user" and "password" properties will be passed explicitly, so they do not need to be included here.
The default value is (int) Property not used. The default value is (String) A custom query to be run when a connection is first created.
+ (String) A custom query to be run when a connection is first created.
The default value is (long) avoid excess validation, only run validation at most at this frequency - time in milliseconds.
- If a connection is due for validation, but has been validated previously within this interval, it will not be validated again.
+ If a connection is due for validation, but has been validated previously within this interval, it will not be validated again.
The default value is (boolean) Register the pool with JMX or not.
+ (boolean) Register the pool with JMX or not.
The default value is (boolean) Set to true if you wish that calls to getConnection should be treated
- fairly in a true FIFO fashion. This uses the (boolean) Set to true if you wish the maxActive
:100
- Idle connections are checked periodically (if enabled) and
- connections that been idle for longer than minEvictableIdleTimeMillis
+ Idle connections are checked periodically (if enabled) and
+ connections that been idle for longer than minEvictableIdleTimeMillis
will be released. (also see testWhileIdle
)30000
(30 seconds)validationInterval
Default value is false
@@ -250,22 +250,22 @@
false
and this property has to be set in order for the
+ The default value is false
and this property has to be set in order for the
pool cleaner/test thread is to run (also see timeBetweenEvictionRunsMillis
)
null
.
Example values are SELECT 1
(mysql), select 1 from dual
(oracle), SELECT 1
(MS Sql Server)
org.apache.tomcat.jdbc.pool.Validator
interface and
@@ -276,10 +276,10 @@
com.mycompany.project.SimpleValidator
.
5000
(5 seconds).60000
(60 seconds).removeAbandonedTimout
.
- If set to true a connection is considered abandoned and eligible for removal if it has been in use
- longer than the removeAbandonedTimeout
Setting this to true can recover db connections from
+ If set to true a connection is considered abandoned and eligible for removal if it has been in use
+ longer than the removeAbandonedTimeout
Setting this to true can recover db connections from
applications that fail to close a connection. See also logAbandoned
The default value is false
.60
(60 seconds). The value should be set to the longest running query your applications
+ 60
(60 seconds). The value should be set to the longest running query your applications
might have.null
.false
.null
.30000
(30 seconds).true
.org.apache.tomcat.jdbc.pool.FairBlockingQueue
+ fairly in a true FIFO fashion. This uses the org.apache.tomcat.jdbc.pool.FairBlockingQueue
implementation for the list of the idle connections. The default value is true
.
This flag is required when you want to use asynchronous connection retrieval.
Setting this flag ensures that threads receive connections in the order they arrive.
@@ -404,25 +404,25 @@
ProxyConnection
class to use String.equals
and set to false
when you wish to use ==
when comparing method names. This property does not apply to added interceptors as those are configured individually.
The default value is true
.
-
(int) Timeout value in seconds. Default value is 0
.
Similar to to the removeAbandonedTimeout
value but instead of treating the connection
- as abandoned, and potentially closing the connection, this simply logs the warning if
- logAbandoned
is set to true. If this value is equal or less than 0, no suspect
+ as abandoned, and potentially closing the connection, this simply logs the warning if
+ logAbandoned
is set to true. If this value is equal or less than 0, no suspect
checking will be performed. Suspect checking only takes place if the timeout value is larger than 0 and
the connection was not abandoned or if abandon check is disabled. If a connection is suspect a WARN message gets
- logged and a JMX notification gets sent once.
+ logged and a JMX notification gets sent once.
(boolean) By default, the jdbc-pool will ignore the +
(boolean) By default, the jdbc-pool will ignore the
DataSource.getConnection(username,password)
call, and simply return a previously pooled connection under the globally configured properties username
and password
, for performance reasons.
The pool can however be used with different credentials each time a connection is used. Should you request a connection with the credentials user1/password1 and the connection
was previously connected using user2/password2, the connection will be closed, and reopened with the requested credentials. This way, the pool size is still managed
- on a global level, and not on a per schema level. To enable the functionality described in the
+ on a global level, and not on a per schema level. To enable the functionality described in the
DataSource.getConnection(username,password)
call, simply set the property alternateUsernameAllowed
to true.
The default value is false
.
@@ -430,14 +430,14 @@
(javax.sql.DataSource) +
(javax.sql.DataSource)
(String) +
(String)
Further interceptors will be added to the core of the pool as the need arises. Contributions are always welcome!
-Interceptors are of course not limited to just java.sql.Connection
but can be used to wrap any
- of the results from a method invokation as well. You could build query performance analyzer that provides JMX notifications when a
+
Interceptors are of course not limited to just java.sql.Connection
but can be used to wrap any
+ of the results from a method invokation as well. You could build query performance analyzer that provides JMX notifications when a
query is running longer than the expected time.
Configuring JDBC interceptors is done using the jdbcInterceptors property.
- The property contains a list of semi colon separated class names. If the classname if not fully qualified it will be prefixed with the
+ The property contains a list of semi colon separated class names. If the classname if not fully qualified it will be prefixed with the
org.apache.tomcat.jdbc.pool.interceptor.
prefix.
Example:
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
- is the same as
+ is the same as
jdbcInterceptors="ConnectionState;StatementFinalizer"
@@ -469,7 +469,7 @@
jdbcInterceptors="ConnectionState;StatementFinalizer(useEquals=true)"
-
Abstract base class for all interceptors, can not be instantiated.
@@ -480,34 +480,34 @@ The default value istrue
.
-
+
Caches the connection for the following attributes autoCommit
, readOnly
,
transactionIsolation
and catalog
.
It is a performance enhancement to avoid roundtrip to the database when getters are called or setters are called with an already set value.
-
Keeps track of all statements created using createStatement
, prepareStatement
or prepareCall
and closes these statements when the connection is returned to the pool.
-
See ResultSet.getStatement().getConnection()
and Statement.getConnection()
-
Keeps track of query performance and issues log entries when queries exceed a time threshold of fail.
The log level used is WARN
-
(int as String) The number of milliseconds a query has to exceed before issuing a log alert. @@ -515,11 +515,11 @@
(int as String) The maximum number of queries to keep track of in order to preserve memory space +
(int as String) The maximum number of queries to keep track of in order to preserve memory space
The default value is 1000
.
Extends the SlowQueryReport
and in addition to log entries it issues JMX notification
@@ -527,7 +527,7 @@
This class uses Tomcat's JMX engine so it wont work outside of the Tomcat container.
By default, JMX notifications are sent through the ConnectionPool mbean if it is enabled.
The SlowQueryReportJmx can also register an MBean if notifyPool=false
-
(boolean as String) Set to false if you want JMX notifications to go to the SlowQueryReportJmx MBean
@@ -541,10 +541,10 @@
The abandoned timer starts when a connection is checked out from the pool.
This means if you have a 30second timeout and run 10x10second queries using the connection
it will be marked abandoned and potentially reclaimed depending on the abandonWhenPercentageFull
- attribute.
- Using this interceptor it will reset the checkout timer every time you perform an operation on the connection or execute a
+ attribute.
+ Using this interceptor it will reset the checkout timer every time you perform an operation on the connection or execute a
query successfully.
-
Other examples of Tomcat configuration for JDBC usage can be found in the Tomcat documentation.
Here is a simple example of how to create and use a data source.
+Here is a simple example of how to create and use a data source.
And here is an example on how to configure a resource for JNDI lookups
- +The Tomcat JDBC connection pool supports asynchronous connection retrieval without adding additional threads to the +
The Tomcat JDBC connection pool supports asynchronous connection retrieval without adding additional threads to the
pool library. It does this by adding a method to the data source called Interceptors are a powerful way to enable, disable or modify functionality on a specific connection or its sub components.
There are many different use cases for when interceptors are useful. By default, and for performance reasons, the connection pool is stateless.
- The only state the pool itself inserts are An interceptor has to extend the An interceptor has to extend the
- When a connection is borrowed from the pool, the interceptor can initialize or in some other way react to the event by implementing the
+ When a connection is borrowed from the pool, the interceptor can initialize or in some other way react to the event by implementing the
- method. This method gets called with two parameters, a reference to the connection pool itself
- When a method on the Pool start/stopFuture<Connection> getConnectionAsync()
.
In order to use the async retrieval, two conditions must be met:
1. You must configure the fairQueue
property to be true
.
2. You will have to cast the data source to org.apache.tomcat.jdbc.pool.DataSource
An example of using the async feature is show below.
@@ -672,38 +672,38 @@
defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog
if
+ The only state the pool itself inserts are defaultAutoCommit, defaultReadOnly, defaultTransactionIsolation, defaultCatalog
if
these are set. These 4 properties are only set upon connection creation. Should these properties be modified during the usage of the connection,
the pool itself will not reset them.org.apache.tomcat.jdbc.pool.JdbcInterceptor
class. This class is fairly simple,
+ org.apache.tomcat.jdbc.pool.JdbcInterceptor
class. This class is fairly simple,
You will need to have a no arg constructorConnectionPool parent
+ method. This method gets called with two parameters, a reference to the connection pool itself ConnectionPool parent
and a reference to the underlying connection PooledConnection con
.
java.sql.Connection
object is invoked, it will cause the
+ When a method on the java.sql.Connection
object is invoked, it will cause the
method to get invoked. The Method method
is the actual method invoked, and Object[] args
are the arguments.
- To look at a very simple example, where we demonstrate how to make the invokation to java.sql.Connection.close()
a noop
+ To look at a very simple example, where we demonstrate how to make the invokation to java.sql.Connection.close()
a noop
if the connection has been closed
There is an observation being made. It is the comparison of the method name. One way to do this would be to do
@@ -712,13 +712,13 @@
According to the JVM spec, method names and static final String end up in a shared constant pool, so the reference comparison should work.
One could of course do this as well:
- The compare(String,Method)
will use the useEquals
flag on an interceptor and do either reference comparison or
+ The compare(String,Method)
will use the useEquals
flag on an interceptor and do either reference comparison or
a string value comparison when the useEquals=true
flag is set.
@@ -727,21 +727,21 @@
When overriding these methods, don't forget to call super if you are extending a class other than JdbcInterceptor
-
Configuring interceptors
Interceptors are configured using the jdbcInterceptors
property or the setJdbcInterceptors
method.
An interceptor can have properties, and would be configured like this
-
Interceptor properties
- Since interceptors can have properties, you need to be able to read the values of these properties within your
+ Since interceptors can have properties, you need to be able to read the values of these properties within your
interceptor. Taking an example like the one above, you can override the setProperties
method.