1. About

1.1. What is datasource-proxy?

datasource-proxy provides simple API to intercept JDBC interactions and allows user to perform own logic before/after query or method executions.
The proxy works as a javax.sql.DataSource and takes listeners. Pre-defined listeners support query logging, slow query detection, query execution statistics, interaction tracing, etc. Also, custom listeners can easily be added to the listener chain.

1.2. Built-in Support

Query and Parameter Logging

You can log executing database queries and parameters with choice of your logging framework(commons, slf4j, java util logging, sysout, or your own). Output can be formatted as JSON.

Slow Query Detector and Loggers

You can add callbacks for slow queries that take longer than threshold time you specified. Slow query loggers are available out of the box.

Connection ID

Each connection is assigned unique ID that is a sequentially increasing number in default implementation. The connection ID is included in logging entry.

Query Metrics

You can collect statistics of executed query such as total time, number of select, insert, update, delete queries, etc. Output can be formatted as JSON.

Custom Logic Injection for Query Execution and Method Execution

You can write own QueryExecutionListener and they can get called before/after query execution. Also, MethodExecutionListener is invoked every interaction with JDBC API internaction.

Web Application Support

ProxyDataSource can be configured via JNDI. Query metrics information are available per web request basis(request-response lifecycle). For UI with JSP, custom tag to access metrics(<dsp:metrics/>) is available as well.

Query and parameter replacement

QueryTransformer and ParameterTransformer allows you to modify executing query and parameters right before calling the database.

Method Tracing

Intercept JDBC API interactions such as getting connection, creating prepared statement, setting parameters, executing query, etc.

1.3. Sample Log Output

Query execution (single line):

Name:MyProxy, Connection:1, Time:1, Success:True, Type:Statement, Batch:False, QuerySize:1, BatchSize:0, Query:["CREATE TABLE users(id INT, name VARCHAR(255))"], Params:[]
Name:MyProxy, Connection:2, Time:5, Success:True, Type:Prepared, Batch:True, QuerySize:1, BatchSize:2, Query:["INSERT INTO users (id, name) VALUES (?, ?)"], Params:[(1,foo),(2,bar)]

Query execution (multiple lines):

Name:MyProxy, Connection:1, Time:3, Success:True
Type:Callable, Batch:True, QuerySize:1, BatchSize:2
Query:["{call getEmployer (?, ?)}"]
Params:[(id=100,name=foo),(id=200,name=bar)]

JSON output:

