Skip to content
Tadaya Tsuyukubo edited this page Oct 13, 2017 · 3 revisions

Documentation has moved to own html from wiki.

Leaving old wiki just for reference.


ProxyDataSource

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

Spring (Java based)

@Bean
public DataSource dataSource(DataSource actualDataSource) {
    return ProxyDataSourceBuilder
            .create(actualDataSource)
            .logQueryToSysOut()
            .countQuery()
            .build();
}

Spring (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>
-->
  • Use net.ttddyy.dsproxy.listener.ChainListener to specify multiple listeners.

JNDI

<Resource name="jdbc/global/myProxy" 
          auth="Container"
          type="net.ttddyy.dsproxy.support.ProxyDataSource"
          factory="net.ttddyy.dsproxy.support.jndi.ProxyDataSourceObjectFactory"
          description="ds"
          listeners="count,sysout,org.example.SampleListener"
          proxyName="DS-PROXY"
          format="json"
          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.

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.

listeners parameter:

name 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

format parameter:

name description
json set logging output format as JSON

See Tomcat configuration examples here:

  • also see ProxyDataSourceObjectFactory javadoc

Programmatic Construction

Use ProxyDataSourceBuilder.

DataSource ds = 
    ProxyDataSourceBuilder
        .create(actualDataSource)
        .name("myProxy")
        .logQueryToSysOut()
        .asJson()
        .build();

DriverManager

Class.forName("org.hsqldb.jdbcDriver");
Connection realConnection = DriverManager.getConnection("jdbc:hsqldb:mem:aname");
JdbcProxyFactory jdbcProxyFactory = new JdkJdbcProxyFactory();
Connection proxyConnection = jdbcProxyFactory.createConnection(realConnection, new CommonsQueryLoggingListener());
...

QueryExecutionListener

net.ttddyy.dsproxy.listener.QueryExecutionListener is an interface. ProxyDataSource calls it before/after executing queries.

Logging Listeners

These listeners write out queries and parameters to logger or sysout.

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

parameters:

name description
writeDataSourceName set false for not to include datasource name in logging. (default:true)
writeAsJson set true for logging to be JSON format. (default: false)
logLevel set log level for commons or slf4j.

Other listeners

  • net.ttddyy.dsproxy.listener.DataSourceQueryCountListener
    Count query executions. Used for query metrics.

  • net.ttddyy.dsproxy.listener.ChainListener
    If you want to run multiple listeners, you can use ChainListener. ChainListener implements QueryExecutionListener and takes list of other listeners. (composite pattern)

Query metrics

You can collect following 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).

DataSourceQueryCountListener

DataSourceQueryCountListener is a QueryExecutionListener.

In ProxyDataSourceBuilder, count() method adds DataSourceQueryCountListener to ProxyDataSource.

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

Writing query counts(QueryCountLogger)

Once you have configured DataSourceQueryCountListener to collect query metrics, you can add one of following implementations to your application to write out metrics to logger(or sysout).

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

Note:
QueryCountLoggerBuilder builder class provides fluent API for some of the classes.

Web support

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="elapsedTime"/>  - Total TIme

dataSource attribute:
Specify datasource name. If not specified, metric value will be summed up from all datasource.

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