Skip to content

Detail

Jump to bottom Edit New page
Tadaya Tsuyukubo edited this page Oct 13, 2017 · 2 revisions

Documentation has moved to own html from wiki.

Leaving old wiki just for reference.

Detail

ProxyDataSource

ProxyDataSource is a java class that implements javax.sql.DataSource. This class works as an entry point for handling(intercepting) all invocations to java.sql.Statement, java.sql.PreparedStatement, and java.sql.CallableStatement.

When constructing ProxyDataSource, it takes QueryExecutionListener and actual datasource. In your application, it behaves as a regular DataSource, but under the hood, it creats proxies and intercept the query executions and invoke QueryExecutionListener before and after calling the actual DB.

To construct ProxyDataSource, ProxyDataSourceBuilder class provides fluent API for not only programmatic construction, but also for enabling built-in QueryExecutionListener (e.g. query and parameter logging).
If you use XML based configuration in spring framework, ProxyDataSource as well as listener classes are also designed to be used in xml directly.

ProxyDataSource can also be constructed via JNDI.
The container, for example tomcat, needs to have datasource-proxy.jar, but your application may not need to have jar dependency because actual implementation class of datasource may not matter to application. For example, if you want to log query executions, your application doesn't have any dependency to classes in datasource-proxy.jar.

Java

DataSource dataSource =  
    ProxyDataSourceBuilder
        .create(actualDataSource)
        .logQueryToSysOut()
        .asJson()
        .build();

XML

<bean id="dataSource" class="net.ttddyy.dsproxy.support.ProxyDataSource">
  <property name="dataSource" ref="[ACTUAL DATASOURCE BEAN]"/>
  <property name="listener" ref="listener"/>
</bean>

<bean id="listener" class="net.ttddyy.dsproxy.listener.CommonsQueryLoggingListener">
  <property name="logLevel" value="INFO"/>       <!-- Default: DEBUG -->
  <property name="writeAsJson" value="true"/>    <!-- Default: false -->
</bean>

<!-- For multiple listeners -->
<!--
<bean id="listener" class="net.ttddyy.dsproxy.listener.ChainListener">
  <property name="listeners">
    <list>
      <bean class="net.ttddyy.dsproxy.listener.CommonsQueryLoggingListener">
      <bean class="net.ttddyy.dsproxy.listener.SLF4JQueryLoggingListener">
      <bean class="net.ttddyy.dsproxy.listener.SystemOutQueryLoggingListener">
      <bean class="net.ttddyy.dsproxy.listener.DataSourceQueryCountListener"/>
    </list>
  </property>
</bean>
-->

Note:
You can use net.ttddyy.dsproxy.listener.ChainListener for injecting multiple listeners to the ProxyDataSource. ChainListener implements QueryExecutionListener interface and takes list of listeners. (composition pattern)

JNDI

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

QueryExecutionListener

QueryExecutionListener is an interface class that you can implement your own logic which will be performed before/after the actual query call.
datasource-proxy comes with QueryExecutionListener implementation classes for logging query executions(query and parameters) and query metrics.

public interface QueryExecutionListener {
    void beforeQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList);
    void afterQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList);
}

ProxyDataSource takes QueryExecutionListener.

method parameters

Based on how queries are executed, beforeQuery/afterQuery methods parameters become followings:

For Normal Statement

  • one QueryInfo in queryInfoList

JDBC API sample:

  statement.executeQuery(...);

For Batch Statement

  • multiple QueryInfo in queryInfoList

JDBC API sample:

  statement.addBatch(...);
  statement.addBatch(...);
  statement.executeBatch(...);

For Normal Prepared/Callable Statement

  • one QueryInfo in queryInfoList
  • one entry in queryArgsList in QueryInfo

JDBC API sample:

PreparedStatement ps = connection.prepareStatement(...);
ps.setInt(1, ...);
ps.executeQuery();

For Batch Prepared/Callable Statement

  • one QueryInfo in queryInfoList
  • multiple entries in queryArgsList in QueryInfo

JDBC API sample:

PreparedStatement ps = connection.prepareStatement(...);

ps.setInt(1, ...);
ps.addBatch();

ps.setInt(1, ...);
ps.addBatch();

ps.executeBatch();

QueryLoggingListeners

These listeners implement QueryExecutionListener and write out queries and query parameters using commons, slf4j or sysout.

  • net.ttddyy.dsproxy.listener.CommonsQueryLoggingListener
  • net.ttddyy.dsproxy.listener.SLF4JQueryLoggingListener
  • net.ttddyy.dsproxy.listener.SystemOutQueryLoggingListener