{"name":"MyProxy", "connection":1, "time":1, "success":true, "type":"Statement", "batch":false, "querySize":1, "batchSize":0, "query":["CREATE TABLE users(id INT, name VARCHAR(255))"], "params":[]}
{"name":"MyProxy", "connection":2, "time":0, "success":true, "type":"Prepared", "batch":true, "querySize":1, "batchSize":3, "query":["INSERT INTO users (id, name) VALUES (?, ?)"], "params":[["1","foo"],["2","bar"],[3","baz"]]}

Query metrics:

Name:"MyProxy", Time:6, Total:1, Success:1, Failure:0, Select:1, Insert:0, Update:0, Delete:0, Other:0
// JSON output
{"name":"MyProxy", "time":10, "total":3, "success":3, "failure":0, "select":1, "insert":2, "update":0, "delete":0, "other":0}

Method Tracing:

Output:
[1][success][0ms][conn=1] ProxyDataSource#getConnection()
[2][success][1ms][conn=1] JDBCConnection#prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)")
[3][success][0ms][conn=1] JDBCPreparedStatement#setString(2,"FOO")
[4][success][0ms][conn=1] JDBCPreparedStatement#setInt(1,100)
[5][success][0ms][conn=1] JDBCPreparedStatement#addBatch()
[6][success][0ms][conn=1] JDBCPreparedStatement#setInt(1,200)
[7][success][0ms][conn=1] JDBCPreparedStatement#setString(2,"BAR")
[8][success][0ms][conn=1] JDBCPreparedStatement#addBatch()
[9][success][1ms][conn=1] JDBCPreparedStatement#executeBatch()
[10][success][0ms][conn=1] JDBCPreparedStatement#close()
[11][success][0ms][conn=1] JDBCConnection#close()

2. Installation

2.1. Dependency

<dependency>
  <groupId>net.ttddyy</groupId>
  <artifactId>datasource-proxy</artifactId>
  <version>[LATEST_VERSION]</version>
</dependency>

There is no dependent library for using datasource-proxy.
If you choose to use logging support for specific logging library, such as commons or slf4j, you need to specify the dependency explicitly.

datasource-proxy does not bring any libraries via transitive dependency.

2.1.1. Snapshot

Snapshot is available via oss sonatype snapshot repository.

To download snapshot jars, enable sonatype snapshot repository:

<repositories>
  <repository>
    <id>sonatype-snapshots-repo</id>
    <url>https://oss.sonatype.org/content/repositories/snapshots</url>
    <releases>
      <enabled>false</enabled>
    </releases>
    <snapshots>
      <enabled>true</enabled>
    </snapshots>
  </repository>
</repositories>

2.2. Supported Java Versions

datasource-proxy works with java 1.6+.
Java8(JDBC4.2) works fine, and planning to support java9(JDBC 4.3) specific feature.

3. How to use

ProxyDataSource implements javax.sql.DataSource, and works as an entry point for all interceptor logic. You can directly instantiate ProxyDataSource, or use builder class ProxyDataSourceBuilder to build it.
Once ProxyDataSource is setup, you can pass it as a datasource to your application.

3.1. Creating ProxyDataSource

ProxyDataSourceBuilder provides fluent API to construct ProxyDataSource instance. Also, it provides builder methods to register built-in or custom listeners.

DataSource dataSource =
    ProxyDataSourceBuilder.create(actualDataSource)  // pass original datasource
        .logQueryByCommons(INFO)    // logQueryBySlf4j(), logQueryByJUL(), logQueryToSysOut()
        .countQuery()               // enable query count metrics
        .logSlowQueryByCommons(10, TimeUnit.MINUTES)  // also by sl4j, jul, system out
        .proxyResultSet()           // enable proxying ResultSet
        .listener(myListener)       // register my custom listener
        .afterMethod(executionContext -> {    // register a custom listener with lambda
            ...
        })
        .build();

3.1.1. DriverManager and Connection Support

From instance of java.sql.Connection, you can create a proxy that is same as what ProxyDataSource returns.

// obtain connection
Class.forName("org.hsqldb.jdbcDriver");
Connection realConnection = DriverManager.getConnection("jdbc:hsqldb:mem:aname");

ProxyConfig proxyConfig = ProxyConfig.Builder.create()
                             // configure more
                            .build();

JdbcProxyFactory jdbcProxyFactory = new JdkJdbcProxyFactory();
Connection proxyConnection = jdbcProxyFactory.createConnection(realConnection, proxyConfig);
...

3.1.2. Spring XML Configuration Support

Since ProxyConfig uses builder style instance creation, it is difficult to directly create its bean in XML based spring config. ProxyConfigSpringXmlSupport class provides setters to build ProxyConfig aiming to support bean creation in XML based spring config file.

Sample XML config
<bean id="dataSource" primary="true" class="net.ttddyy.dsproxy.support.ProxyDataSource">
  <property name="dataSource" ref="actualDataSource"/>
  <property name="proxyConfig" ref="proxyConfig"/>
  <!--
    Alternatively, you can use SpEL to invoke create() method to get ProxyConfig bean.

    <property name="proxyConfig" value="#{proxyConfigSupport.create()}"/>
    -->
</bean>

<bean id="proxyConfig"
      factory-bean="proxyConfigSupport"
      factory-method="create"/>

<bean id="proxyConfigSupport" class="net.ttddyy.dsproxy.support.ProxyConfigSpringXmlSupport">
  <property name="dataSourceName" value="my-ds"/>
  <property name="queryListener" ref="queryListener"/>
  <property name="methodListener" ref="methodListener"/>
</bean>

<bean id="queryListener" class="net.ttddyy.dsproxy.listener.ChainListener">
  <property name="listeners">
    <list>
      <bean class="net.ttddyy.dsproxy.listener.logging.SystemOutQueryLoggingListener"/>
    </list>
  </property>
</bean>

<bean id="methodListener" class="net.ttddyy.dsproxy.listener.CompositeMethodListener">
  <property name="listeners">
    <list>
      <bean class="net.ttddyy.dsproxy.listener.TracingMethodListener"/>
    </list>
  </property>
</bean>

3.1.3. JNDI Support

<Resource name="jdbc/global/myProxy"
          auth="Container"
          type="net.ttddyy.dsproxy.support.ProxyDataSource"
          factory="net.ttddyy.dsproxy.support.jndi.ProxyDataSourceObjectFactory"
          description="ds"
          listeners="commons,count"
          proxyName="MyProxy"
          dataSource="[REFERENCE_TO_ACTUAL_DATASOURCE_RESOURCE]"  <!-- ex: java:jdbc/global/myDS -->
/>

datasource-proxy.jar and your choice of logging library(commons, slf4j, etc) needs to be accessible from container.

Table 1. JNDI Resource parameters
Parameter Description

dataSource (required)

Reference to actual datasource resource. ex: java:jdbc/global/myDS

proxyName

ProxyDataSource name

logLevel

Loglevel for commons-logging or slf4j. ex: DEBUG, INFO, etc.

loggerName

Name for logger. (since v1.3.1)

listeners

Fully qualified class name of QueryExecutionListener implementation class,or predefined values below. Can be comma delimited.

queryTransformer

Fully qualified class name of QueryTransformer implementation class.

parameterTransformer

Fully qualified class name of ParameterTransformer implementation class.

Table 2. JNDI Resource "listeners" parameter
Parameter Description

sysout

Alias to net.ttddyy.dsproxy.listener.logging.SystemOutQueryLoggingListener

commons

Alias to net.ttddyy.dsproxy.listener.logging.CommonsQueryLoggingListener

slf4j

Alias to net.ttddyy.dsproxy.listener.logging.SLF4JQueryLoggingListener

count

Alias to net.ttddyy.dsproxy.listener.logging.DataSourceQueryCountListener

x.y.z.MyQueryExecutionListener

Fully qualified class name of QueryExecutionListener implementation

Table 3. JNDI Resource "format" parameter
Parameter Description

json

Set logging output format as JSON

See Tomcat configuration examples in datasource-proxy-examples project:

Also see ProxyDataSourceObjectFactory javadoc

3.2. Generated Keys

3.2.1. Auto retrieve generated keys

When 1) generated-keys are available(e.g.: Statement#RETURN_GENERATED_KEYS is specified at execution of statement or at creation of prepared/callable statement) and 2) query execution methods(execute, executeUpdate, executeBatch, etc) are called, automatically calls getGeneratedKeys() and make the returned ResultSet available via ExecutionInfo#getGeneratedKeys() in QueryExecutionListener.

To enable auto retrieval of generated-keys:

builder
  .autoRetrieveGeneratedKeys(...)
  .build();
When getGeneratedKeys() is called:
If there is a cached(auto retrieved) generated-keys and it is still open, then cached ResultSet is returned. If there is no cached generated-keys or cached one exists but it is already closed, it will perform the actual getGeneratedKeys() on underlying Statement object, keep the result into the cache, then return it.

If you want to always retrieve generated-keys without changing application code that may be executing query without Statement#RETURN_GENERATED_KEYS, see Replace invoking method and parameters. You could modify query executions to set RETURN_GENERATED_KEYS.

For batch execution: executeBatch() and executeLargeBatch()

According to JDBC specification, returning getGeneratedKeys() for executeBatch() and executeLargeBatch() is implementation specific:

Note – It is implementation-defined as to whether Statement.getGeneratedKeys will return generated values after invoking the executeBatch or executeLargeBatch methods.

— from JDBC specification

In datasource-proxy, whether to auto-retrieve batch execution is configurable by ProxyDataSourceBuilder#retrieveGeneratedKeysForBatch().

builder
  .autoRetrieveGeneratedKeys(...)
  .retrieveGeneratedKeysForBatch(false, true)  // first arg is for Statement,
                                               // second is for Prepared/Callable
  .build();

Default behavior for batch executions:

Table 4. Default behavior for auto retrieving generated keys for batch execution
Type Auto Retrieval

Statement

NOT Retrieve

Prepared/Callable

Retrieve

3.2.2. Auto close generated keys

When auto-close is enabled, close the auto-retrieved generated-keys after all QueryExecutionListener#afterQuery() are performed.

This option is used with auto retrieve generated keys feature.

builder
  .autoRetrieveGeneratedKeys(true)  // specify `true` or `false` to auto-close the `ResultSet`
  .build();

If you want to reuse the auto-retrieved generated-keys in upper layer, such as OR mapper or application, turn off this auto-close feature; so that, cached generated-keys will be returned when getGeneratedKeys() is called. Based on jdbc-driver implementation, calling getGeneratedKeys() might be limited to once. When you decided to reuse generated-keys, you might need to support repeatable read on the ResultSet. see Proxying generated keys for how to support repeatable read on generated-keys.

When getGeneratedKeys() is called, the returned ResultSet will NOT be closed by this feature since it is not auto-retrieved generated-keys.

3.2.3. Proxying generated keys

Create a proxy for generated-keys' ResultSet. This applies to both auto-retrieved generated-keys and result of getGeneratedKeys().

Usecase

You have a QueryExecutionListener that reads values from generated-keys' ResultSet. Also, in upper layer, such as ORM or application logic, it needs to use the same generated-keys' ResultSet.

Based on ResultSet implementation, reading values may be limited to only once; therefore, when QueryExecutionListener reads values from generated-keys' ResultSet and upper layer tries to read values from it, the ResultSet may not allow the repeated read.

To allow multiple reads, you can proxy the generated-keys' ResultSet with RepeatableReadResultSetProxyLogic.

builder
   // specify proxy factory for generated-keys' result-set
  .autoRetrieveGeneratedKeys(false, new RepeatableReadResultSetProxyLogicFactory());
  // equivalent of above
  //   .autoRetrieveGeneratedKeysWithRepeatableReadProxy(false);
  .build();

3.3. Proxying ResultSet

By default, datasource-proxy does NOT proxy ResultSet. However, in some case, you want to return a proxied ResultSet - for example, apply MethodExecutionListener on ResultSet.

To enable ResultSet proxy, ProxyDataSourceBuilder has proxyResultSet() method.

builder
  // configure listeners
  .proxyResultSet()  // enable proxying result set
  .build();

In addition, the proxyResultSet() method takes ResultSetProxyLogicFactory to add some logic on interacting with ResultSet. Default logic is no-op.

This proxying does NOT apply to the ResultSet from getGeneratedKeys(). To proxy the result of getGeneratedKeys(), see Proxying generated keys.

3.3.1. Repeatable read ResultSet

RepeatableReadResultSetProxyLogic allows ResultSet to be consumed more than once.

builder
  // configure listeners
  .repeatableReadResultSet()
//  .proxyResultSet(new RepeatableReadResultSetProxyLogicFactory())  // same as above
  .build();

3.3.2. Repeatable read ResultSet with CachedRowSetResultSetProxyLogic

New result set proxy, CachedRowSetResultSetProxyLogic is introduced at version 1.4.7. CachedRowSetResultSetProxyLogic uses javax.sql.rowset.CachedRowSet as underlying implementation, which is a sub-interface of ResultSet and provides cursor scrollability in disconnected fashion.
Default CachedRowSet implementation is com.sun.rowset.CachedRowSetImpl for oracle JDK (note for com.sun package), which you can change via RowSetFactory (please see details on JDK documentation).

To use CachedRowSetResultSetProxyLogic for ResultSet proxy, simply supply its proxy-logic factory, CachedRowSetResultSetProxyLogicFactory, to ProxyDataSourceBuilder when enabling result set proxy.

builder
  // proxy for regular ResultSet
  .proxyResultSet(new RepeatableReadResultSetProxyLogicFactory())
   // proxy for generated-keys
  .autoRetrieveGeneratedKeys(false, new RepeatableReadResultSetProxyLogicFactory());
  .build();

4. Listeners

Currently, datasource-proxy provides following listeners:

  • QueryExecutionListener

  • MethodExecutionListener

  • JdbcLifecycleEventListener

4.1. QueryExecutionListener

QueryExecutionListener is called when query execution methods are invoked. (e.g.: execute, executeBatch, executeQuery, etc.)

QueryExecutionListener:
void beforeQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList);

void afterQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList);

ExecutionInfo contains contextual information about query execution(statement type, batch, connection id, etc), and QueryInfo contains actual query string and parameters.

For afterQuery callback, ExecutionInfo contains additional information - result of query, time took to perform query, and thrown exception.

Based on the type of query execution, number of queries and parameters changes.

Execution Type # of Query # of Query Parameters

Statement

1

empty

Statement (batch)

N

empty

Prepared Statement

1

1

Prepared Statement (batch)

1

N

Callable Statement

1

1

Callable Statement (batch)

1

N

4.2. MethodExecutionListener

MethodExecutionListener intercepts JDBC API calls on followings:

  • Connection, Statement, PreparedStatement, CallableStatement: All methods

  • ResultSet: All methods when result set proxy is enabled.

  • ProxyDataSource: getConnection() method

4.2.1. Replace invoking method and parameters

In MethodExecutionListener#beforeMethod(), you could change the invoking method and its parameters on target JDBC object.

For example, in application code, it calls Statement#execute(String). However, in order to leverage generated-keys retrieval, you may want to call Statement#execute(String,int) instead without changing application code.

To update/replace method and parameters, you could set new method or parameters via MethodExecutionContext#setMethod() and MethodExecutionContext#setMethodArgs() in MethodExecutionListener#beforeMethod().

For above example use case:

private static final Method EXECUTE_METHOD = Statement.class.getMethod("execute", String.class);
private static final Method EXECUTE_WITH_INT_METHOD = Statement.class.getMethod("execute", String.class, int.class);

@Override
public void beforeMethod(MethodExecutionContext executionContext) {

  // replace "Statement#execute(sql)" to "Statement#execute(sql, RETURN_GENERATED_KEYS)"
  if(EXECUTE_METHOD.equals(executionContext.getMethod())) {

    Object[] orgArgs = executionContext.getMethodArgs();
    Object[] newArgs = new Object[]{orgArgs[0], Statement.RETURN_GENERATED_KEYS};

    executionContext.setMethod(EXECUTE_WITH_INT_METHOD);  // new method
    executionContext.setMethodArgs(newArgs);  // new method params
  }

}

4.3. JdbcLifecycleEventListener

JdbcLifecycleEventListener interface defines before/after method callbacks for all of JDBC interfaces that datasource-proxy make proxies(DataSource, Connection, Statement, PreparedStatement, CallableStatement, and ResultSet). Also, this interface defines before/after execution of all method invocations and query executions, analogous to MethodExecutionListener and QueryExecutionListener.

To implement your listener, JdbcLifecycleEventListenerAdapter class, which implements all interface methods with no-op, can be extended, and selectively overrides any callback methods you have interests.

DataSource dataSource =
  ProxyDataSourceBuilder.create(actualDataSource)
    .listener(new JdbcLifecycleEventListenerAdapter() {
      @Override
      public void beforeGetConnection(MethodExecutionContext executionContext) {
        // impl for before DataSource#getConnection();
      }

      @Override
      public void afterRollback(MethodExecutionContext executionContext) {
        // impl for after Connection#rollback();
      }
    })
    .build();

4.3.1. Overloaded methods

Since JDBC API classes are hierarchical (e.g.: PreparedStatement extends Statement) some of the methods are overloaded. For example, PreparedStatement#execute method is overloading Statement#execute. Another example is PreparedStatement#setInt and CallableStatement#setInt.

JdbcLifecycleEventListener methods are called back by comparing the invoked method name. Therefore, in this example, [before|after]Execute and [before|after]setInt will be called. If you need to know exactly which of the method is called, you could check the invoked instance, method, or passed arguments to diagnose the actual invoked method when it is overloaded.

5. Built-in Listeners

5.1. Query Logging Listener

Query logging listener is the most used listener that logs executing query with actual parameters to commons/slf4j/JUL logger or System.out.

Sample output

Name:MyProxy, Connection:1, Time:1, Success:True, Type:Statement, Batch:False, QuerySize:1, BatchSize:0, Query:["CREATE TABLE users(id INT, name VARCHAR(255))"], Params:[]
Name:MyProxy, Connection:2, Time:5, Success:True, Type:Prepared, Batch:True, QuerySize:1, BatchSize:2, Query:["INSERT INTO users (id, name) VALUES (?, ?)"], Params:[(1,foo),(2,bar)]
Key Value

Name

Name of ProxyDataSource

Connection

Connection ID

Time

How long query took to execute in ms.

Success

Query execution was successful or not.

Type

Type of statement (Statement/Prepared/Callable).

Batch

Batch execution.

QuerySize

Number of queries.

BatchSize

Number of batch.

Query

Query

Params

Query parameters

ProxyDataSourceBuilder provides methods to easily configure and register these listeners.

builder
  .logQueryByCommons()   // log using Commons
  .logQueryBySlf4j()     // log using SLF4J
  .logQueryByJUL()       // log using Java Util Logging
  .logQueryToSysOut()    // log using System.out

  .multiline()           // enable multiline output
  .build();
If you choose to use commons or slf4j loggers, you need to add dependency to your project accordingly.

By default, each log entry is printed to single line. If multiline() option is set, it prints out a log over multiple lines. Also, json() option prints out log in json format.

Here is another example printing multiline log with formatting query with formatter from Hibernate.

    QueryLogEntryCreator logEntryCreator = new DefaultQueryLogEntryCreator() {
        @Override
        protected String formatQuery(String query) {
            return FormatStyle.BASIC.getFormatter().format(query);  // use Hibernte formatter
        }
    };
    creator.setMultiline(true);  // enable multiline output

    // print out to system.out
    SystemOutQueryLoggingListener listener = new SystemOutQueryLoggingListener();
    listener.setQueryLogEntryCreator(creator);

    return ProxyDataSourceBuilder
                .create(actualDataSource)
                .listener(listener)
                .build()

5.2. Slow Query Logging Listener

When query takes more than specified threshold, SlowQueryListener executes a callback method. The callback is invoked only once for the target query if it exceeds the threshold time.

Combining logging and slow query detection, following built-in classes writes out slow query to its corresponding logger.

  • CommonsSlowQueryListener

  • SLF4JSlowQueryListener

  • JULSlowQueryListener

  • SystemOutSlowQueryListener

ProxyDataSourceBuilder provides methods to configure those listeners.

builder
  .logSlowQueryByCommons(1, TimeUnit.SECONDS)   // log slow query using Commons
  .logSlowQueryBySlf4j(1, TimeUnit.SECONDS)     // log slow query using Slf4J
  .logSlowQueryByJUL(1, TimeUnit.SECONDS)       // log slow query using Java Util Logging
  .logSlowQueryToSysOut(1, TimeUnit.SECONDS)    // log slow query using System.out

  .multiline()           // enable multiline output
  .build();
Elapsed Time

SlowQueryListener triggers callback when query execution exceeds specified threshold time.

For example, when 1 min is set to the threshold in a slow query logging listener and a query takes 5 min to run, it will log the query when 1 min has passed while the query is still running.
This is expected behavior; however, elapsed time in ExecutionInfo is not populated because it is still executing the query.

To log queries that have passed threshold with elapsed time populated, you can customize existing Query Logging Listener instead of SlowQueryListener.

Here is sample implementation with SLF4JQueryLoggingListener:

long thresholdInMills = ...
SLF4JQueryLoggingListener listener = new SLF4JQueryLoggingListener() {
  @Override
  public void afterQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
    // call query logging logic only when it took more than threshold
    if (thresholdInMills <= execInfo.getElapsedTime()) {
      super.afterQuery(execInfo, queryInfoList);
    }
  }
};

