JDBC interceptors
To see an example of how to use an interceptor, take a look atorg.apache.tomcat.jdbc.pool.interceptor.ConnectionState. This simple interceptor is a cache of three attributes, transaction isolation level, auto commit and read only state, in order for the system to avoid not needed roundtrips to the database.
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 invocation 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
Configuring JDBC interceptors is done using the jdbcInterceptors property. The property contains a list of semicolon separated class names. If the classname is not fully qualified it will be prefixed with theorg.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
jdbcInterceptors=”ConnectionState;StatementFinalizer”
Interceptors can have properties as well. Properties for an interceptor are specified within parentheses after the class name. Several properties are separated by commas.
Example:
jdbcInterceptors=”ConnectionState;StatementFinalizer(useEquals=true)”
Extra whitespace characters around class names, property names and values are ignored.
org.apache.tomcat.jdbc.pool.JdbcInterceptor
Abstract base class for all interceptors, can not be instantiated.
| Attribute | Description |
| useEquals | (boolean) Set to true if you wish the ProxyConnection class to use String.equals and set to false when you wish to use == when comparing method names. The default value is true. |
org.apache.tomcat.jdbc.pool.interceptor.ConnectionState
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.
org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer
Keeps track of all statements created using createStatement, prepareStatement or prepareCall and closes these statements when the connection is returned to the pool.
org.apache.tomcat.jdbc.pool.interceptor.StatementCache
Caches PreparedStatement and/or CallableStatement instances on a connection.
The statements are cached per connection. The count limit is counted globally for all connections that belong to the same pool. Once the count reaches max, subsequent statements are not returned to the cache and are closed immediately.
| Attribute | Description |
| prepared | (boolean as String) Enable caching of PreparedStatement instances created usingprepareStatement calls. The default value is true. |
| callable | (boolean as String) Enable caching of CallableStatement instances created usingprepareCall calls. The default value is false. |
| max | (int as String) Limit on the count of cached statements across the connection pool. The default value is 50. |
org.apache.tomcat.jdbc.pool.interceptor.StatementDecoratorInterceptor
- Interceptor to wrap statements and result sets in order to prevent access to the actual connection using the methods ResultSet.getStatement().getConnection() and Statement.getConnection()
org.apache.tomcat.jdbc.pool.interceptor.QueryTimeoutInterceptor
Automatically calls java.sql.Statement.setQueryTimeout(seconds) when a new statement is created. The pool itself doesn’t timeout the query, it is still up to the JDBC driver to enforce query timeouts.
| Attribute | Description |
| queryTimeout | (int as String) The number of seconds to set for the query timeout The default value is 1000milliseconds. |
org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReport
Keeps track of query performance and issues log entries when queries exceed a time threshold of fail. The log level used is WARN
| Attribute | Description |
| threshold | (int as String) The number of milliseconds a query has to exceed before issuing a log alert. The default value is 1000 milliseconds. |
| maxQueries | (int as String) The maximum number of queries to keep track of in order to preserve memory space The default value is 1000. |
org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx
Extends the SlowQueryReport and in addition to log entries it issues JMX notification for monitoring tools to react to. Inherits all the attributes from its parent class. 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
| Attribute | Description |
| notifyPool | (boolean as String) Set to false if you want JMX notifications to go to the SlowQueryReportJmxMBean The default value is true. |
| objectName | (String) Define a valid javax.management.ObjectName string that will be used to register this object with the platform mbean server The default value is null and the object will be registered using tomcat.jdbc:type=org.apache.tomcat.jdbc.pool.interceptor.SlowQueryReportJmx,name=the-name-of-the-pool |
org.apache.tomcat.jdbc.pool.interceptor.ResetAbandonedTimer
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 query successfully.
