springboot+mybatis 使用mybatis的拦截器去控制sql语句的输出

其实很多项目都是直接配置mybatis的配置文件，比如：

# 日志   
logging:
    level:
      com.chongdong.data.mapper : debug
      com.chongdong: info

so，我这个文章貌似没什么实用。

其实不然，如果是批量操作的话，那么日志中的sql和参数是非常庞大的，这对后期的维护和问题的定位很不利，所以我自己通过mybatis的拦截器实现了一个类似mybatis自带日志的日志收集功能。

sql日志拦截器:

package com.chongdong.common.interceptor;
import com.chongdong.common.util.DateUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.DateFormat;
import java.util.*;

/**
 * 自定义mybatis拦截器，用于控制mapper日志的输出
 * 注意： 如果mybatis版本高于3.4，将注解换为第一个
 * @author yanxh
 */
//@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class})})
public class SqlLogInterceptor implements Interceptor {

    /**换行符**/
    public static final String N = "\n";
    /**tab符**/
    public static final String T = "\t";
    /**空格**/
    public static final String BLANK = " ";
    /**双空格**/
    public static final String DOUBLE_BLANK = "  ";
    /**sql限定长度**/
    public static final int LEN = 1000;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
                SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());

        /**
         * 先拦截到RoutingStatementHandler，里面有个StatementHandler类型的delegate变量，
         * 其实现类是BaseStatementHandler，然后就到BaseStatementHandler的成员变量mappedStatement
         */
        MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");

        /**
         * id为执行的mapper方法的全路径名，如com.chongdong.ByrAddressMapper.getListByParams
         */
        String id = mappedStatement.getId();

        /**
         * sql语句类型 select、delete、insert、update
         */
        String sqlCommandType = mappedStatement.getSqlCommandType().toString();
        BoundSql boundSql = statementHandler.getBoundSql();
        Object parameterObject = boundSql.getParameterObject();
        String params = getParameterValue(parameterObject);

        // 获取到原始sql语句
        String sql = boundSql.getSql();
        Object proceed = null;
        
        /**
         * 输出日志
         */
        System.out.println();
        System.out.println(getPrefix(id) + " ==> type: " + sqlCommandType);
        String trimSql = trim(sql);
        if(trimSql.length() > LEN){
            trimSql = "SQL长度超过" + LEN + "，放弃日志输出...";
        }
        System.out.println(getPrefix(id) + " ==> sql: " + trimSql);
        if(params != null && params.length() > LEN){
            params = "参数长度超过" + LEN + "，放弃日志输出...";
        }
        System.out.println(getPrefix(id) + " ==> Parameters: " + params);
        //记录耗时
        long start = System.currentTimeMillis();
        // 执行真正的方法
        proceed = invocation.proceed();
        long end = System.currentTimeMillis();
        if(proceed instanceof PreparedStatement){
            System.out.println(111);
        }
        //System.out.println(getPrefix(id) + "total : " + rows);
        System.out.println(getPrefix(id) + " ==> time: " + (end - start) + "ms");
        System.out.println();
        return proceed;

    }

    /**
     * 获取日志前缀
     * @param id
     * @return
     */
    private String getPrefix(String id) {
        String date = DateUtil.getDateYYYYMMDDHHMMSSS(new Date());
        return date + BLANK + id + BLANK;
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }


    @Override
    public void setProperties(Properties properties) {}

    /**
     * 美化sql（去掉多余空白）
     * @param sql
     * @return
     */
    private String trim(String sql){
        if(StringUtils.isBlank(sql)){
            return "执行sql为空";
        }
        sql = sql.trim();
        while (sql.indexOf(N) != -1){
            sql = sql.replaceAll(N, BLANK);
        }
        while (sql.indexOf(T) != -1){
            sql = sql.replaceAll(T, BLANK);
        }
        while (sql.indexOf(DOUBLE_BLANK) != -1){
            sql = sql.replaceAll(DOUBLE_BLANK, BLANK);
        }
        return sql;
    }


    /**
     * 获取参数
     * @param obj
     * @return
     */
    private String getParameterValue(Object obj) {
        StringBuffer params = new StringBuffer();
        if (obj instanceof String) {
            params.append("'").append(obj.toString()).append("'");
        } else if (obj instanceof Date) {
            DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
            params.append("'").append(formatter.format(new Date())).append("'");
        } else {
            if (obj != null) {
                params.append(obj.toString());
            }
        }
        return params.toString();
    }
}

mybatis的配置类

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactoryBean(DataSource dataSource) {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        if(StringUtils.isNotBlank(typeAliasesPackage)){
            bean.setTypeAliasesPackage(typeAliasesPackage);
        }
       
        // 自定义的sql日志拦截器
		Interceptor sqlLogInterceptor = new SqlLogInterceptor();
		Interceptor[] plugins =  new Interceptor[]{ sqlLogInterceptor};
        bean.setPlugins(plugins);
        try {
            bean.setMapperLocations(resolver.getResources(xmlLocation));
            return bean.getObject();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }
    }