listener.setLogLevel(SLF4JLogLevel.WARN);

This implementation will log queries that took longer than specified threshold AFTER query execution has finished. So that, query execution time is populated.

5.3. Query Count Listener

DataSourceQueryCountListener collects statistics of executed queries, such as number of query types(select, insert, update, delete), statement types(statement, prepared, callable), etc. This class also takes strategy to store such metrics. Default strategy stores metrics in thread-local expecting metrics get reset at the end of the request-response lifecycle. Another strategy is SingleQueryCountHolder. This accumulates query metrics from all threads until explicitly clear the metrics.

builder.
  .countQuery()    // enable collecting query metrics
//  .countQuery(new SingleQueryCountHolder())  // enable and specify query count holder
  .build();

Stored metrics can be retrieved by following static method.

QueryCountHolder.get()
Semantics changes based on how metrics are stored - thread local vs global map. When thread local is chosen, you need to clean up the stored metrics at the end of request-response lifecycle. You can call QueryCountHolder.clear() explicitly. Or, if built-in query-count-logging-listeners(will be addressed below) are registered, they automatically call clear() after it logs the count stats.

Combination of count listener and logging, datasource-proxy has built-in support for logging current query count metrics to loggers(commons, slf4j, jul, or system.out). They are available in servlet Filter and ServletRequestListener implementation. Furthermore, there is a custom tag for jsp.

