1、jar包下载 p6spy-1.3.jar.zip
2、新建spy.properties
#module.log=com.p6spy.engine.logging.P6LogFactory
#重写P6LogFactory
module.log=com.spy.test.P6LogFactory
#设置应用真正使用的JDBC Driver
realdriver=com.mysql.jdbc.Driver
#设置使用p6spy driver来做代理
deregisterdrivers=true
#取消JDBC URL前缀,if=true then url must be prefixed with p6spy:
useprefix=true
#配置记录Log例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,batch,resultset
#日志输出到控制台
appender=com.p6spy.engine.logging.appender.StdoutLogger
#时间格式
dateformat=hh:mm:ss
3、spring数据源配置,封装原来的数据源即可
<bean name="dataSourceDBCP" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="dataSource" class="com.p6spy.engine.spy.P6DataSource">
<constructor-arg ref="dataSourceDBCP"></constructor-arg>
</bean>
4、重写P6LogFactory,目的是为了过滤resultset,一定要使用重写的P6LogFactory,否则将会输出大量重复的sql语句
package com.spy.test;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.p6spy.engine.spy.P6Statement;
public class P6LogFactory extends com.p6spy.engine.logging.P6LogFactory{
@Override
public ResultSet getResultSet(ResultSet real, P6Statement statement,String preparedQuery, String query) throws SQLException {
return real;
}
}
5、调试sql输出结果集
04:14:23|16|0|statement|select count(1)
from t_trading a
where a.customerId=?|select count(1)
from t_trading a
where a.customerId='151'