使用p6spy监视mybatis输出sql

由于工程是maven创建的，在pom.xml里添加

 

<!-- p6spy自动打印完整sql -->
<dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>2.1.3</version>
        </dependency>

 

并在src/main/resource下添加spy.properties，并添加

 

driverlist=com.mysql.jdbc.Driver

appender=cn.com.mofit.edocSafe.util.MyP6SpyLogger

 

编写自定义的Logger

内容如下：

 

 

package cn.com.mofit.edocSafe.util;




import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintStream;
import java.text.SimpleDateFormat;


import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import com.p6spy.engine.spy.appender.StdoutLogger;


public class MyP6SpyLogger
  extends StdoutLogger
{
  private static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS");
  
  public  void setLogfile(String fileName)
  {
    try
    {
      this.qlog = new PrintStream(new FileOutputStream(fileName, true));
    }
    catch (IOException e)
    {
      e.printStackTrace(System.err);
    }
  }
  
  public void logSQL(int connectionId, String now, long elapsed, String category, String prepared, String sql)
  {
    String judgeSql = sql.trim().toLowerCase();
    if ((judgeSql.startsWith("select")) || (judgeSql.startsWith("insert")) || (judgeSql.startsWith("update")) || (judgeSql.startsWith("delete")))
    {
      String logEntry = "----------# " + this.format.format(Long.valueOf(System.currentTimeMillis())) + " | took " + elapsed + "ms | " + category + " | connection " + connectionId + "\n" + sql + "; \n";
      
      logText(logEntry);
    }
  }
  public void logText(String text) {
 super.setStrategy(new MessageFormattingStrategy() {

@Override
public String formatMessage(int connectionId, String now, long elapsed,
String category, String prepared, String sql) {
String judgeSql = sql.trim().toLowerCase();
   if ((judgeSql.startsWith("select")) || (judgeSql.startsWith("insert")) || (judgeSql.startsWith("update")) || (judgeSql.startsWith("delete")))
   {
     String logEntry = "----------# " + MyP6SpyLogger.format.format(Long.valueOf(System.currentTimeMillis())) + " | took " + elapsed + "ms | " + category + " | connection " + connectionId + "\n" + sql + "; \n";
     
     return logEntry;
   }
   return null;
}
});
 super.logText(text);
  }
}

spring配置文件如下：

 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:jee="http://www.springframework.org/schema/jee" 
    xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:jpa="http://www.springframework.org/schema/data/jpa"
    xmlns:task="http://www.springframework.org/schema/task"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:util="http://www.springframework.org/schema/util"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd  
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd  
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd  
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.1.xsd  
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.1.xsd  
         http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-3.0.xsd
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd  
        http://www.springframework.org/schema/data/jpa http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
        http://www.springframework.org/schema/jee  http://www.springframework.org/schema/jee/spring-jee-4.1.xsd"
    default-lazy-init="false">
    
	<!--将针对注解的处理器配置好  -->
	<context:annotation-config /> 
	<!-- 自动扫描该包，使SpringMVC认为包下用了@controller注解的类是控制器 -->
	<!-- <context:component-scan base-package="com.mofit.fs.exchange" /> -->
	<context:component-scan base-package="com.mofit.fs.exchange">  
    <!--将Controller的注解排除掉 -->   
    <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller" />  
    </context:component-scan>
	
    <!-- 引入jdbc配置文件 -->
	<context:property-placeholder location="classpath:jdbc.properties" />
	
	<!--创建jdbc数据源 -->
    <bean id="dataSource"  
        class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">  
        <property name="driverClassName" value="${jdbc.driverClassName}" />
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
    </bean> 
  <bean id="p6dataSource" class="com.p6spy.engine.spy.P6DataSource">
        <constructor-arg>
            <ref local="dataSource"/>
        </constructor-arg>
    </bean>
  
  	<!-- spring和MyBatis整合，不需要mybatis的配置映射文件 -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">  
        <property name="dataSource" ref="p6dataSource"></property>  
        <property name="configLocation" value="classpath:mybatis-config.xml" />   
    </bean>  

    <!-- mybatis自动扫描加载Sql映射文件/接口 : MapperScannerConfigurer sqlSessionFactory  
  		basePackage:指定sql映射文件/接口所在的包（自动扫描）  
    -->  
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">  
        <property name="basePackage" value="com.mofit.fs.exchange"></property>
    </bean>  
  
    <!-- 事务管理 : DataSourceTransactionManager dataSource:引用上面定义的数据源  -->  
    <bean id="transactionManager"  
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
        <property name="dataSource" ref="p6dataSource"></property>  
    </bean>
	 <!-- 事务控制   -->  
    <tx:annotation-driven transaction-manager="transactionManager"/> 
</beans>  

 

 

 

 

 

然后编译工程，重新部署，重启即可