5.3.1. Query count logging with servlet Filter

  • CommonsQueryCountLoggingServletFilter

  • JULQueryCountLoggingServletFilter

  • SLF4JQueryCountLoggingServletFilter

  • SystemOutQueryCountLoggingServletFilter

QueryCountLoggerBuilder class helps constructing filter instance.

with web.xml:
<filter>
  <filter-name>queryCountFilter</filter-name>
  <filter-class>net.ttddyy.dsproxy.support.CommonsQueryCountLoggingServletFilter</filter-class>
  <init-param>  <!-- OPTIONAL -->
    <param-name>clearQueryCounter</param-name>
    <param-value>true</param-value>
  </init-param>
  <init-param>  <!-- OPTIONAL -->
    <param-name>logLevel</param-name>
    <param-value>INFO</param-value>
  </init-param>
</filter>

5.3.2. Query count logging with ServletRequestListener

  • CommonsQueryCountLoggingRequestListener

  • JULQueryCountLoggingRequestListener

  • SLF4JQueryCountLoggingRequestListener

  • SystemOutQueryCountLoggingRequestListener

  • QueryCounterClearServletRequestListener

with web.xml:
<context-param>  <!-- OPTIONAL -->
  <param-name>queryCountCommonsLogLevel</param-name>
  <param-value>INFO</param-value>
