Configuring P6Spy

p6spy

P6Spy is a framework that enables database data to be seamlessly intercepted and logged with no code changes to existing application. The P6Spy distribution includes P6Log, an application which logs all JDBC transactions for any Java application.

pom.xml maven dependency

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>2.0.2</version>
</dependency>

spy.properties example

realdriver=oracle.jdbc.driver.OracleDriver
appender=com.p6spy.engine.spy.appender.Slf4JLogger
logMessageFormat=eu.jsan.duvi.util.MultiLineFormat

MultiLineFormat.java message formatting

package eu.jsan.duvi.util;

import org.hibernate.engine.jdbc.internal.BasicFormatterImpl;

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;

public class MultiLineQueryFormat implements MessageFormattingStrategy {

    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category, String prepared, String sql) {
        return new StringBuilder().append(category).append(" (").append(elapsed).append(" ms)")
                .append(new BasicFormatterImpl().format(sql)).toString();
    }
}

JDBC datasource

<Resource name="jdbc/duvi" auth="Container" factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
        type="javax.sql.DataSource" username="username" password="password" driverClassName="com.p6spy.engine.spy.P6SpyDriver"
        url="jdbc:p6spy:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = service_name)))"
        initialSize="5" minIdle="2" maxIdle="5" maxActive="40" maxWait="60000" validateConnectionOnBorrow="true"
        sqlForValidateConnection="SELECT 1 from DUAL" />

jsaneu/Configuring P6Spy.md