"output log level", "output format(JSON)", etc. are configurable.

Output example

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

As JSON

{"name":"MyDS", "time":1, "success":true, "type":"Prepared", "batch":true, "querySize":1, "batchSize":2, "query":["INSERT INTO users (id, name) VALUES (?, ?)"], "params":[{"1":"1","2":"foo"},("1":"2","2":"bar")]}

Query metrics

datasource-proxy collects following query metrics:

  • num of queries by query-type(select, insert, update, delete)
  • num of queries by statement-type(statement, prepared, callable)
  • num of database calls (total, success, failure)
  • total time to run queries

To log query metrics, you need to configure 1) DataSourceQueryCountListener to collect query execution metrics, and 2) QueryCountLogger to write out metrics to logger(commons, slf4j, sysout, etc).

net.ttddyy.dsproxy.QueryCount holds all query metrics per datasource.
net.ttddyy.dsproxy.QueryCountHolder provides static method access to QueryCount.
Default implementation uses thread local to store the query counts.

Output example

Name:"MyProxy", Time:6, Total:1, Success:1, Failure:0, Select:1, Insert:0, Update:0, Delete:0, Other:0, Statement:1, Prepared:0, Callable:0

As JSON

{"name":"MyProxy", "time":6, "total":1, "success":1, "failure":0, "select":1, "insert":0, "update":0, "delete":0, "other":0, "statement":1, "prepared":0, "callable":0}

1) Collecting query metrics

Every database call, DataSourceQueryCountListener collects query metrics.

Add the DataSourceQueryCountListener to ProxyDataSource.

DataSource dataSource =  
    ProxyDataSourceBuilder
        .create(actualDataSource)
        .count()    // add `DataSourceQueryCountListener` to ProxyDataSource 
        .build();

2) Writing query counts(QueryCountLogger)

There are multiple implementations to output query metrics into loggers(commons, log4j) or system-out.
Builder class net.ttddyy.dsproxy.support.QueryCountLoggerBuilder is also available for creating QueryCountLogger.

Servlet Filter (javax.servlet.Filter):

  • net.ttddyy.dsproxy.support.CommonsQueryCountLoggingServletFilter
  • net.ttddyy.dsproxy.support.SLF4JQueryCountLoggingServletFilter
  • net.ttddyy.dsproxy.support.SystemOutQueryCountLoggingServletFilter

Servlet Request Listener (javax.servlet.ServletRequestListener):

  • net.ttddyy.dsproxy.support.CommonsQueryCountLoggingRequestListener
  • net.ttddyy.dsproxy.support.SLF4JQueryCountLoggingRequestListener

Spring HandlerInterceptor (org.springframework.web.servlet.HandlerInterceptor):

  • net.ttddyy.dsproxy.support.CommonsQueryCountLoggingHandlerInterceptor
  • net.ttddyy.dsproxy.support.SLF4JQueryCountLoggingHandlerInterceptor
  • net.ttddyy.dsproxy.support.SystemOutQueryCountLoggingHandlerInterceptor

Web support

To access query metrics in jsp, there is a custom tag.

<%@ 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="call"/>  - Num of DB Call
<dsp:metrics metric="elapsedTime"/>  - Total TIme

Query and parameter replacement

net.ttddyy.dsproxy.transform.QueryTransformer and net.ttddyy.dsproxy.transform.ParameterTransformer allows you to modify executing query statement and/or parameters.

ProxyDataSourceBuilder
    .create(actualDataSource)
    .queryTransformer(myQueryTransformer)
    .parameterTransformer(myParameterTransformer)
    .build();
<bean id="dataSource" class="net.ttddyy.dsproxy.support.ProxyDataSource">
  <property name="dataSource" ref="[ACTUAL DATASOURCE BEAN]"/>
  <property name="interceptorHolder" ref="interceptorHolder"/>
</bean>

<bean id="interceptorHolder" class="net.ttddyy.dsproxy.proxy.InterceptorHolder">
  <property name="listener" ref="..."/>
  <property name="queryTransformer" ref="..."/>
  <property name="parameterTransformer" ref="..."/>
</bean>

Sample Applications

There are example projects here: https://github.com/ttddyy/datasource-proxy-examples

Add a custom footer
Add a custom sidebar
Clone this wiki locally