springboot+Mybatis打印sql执行日志

一、预期效果

使用log日志，记录每条mybatis执行的sql语句。与结果

二、代码

1、springboot项目+继承logj日志体系

可以参考文档

springboot日志整合到数据库(AOP切面)_无敌小田田的博客-CSDN博客_springboot日志管理保存到数据库

2、修改log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <settings>
        <setting name="callSettersOnNulls" value="true"/>
        <setting name="jdbcTypeForNull" value="NULL" />
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--配置分页插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
        <!--mybatis sql解析-->
        <plugin interceptor="com.longze.fengya.aop.MybatisInterceptor">
        </plugin>
        <!--mybatis sql打印出参 设置-->
<!--        <plugin interceptor="com.longze.fengya.aop.InterceptorForQry">-->
<!--        </plugin>-->
    </plugins>


       </configuration>

3、sql解析工具类

import com.google.common.base.Joiner;
import com.google.common.base.Stopwatch;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.regex.Matcher;

/**
 * MyBatis拦截器打印不带问号的完整sql语句
 *
 * @author gogym
 * @version 2018年8月13日
 * @see MybatisInterceptor
 * @since
 */
@Intercepts({
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class,
                Object.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class,
                Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class,
                Object.class, RowBounds.class, ResultHandler.class})})
@Component
public class MybatisInterceptor implements Interceptor
{
    public static final Logger logger = LoggerFactory.getLogger(MybatisInterceptor.class);

    /***
     * @description: 重写intercept，拦截sql。拼接完整sql
     * @return: java.lang.Object
     * @author Fengqx
     * @date: 2022/9/11 10:26
     */
    @Override
    public Object intercept(Invocation invocation)
            throws Throwable
    {
        Object returnValue;
        Stopwatch stopwatch = Stopwatch.createStarted();
        returnValue = invocation.proceed();
        long time = stopwatch.elapsed(TimeUnit.MILLISECONDS);
        try
        {
            // 获取xml中的一个select/update/insert/delete节点，是一条SQL语句
            MappedStatement ms = (MappedStatement)invocation.getArgs()[0];
            Object parameter = null;
            // 获取参数，if语句成立，表示sql语句有参数，参数格式是map形式
            if (invocation.getArgs().length > 1)
            {
                parameter = invocation.getArgs()[1];
            }
            Method method=invocation.getMethod();
            String name = method.getName();
            String commandName = ms.getSqlCommandType().name();
            if(commandName.startsWith("INSERT")){
                name=name+"=新增";
            }else if(commandName.startsWith("UPDATE")){
                name=name+"=修改";
            }else if(commandName.startsWith("DELETE")){
                name=name+"=删除";
            }else if(commandName.startsWith("SELECT")){
                name=name+"=查询";
            }
            String sqlId = ms.getId();      //若获取节点的id，即sql语句的id
            BoundSql boundSql = ms.getBoundSql(parameter); // BoundSql就是封装myBatis最终产生的sql类
            Configuration configuration = ms.getConfiguration(); // 获取节点的配置
            String sql = getSql(configuration, boundSql, sqlId,time,returnValue,name); // 获取到最终的sql语句
            logger.info(sql);
        }
        catch (Exception e)
        {
            logger.error("拦截sql出错，出错原因："+e.getMessage());
            e.printStackTrace();
        }
        // 执行完上面的任务后，不改变原有的sql执行过程
        return returnValue;
    }

