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.
With built-in interceptors, it supports query logging, slow query detection, query statistics, interaction tracing, and more out of the box, as well as adding your own interceptors.

1.2. Feature

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

You can write own QueryExecutionListener and they can get called before/after query execution.

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.

1.3. Example Log

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}

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.SystemOutQueryLoggingListener

commons

Alias to net.ttddyy.dsproxy.listener.CommonsQueryLoggingListener

slf4j

Alias to net.ttddyy.dsproxy.listener.SLF4JQueryLoggingListener

count

Alias to net.ttddyy.dsproxy.listener.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. 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.

3.2.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. Listeners

Currently, datasource-proxy provides two types of listener, QueryExecutionListener and MethodExecutionListener.

3.3.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

3.3.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. Built-in Listeners

4.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()

4.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.

4.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.

4.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>

4.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>

4.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 4. 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

4.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();

4.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) -> {
          ...
      })

5. Sample Configuration

5.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

5.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>

5.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>

5.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>

5.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();
    }
}

5.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

5.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}

5.8. More examples

See more examples in datasource-proxy-examples project.

6. Development

Github repository: https://github.com/ttddyy/datasource-proxy

6.1. Build Documentation

Generate index.html

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

Http preview

> ./mvnw asciidoctor:http@output-html

7. Changelog

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