Log SQL that is communicated with the database

Question

I am working on a SQL Logger for my web application. I want to be able to Log the SQL query that is fired by user-interaction with the GUI. I'm working in a Spring environment, working with maven and mybatis. I package my webapp into a war and deploy it onto tomcat.

I want to be able to use the SQL directly to access the database. I can try and use a SocketAppender/HTMLAppender like in Log4j, and post the queries so that other non-SQL people can use it directly without editing it, or removing garbage from the queries to see the populated data.

I came across several Logger tools that can log SQL.

1. jdbcdslog-exp
2. P6Spy
3. JdbcProxy
4. log4jdbc
5. SqlRecorder

What I am specifically looking for:

1. Pure SQL queries without almost little or no garbage (like the time at which the query was fired jdbcdslog-exp recorded data)
2. If I get a little unwanted data I want to be able to remove it to generate pure queries. I'm trying to figure out how jdbcdslog does it, but having a hard time trying to make it work.
3. I want to know if any of the above tools can provide us some metadata about the queries (like which package did the query belong to), so that I can separate out my log data as per packages.

Answer 1

i use jdbcdslog-exp for cases like this.

add jdbcdslog-1.0.6.2.jar to classpath.

add categories to log4j configuration (set INFO for ResultSetLogger too if you want - beware of OutOfMemory)

<category name="org.jdbcdslog.StatementLogger">
  <priority value="INFO"/>
</category>  
<category name="org.jdbcdslog.ResultSetLogger">
  <priority value="FATAL"/>
</category>

if you need to switch statement logging by runtime you can start your log4j with

PropertyConfigurator.configureAndWatch("log4j.xml", 60000);

change your jdbc connection parameters (here example for hsql in jboss)

from

<connection-url>jdbc:hsqldb:hsql://localhost:1801</connection-url>
<driver-class>org.hsqldb.jdbcDriver</driver-class>

to

<connection-url>jdbc:jdbcdslog:hsqldb:hsql://localhost:1801;targetDriver=org.hsqldb.jdbcDriver</connection-url>
<driver-class>org.jdbcdslog.DriverLoggingProxy</driver-class>