    // 封装了一下sql语句，使得结果返回完整xml路径下的sql语句节点id + sql语句
    public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId,
                                long time,Object result,String name)
    {
        Map<String,Object> map = showSql(configuration, boundSql);
        String message = "[MybatisInterceptor]执行["+name+"]时间["+new Timestamp(System.currentTimeMillis()) +"]sql耗时["+(double)time/1000+"]s";
        StringBuilder str = new StringBuilder(100);
        List<ParameterMapping> parameterMappings=boundSql.getParameterMappings();
        str.append("\n").append("---------------------- begin [SQL Execute Message] ------------------\n");
        str.append("[方法] ").append(sqlId).append("。");
        str.append("[sql] ").append(map.get("sql")).append("。");
        str.append("[参数映射] ").append(parameterMappings).append("。");
        str.append("[参数对象] ").append(Joiner.on(",").join((Iterable<?>)map.get("parameters"))).append("。");
        str.append("[结果]");
        if(result!=null){
            if(result instanceof List){
                str.append("共").append(((List<?>) result).size()).append("条记录。");
            }else if(result instanceof Collection){
                str.append("共").append(((Collection) result).size()).append("条记录。");
            }else{
                str.append("共1条记录").append("。");
            }
//            str.append("[结果详情] ").append(JSON.toJSONString(result));
        }else{
            str.append("[结果]  NULL").append("。");
        }
        str.append(" [执行信息] ").append(message);
        str.append("\n");
        str.append("---------------------- end [SQL Execute Message] ------------------\n");
        return str.toString();
    }

    // 如果参数是String，则添加单引号， 如果是日期，则转换为时间格式器并加单引号； 对参数是null和不是null的情况作了处理
    private static String getParameterValue(Object obj)
    {
        String value = null;
        if (obj instanceof String)
        {
            value = "'" + obj.toString() + "'";
        }
        else if (obj instanceof Date)
        {
            SimpleDateFormat formatter=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            value = "'" + formatter.format(obj) + "'";
        }
        else
        {
            if (obj != null)
            {
                value = obj.toString();
            }
            else
            {
                value = "";
            }
        }
        return value;
    }

    // 进行？的替换
    public static Map<String,Object> showSql(Configuration configuration, BoundSql boundSql)
    {   Map<String,Object> map=new HashMap<>(0);
        // 获取参数
        Object parameterObject = boundSql.getParameterObject();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        // sql语句中多个空格都用一个空格代替
        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
        List<String> list=new ArrayList<>();
        if (CollectionUtils.isNotEmpty(parameterMappings) && parameterObject != null)
        {
            // 获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            // 如果根据parameterObject.getClass(）可以找到对应的类型，则替换
            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass()))
            {
                sql = sql.replaceFirst("\\?",
                        Matcher.quoteReplacement(getParameterValue(parameterObject)));
                list.add(parameterObject+"("+parameterObject.getClass().getSimpleName()+")");
            }
            else
            {
                // MetaObject主要是封装了originalObject对象，提供了get和set的方法用于获取和设置originalObject的属性值,主要支持对JavaBean、Collection、Map三种类型对象的操作
                MetaObject metaObject = configuration.newMetaObject(parameterObject);
                for (ParameterMapping parameterMapping : parameterMappings)
                {
                    String propertyName = parameterMapping.getProperty();
                    if (metaObject.hasGetter(propertyName))
                    {
                        Object obj = metaObject.getValue(propertyName);
                        sql = sql.replaceFirst("\\?",
                                Matcher.quoteReplacement(getParameterValue(obj)));
                        list.add(parameterMapping.getProperty()+"-"+(obj==null?null:obj.toString())+"("+(obj==null?null:obj.getClass().getSimpleName())+")");
                    }
                    else if (boundSql.hasAdditionalParameter(propertyName))
                    {
                        // 该分支是动态sql
                        Object obj = boundSql.getAdditionalParameter(propertyName);
                        sql = sql.replaceFirst("\\?",
                                Matcher.quoteReplacement(getParameterValue(obj)));
                        list.add(parameterMapping.getProperty()+"-"+(obj==null?null:obj.toString())+"("+(obj==null?null:obj.getClass().getSimpleName())+")");
                    }
                    else
                    {
                        // 打印出缺失，提醒该参数缺失并防止错位
                        sql = sql.replaceFirst("\\?", "缺失");
                        list.add("缺失");
                    }
                }
            }
        }
        map.put("sql",sql);
        map.put("parameters",list);
        return map;
    }

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

    @Override
    public void setProperties(Properties properties)
    {

    }
}

3、出参解析工具类

import java.util.Properties;

import com.alibaba.fastjson.JSON;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

/**
 * 打印结果拦截器 〈功能详细描述〉
 *
 * @author gogym
 * @version 2019年4月2日
 * @see InterceptorForQry
 * @since
 */
@Intercepts({@Signature(type = Executor.class, method = "query", args = {MappedStatement.class,
        Object.class, RowBounds.class, ResultHandler.class})})
public class InterceptorForQry implements Interceptor
{

    @SuppressWarnings({"rawtypes", "unchecked"})
    public Object intercept(Invocation invocation)
            throws Throwable
    {
        Object result = invocation.proceed(); // 执行请求方法，并将所得结果保存到result中
        String str = JSON.toJSONString(result);
        System.out.println(str);
        return result;
    }

    public Object plugin(Object target)
    {
        return Plugin.wrap(target, this);
    }

    public void setProperties(Properties arg0)
    {}
}

4、最后再搭配着日志级别，便可以打印执行sql

#日志保存
logging.config=classpath:log4j2.xml
#打印sql 本地调试trace  默为DEBUG不打印sql
#logging.level.com.longze.fengya=info

注意事项：

进行打印对象时候，还会发现Object对象被直接打印了路径。

因为创建的实体类很多没有 toString 方法。最好后面新建实体类，新建get、set方法之余增加tostring方法

原文链接：https://blog.csdn.net/qq_36602951/article/details/126889084