Using log4jdbc to get sql statement with parameters

If you are viewing jdbc by log4j, you see sql statements with ?, ?, ? and in the next line it shows the real parameters values. If you want to get the sql and run it somewhere else, like toad, you need copy the parameters value and replace the ? in the sql statement. If the sql has a lot of parameters, it is boring and slow to get the statement.

Using log4jdbc can provide you a sql statement with parameters in it.

Here is how to setup log4jdbc in weblogic.

• add jar files in weblogic classpath:
  • log4jdbc4-1.2.jar
    slf4j-api-1.6.6.jar
    slf4j-log4j12-1.6.6.jar
    log4j-1.2.16.jar;
• add log4j.xml into classpath
  • The sample log4j.xml is in this dir “LOG4J_HOME/doc”. There is an error in the sample file, look for “<appender-ref ref="connection-appender"/>”, actually there is no connection-appender. It should be jdbc-appender.
    Default, the sql statement “jdbc.sqlonly” is sent to “sql-appender”, which is a file. I change it to output to console
  <appender name="sql-appender" class="org.apache.log4j.ConsoleAppender">
      <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" value="-----&gt; %d{yyyy-MM-dd HH:mm:ss.SSS} &lt;%t&gt; %m%n%n"/>
      </layout>
  </appender>
• change the settings of connection pool on weblogic
  • before it is
    jdbc:oracle:thin:@hostname:port@ORACLE_SID
    oracle.jdbc.OracleDriver
    Change it to
    jdbc:log4jdbc:oracle:thin:@hostname:port@ORACLE_SID
    net.sf.log4jdbc.DriverSpy

After restart weblogic, you should see this on the console.
2012-07-17 09:17:43.664 DEBUG debug: ... log4jdbc initializing ...
2012-07-17 09:17:43.664 DEBUG debug:   log4jdbc.properties not found on classpath
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.debug.stack.prefix is not defined
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.sqltiming.warn.threshold is not defined
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.sqltiming.error.threshold is not defined
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.dump.booleanastruefalse is not defined (using default value false)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.dump.sql.maxlinelength is not defined (using default of 90)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.dump.fulldebugstacktrace is not defined (using default value false)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.statement.warn is not defined (using default value false)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.dump.sql.select is not defined (using default value true)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.dump.sql.insert is not defined (using default value true)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.dump.sql.update is not defined (using default value true)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.dump.sql.delete is not defined (using default value true)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.dump.sql.create is not defined (using default value true)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.dump.sql.addsemicolon is not defined (using default value false)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.auto.load.popular.drivers is not defined (using default value true)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.trim.sql is not defined (using default value true)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.trim.sql.extrablanklines is not defined (using default value true)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.suppress.generated.keys.exception is not defined (using default value false)
2012-07-17 09:17:43.664 DEBUG debug: x log4jdbc.drivers is not defined
2012-07-17 09:17:43.695 DEBUG debug:   FOUND DRIVER com.mysql.jdbc.Driver
2012-07-17 09:17:43.726 DEBUG debug:   FOUND DRIVER com.sybase.jdbc2.jdbc.SybDriver
2012-07-17 09:17:43.726 DEBUG debug:   FOUND DRIVER oracle.jdbc.OracleDriver
2012-07-17 09:17:43.726 DEBUG debug:   FOUND DRIVER oracle.jdbc.driver.OracleDriver
2012-07-17 09:17:43.742 DEBUG debug:   FOUND DRIVER weblogic.jdbc.sqlserver.SQLServerDriver
2012-07-17 09:17:43.742 DEBUG debug: ... log4jdbc initialized! ...

Then run any query through that connection pool, we can get the sql statement with parameters from the console.