</context-param>

<listener>
  <listener-class>net.ttddyy.dsproxy.support.CommonsQueryCountLoggingRequestListener</listener-class>
</listener>

5.3.3. Taglib Support

For jsp, a custom tag is supported to display query count metrics.

Declare custom tag:

<%@ taglib prefix="dsp" uri="http://www.ttddyy.net/dsproxy/tags" %>

When datasource is not specfied, total number of each datasource will be displayed.

<dsp:metrics metric="select"/>
<dsp:metrics metric="update"/>
<dsp:metrics metric="insert"/>
<dsp:metrics metric="delete"/>
<dsp:metrics metric="other"/>
<dsp:metrics metric="total"/>
<dsp:metrics metric="call"/>
<dsp:metrics metric="failure"/>
<dsp:metrics metric="time"/>

Specific datasource

<dsp:metrics metric="select" dataSource="FOO"/>
<dsp:metrics metric="update" dataSource="FOO"/>
<dsp:metrics metric="insert" dataSource="FOO"/>
<dsp:metrics metric="delete" dataSource="FOO"/>
<dsp:metrics metric="other" dataSource="FOO"/>
<dsp:metrics metric="total" dataSource="FOO"/>
<dsp:metrics metric="call" dataSource="FOO"/>
<dsp:metrics metric="failure" dataSource="FOO"/>
<dsp:metrics metric="time" dataSource="FOO"/>
Table 5. metric attribute
Name Description

select

Num of select queries

insert

Num of insert queries

update

Num of update queries

delete

Num of delete queries

other

Num of other queries

statement

Total num of statements

prepared

Total num of prepared statements

callable

Total num of callable statements

total

Total num of queries

success

Num of success queries

failure

Num of failure queries

time

Query execution time

5.4. Tracing Method Listener

TracingMethodListener is a method execution listener that prints out all JDBC API interaction.

Sample JDBC interaction using HSQL:

Code:
Connection conn = dataSource.getConnection()
PreparedStatement ps = conn.prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)");
ps.setString(2, "FOO");
ps.setInt(1, 100);
ps.addBatch();
ps.setInt(1, 200);
ps.setString(2, "BAR");
ps.addBatch();
ps.executeBatch();
ps.close();
conn.close();
Output:
[1][success][0ms][conn=1] ProxyDataSource#getConnection()
[2][success][1ms][conn=1] JDBCConnection#prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)")
[3][success][0ms][conn=1] JDBCPreparedStatement#setString(2,"FOO")
[4][success][0ms][conn=1] JDBCPreparedStatement#setInt(1,100)
[5][success][0ms][conn=1] JDBCPreparedStatement#addBatch()
[6][success][0ms][conn=1] JDBCPreparedStatement#setInt(1,200)
[7][success][0ms][conn=1] JDBCPreparedStatement#setString(2,"BAR")
[8][success][0ms][conn=1] JDBCPreparedStatement#addBatch()
[9][success][1ms][conn=1] JDBCPreparedStatement#executeBatch()
[10][success][0ms][conn=1] JDBCPreparedStatement#close()
[11][success][0ms][conn=1] JDBCConnection#close()

ProxyDataSourceBuilder provides following methods:

builder
  .traceMethods()
  .build();

To change output, the builder method also takes a string consumer.

builder
  // change the output to logger
  .traceMethods(message -> logger.debug(message))
  .build();

traceMethodsWhen method takes a boolean supplier to dynamically turn on/off tracing.

builder
  .traceMethodsWhen(() -> ...condition to perform tracing... )
  .build();

5.5. Custom Listeners

When you create a custom listener, you can register it via listener() or methodListener() methods.

  builder
      .listener(myQueryListener)          // register QueryExecutionListener
      .methodListener(myMethodListener)   // register MethodExecutionListener
      .build();

Also, with Java8 lambda, builder provides simple methods to inline listener definitions.

  • beforeQuery() and afterQuery() to register QueryExecutionListener

  • beforeMethod() and afterMethod() to register MethodExecutionListener

  builder
      // register MethodExecutionListener
      .afterMethod(executionContext -> {
        ....
      })
      // register QueryExecutionListener
      .afterQuery((execInfo, queryInfoList) -> {
          ...
      })

