SamLewis.me Just another Java blog

Sometimes it is useful to log the jdbc statements for an application. With a Jdbc proxy driver you can log the actual SQL and parameters are sent to your Jdbc driver to help debug tricky problems.

p6spy has been around for a decade to do this job but it is not actively developed with the last update in 2003. log4jdbc is an actively developed alternative but this post demonstrates jdbcdslog which has some nice features:

• Can be attached to a Connection, Driver or DataSource(XA,Pooling)
• Logs bind parameters for PreparedStatement / CallableStatement
• Logs result of queries from ResultSets
• Can log queries execution time
• Can detect slow queries
• Logging engine agnostic – uses SLF4J
• Can log calling method stack trace.

So lets create an application that tests jdbcdslog with my favourite embedded database H2. First, create a maven pom.xml file:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>me.samlewis.blog</groupId>
	<artifactId>jdbc-logging</artifactId>
	<version>1.0</version>
	<dependencies>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<version>1.3.163</version>
		</dependency>
		<dependency>
			<groupId>com.googlecode.usc</groupId>
			<artifactId>jdbcdslog</artifactId>
			<version>1.0.6</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-simple</artifactId>
			<version>1.5.10</version>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.9</version>
			<scope>test</scope>
		</dependency>
	</dependencies>
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.0.2</version>
				<configuration>
					<source>1.6</source>
					<target>1.6</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Then create a java class named TestJdbcLogging in a package of your choice under src/test/java:

package me.samlewis.blog.jdbclogging;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import org.junit.Test;

public class TestJdbcLogging
{
	@Test
	public void testJdbcLogging() throws Exception
	{
		Class.forName("org.jdbcdslog.DriverLoggingProxy");

		String targetDriver = "org.h2.Driver";
		String targetUrl = "h2:mem:db1";

		String url = "jdbc:jdbcdslog:" + targetUrl + ";targetDriver=" + targetDriver;

		Connection conn = DriverManager.getConnection(url);

		Statement stmt = conn.createStatement();

		stmt.execute("CREATE TABLE CHEESE(NAME VARCHAR(200), STRENGTH INT)");

		stmt.execute("INSERT INTO CHEESE(NAME, STRENGTH) VALUES('Edam', 2)");
		stmt.execute("INSERT INTO CHEESE(NAME, STRENGTH) VALUES('Cheddar', 5)");
		stmt.execute("INSERT INTO CHEESE(NAME, STRENGTH) VALUES('Vieux Boulogne', 10)");
	}
}

The format of the connection url is:

jdbc:jdbcdslog:<original URL>;targetDriver=<original JDBC driver full class name>

Now we can run the test with mvn test and this is the output:

-------------------------------------------------------
 T E S T S
-------------------------------------------------------
Running me.samlewis.blog.jdbclogging.TestJdbcLogging
20 [main] INFO org.jdbcdslog.ConnectionLogger - connect to URL jdbc:jdbcdslog:h2:mem:db1;targetDriver=org.h2.Driver with properties: {}
390 [main] INFO org.jdbcdslog.StatementLogger - java.sql.Statement.execute CREATE TABLE CHEESE(NAME VARCHAR(200), STRENGTH INT)
392 [main] INFO org.jdbcdslog.StatementLogger - java.sql.Statement.execute INSERT INTO CHEESE(NAME, STRENGTH) VALUES('Edam', 2)
392 [main] INFO org.jdbcdslog.StatementLogger - java.sql.Statement.execute INSERT INTO CHEESE(NAME, STRENGTH) VALUES('Cheddar', 5)
393 [main] INFO org.jdbcdslog.StatementLogger - java.sql.Statement.execute INSERT INTO CHEESE(NAME, STRENGTH) VALUES('Vieux Boulogne', 10)
Tests run: 1, Failures: 0, Errors: 0, Skipped: 0, Time elapsed: 0.529 sec

The SQL is logged via SLF4J, in this case just to the console but SLF4J can log to your favourite logging framework.

This example shows that you can add SQL logging to your application with no code changes by adding a jar to the classpath and tweaking the JDBC driver and URL.

You can download the example project here.