一招搞定！自定义MyBatis拦截器，这才是我想看的SQL日志！-育师

我们使用 MyBatis Plus 通常通过配置文件中设置 log-impl 属性来指定日志实现，以打印 SQL 语句。

mybatis-plus: configuration: log-impl:org.apache.ibatis.logging.stdout.StdOutImpllogging: level: org.ylzl.eden.demo.mapper:DEBUG

打印出来的 SQL 内容如下:

==> Preparing: SELECT id,login,email,activated,locked,lang_key,activation_key,reset_key,reset_date,created_by,created_date,last_modified_by,last_modified_date FROM demo_user WHERE id=? ==> Parameters: 1(Long) <== Columns: ID, LOGIN, EMAIL, ACTIVATED, LOCKED, LANG_KEY, ACTIVATION_KEY, RESET_KEY, RESET_DATE, CREATED_BY, CREATED_DATE, LAST_MODIFIED_BY, LAST_MODIFIED_DATE <== Row: 1, admin, 1813986321@qq.com, TRUE, FALSE, zh-cn, null, null, null, system, 2025-02-10 22:31:03.818, system, null <== Total: 1

然而，默认的日志输出格式存在以下不足：

缺少日志时间，无法快速定位 SQL 执行时间。
SQL 语句可读性差，复杂的 SQL 语句难以阅读。
日志存储成本高：SQL 模板占用较多字符，增加了日志存储成本。

目标

通过 MyBatis 的拦截器实现 SQL 原始语句的打印。

实现

首先，自定义 MyBatis 拦截器，实现org.apache.ibatis.plugin.Interceptor接口。

@Intercepts({@Signature(method ="query", type = Executor.class,args= {MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class}), @Signature(method="query", type = Executor.class,args= {MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class,CacheKey.class,BoundSql.class}), @Signature(method="update", type = Executor.class,args= {MappedStatement.class,Object.class}) })publicclassMybatisSqlLogInterceptorimplementsInterceptor{privatestaticfinalLogger log = LoggerFactory.getLogger("MybatisSqlLog");privateDuration slownessThreshold = Duration.ofMillis(1000);@OverridepublicObjectintercept(Invocation invocation)throwsThrowable { MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; String mapperId = mappedStatement.getId(); String originalSql = MybatisUtils.getSql(mappedStatement, invocation);longstart = SystemClock.now(); Object result = invocation.proceed();longduration = SystemClock.now() - start;// 当 SQL 执行超过我们设置的阈值，转为 WARN 级别if(Duration.ofMillis(duration).compareTo(slownessThreshold) < 0) { log.info("{} execute sql: {} ({} ms)", mapperId, originalSql, duration); }else{ log.warn("{} execute sql took more than {} ms: {} ({} ms)", mapperId, slownessThreshold.toMillis(), originalSql, duration); }returnresult; }@OverridepublicObjectplugin(Object target) {if(targetinstanceofExecutor) {returnPlugin.wrap(target,this); }returntarget; }// 设置慢 SQL 阈值，单位为秒publicvoidsetSlownessThreshold(Duration slownessThreshold) {this.slownessThreshold = slownessThreshold; } }

笔者编写了一个工具类负责解析 MyBatis 执行语句，还原为可执行的 SQL 内容。

@UtilityClasspublicclassMybatisUtils{privatestaticfinalPattern PARAMETER_PATTERN = Pattern.compile("\\?");publicStringgetSql(MappedStatement mappedStatement, Invocation invocation) { Object parameter =null;if(invocation.getArgs().length > 1) { parameter = invocation.getArgs()[1]; } BoundSql boundSql = mappedStatement.getBoundSql(parameter); Configuration configuration = mappedStatement.getConfiguration();returnresolveSql(configuration, boundSql); }privatestaticStringresolveSql(Configuration configuration, BoundSql boundSql) { Object parameterObject = boundSql.getParameterObject(); List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); String sql = boundSql.getSql().replaceAll("[\\s]+"," ");if(!parameterMappings.isEmpty() && parameterObject !=null) { TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();if(typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(resolveParameterValue(parameterObject))); }else{ MetaObject metaObject = configuration.newMetaObject(parameterObject); Matcher matcher = PARAMETER_PATTERN.matcher(sql); StringBuffer sqlBuffer =newStringBuffer();for(ParameterMapping parameterMapping : parameterMappings) { String propertyName = parameterMapping.getProperty(); Object obj =null;if(metaObject.hasGetter(propertyName)) { obj = metaObject.getValue(propertyName); }elseif(boundSql.hasAdditionalParameter(propertyName)) { obj = boundSql.getAdditionalParameter(propertyName); }if(matcher.find()) { matcher.appendReplacement(sqlBuffer, Matcher.quoteReplacement(resolveParameterValue(obj))); } } matcher.appendTail(sqlBuffer); sql = sqlBuffer.toString(); } }returnsql; }privatestaticStringresolveParameterValue(Object obj) {if(objinstanceofCharSequence) {return"'"+ obj +"'"; }if(objinstanceofDate) { DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);return"'"+ formatter.format(obj) +"'"; }returnobj ==null?"": String.valueOf(obj); } }

将 MyBatis 拦截器设置为 Spring 自动装配。

@AutoConfigureAfter(DataSourceAutoConfiguration.class) @ConditionalOnBean(SqlSessionFactory.class) @ConditionalOnProperty(name="mybatis.plugin.sql-log.enabled")@EnableConfigurationProperties({MybatisPluginProperties.class}) @RequiredArgsConstructor@Slf4j@Role(BeanDefinition.ROLE_INFRASTRUCTURE) @Configuration(proxyBeanMethods=false)publicclassMybatisPluginAutoConfiguration{privatefinalMybatisPluginProperties mybatisPluginProperties;@BeanpublicMybatisSqlLogInterceptormybatisSqlLogInterceptor() { MybatisSqlLogInterceptor interceptor =newMybatisSqlLogInterceptor(); interceptor.setSlownessThreshold(mybatisPluginProperties.getSqlLog().getSlownessThreshold());returninterceptor; } }@Data@ConfigurationProperties(prefix ="mybatis.plugin")publicclassMybatisPluginProperties{privatefinalSqlLog sqlLog =newSqlLog();@DatapublicstaticclassSqlLog{privatebooleanenabled =true;privateDuration slownessThreshold = Duration.ofMillis(1000); } }

当项目配置了属性mybatis.plugin.sql-log.enabled=true时，SQL 拦截将生效，打印的内容如下。

2024-02-10 23:03:01.845 INFO [dev] [XNIO-1 task-1] org.ylzl.eden.demo.infrastructure.user.database.UserMapper.selectById execute sql: SELECT id,login,fied_date FROM demo_user WHERE id=1 (10 ms)

这种日志格式比较符合我们实际的生产要求：提供日志时间、可运行的 SQL、执行耗时。