6. Sample Configuration

6.1. Use Case

  • log all queries to System.out as JSON

  • log query metrics to commons logger at the end of each http request using servlet-filter

6.2. pom.xml

Install datasource-proxy and commons-logging since we chose to use commons logger for query metrics.

<dependency>
  <groupId>net.ttddyy</groupId>
  <artifactId>datasource-proxy</artifactId>
  <version>[LATEST-VERSION]</version>
</dependency>

<dependency>
  <groupId>commons-logging</groupId>
  <artifactId>commons-logging</artifactId>
  <version>[VERSION]</version>
</dependency>

6.3. web.xml

Add CommonsQueryCountLoggingServletFilter for query metrics

  <filter>
    <filter-name>queryCountFilter</filter-name>
    <filter-class>net.ttddyy.dsproxy.support.CommonsQueryCountLoggingServletFilter</filter-class>
    <!-- Default to DEBUG level, use "logLevel" filter param to change the log level -->
  </filter>

  <filter-mapping>
   <filter-name>queryCountFilter</filter-name>
   <url-pattern>*</url-pattern>
  </filter-mapping>

6.4. DataSource

Create a proxy datasource that performs:

  • log query execution to System.out as json

  • collect query metrics

Java based:
@Bean
public DataSource dataSource(DataSource actualDataSource) {
    return ProxyDataSourceBuilder
            .create(actualDataSource)
            .name("dataSource")
            .logQueryToSysOut()    // log query execution to System.out
            .asJson()
            .countQuery()          // collect query metrics
            .build();
}
XML based(applicationContext.xml)
<bean id="dataSource" primary="true" class="net.ttddyy.dsproxy.support.ProxyDataSource">
  <property name="dataSource" ref="actualDataSource"/>
  <property name="proxyConfig" ref="proxyConfig"/>
</bean>

<bean id="proxyConfig" factory-bean="proxyConfigSupport" factory-method="create"/>

<bean id="proxyConfigSupport" class="net.ttddyy.dsproxy.support.ProxyConfigSpringXmlSupport">
  <property name="dataSourceName" value="my-ds"/>
  <property name="queryListener" ref="queryListeners"/>
</bean>

<bean id="queryListeners" class="net.ttddyy.dsproxy.listener.ChainListener">
  <property name="listeners">
    <list>
      <bean class="net.ttddyy.dsproxy.listener.logging.SystemOutQueryLoggingListener">
        <property name="writeAsJson" value="true" />
      </bean>
      <bean class="net.ttddyy.dsproxy.listener.DataSourceQueryCountListener"/>
    </list>
  </property>
</bean>

6.5. Controller

Write a simple controller that returns query metrics info as json. (using spring-boot)

@RestController
public class MyController {

    @RequestMapping
    public QueryCount index() throws Exception {
        // do some database operations
        return QueryCountHolder.getGrandTotal();
    }
}

6.6. taglib (optional)

Display query metrics on jsp.

<%@ taglib prefix="dsp" uri="http://www.ttddyy.net/dsproxy/tags" %>

<dsp:metrics metric="select"/>  - Select
<dsp:metrics metric="update" dataSource="FOO" />  - Num of update queries for datasource FOO
<dsp:metrics metric="total"/>  - Total Queries
<dsp:metrics metric="time"/>  - Total TIme

6.7. Accessing application

> curl http://localhost:8080
{"select":1,"insert":0,"update":0,"delete":0,"other":0,"total":1,"failure":0,"success":1,"time":6}

6.8. More examples

See more examples in datasource-proxy-examples project.

7. Development

7.1. Build Documentation

Generate index.html

> ./mvnw asciidoctor:process-asciidoc@output-html

Http preview

> ./mvnw asciidoctor:http@output-html

8. Changelog

1.7

Improvements

  • When asJson() is called on ProxyDataSourceBuilder, slow listeners also write the output as json. (Issue-66)

  • Support DataSource that implements AutoCloseable. (Issue-68)

1.6

Improvements

  • GlobalConnectionIdManager is added. This id-manager generates unique connection ids(sequentially increasing number) across datasources within JVM.

  • On JdbcLifecycleEventListener, method names have updated to map invoked method name only. Classname has removed in order to improve the interception for overloaded methods.

    The callback method is determined by invoked method name. See the details on below "Breaking Changes" section.

