解放生产力之MybatisPlus自定义SQL日志打印

自定义SQL日志打印"/>

解放生产力之MybatisPlus自定义SQL日志打印

MybatisPlus本身对SQL的应用淋漓尽致，但是一般控制台中SQL日志打印出来，仍然需要手动去填写参数，特别麻烦，今天就这个问题给出解决方法，只针对与MybatisPlus，那种插件之类的不在讨论范围之内哈；

直接上代码：

package com.**mon.config;import com.baomidou.mybatisplus.core.enums.IEnum;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.handlers.AbstractSqlParserHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.util.CollectionUtils;import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;/*** @ProjectName: envinfo_hbt* @Package: com.**mon.config* @ClassName: MybatisSqlInterceptor* @author: Wang* @description: 解放生产力之MybatisPlus自定义SQL日志打印* @date: 2022/4/26 16:51* @version: 1.0*/@Slf4j
@Intercepts({@Signature(type = StatementHandler.class,method = "query",args = {Statement.class, ResultHandler.class}
), @Signature(type = StatementHandler.class,method = "update",args = {Statement.class}
), @Signature(type = StatementHandler.class,method = "batch",args = {Statement.class}
)})
public class MybatisSqlInterceptor  extends AbstractSqlParserHandler implements Interceptor {/*** 获取配置中需要拦截的表,自定义配置，逗号隔开*/@Value("#{'${tmall.sync.tables:}'.split(',')}")private List<String> tableNames;/*** 忽略插入sql_log表的语句*/private static final String IGNORE_SQL_PREFIX = "insert into sql_log";@Overridepublic Object intercept(Invocation invocation) throws Throwable {if (CollectionUtils.isEmpty(tableNames)) {return invocation.proceed();}StatementHandler statementHandler = PluginUtils.realTarget(invocation.getTarget());MetaObject metaObject = SystemMetaObject.forObject(statementHandler);MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql");String sql = boundSql.getSql().replaceAll("\\s+", " ").toLowerCase();if (sql.toLowerCase(Locale.ENGLISH).startsWith(IGNORE_SQL_PREFIX)) {return invocation.proceed();}List<ParameterMapping> parameterMappings = new ArrayList<>(boundSql.getParameterMappings());Object parameterObject = boundSql.getParameterObject();if (parameterMappings.isEmpty() && parameterObject == null) {log.warn("parameterMappings is empty or parameterObject is null");return invocation.proceed();}Configuration configuration = mappedStatement.getConfiguration();TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();try {this.sqlParser(metaObject);String parameter = "null";MetaObject newMetaObject = configuration.newMetaObject(parameterObject);for (ParameterMapping parameterMapping : parameterMappings) {if (parameterMapping.getMode() == ParameterMode.OUT) {continue;}String propertyName = parameterMapping.getProperty();if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {parameter = getParameterValue(parameterObject);} else if (newMetaObject.hasGetter(propertyName)) {parameter = getParameterValue(newMetaObject.getValue(propertyName));} else if (boundSql.hasAdditionalParameter(propertyName)) {parameter = getParameterValue(boundSql.getAdditionalParameter(propertyName));}sql = sql.replaceFirst("\\?", parameter);}log.info("===========EXECUTE SQL===========");log.info("------> "+ sql);log.info("===========EXECUTE END===========");// 将拦截到的sql语句插入日志表中} catch (Exception e) {log.error(String.format("intercept sql error: [%s]", sql), e);}return invocation.proceed();}/*** 获取参数** @param param Object类型参数* @return 转换之后的参数*/private static String getParameterValue(Object param) {if (param == null) {return "null";}if (param instanceof Number) {return param.toString();}String value = null;if (param instanceof String) {value = param.toString();} else if (param instanceof Date) {new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) param);} else if (param instanceof IEnum) {value = String.valueOf(((IEnum) param).getValue());} else {value = param.toString();}return StringUtils.quotaMark(value);}@Overridepublic Object plugin(Object o) {if (o instanceof StatementHandler) {return Plugin.wrap(o, this);}return o;}@Overridepublic void setProperties(Properties properties) {}}

然后在MybatisPlus配置文件中进行配置即可，如下：

package com.**mon.config;import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;/*** @Package: com.**mon.config* @ClassName: MyBatisPlusConfig* @author: Wang* @description: MP配置项 注意版本 3.3.2* @date: 2022/4/7 11:53* @version: 1.0*/@Configuration
public class MyBatisPlusConfig {/*** 分页插件*/@Beanpublic PaginationInterceptor paginationInterceptor() {PaginationInterceptor paginationInterceptor = new PaginationInterceptor();// 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false// paginationInterceptor.setOverflow(false);// 设置最大单页限制数量，默认 500 条，-1 不受限制paginationInterceptor.setLimit(-1);// 开启 count 的 join 优化,只针对部分 left joinpaginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));return paginationInterceptor;}/*** 自定义sql打印格式* @return*/@Beanpublic MybatisSqlInterceptor performanceInterceptor() {return new MybatisSqlInterceptor();}}

看下SQL打印的样式：

 自动填充相应的参数；