个人随笔
目录
SpringBoot Mybatis 新增拦截器统计sql的时间和修改sql
2024-11-28 22:08:53

背景需求

在业务功能开发过程中,我们可能会遇到如下一些需求

1、统计每条sql的执行时间

后台打印每条查询sql的执行时间,用于后续分析。

2、对机构数据权限做通用的处理

对每条执行的sql进行统一的机构数据权限校验,开发人员不用每个都单独编写机构校验逻辑。

思路

下面仅提供实现思路和具体的相关demo,后续的实际实现留待以后遇到相似的业务需求先!

我们可以用Mybatis的拦截器来处理。

1、统计每条sql的执行时间

我们新建一个Mybatis的拦截器,在执行sql的前后打印时间日志即可。

拦截器代码如下

  1. package com.suibibk.interceptor;
  2. import org.apache.ibatis.cache.CacheKey;
  3. import org.apache.ibatis.executor.Executor;
  4. import org.apache.ibatis.mapping.BoundSql;
  5. import org.apache.ibatis.mapping.MappedStatement;
  6. import org.apache.ibatis.plugin.*;
  7. import org.apache.ibatis.session.ResultHandler;
  8. import org.apache.ibatis.session.RowBounds;
  9. import java.util.Properties;
  10. @Intercepts({
  11. @Signature(type = Executor.class, method = "query",
  12. args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
  13. @Signature(type = Executor.class, method = "query",
  14. args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
  15. @Signature(type = Executor.class, method = "queryCursor",
  16. args = {MappedStatement.class, Object.class, RowBounds.class}),
  17. @Signature(type = Executor.class, method = "update",
  18. args = {MappedStatement.class, Object.class})
  19. })
  20. public class SqlExecuteTimeInterceptor implements Interceptor {
  21. @Override
  22. public Object intercept(Invocation invocation) throws Throwable {
  23. Object[] args = invocation.getArgs();
  24. MappedStatement mappedStatement = (MappedStatement) args[0];
  25. long startTime = System.currentTimeMillis();
  26. Object result = invocation.proceed();
  27. long endTime = System.currentTimeMillis();
  28. long timeElapsed = endTime - startTime;
  29. String sqlId = mappedStatement.getId();
  30. System.out.println("执行SQL【" + sqlId + "】耗时:" + timeElapsed + "ms");
  31. return result;
  32. }
  33. @Override
  34. public Object plugin(Object target) {
  35. return Plugin.wrap(target, this);
  36. }
  37. @Override
  38. public void setProperties(Properties properties) {
  39. // 可以配置拦截器的属性
  40. }
  41. }

然后注册拦截器

  1. package com.suibibk.config;
  2. import com.suibibk.interceptor.AuthorityInterceptor;
  3. import com.suibibk.interceptor.SqlExecuteTimeInterceptor;
  4. import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
  5. import org.springframework.context.annotation.Bean;
  6. import org.springframework.context.annotation.Configuration;
  7. @Configuration
  8. public class MyBatisConfig {
  9. @Bean
  10. public ConfigurationCustomizer configurationCustomizer() {
  11. return configuration -> {
  12. //configuration.addInterceptor(new AuthorityInterceptor());
  13. configuration.addInterceptor(new SqlExecuteTimeInterceptor());
  14. };
  15. }
  16. }

上面的 AuthorityInterceptor拦截器是第二个问题的拦截器,这里不用理会。

搞定。

2、对机构数据权限做通用的处理

这里的思路是,我们新增一个注解,在需要校验机构数据权限的Mapper方法里面加上该注解,然后拦截器里面判断到方法是否有这个注解,有则修改sql,在后面拼接机构数据权限校验的代码,这里先举个例子,后续其实应该是根据当前登录用户所属机构来拼接。具体怎么获取用户的信息,我们可以借助使用 ThreadLocal 存储当前用户信息,比如如下代码例子

  1. public class UserContextHolder {
  2. private static final ThreadLocal<Long> userContextHolder = new ThreadLocal<>();
  3. public static void setUserId(Long userId) {
  4. userContextHolder.set(userId);
  5. }
  6. public static Long getUserId() {
  7. return userContextHolder.get();
  8. }
  9. public static void clear() {
  10. userContextHolder.remove();
  11. }
  12. }

我们可以在controller或者springboot的拦截器过滤器将用户的信息token放入当前线程中,后续Mybatis的拦截器就可以获取了。这里不展开。

首先定义注解

  1. package com.suibibk.annotation;
  2. import java.lang.annotation.ElementType;
  3. import java.lang.annotation.Retention;
  4. import java.lang.annotation.RetentionPolicy;
  5. import java.lang.annotation.Target;
  6. /**
  7. * Mybatis权限检查注解,拦截StatementHandler的prepare方法 拦截器见AuthorityInterceptor
  8. * @author bbq
  9. */
  10. @Target({ElementType.METHOD,ElementType.TYPE})
  11. @Retention(RetentionPolicy.RUNTIME)
  12. public @interface ACheck {
  13. String value() default "";
  14. }

然后使用注解

  1. public interface TestMapper {
  2. @ACheck("admin")
  3. public List<LevelInfo> getLevelInfos();
  4. }

然后拦截器

  1. package com.suibibk.interceptor;
  2. import com.suibibk.annotation.ACheck;
  3. import org.apache.commons.lang.StringUtils;
  4. import org.apache.ibatis.cache.CacheKey;
  5. import org.apache.ibatis.executor.Executor;
  6. import org.apache.ibatis.mapping.BoundSql;
  7. import org.apache.ibatis.mapping.MappedStatement;
  8. import org.apache.ibatis.mapping.SqlSource;
  9. import org.apache.ibatis.plugin.*;
  10. import org.apache.ibatis.reflection.MetaObject;
  11. import org.apache.ibatis.session.ResultHandler;
  12. import org.apache.ibatis.session.RowBounds;
  13. import java.lang.reflect.Field;
  14. import java.lang.reflect.Method;
  15. import java.lang.reflect.Modifier;
  16. import java.util.Properties;
  17. @Intercepts({
  18. @Signature(type = Executor.class,//四种类型接口中的某一个接口,如Executor.class。可拦截方法有 Executor、ParameterHandler 、ResultSetHandler 、StatementHandler,
  19. method = "query",////对应接口中的某一个方法名,比如Executor的query方法。
  20. //对应接口中的某一个方法的参数,比如Executor中query方法因为重载原因,有多个,args就是指明参数类型,从而确定是具体哪一个方法。
  21. args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
  22. @Signature(type = Executor.class, method = "update",
  23. args = {MappedStatement.class, Object.class})
  24. })
  25. public class AuthorityInterceptor implements Interceptor {
  26. private static final long serialVersionUID = 1L;
  27. @Override
  28. public Object intercept(Invocation invocation) throws Throwable {
  29. final MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
  30. Object parameter = invocation.getArgs()[1];
  31. BoundSql boundSql = mappedStatement.getBoundSql(parameter);
  32. if (StringUtils.isBlank(boundSql.getSql())) {
  33. return null;
  34. }
  35. String originalSql = boundSql.getSql().trim();
  36. String mid = mappedStatement.getId();
  37. String name = StringUtils.substringAfterLast(mid, ".");
  38. Class<?> classType = Class.forName(mid.substring(0, mid.lastIndexOf(".")));
  39. ACheck aCheck = null;
  40. //拦截类
  41. if (classType.isAnnotationPresent(ACheck.class) && classType.getAnnotation(ACheck.class) != null) {
  42. aCheck = classType.getAnnotation(ACheck.class);
  43. originalSql = handleSQL(originalSql, aCheck);
  44. } else {
  45. //拦截方法
  46. for (Method method : classType.getMethods()) {
  47. if (!name.equals(method.getName())) {
  48. continue;
  49. } else {
  50. if (method.isAnnotationPresent(ACheck.class) && method.getAnnotation(ACheck.class) != null) {
  51. aCheck = method.getAnnotation(ACheck.class);
  52. originalSql = handleSQL(originalSql, aCheck);
  53. }
  54. break;
  55. }
  56. }
  57. }
  58. BoundSql newBoundSql = new BoundSql(mappedStatement.getConfiguration(), originalSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
  59. MappedStatement newMs = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(newBoundSql));
  60. invocation.getArgs()[0] = newMs;
  61. invocation.getArgs()[5] = newBoundSql;
  62. return invocation.proceed();
  63. }
  64. public String handleSQL(String originalSql, ACheck aCheck){
  65. String atv = aCheck.value();
  66. if (StringUtils.isNotBlank(atv)){
  67. try{
  68. /**
  69. 此处应为你的sql拼接,替换第一个where可以实现绝大多数sql,当然复杂sql除外,所以复杂sql还是需要例外处理
  70. User user = null;
  71. user = UserUtils.getUser();
  72. String tid;
  73. if(user != null && StringUtils.isNotBlank(tid = user.getTenantId())){
  74. originalSql = replace(originalSql, "where", "where "+atv+"='"+tid+"' and");
  75. originalSql = replace(originalSql, "WHERE", "WHERE "+atv+"='"+tid+"' and");
  76. }
  77. **/
  78. originalSql = "select * from levelinfo where id=1104145748397654016 order by sort";
  79. }catch (Exception e){
  80. e.printStackTrace();
  81. }
  82. }
  83. return originalSql;
  84. }
  85. public static String replace(String string, String toReplace, String replacement) {
  86. // int pos = string.lastIndexOf(toReplace);
  87. int pos = string.indexOf(toReplace);
  88. if (pos > -1) {
  89. return string.substring(0, pos)
  90. + replacement
  91. + string.substring(pos + toReplace.length(), string.length());
  92. } else {
  93. return string;
  94. }
  95. }
  96. @Override
  97. public Object plugin(Object target) {
  98. return Plugin.wrap(target, this);
  99. }
  100. @Override
  101. public void setProperties(Properties properties) {
  102. // 可以配置拦截器的属性
  103. }
  104. private MappedStatement copyFromMappedStatement(MappedStatement ms,
  105. SqlSource newSqlSource) {
  106. MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(),
  107. ms.getId(), newSqlSource, ms.getSqlCommandType());
  108. builder.resource(ms.getResource());
  109. builder.fetchSize(ms.getFetchSize());
  110. builder.statementType(ms.getStatementType());
  111. builder.keyGenerator(ms.getKeyGenerator());
  112. if (ms.getKeyProperties() != null) {
  113. for (String keyProperty : ms.getKeyProperties()) {
  114. builder.keyProperty(keyProperty);
  115. }
  116. }
  117. builder.timeout(ms.getTimeout());
  118. builder.parameterMap(ms.getParameterMap());
  119. builder.resultMaps(ms.getResultMaps());
  120. builder.cache(ms.getCache());
  121. return builder.build();
  122. }
  123. public static class BoundSqlSqlSource implements SqlSource {
  124. BoundSql boundSql;
  125. public BoundSqlSqlSource(BoundSql boundSql) {
  126. this.boundSql = boundSql;
  127. }
  128. public BoundSql getBoundSql(Object parameterObject) {
  129. return boundSql;
  130. }
  131. }
  132. }

上面我这里为了了替换sql,用了这段逻辑

  1. invocation.getArgs()[0] = newMs;
  2. invocation.getArgs()[5] = newBoundSql;

这里不一定是5,还要根据参数的数目,具体可以去看Mybatis的分页插件pageHelper怎么做的,才能做到兼容各种情况。

然后就可以了。

后续实际使用再来更新!

 14

啊!这个可能是世界上最丑的留言输入框功能~


当然,也是最丑的留言列表

有疑问发邮件到 : suibibk@qq.com 侵权立删
Copyright : 个人随笔   备案号 : 粤ICP备18099399号-2