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

然后注册拦截器

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

上面的 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;
  3. import java.lang.annotation.ElementType;
  4. import java.lang.annotation.Retention;
  5. import java.lang.annotation.RetentionPolicy;
  6. import java.lang.annotation.Target;
  8. /**
  9.  * Mybatis权限检查注解，拦截StatementHandler的prepare方法 拦截器见AuthorityInterceptor
  10.  * @author bbq
  11.  */
  12. @Target({ElementType.METHOD,ElementType.TYPE})
  13. @Retention(RetentionPolicy.RUNTIME)
  14. public @interface ACheck {
  15.     String value() default "";
  16. }

然后使用注解

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

然后拦截器

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

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

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

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

然后就可以了。

后续实际使用再来更新！

 101

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


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

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