How To Guide

---

Documentation has moved to own html from wiki.

• User Guide (Current Version)
• User Guide (Snapshot)

Leaving old wiki just for reference.

---

How to configure datasource-proxy.

• Log query executions
• Log query metrics per web request
• Use multiple datasource
• Use multiple listeners
• Construct ProxyDataSource
• Use query metrics in your code
• Clear query metrics at the end of http request
• Use spring bean name/id as datasource name
• Use custom tags
• Use JNDI
• Transform/Replace query statement and/or parameters

---

# Log query executions

Using Log4j or SLF4J, you can log query execution time and all database calls with real query parameters.

setting

java

@Bean
public DataSource dataSource(DataSource actualDataSource) {
    return ProxyDataSourceBuilder
            .create(actualDataSource)
            .logQueryByCommons(INFO)    // or logQueryBySlf4j(INFO)
            .build();
}

xml

<bean id="dataSource" class="net.ttddyy.dsproxy.support.ProxyDataSource">
  <property name="dataSource" ref="[ACTUAL DATASOURCE BEAN]"/>
  <property name="listener">
    <bean class="net.ttddyy.dsproxy.listener.CommonsQueryLoggingListener">
      <property name="logLevel" value="INFO"/> <!-- Default DEBUG -->
    </bean>
  </property>
</bean>

• use net.ttddyy.dsproxy.listener.SLF4JQueryLoggingListener class for SLF4J

log output

Name:MyProxy, Time:1, Success:True, Type:Statement, Batch:False, QuerySize:1, BatchSize:0, Query:["CREATE TABLE users(id INT, name VARCHAR(255))"], Params:[]
Name:MyProxy, 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)]

---

# Log query metrics per web request log statistical data of all jdbc call during a http request-response lifecycle. (per datasource)

metrics:

• total database execution time
• number of database call
• number of queries per type(select, update, insert, delete, and other)

There are three types of support classes for each Log4j and SLF4J.

• Servlet Filter
• Servlet Request Listener
• Spring HandlerInterceptor

Additionally, you can access this metrics information stored in thread local by:

QueryCountHolder.get("datasource_name");

setting

common setting:

java

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

xml

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

### By Servlet Filter

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>

• use net.ttddyy.dsproxy.support.SLF4JQueryCountLoggingServletFilter for SLF4J

### By Servlet Request Listener

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>

• use net.ttddyy.dsproxy.support.SLF4JQueryCountLoggingRequestListener for SLF4J

### By HandlerInterceptor (spring-mvc)

servlet-context.xml

<bean id="urlMapping" class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">
  <property name="urlMap">
    <map>
      <entry key="*" value-ref="someController"/>
    </map>
  </property>
  <property name="interceptors">
    <bean class="net.ttddyy.dsproxy.support.CommonsQueryCountLoggingHandlerInterceptor">
      <property name="clearQueryCounter" value="true"/> <!-- OPTIONAL -->
    </bean>
  </property>
</bean>

• use net.ttddyy.dsproxy.support.SLF4JQueryCountLoggingHandlerInterceptor for SLF4J

log output

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

---

# Use multiple datasource

Set "dataSourceName" to each ProxyDataSource instance.

Statistics will be accumulated among same name ProxyDataSource instances.

setting

java

@Bean
public DataSource dataSourceFoo(DataSource actualDataSource) {
    return ProxyDataSourceBuilder
            .create(actualDataSource)
            .name("ds-foo")
            .countQuery()
            .build();
}

@Bean
public DataSource dataSourceBar(DataSource actualDataSource) {
    return ProxyDataSourceBuilder
            .create(actualDataSource)
            .name("ds-bar")
            .countQuery()
            .build();
}

xml

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

<bean id="dataSourceBar" class="net.ttddyy.dsproxy.support.ProxyDataSource">
  <property name="dataSource" ref="[ANOTHER ACTUAL DATASOURCE BEAN]"/>
  <property name="dataSourceName" value="ds-bar"/>
  <property name="listener">
    <bean class="net.ttddyy.dsproxy.listener.DataSourceQueryCountListener"/>
  </property>
</bean>

##log example

Name:"ds-foo", Time:3, Total:1, Success:1, Failure:0, Select:1, Insert:0, Update:0, Delete:0, Other:0, Statement:0, Prepared:1, Callable:0
Name:"ds-bar", Time:7, Total:3, Success:3, Failure:0, Select:2, Insert:1, Update:0, Delete:0, Other:0, Statement:0, Prepared:1, Callable:0

---

# Use multiple listeners

In XML, ChainListener implements QueryExecutionListener interface and delegates call to its child listeners.

setting

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

