Spring4.0.2使用p6spy打印mybatis执行SQL

使用log4j记录日志时，发现mybatis执行sql语句时打印的比较乱，所以使用p6spy开源单独记录SQL执行的过程，便于分析sql执行的结果因为使用的maven搭建的框架所以在pom.xml中加入p6spy的jar包

<dependency>            <groupId>p6spy</groupId>            <artifactId>p6spy</artifactId>            <version>2.1.3</version>        </dependency>

在web.xml中加入

<context-param><param-name>p6spyConfigLocation</param-name><param-value>WEB-INF/classes/spy.properties</param-value></context-param>

在Spring-mybatis.xml文件中替换原来的数据中链接改为

<!-- JDBC配置 --><bean id="propertyConfigurer"class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"><property name="location" value="classpath:/jdbc.properties" /></bean><bean id="dataSource" class="com.p6spy.engine.spy.P6DataSource"><constructor-arg index="0"><bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"destroy-method="close"><property name="driverClassName" value="${driver}" /><property name="url" value="${url}" /><property name="username" value="${username}" /><property name="password" value="${password}" /><!-- 初始化连接大小 --><property name="initialSize" value="${initialSize}"></property><!-- 连接池最大数量 --><property name="maxActive" value="${maxActive}"></property><!-- 连接池最大空闲 --><property name="maxIdle" value="${maxIdle}"></property><!-- 连接池最小空闲 --><property name="minIdle" value="${minIdle}"></property><!-- 获取连接最大等待时间 --><property name="maxWait" value="${maxWait}"></property></bean></constructor-arg></bean><bean id="dataSources" class="com.p6spy.engine.spy.P6DataSource"><constructor-arg ref="dataSource"></constructor-arg></bean><bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"><property name="dataSource" ref="dataSource"></property></bean>

加入spy.properties文件

module.log=com.p6spy.engine.logging.P6LogFactory#module.outage=com.p6spy.engine.outage.P6OutageFactory# oracle driverrealdriver=oracle.jdbc.driver.OracleDriver# mysql driverrealdriver=com.mysql.jdbc.Driverderegisterdrivers=trueexecutionthreshold=outagedetection=falseoutagedetectioninterval=################################################################# COMMON PROPERTIES                                            ################################################################## filter what is loggedfilter=false# comma separated list of tables to include when filteringinclude     = # comma separated list of tables to exclude when filteringexclude     =# sql expression to evaluate if using regex filteringsqlexpression = # turn on tracingautoflush   = true# sets the date format using Java's SimpleDateFormat routinedateformat=yyyyMMdd HH:mm:ss SSS#list of categories to explicitly include includecategories=#list of categories to exclude: error, info, batch, debug, statement,#commit, rollback and result are valid valuesexcludecategories=info,debug,result,batch#allows you to use a regex engine or your own matching engine to determine #which statements to log##stringmatcher=com.p6spy.engine.common.GnuRegexMatcher#stringmatcher=com.p6spy.engine.common.JakartaRegexMatcherstringmatcher=# prints a stack trace for every statement loggedstacktrace=false# if stacktrace=true, specifies the stack trace to printstacktraceclass=# determines if property file should be reloadedreloadproperties=false# determines how often should be reloaded in secondsreloadpropertiesinterval=60#if=true then url must be prefixed with p6spy:useprefix=false#specifies the appender to use for loggingappender=com.p6spy.engine.spy.appender.FileLogger# name of logfile 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     =  ../logs/spy.log # append to  the p6spy log file.  if this is set to false the# log file is truncated every time.  (file logger only)append=true#The following are for log4j logging only#log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender#log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout#log4j.appender.STDOUT.layout.ConversionPattern=p6spy  [%d] [%t] [%-5p] %c - %m%n log4j.logger.p6spy=INFO

这样就可以在spy.log文件中查看SQL执行的动态了