Configuration and Usage

Key Terminology

1. SqlRecorder - The main driver class that proxies the jdbc driver
2. Listeners - The classes that listen to sql events and log them to appropriate places(console, file etc.)
3. Filters - The classes which determine if a query is going to be logged by the listener

Installation

Download source and run:

mvn clean install

Both the source and binary will installed in your local repository. Add the following dependency to your application:

    <groupId>sqlrecorder</groupId>
    <artifactId>sqlrecorder</artifactId>
    <version>0.2</version>

Configuring SqlRecorder

High level steps

1. Create a config file
2. Change your jdbc configuration in app to use the SqlRecorder as your jdbc driver instead of the regular jdbc driver
3. Add system property referring to the config file when starting the jvm

Details

1. SqlRecorder uses spring to bootstrap the initial configuration. The sample config is given here for reference:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:util="http://www.springframework.org/schema/util"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

    <bean id="sqlRecorder" class="org.sqlrecorder.SqlRecorder">
        <constructor-arg value="org.hsqldb.jdbc.JDBCDriver"/>
        <constructor-arg>
            <util:list>
                <bean id="consoleLogger" class="org.sqlrecorder.events.listener.ConsoleOutputListener"></bean>
                <bean id="fileLogger" class="org.sqlrecorder.events.listener.FileOutputListener">
                    <constructor-arg value="/tmp/query.log"/>
                    <constructor-arg ref="filterSql"/>                    
                </bean>
            </util:list>
        </constructor-arg>
    </bean>

    <bean id="filterSql" class="org.sqlrecorder.events.filter.ExactSqlMatchFilter">
        <constructor-arg value="SELECT 1"/>
    </bean>
</beans>

This is a standard spring config file:

a. SqlRecorder takes in the original driver class name it is going to proxy and a list of listeners
b. It needs at least 1 listener
c. Can add as many listeners as we want
d. If multiple listeners are present,queries will be sent to all the listeners
e. Filters are optional
f. Can add as many filters as we want. If there are multiple filters and any one of them return true,the query will not be logged.

2. Change your app configuration to use SqlRecorder as the driver class instead of the regular driver. [TODO add code]
3. Add one of the following system property to the jvm startup option:

      //If the config file is located on a file system that is not on the classpath: 
      -Dsqlrecorder.config.location=file:/home/dir/config.xml

OR

      //If the config file is located in the root classpath(i.e the classes folder)
      -Dsqlrecorder.config.location=classpath:config.xml

Here config.xml is the name of the config file that was created in the first step.

Creating custom listeners

All listeners must implement the following interface:

public interface StatementListener {
    String id();//returns a string identifying the listener
    void queryExecuted(ExecuteEvent e);// the main method which will log the query
    List<String> executedQueries();//returns empty list for now.. for newer features
    boolean returnsExecutedQueries();//returns false for now.. for newer features
    void shutDown();//any cleanup tasks to be done like closing files etc.
}

Look at FileOutputListener.java for details

Creating custom filter

All filters must implement the following interface:

public interface SqlOutputFilter {
    String id();//returns the id for this filter

    //if it returns true, the sql will not be logged to o/p(console,file etc..)
    boolean filter(String sql);
}

Look at ExactSqlMatchFilter.java for details