1

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.
ProgrammingPanda
  • 143
  • 2
  • 11

1 Answers1

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>
Frank M.
  • 997
  • 1
  • 10
  • 19
  • Thank you for your reply. Is there a way by which I can get the class from which the query was fired. There is a %C parameter that can be used with Log4j pattern Layout which can give us the class from which the query was fired, but it does not give me the required class when used with jdbcdslog. – ProgrammingPanda Jul 30 '13 at 17:36
  • in [code.google.com/p/jdbcdslog/wiki/UserGuide](http://code.google.com/p/jdbcdslog/wiki/UserGuide) you can find in section _Configuration parameters_ parameter **jdbcdslog.printStackTrace**. in stackTrace you can find calling class and method. – Frank M. Jul 31 '13 at 07:45
  • M, I want to be able to get access to this information runtime. Like it comes along as a metadata of the query. the issue is here http://stackoverflow.com/questions/17954848/get-the-name-of-the-package-from-which-a-sql-query-was-fired – ProgrammingPanda Jul 31 '13 at 18:15
  • Cool. Sounds interesting.. But i think you need stacktrace in every case. – Frank M. Aug 01 '13 at 09:08