Breaking Changes

  • JdbcLifecycleEventListener method names have changed.
    (e.g.: beforeGetConnectionOnDataSource()beforeGetConnection())

    Previously, the method names on the listener had convention of: [before|after]<MethodName>On<ClassName>
    e.g.: beforeGetConnectionOnDataSource()
    This has changed to [before|after]<MethodName>
    e.g.: beforeGetConnection()

    For overloaded methods (e.g.: Statement#execute and PreparedStatement#execute), please reference the documentation: Overloaded methods

1.5.1

New Features and Improvements

  • Add custom value store on ExecutionInfo and MethodExecutionContext Mainly used for passing values between before and after listener callback.

  • Stopwatch and StopwatchFactory are added for elapsed time calculation.
    Elapsed time calculation for query(ExecutionInfo#getElapsedTime) and method(MethodExecutionContext.getElapsedTime()) have been updated to use Stopwatch class.
    Stopwatch is created by StopwatchFactory, and it is configurable in ProxyConfig.
    Two implementation classes are available - SystemStopwatchFactory which creates SystemStopwatch and NanoTimeStopwatchFactory which creates NanoTimeStopwatch.
    SystemStopwatch uses System.currentTimeMillis() and NanoTimeStopwatch uses System.nanoTime() to calculate elapsed time. Default is set to SystemStopwatchFactory.

SlowQueryListener needs to set StopwatchFactory independently from ProxyConfig in order to calculate ExecutionInfo#getElapsedTime() for running slow queries.

Bug Fixes

  • Fix NullPointerException when Object methods(toString(), hashCode(), etc) is called on JdbcLifecycleEventListener.

1.5

  • JdbcLifecycleEventListener is added.
    This listener defines before/after callback methods for all of proxy classes(DataSource, Connection, Statement, PreparedStatement, CallableStatement, and ResultSet).
    See details on JdbcLifecycleEventListener.

1.4.10

  • Fix empty log line for getGeneratedKeys() and getResultSet() method calls. (Issue-54)

1.4.9

1.4.8

  • Populate ExecutionInfo#elapsedTime when query execution has failed.

  • Change ConnectionIdManager#getId() to return String instead of long.

1.4.7

1.4.6

  • Method and parameter replacement in MethodExecutionListener.
    See details on Replace invoking method and parameters.

  • Updated Auto retrieve generated keys feature. Auto-retrieval is now only called when key-generation is specified.
    Following methods specify key-generation:

    • Connection#prepareStatement(String, int[])

    • Connection#prepareStatement(String, String[])

    • Statement#execute(String, int[])

    • Statement#execute(String, String[])

    • Statement#executeUpdate(String, int[])

    • Statement#executeUpdate(String, String[])

    • Statement#executeLargeUpdate(String, int)

    • Statement#executeLargeUpdate(String, int[])

    • Statement#executeLargeUpdate(String, String[])

  • Make generated-keys auto-retrieval for batch execution configurable.

    • ProxyDataSourceBuilder#retrieveGeneratedKeysForBatch

      • batch execution for Statement: auto-retrieval is set to false to default

      • batch execution for Prepared/Callable: auto-retrieval is set to true to default

1.4.5

  • ConnectionInfo now has information whether the connection is closed or not.

  • Generated keys support.
    ResultSet for getGeneratedKeys() now has own configuration. See details on Generated Keys
    Thanks Réda Housni Alaoui(@reda-alaoui) for this contribution!!

1.4.4

  • Added TracingMethodListener that prints out JDBC API interaction. See details on Tracing Method Listener.

  • ProxyConfigSpringXmlSupport is added to support creating ProxyConfig bean in XML based spring config.

Sample XML config:
<bean id="proxyConfig"
      factory-bean="proxyConfigSupport"
      factory-method="create"/>

<bean id="proxyConfigSupport" class="net.ttddyy.dsproxy.support.ProxyConfigSpringXmlSupport">
  <property name="dataSourceName" value="my-ds"/>
  <property name="queryListener" ref="..."/>
  <property name="methodListener" ref="..."/>
</bean>

1.4.3

  • QueryLoggingListeners(Commons, SLF4J, JUL) added overridable loggingCondition callback(boolean supplier) that simply decides whether to skip entire logging logic based on the current log level set on its logger.

    e.g.: when SLF4JQueryLoggingListener writes SQL in DEBUG level, but the logger is set to INFO(more serious than DEBUG), then it will NOT perform logging logic including constructing log statement, etc.

  • Proxying ResultSet is refactored to align how other proxies are managed.

    Also, existing resultset-proxy is renamed to RepeatableReadResultSetProxyLogic. As part of refactoring, ResultSetProxyJdbcProxyFactory is removed. To enable proxying ResultSet, ProxyDataSourceBuilder now has #proxyResultSet() and #repeatableReadResultSet() methods.

    // before
    builder.jdbcProxyFactory(new ResultSetProxyJdbcProxyFactory()).build();
    // new
    builder.repeatableReadResultSet().build();  // or
    builder.proxyResultSet(new RepeatableReadResultSetProxyFactory()).build();
  • ProxyConfig is added to represent all proxy related configurations (datasource name, listeners, proxy factory, connection id manager). All values on InterceptorHolder are moved to ProxyConfig and InterceptorHolder class is removed.

  • MethodExecutionListener is added.

    MethodExecutionListener is a new type of listener that intercepts JDBC API calls: - Connection, Statement, PreparedStatement, CallableStatement: All methods - ResultSet: All methods when result set proxy is enabled. (ProxyDataSourceBuilder#[proxyResultSet()|repeatableReadResultSet()]) - ProxyDataSource: getConnection() method

    listeners can be registered via ProxyDataSourceBuilder#methodListener().

    builder.methodListener(myMethodListener).build();
  • ProxyDataSourceBuilder has added beforeMethod(), afterMethod(), beforeQuery(), and afterQuery() methods. These methods help inlining listener definitions especially with Java8 Lambda expression.

    ProxyDataSourceBuilder
      .create(actualDataSource)
      .name("MyDS")
      .proxyResultSet()  // apply listener on resultset
      // register MethodExecutionListener
      .afterMethod(executionContext -> {
          Method method = executionContext.getMethod();
          Class<?> targetClass = executionContext.getTarget().getClass();
          System.out.println(targetClass.getSimpleName() + "#" + method.getName());
      })
      // register QueryExecutionListener
      .afterQuery((execInfo, queryInfoList) -> {
          System.out.println("Query took " + execInfo.getElapsedTime() + "msec");
      })
      .build();

    sample output:

    # code:
    Connection conn = ds.getConnection();
    PreparedStatement ps = conn.prepareStatement("INSERT INTO users (id, name) VALUES (?, ?)");
    ps.setString(2, "FOO");
    ps.setInt(1, 3);
    ps.addBatch();
    ps.setInt(1, 4);
    ps.setString(2, "BAR");
    ps.addBatch();
    ps.executeBatch();
    ps.close();
    conn.close();
    # output:
    ProxyDataSource#getConnection
    JDBCConnection#prepareStatement
    JDBCPreparedStatement#setString
    JDBCPreparedStatement#setInt
    JDBCPreparedStatement#addBatch
    JDBCPreparedStatement#setInt
    JDBCPreparedStatement#setString
    JDBCPreparedStatement#addBatch
    JDBCPreparedStatement#executeBatch
    Query took 1msec
    JDBCPreparedStatement#close
    JDBCConnection#close

1.4.2

  • Assign connection ID on each connection

    When a connection is obtained from DataSource(DataSource.getConnection()), sequentially increasing unique number is assigned as its connection ID. (default implementation: DefaultConnectionIdManager) The connection ID is printed as Connection in logging.

  • Remove methods that don’t take dataSourceName on JdbcProxyFactory

    Instead, you need to specify null, empty String, or datasource name to the dataSourceName parameter. Following methods are removed:

    • Connection createConnection(Connection connection, InterceptorHolder interceptorHolder);

    • Statement createStatement(Statement statement, InterceptorHolder interceptorHolder);

    • PreparedStatement createPreparedStatement(PreparedStatement preparedStatement, String query, InterceptorHolder interceptorHolder);

  • DataSourceQueryCountListener now takes a strategy to resolve QueryCount.

    Default uses ThreadQueryCountHolder that uses thread local to hold QueryCount. This behaves same as before that the QueryCount holds per request counts(servlet request-response lifecycle).

    SingleQueryCountHolder uses single instance to hold count values. Therefore, this holds total accumulated values from all threads.

  • Update SlowQueryListener to use daemon threads as default.

    It is configurable by SlowQueryListener#setUseDaemonThread method.

1.4.1

  • Add setLog/setLogger to {Commons|SLF4J|JUL}QueryLoggingListener to allow users to set custom logger. Also added getters as well.

  • Update ~QueryCountLoggingServletFilter to allow configuring logger by name

  • Add query count logging implementation for JUL(Java Util Logging)

    • JULQueryCountLoggingHandlerInterceptor

    • JULQueryCountLoggingRequestListener

    • JULQueryCountLoggingServletFilter

  • Fix writing log with null in parameter set methods. (e.g: setString(1, null); )

  • Add SlowQueryListener that triggers callback method when query takes longer than specified threshold time.

    Also, added slow query logging listeners: CommonsSlowQueryListener JULSlowQueryListener SLF4JSlowQueryListener SystemOutSlowQueryListener

    In ProxyDataSourceBuilder, these methods are added: logSlowQueryByCommons() logSlowQueryByJUL() logSlowQueryBySlf4j() logSlowQueryToSysOut()

  • Add support to easily apply formatters on each query for logging.

    DefaultQueryLogEntryCreator#formatQuery() method has added. Subclass can override this method to provides formatted query.

    Example with BasicFormatterImpl in Hibernate.

    // set this instance to logging listeners
    public class PrettyQueryEntryCreator extends DefaultQueryLogEntryCreator {
      private Formatter formatter = FormatStyle.BASIC.getFormatter();  // from hibernate
    
      @Override
      protected String formatQuery(String query) {
        return this.formatter.format(query);
      }
    }
  • Add multiline output support for query logging.

    DefaultQueryLogEntryCreator now has setMultiline() method, and ProxyDataSourceBuilder also has added multiline() method. When multiline is enabled, logged query entries become multi lined.

    sample log output:

    Name:MyDS, Time:0, Success:True
    Type:Prepared, Batch:True, QuerySize:1, BatchSize:2
    Query:["INSERT INTO users (id, name) VALUES (?, ?)"]
    Params:[(1,foo),(2,bar)]

    set up with builder:

    DataSource dataSource =
        ProxyDataSourceBuilder
            .create(actualDataSource)
            .logQueryByCommons(INFO)
            .logSlowQueryByCommons(10, TimeUnit.MINUTES)
            .multiline()   // applies to both query logger and slow query logger
            .build();
  • Deprecate {Commons|SLF4J|JUL}QueryLoggingListener#resetLogger() methods.

    Use newly added setLog(String) or setLogger(String) method instead.

1.4

  • Move logging related listeners to sub package

    • from net.ttddyy.dsproxy.listener to net.ttddyy.dsproxy.listener.logging

  • classes for logging entry creation has been updated

    • QueryLogEntryCreator#getLogEntryAsJson has removed.

    • JSON style log entry creators is pulled up to DefaultJsonQueryLogEntryCreator

    • To use JSON style logging, you can set the QueryLogEntryCreator to [Commons|SLF4J|JUL|SystemOut]QueryLoggingListener#setQueryLogEntryCreator()

    • OracleOutputParameterLogEntryCreator has been split to OutputParameterLogEntryCreator and OutputParameterJsonLogEntryCreator

  • DefaultQueryLogEntryCreator#writeParamsForSingleEntry() has split to writeParamsEntryForSinglePreparedEntry() and writeParamsForSingleCallableEntry()

  • Do not include parameter index for logging prepared statement.

    Before(v1.3.3):

    ..., Params:[(1=10,2=foo),(1=20,2=bar)]
    ..., Params:[(1=30,2=FOO),(1=40,2=BAR)]
    ..., "params":[{"1":"10","2":"foo"},{"1":"20","2":"bar"}]}
    ..., "params":[{"1":"30","2":"FOO"},{"1":"40","2":"BAR"}]}

    Now:

    ..., Params:[(10,foo),(20,bar)]
    ..., Params:[(30,FOO),(40,BAR)]
    ..., "params":[["10","foo"],["20","bar"]]}
    ..., "params":[["30","FOO"],["40","BAR"]]}
  • Add JULQueryLoggingListener which uses JUL(Java Utils Logging) to log executed queries

  • Update logging for setNull and registerOutParameter to include sqltype

    e.g.: NULL(VARCHAR), OUTPUT(VARCHAR[12])

  • ResultSetProxyJdbcProxyFactory to create a proxy ResultSet that can be consumed more than once.

    Thanks Liam Williams for this contribution!!

  • QueryExecutionListener receives same instance of ExecutionInfo in beforeQuery and afterQuery methods

1.3.3

  • update DefaultQueryLogEntryCreator to allow subclasses to override log entry details

1.3.2

  • add CommonsOracleOutputParameterLoggingListener

  • add new listener for oracle to log output params. CommonsOracleOutputParameterLoggingListener

1.3.1

  • make logger name configurable in CommonsQueryLoggingListener and SLF4JQueryLoggingListener

  • setNull and registerOutParameter receives descriptive string value in QueryInfo#getQueryArgsList (temporal implementation)

  • ExecutionInfo will have access to the statement/prepared/callable object used by the execution

1.3

  • update minimum jdk to java6+

  • add java8 new jdbc API (JDBC 4.2)

  • new JNDI support class: ProxyDataSourceObjectFactory

  • new fluent API builder: ProxyDataSourceBuilder

  • logging:

    • update log format

    • add json format

    • more entries: statement-type, batch, batch-size

    • new logger for System.Out

  • change metric names: call ⇒ total, elapsedTime ⇒ time, added success, failure, etc.

  • rename ~QueryCountLoggingFilter to ~QueryCountServletFilter

  • remove deprecated methods

1.2.1

  • fixed prepared statement getting already executed queries in listener (Issue #9)

1.2

  • QueryTransformer and ParameterTransformer for query and parameter replacement