这是一个或许对你有用的社群 一对一交流/面试小册/简历优化/求职解惑欢迎加入「芋道快速开发平台」知识星球。下面是星球提供的部分资料《项目实战视频》从书中学往事上“练”《互联网高频面试题》面朝简历学习春暖花开《架构 x 系统设计》摧枯拉朽掌控面试高频场景题《精进 Java 学习指南》系统学习互联网主流技术栈《必读 Java 源码专栏》知其然知其所以然这是一个或许对你有用的开源项目国产Star破10w的开源项目前端包括管理后台、微信小程序后端支持单体、微服务架构RBAC权限、数据权限、SaaS多租户、商城、支付、工作流、大屏报表、ERP、CRM、AI大模型、IoT物联网等功能多模块https://gitee.com/zhijiantianya/ruoyi-vue-pro微服务https://gitee.com/zhijiantianya/yudao-cloud视频教程https://doc.iocoder.cn【国内首批】支持 JDK17/21SpringBoot3、JDK8/11Spring Boot2双版本来源juejin.cn/post/7483708438683893795背景目标实现产出背景MyBatis Plus 通过配置文件中设置 log-impl 属性来指定日志实现以打印 SQL 语句。mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl logging: 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, 1813986321qq.com, TRUE, FALSE, zh-cn, null, null, null, system, 2025-02-10 22:31:03.818, system, null Total: 1然而默认的日志输出格式存在以下不足缺少日志时间无法快速定位 SQL 执行时间。SQL 语句可读性差复杂的 SQL 语句难以阅读。日志存储成本高SQL 模板占用较多字符增加了日志存储成本。基于 Spring Boot MyBatis Plus Vue Element 实现的后台管理系统 用户小程序支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能项目地址https://github.com/YunaiV/ruoyi-vue-pro视频教程https://doc.iocoder.cn/video/目标通过 MyBatis 的拦截器实现 SQL 原始语句的打印。基于 Spring Cloud Alibaba Gateway Nacos RocketMQ Vue Element 实现的后台管理系统 用户小程序支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能项目地址https://github.com/YunaiV/yudao-cloud视频教程https://doc.iocoder.cn/video/实现首先自定义 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}) }) public class MybatisSqlLogInterceptor implements Interceptor { privatestaticfinal Logger log LoggerFactory.getLogger(MybatisSqlLog); private Duration slownessThreshold Duration.ofMillis(1000); Override public Object intercept(Invocation invocation) throws Throwable { MappedStatement mappedStatement (MappedStatement) invocation.getArgs()[0]; String mapperId mappedStatement.getId(); String originalSql MybatisUtils.getSql(mappedStatement, invocation); long start SystemClock.now(); Object result invocation.proceed(); long duration 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); } return result; } Override public Object plugin(Object target) { if (target instanceof Executor) { return Plugin.wrap(target, this); } return target; } // 设置慢 SQL 阈值单位为秒 public void setSlownessThreshold(Duration slownessThreshold) { this.slownessThreshold slownessThreshold; } }笔者编写了一个工具类负责解析 MyBatis 执行语句还原为可执行的 SQL 内容。UtilityClass publicclass MybatisUtils { privatestaticfinal Pattern PARAMETER_PATTERN Pattern.compile(\\?); public String getSql(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(); return resolveSql(configuration, boundSql); } private static String resolveSql(Configuration configuration, BoundSql boundSql) { Object parameterObject boundSql.getParameterObject(); ListParameterMapping 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 new StringBuffer(); 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(); } } return sql; } private static String resolveParameterValue(Object obj) { if (obj instanceof CharSequence) { return obj ; } if (obj instanceof Date) { DateFormat formatter DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA); return formatter.format(obj) ; } return obj 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) publicclass MybatisPluginAutoConfiguration { privatefinal MybatisPluginProperties mybatisPluginProperties; Bean public MybatisSqlLogInterceptor mybatisSqlLogInterceptor() { MybatisSqlLogInterceptor interceptor new MybatisSqlLogInterceptor(); interceptor.setSlownessThreshold(mybatisPluginProperties.getSqlLog().getSlownessThreshold()); return interceptor; } } Data ConfigurationProperties(prefix mybatis.plugin) publicclass MybatisPluginProperties { privatefinal SqlLog sqlLog new SqlLog(); Data publicstaticclass SqlLog { privateboolean enabled true; private Duration slownessThreshold Duration.ofMillis(1000); } }当项目配置了属性mybatis.plugin.sql-log.enabledtrue时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,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 id1 (10 ms)这种日志格式比较符合我们实际的生产要求提供日志时间、可运行的 SQL、执行耗时。产出团队引入这个组件后在定位生产 SQL 问题时比原来清晰多了并且日志文件缩减了 30% 存储成本。本文涉及的代码完全开源感兴趣的伙伴可以查阅https://github.com/shiyindaxiaojie/eden-architect/tree/main/eden-components/eden-spring-boot-starters/eden-mybatis-spring-boot-starter欢迎加入我的知识星球全面提升技术能力。 加入方式“长按”或“扫描”下方二维码噢星球的内容包括项目实战、面试招聘、源码解析、学习路线。文章有帮助的话在看转发吧。 谢谢支持哟 (*^__^*
一招搞定！ 自定义MyBatis拦截器，SQL日志存储成本直降30%
这是一个或许对你有用的社群 一对一交流/面试小册/简历优化/求职解惑欢迎加入「芋道快速开发平台」知识星球。下面是星球提供的部分资料《项目实战视频》从书中学往事上“练”《互联网高频面试题》面朝简历学习春暖花开《架构 x 系统设计》摧枯拉朽掌控面试高频场景题《精进 Java 学习指南》系统学习互联网主流技术栈《必读 Java 源码专栏》知其然知其所以然这是一个或许对你有用的开源项目国产Star破10w的开源项目前端包括管理后台、微信小程序后端支持单体、微服务架构RBAC权限、数据权限、SaaS多租户、商城、支付、工作流、大屏报表、ERP、CRM、AI大模型、IoT物联网等功能多模块https://gitee.com/zhijiantianya/ruoyi-vue-pro微服务https://gitee.com/zhijiantianya/yudao-cloud视频教程https://doc.iocoder.cn【国内首批】支持 JDK17/21SpringBoot3、JDK8/11Spring Boot2双版本来源juejin.cn/post/7483708438683893795背景目标实现产出背景MyBatis Plus 通过配置文件中设置 log-impl 属性来指定日志实现以打印 SQL 语句。mybatis-plus: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl logging: 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, 1813986321qq.com, TRUE, FALSE, zh-cn, null, null, null, system, 2025-02-10 22:31:03.818, system, null Total: 1然而默认的日志输出格式存在以下不足缺少日志时间无法快速定位 SQL 执行时间。SQL 语句可读性差复杂的 SQL 语句难以阅读。日志存储成本高SQL 模板占用较多字符增加了日志存储成本。基于 Spring Boot MyBatis Plus Vue Element 实现的后台管理系统 用户小程序支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能项目地址https://github.com/YunaiV/ruoyi-vue-pro视频教程https://doc.iocoder.cn/video/目标通过 MyBatis 的拦截器实现 SQL 原始语句的打印。基于 Spring Cloud Alibaba Gateway Nacos RocketMQ Vue Element 实现的后台管理系统 用户小程序支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能项目地址https://github.com/YunaiV/yudao-cloud视频教程https://doc.iocoder.cn/video/实现首先自定义 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}) }) public class MybatisSqlLogInterceptor implements Interceptor { privatestaticfinal Logger log LoggerFactory.getLogger(MybatisSqlLog); private Duration slownessThreshold Duration.ofMillis(1000); Override public Object intercept(Invocation invocation) throws Throwable { MappedStatement mappedStatement (MappedStatement) invocation.getArgs()[0]; String mapperId mappedStatement.getId(); String originalSql MybatisUtils.getSql(mappedStatement, invocation); long start SystemClock.now(); Object result invocation.proceed(); long duration 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); } return result; } Override public Object plugin(Object target) { if (target instanceof Executor) { return Plugin.wrap(target, this); } return target; } // 设置慢 SQL 阈值单位为秒 public void setSlownessThreshold(Duration slownessThreshold) { this.slownessThreshold slownessThreshold; } }笔者编写了一个工具类负责解析 MyBatis 执行语句还原为可执行的 SQL 内容。UtilityClass publicclass MybatisUtils { privatestaticfinal Pattern PARAMETER_PATTERN Pattern.compile(\\?); public String getSql(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(); return resolveSql(configuration, boundSql); } private static String resolveSql(Configuration configuration, BoundSql boundSql) { Object parameterObject boundSql.getParameterObject(); ListParameterMapping 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 new StringBuffer(); 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(); } } return sql; } private static String resolveParameterValue(Object obj) { if (obj instanceof CharSequence) { return obj ; } if (obj instanceof Date) { DateFormat formatter DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA); return formatter.format(obj) ; } return obj 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) publicclass MybatisPluginAutoConfiguration { privatefinal MybatisPluginProperties mybatisPluginProperties; Bean public MybatisSqlLogInterceptor mybatisSqlLogInterceptor() { MybatisSqlLogInterceptor interceptor new MybatisSqlLogInterceptor(); interceptor.setSlownessThreshold(mybatisPluginProperties.getSqlLog().getSlownessThreshold()); return interceptor; } } Data ConfigurationProperties(prefix mybatis.plugin) publicclass MybatisPluginProperties { privatefinal SqlLog sqlLog new SqlLog(); Data publicstaticclass SqlLog { privateboolean enabled true; private Duration slownessThreshold Duration.ofMillis(1000); } }当项目配置了属性mybatis.plugin.sql-log.enabledtrue时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,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 id1 (10 ms)这种日志格式比较符合我们实际的生产要求提供日志时间、可运行的 SQL、执行耗时。产出团队引入这个组件后在定位生产 SQL 问题时比原来清晰多了并且日志文件缩减了 30% 存储成本。本文涉及的代码完全开源感兴趣的伙伴可以查阅https://github.com/shiyindaxiaojie/eden-architect/tree/main/eden-components/eden-spring-boot-starters/eden-mybatis-spring-boot-starter欢迎加入我的知识星球全面提升技术能力。 加入方式“长按”或“扫描”下方二维码噢星球的内容包括项目实战、面试招聘、源码解析、学习路线。文章有帮助的话在看转发吧。 谢谢支持哟 (*^__^*