<bean id="listeners" class="net.ttddyy.dsproxy.listener.ChainListener">
  <property name="listeners">
    <list>
      <bean class="net.ttddyy.dsproxy.listener.CommonsQueryLoggingListener"/>
      <bean class="net.ttddyy.dsproxy.listener.DataSourceQueryCountListener"/>
    </list>
  </property>
</bean>

---

# Construct `ProxyDataSource`

ProxyDataSource is a POJO which implements DataSource interface. ProxyDataSourceBuilder is preferred way to construct ProxyDataSource, but you can still directly instantiate ProxyDataSource and set it up.

sample

// Retrieve real datasource such as looking up JNDI
//   DataSource dataSource = (DataSource)((new InitialContext()).lookup("java:comp/env/ref/ds"));
//

// prepare multiple listeners
CommonsQueryLoggingListener commonsQueryLoggingListener= new CommonsQueryLoggingListener();
DataSourceQueryCountListener dataSourceQueryCountListener= new DataSourceQueryCountListener();

// use ChainListener to execute multiple listeners
ChainListener chainListener = new ChainListener();
chainListener.addListener(commonsQueryLoggingListener);
chainListener.addListener(dataSourceQueryCountListener);

// craete proxy datasource
ProxyDataSource proxyDS = new ProxyDataSource();
proxyDS.setDataSource(dataSource);
proxyDS.setListener(chainListener);
proxyDS.setDataSourceName("MyDataSource");

---

# Use query metrics in your code

how to

Query metrics is stored in thread local variable. You can retrieve it by following method.
You may need to clean up thread local variable by yourself.

QueryCountHolder.get()

See following classes for clearing query metrics:

• net.ttddyy.dsproxy.support.QueryCounterClearFilter
• net.ttddyy.dsproxy.support.QueryCounterClearServletRequestListener
• net.ttddyy.dsproxy.support.QueryCounterClearHandlerInterceptor

---

# Clear query metrics at the end of http request

At the end of the request-response lifecycle, QueryCount in thread local needs to be cleaned up since web container usually reuses thread. Without resetting thread local value, query metrics might be mixed up in future requests.

If ~QueryCountLogging~ classes are already configured to use, you don't need to add following configuration.
~QueryCountLogging~ classes clean up thread local variable by default.

setting

To clean up query metrics, you can use one of following classes.

• QueryCounterClearFilter (servlet-filter)
• QueryCounterClearHandlerInterceptor (spring-mvc)
• QueryCounterClearServletRequestListener (servlet-request-listener)

QueryCounterClearFilter (servlet-filter)

web.xml

<filter>
  <filter-name>queryCounterClearFilter</filter-name>
  <filter-class>net.ttddyy.dsproxy.support.QueryCounterClearFilter</filter-class>
</filter>
<filter-mapping>
  <filter-name>queryCounterClearFilter</filter-name>
  <servlet-name>....</servlet-name>
</filter-mapping>

QueryCounterClearHandlerInterceptor (spring-mvc)

servlet-context.xml

<bean id="urlMapping" class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">
  <property name="urlMap">
    <map>
      <entry key="*" value-ref="someController"/>
    </map>
  </property>
  <property name="interceptors">
    <bean class="net.ttddyy.dsproxy.support.QueryCounterClearHandlerInterceptor"/>
  </property>
</bean>

QueryCounterClearServletRequestListener (servlet-request-listener)

web.xml

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

---

# Use spring bean name/id as datasource name

Use BeanNameProxyDataSource class. This class implements org.springframework.beans.factory.BeanNameAware interface and uses bean name/id to its datasourceName unless explicitly dataSourceName is specified.

setting

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

---

# Use custom tags

taglibs to display query metrics in jsp.

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

---

# Use JNDI

JNDI supports classes:

• net.ttddyy.dsproxy.support.jndi.ProxyDataSourceObjectFactory

see JNDI configuration detail on Configuration

---

# Transform/Replace query statement and/or parameters

This feature is available since version 1.2.

• For transforming query statement, implement net.ttddyy.dsproxy.transform.QueryTransformer interface.
• For transforming query parameters, implement net.ttddyy.dsproxy.transform.ParameterTransformer interface.

setting example

<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="queryTransformer" ref="[YOUR QUERY TRANSFORMER IMPL BEAN]"/>  <!-- optional -->
  <property name="parameterTransformer" ref="[YOUR QUERY PARAMETER TRANSFORMER IMPL BEAN]"/>  <!-- optional -->
  <property name="listener" ref="[YOUR QUERY-EXECUTION-LISTENER IMPL BEAN]"/>  <!-- optional -->
</bean>

---