datasource-proxy User Guide

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)]

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)]

{"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"]]}

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}

[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()

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

DataSource dataSource =
    ProxyDataSourceBuilder.create(actualDataSource)  // pass original datasource
        .logQueryByCommons(INFO)    // logQueryBySlf4j(), logQueryByJUL(), logQueryToSysOut()
        .loggingFilter(myFilter)    // filter which queries to log
        .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();  // returns ProxyDataSource instance

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

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

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

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

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)]

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

// v1.9 or above
Formatter formatter = FormatStyle.BASIC.getFormatter();
DataSource ds = ProxyDataSourceBuilder
                  .create(actualDataSource)
                  .formatQuery(formatter::format)
                  .multiline()
                  .logQueryToSysOut()
                  .build()

// prior to v1.9
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()

builder
  .logQueryBySlf4j()
  // Only log queries that are not to the 'audit' table
  .loggingFilter((execInfo, queryInfoList) ->
    queryInfoList.stream().noneMatch(q -> q.getQuery().contains("audit"))
  )

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

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

QueryCountHolder.get()

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

[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()

builder
  .traceMethods()
  .build();

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

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

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

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

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

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

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

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

@RestController
public class MyController {

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

<%@ 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

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

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

> ./mvnw asciidoctor:http@output-html

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