P6spy sql跟踪使用



P6spy sql跟踪使用

1      准备

2      使用

1. 将p6spy.jar加入工程的lib中

2. 将spy.properties加入到工程classpath下面

3      配置

1. 配置原始的driverClassName为com.p6spy.engine.spy.P6SpyDriver；

2. 配置spy.properties

1. 配置真实驱动realdriver=com.mysql.jdbc.Driver

2. 配置日志等级以及不需要日志的类型

   excludecategories=info,debug,result,batch,resultset

 

《spy.properties》

module.log=com.p6spy.engine.logging.P6LogFactory

#module.outage=com.p6spy.engine.outage.P6OutageFactory

 

# oracle driver

#realdriver=oracle.jdbc.driver.OracleDriver

 

#mysql Connector/J driver

realdriver=com.mysql.jdbc.Driver

 

#informix driver

#realdriver=com.informix.jdbc.IfxDriver

 

#ibm db2 driver

#realdriver=COM.ibm.db2.jdbc.net.DB2Driver

 

# themysql open sourcedriver

#realdriver=org.gjt.mm.mysql.Driver

 

#specifies another driver to use

realdriver2=

#specifies a third driver to use

realdriver3=

 

deregisterdrivers=false

 

executionthreshold=

 

 

outagedetection=false

outagedetectioninterval=

 

################################################################

# COMMON PROPERTIES                                           #

################################################################

 

# filter what is logged

filter=false

 

# comma separated list of tablesto include when filtering

include    =

# comma separated list of tablesto exclude when filtering

exclude    =

 

#sql expression toevaluate if usingregex filtering

sqlexpression =

 

 

# turn on tracing

autoflush  =true

 

# sets the date format usingJava's SimpleDateFormat routine

dateformat=

 

#list of categories to explicitlyinclude

includecategories=

 

#list of categories to exclude:error, info, batch, debug, statement,

#commit,rollback andresult are valid values

excludecategories=info,debug,result,batch,resultset

 

 

#allows you to use aregexengine or your own matching engine to determine

#which statements to log

#

#stringmatcher=com.p6spy.engine.common.GnuRegexMatcher

#stringmatcher=com.p6spy.engine.common.JakartaRegexMatcher

stringmatcher=

 

# prints a stack trace for everystatement logged

stacktrace=false

# ifstacktrace=true,specifies the stack trace to print

stacktraceclass=

 

# determines if property fileshould be reloaded

reloadproperties=false

# determines how often should bereloaded in seconds

reloadpropertiesinterval=60

 

#if=true thenurl must beprefixed with p6spy:

useprefix=false

 

#specifies the appender to use forlogging

appender=com.p6spy.engine.logging.appender.Log4jLogger

#appender=com.p6spy.engine.logging.appender.StdoutLogger

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

 

# name oflogfile to use,note Windows users should make sure to use forward slashes in their pathname(e:/test/spy.log) (used for file logger only)

logfile    =d:/log/eorder_spy.log

 

# append to the p6spy log file. if this is set to false the

# log file is truncated everytime. (file logger only)

append=true

log4j.appender.logf=org.apache.log4j.RollingFileAppender

log4j.appender.logf.File=d:/log/eorder_spy.log

log4j.appender.logf.MaxFileSize=10240KB

log4j.appender.logf.layout=org.apache.log4j.PatternLayout

log4j.appender.logf.layout.ConversionPattern=p6spy-%m%n

#Keep three backup files.

log4j.appender.logf.MaxBackupIndex=5

 

#The following are for log4jlogging only

log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender

log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout

log4j.appender.STDOUT.layout.ConversionPattern=p6spy-%m%n

 

log4j.appender.SQLPROFILER_CLIENT=org.apache.log4j.net.SocketAppender

log4j.appender.SQLPROFILER_CLIENT.RemoteHost=localhost

log4j.appender.SQLPROFILER_CLIENT.Port=4445

log4j.appender.SQLPROFILER_CLIENT.LocationInfo=true

 

#log4j.logger.p6spy=INFO,STDOUT

#log4j.logger.p6spy=DEBUG,logf,SQLPROFILER_CLIENT

log4j.logger.p6spy=DEBUG,STDOUT

4      注意说明

4.1    resultset

p6spy会将对查询结果的每条数据记录日志，这样导致调试不方便，日志文件增大速度快。

然而p6spy已经停止升级更新。对此修改源码如下

com.p6spy.engine.spy. P6ResultSet

public boolean next()

   throwsSQLException

 {

   if(this.currRow > -1) {

     StringBuffer buffer = new StringBuffer();

     String comma = "";

     for(Iterator itr = this.resultMap.keySet().iterator(); itr.hasNext(); ) {

       String index = (String)itr.next();

       buffer.append(comma);

       buffer.append(index);

       buffer.append(" = ");

       buffer.append((String)this.resultMap.get(index));

       comma = ", ";

     }

     P6LogQuery.log("resultset", query,buffer.toString());

     this.resultMap.clear();

   }

   this.currRow += 1;

   returnthis.passthru.next();

 }

改为（让resultset能够配置）

public boolean next()

   throwsSQLException

 {

   if(this.currRow > -1) {

     StringBuffer buffer = new StringBuffer();

     String comma = "";

     for(Iterator itr = this.resultMap.keySet().iterator(); itr.hasNext(); ) {

       String index = (String)itr.next();

       buffer.append(comma);

       buffer.append(index);

       buffer.append(" = ");

       buffer.append((String)this.resultMap.get(index));

       comma = ", ";

     }

 

     P6Connection p6connection =(P6Connection)this.statement.getConnection();

     P6LogQuery.logElapsed(p6connection.getId(), System.currentTimeMillis(),"resultset", this.preparedQuery, this.query);

 

     this.resultMap.clear();

   }

   this.currRow += 1;

   returnthis.passthru.next();

 }

 

publicvoidlogSQL(intconnectionId, String now, longelapsed, String category, String prepared, String sql) {

   String logEntry = now + "|"+ elapsed +"|"+(connectionId==-1 ? "" :String.valueOf(connectionId))+"|"+category+"|"+prepared+"|"+sql;  

logText(logEntry);

}

改为如下（优化sql日志）

 public void logSQL(int connectionId, Stringnow, long elapsed, String category, String prepared, String sql)

 {

   long n = Long.parseLong(now);

   String nowDate = newSimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(n));

   sql = sql.replaceAll("\\s+"," ");

   String logEntry = nowDate + "|" +elapsed + "|" + (connectionId != -1 ? String.valueOf(connectionId) :"") + "|" + category + "|" + sql;

   logText(logEntry);

 }

重新编译打包

5      下载

相关文件下载

下载中p6spy.jar已经修改了源码

http://download.csdn.net/detail/u010648190/7643329