Search code examples
javaspringloggingp6spylog4jdbc

How to configure p6spy to redirect its output into a file

I was able to successfully configure P6Spy to intercept the JDBC calls and output them to Eclipse's console. However, I'd like to have those logs with the binding parameters captured in the file. Could someone please give me a hand on how to to that. Here are my current configurations:

log4j.properties

# Default log level
log4j.rootCategory=ERROR, console, FileAppender

log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%-5p %d [%t] %c: %m%n

# DEBUG File
log4j.appender.FileAppender=org.apache.log4j.RollingFileAppender
log4j.appender.FileAppender.Threshold=DEBUG
log4j.appender.FileAppender.File=claims_logger.log
log4j.appender.FileAppender.MaxFileSize=1024KB
log4j.appender.FileAppender.MaxBackupIndex=2
log4j.appender.FileAppender.layout=org.apache.log4j.PatternLayout
log4j.appender.FileAppender.layout.ConversionPattern=%-5p %d [%t] %c: %m%n

log4j.logger.org.apache=WARN

 log4j.logger.org.hibernate.engine.StatefulPersistenceContext.ProxyWarnLog=ERROR


log4j.logger.org.springframework.beans.factory=WARN

log4j.logger.org.mule=INFO
log4j.logger.org.mule.modules.sqs=DEBUG

log4j.logger.org.fuwt=DEBUG

spy.properties

realdriver=org.postgresql.Driver
useprefix=false
deregisterdrivers=true
module.log=com.p6spy.engine.logging.P6LogFactory

executionthreshold=

outagedetection=false
outagedetectioninterval=

filter=false

include     =

exclude     =

sqlexpression =

autoflush   = true

dateformat=yyyy-MM-dd hh:mm:ss

includecategories=

excludecategories=info,debug,result,batch

stringmatcher=

stacktrace=false

stacktraceclass=

reloadproperties=false


reloadpropertiesinterval=60


appender=com.p6spy.engine.logging.appender.StdoutLogger
append=true

log4j.additivity.p6spy=false

log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout
log4j.appender.STDOUT.layout.ConversionPattern=p6spy - %m%n


log4j.logger.p6spy=DEBUG,STDOUT

Spring is building its datasource using: 

jdbc.driverClassName=com.p6spy.engine.spy.P6SpyDriver

I'm using P6Spy version 1.3.

I read that log4jdbc is a preferred way of doing SQL interception for my use case, however it seems it requires slightly more configuring, i.e. one needs to modify jdbc URL as per their doc. Any experience on how parameter capture is done in either frameworks and redirected to a file is appreciated.

Thank you in advance.

Solution

  • Turns out, there's a difference between the namespaces for appender classes in p6spy between its major versions. We happened to be using version 1.3 of p6spy where FileLogger appender resides in:

    appender=com.p6spy.engine.logging.appender.FileLogger

    Mistakenly, we were using the following configuration, which pertains to version 2.x of p6spy:

    appender=com.p6spy.engine.spy.appender.FileLogger

    and that lead to wrong behavior.

    What clued us on to this was more thorough examination of the console log where the following line was discovered:

    Cannot instantiate com.p6spy.engine.spy.appender.FileLogger, even on second attempt.  Logging to file log4jaux.log: java.lang.ClassNotFoundException: com.p6spy.engine.spy.appender.FileLogger