用了MyBatis-PLUS的项目 如何优雅的打印SQL

说明

在使用MyBatis-Plus作为ORM框架的时候，会发现默认的日志输出是下面这样的：

在参数少并且SQL简单的情况下，这样的SQL我们能通过手动去替换占位符，来获取到真正执行的SQL。但是如果是比较复杂的SQL，或者查询参数比较多的话，一个个替换就比较费时费力了。
我们可以通过实现com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor 这个接口，从实现优雅的打印带有完整参数值的sql

实现InnerInterceptor接口

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.ParameterMapping;

import java.sql.Connection;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.stream.Collectors;

@Slf4j
public class SqlLogInnerInterceptor implements InnerInterceptor {

    // 重写beforePrepare
    @Override
    public void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {
        BoundSql boundSql = sh.getBoundSql();
        String sql = getSql(boundSql);
        log.info(sql);
    }

    private String getSql(BoundSql boundSql) {
        // 获取到执行的SQL
        String sql = boundSql.getSql();
        // SQL换行替换成空格，多个空格使用一个空格代替
        sql = sql.replaceAll("\r\n", " ")
                .replace("\n", " ")
                .replaceAll("[\\s]+", " ");

        if (CollectionUtil.isEmpty(boundSql.getParameterMappings())) {
            return sql;
        }
        // 各种参数在对象中的路径
        List<String> properties = boundSql.getParameterMappings().stream().map(ParameterMapping::getProperty).collect(Collectors.toList());
        if (CollectionUtil.isEmpty(properties)) {
            return sql;
        }
        // boundSql.getParameterObject() 普通参数所在的类，比如接口参入的参数
        if (boundSql.getParameterObject() != null) {
            JSON parameterObject = JSONUtil.parse(boundSql.getParameterObject());
            for (String property : properties) {
                Object path = JSONUtil.getByPath(parameterObject, property);
                if (ObjectUtils.isNotEmpty(path)) {
                    String value = getParameterValue(path);
                    sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(value));
                    continue;
                }
                // boundSql.getAdditionalParameters() 可以理解为框架定义的参数所在的类，比如分页参数
                Map<String, Object> additionalParameters = boundSql.getAdditionalParameters();
                if (CollectionUtil.isNotEmpty(additionalParameters)) {
                    String object = Convert.toStr(additionalParameters.get(property));
                    sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(object));
                    continue;
                }
                sql = sql.replaceFirst("\\?", "缺失");
            }
            return sql;
        }
        for (String property : properties) {
            Map<String, Object> additionalParameters = boundSql.getAdditionalParameters();
            if (CollectionUtil.isNotEmpty(additionalParameters)) {
                String object = Convert.toStr(additionalParameters.get(property));
                sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(object));
                continue;
            }
            sql = sql.replaceFirst("\\?", "缺失");
        }
        return sql;
    }
    
    private static String getParameterValue(Object object) {
        String value = "";
        if (object instanceof String) {
            value = "'" + object + "'";
        } else if (object instanceof Date) {
            value = "'" + DateUtil.format((Date) object, "yyyy-MM-dd HH:mm:ss") + "'";
        } else if (object instanceof LocalDateTime) {
            value = "'" + LocalDateTimeUtil.format((LocalDateTime) object, "yyyy-MM-dd HH:mm:ss") + "'";
        } else if (!ObjectUtils.isEmpty(object)) {
            value = object.toString();
        }
        return value;
    }
}

将自定义的interceptor注入

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 框架自带的分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        // 自定义优雅打印sql的插件
        interceptor.addInnerInterceptor(new SqlLogInnerInterceptor());
        return interceptor;
    }
}