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 driver
realdriver=oracle.jdbc.driver.OracleDriver

# mysql driver
realdriver=com.mysql.jdbc.Driver

deregisterdrivers=true

executionthreshold=

outagedetection=false
outagedetectioninterval=

################################################################
# COMMON PROPERTIES                                            #
################################################################

# filter what is logged
filter=false

# comma separated list of tables to include when filtering
include     = 
# comma separated list of tables to exclude when filtering
exclude     =

# sql expression to evaluate if using regex filtering
sqlexpression = 


# turn on tracing
autoflush   = true

# sets the date format using Java's SimpleDateFormat routine
dateformat=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 values
excludecategories=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.JakartaRegexMatcher
stringmatcher=

# prints a stack trace for every statement logged
stacktrace=false
# if stacktrace=true, specifies the stack trace to print
stacktraceclass=

# determines if property file should be reloaded
reloadproperties=false
# determines how often should be reloaded in seconds
reloadpropertiesinterval=60

#if=true then url must be prefixed with p6spy:
useprefix=false

#specifies the appender to use for logging
appender=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执行的动态了