Mybatis代理Mapper统一获取Mapeer执行结果及执行SQL

迈不过友情╰ 2022-10-28 12:26 268阅读 0赞

### 文章目录 ###

*   *  需求
     *  记录表结构
     *  实现
     *  SqlUtils
     *  使用

## 需求 ##

由于数据源的切换，需要对某个Mapper进行数据监测，需要记录Mapper中所有执行方法的结果，执行SQL，参数等基本信息

## 记录表结构 ##

create table sql_test
    (
        method      text,
        shard       integer,
        ck_time     text,
        ck_total    integer,
        pg_total    integer,
        create_time timestamp default now(),
        ck_result   text,
        pg_result   text,
        pg_time     text,
        pg_sql      text,
        ck_sql      text
    );
    
    comment on column sql_test.method is '方法';
    
    comment on column sql_test.shard is '节点';
    
    comment on column sql_test.ck_time is 'ck查询耗时';
    
    comment on column sql_test.ck_total is 'ck查询总数量';
    
    comment on column sql_test.pg_total is 'pg查询总数量';
    
    comment on column sql_test.ck_result is 'ck返回结果';
    
    comment on column sql_test.pg_result is 'pg返回结果';
    
    comment on column sql_test.pg_time is 'pg查询耗时';
    
    alter table sql_test
        owner to postgres;

其中分两个数据源 大概是ck数据源和pg数据源

## 实现 ##

基于动态代理来实现

搭理类  
CKOrderDetailMapperProxy

@Slf4j
    public class CKOrderDetailMapperProxy { 
    
        private final Object target;
        private SqlSessionFactory sqlSessionFactory;
    
        public CKOrderDetailMapperProxy(Object target, SqlSessionFactory sqlSessionFactory) { 
            this.target = target;
            this.sqlSessionFactory = sqlSessionFactory;
        }
    
        // 为目标对象生成代理对象
        public Object getProxyInstance(SqlTestResultMapper sqlTestResultMapper) { 
            return Proxy.newProxyInstance(target.getClass().getClassLoader(), target.getClass().getInterfaces(),
                    (proxy, method, args) -> { 
                        SqlTestResult sqlTestResult = new SqlTestResult();
                        String methodName = method.getName();
                        String sql = SqlUtils.getMybatisSql(method, args, sqlSessionFactory);
                        int size = 0;
                        String result = null;
                        StopWatch stopWatch = new StopWatch();
                        stopWatch.start();
                        // 执行目标对象方法
                        Object returnValue = method.invoke(target, args);
                        if (Objects.nonNull(returnValue)) { 
                            if (returnValue instanceof Collection) { 
                                Collection list = (Collection) returnValue;
                                size = list.size();
                                result = JSON.toJSONString(list);
    
                            } else if (returnValue instanceof Long) { 
                                Long value = (Long) returnValue;
                                size = value.intValue();
                            } else if (returnValue instanceof Integer) { 
                                size = (int) returnValue;
                            } else { 
                                size = 0;
                            }
                        }
                        stopWatch.stop();
                        double totalTimeSeconds = stopWatch.getTotalTimeSeconds();
                        sqlTestResult.setMethod(methodName);
                        sqlTestResult.setShard(4);
                        sqlTestResult.setCkTime(totalTimeSeconds);
                        sqlTestResult.setCkResult(result);
                        sqlTestResult.setCkTotal(size);
                        sqlTestResult.setCkSql(sql);
                        sqlTestResultMapper.insert(sqlTestResult);
                        log.info("方法：" + methodName + "," + "耗时: " + totalTimeSeconds + ","
                                + "总数: " + size);
                        return returnValue;
                    });
        }
    
    
    
    }

## SqlUtils ##

public class SqlUtils { 
    
        /** * 获取Mybatis执行SQL * @param method * @param sqlSessionFactory * @return java.lang.String * @author wh * @date 2021/2/5 */
        public static String getMybatisSql(Method method, Object[] objects, SqlSessionFactory sqlSessionFactory) throws IllegalAccessException { 
            Map<String,Object> map = new HashMap<>();
            //1.获取namespace+methdoName 类路径+名字
            String namespace = method.getDeclaringClass().getName();
            //方法名
            String methodName = method.getName();
            //2.根据namespace+methdoName获取相对应的MappedStatement
            Configuration configuration = sqlSessionFactory.getConfiguration();
            MappedStatement mappedStatement = configuration.getMappedStatement(namespace+"."+methodName);
            Annotation[][] parameterAnnotations = method.getParameterAnnotations();
            for (int i = 0;i<parameterAnnotations.length;i++){ 
                Object object = objects[i];
                if (parameterAnnotations[i].length == 0){  //说明该参数没有注解，此时该参数可能是实体类，也可能是Map，也可能只是单参数
                    if (object.getClass().getClassLoader() == null && object instanceof Map){ 
                        map.putAll((Map<? extends String, ?>) object);
                    }else{ //形参为自定义实体类
                        map.putAll(objectToMap(object));
                    }
                }else{ //说明该参数有注解，且必须为@Param
                    for (Annotation annotation : parameterAnnotations[i]){ 
                        if (annotation instanceof Param){ 
                            map.put(((Param) annotation).value(),object);
                        }
                    }
                }
            }
            //5.获取boundSql
            BoundSql boundSql = mappedStatement.getBoundSql(map);
            return showSql(configuration,boundSql);
        }
    
        /** * 解析BoundSql，生成不含占位符的SQL语句 * @param configuration * @param boundSql * @return java.lang.String * @author wh * @date 2021/2/5 */
        private  static String showSql(Configuration configuration, BoundSql boundSql) { 
            Object parameterObject = boundSql.getParameterObject();
            // 获取方法参数
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            // 格式化SQL
            String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
            if (parameterMappings.size() > 0 && parameterObject != null) { 
                // 类型解析器
                TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
                if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) { 
                    sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
                } else { 
                    MetaObject metaObject = configuration.newMetaObject(parameterObject);
                    for (ParameterMapping parameterMapping : parameterMappings) { 
                        String propertyName = parameterMapping.getProperty();
                        String[] s =  metaObject.getObjectWrapper().getGetterNames();
                        Arrays.toString(s);
                        if (metaObject.hasGetter(propertyName)) { 
                            Object obj = metaObject.getValue(propertyName);
                            sql = sql.replaceFirst("\\?", getParameterValue(obj));
                        } else if (boundSql.hasAdditionalParameter(propertyName)) { 
                            Object obj = boundSql.getAdditionalParameter(propertyName);
                            sql = sql.replaceFirst("\\?", getParameterValue(obj));
                        }
                    }
                }
            }
            return sql;
        }
    
        /** * 若为字符串或者日期类型，则在参数两边添加'' * @param obj * @return java.lang.String * @author wh * @date 2021/2/5 */
        private static String getParameterValue(Object obj) { 
            String value;
            if (obj instanceof String) { 
                value = "'" + obj.toString() + "'";
            } else if (obj instanceof Date) { 
                DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
                value = "'" + formatter.format(new Date()) + "'";
            } else { 
                if (obj != null) { 
                    value = obj.toString();
                } else { 
                    value = "";
                }
            }
            return value;
        }
    
        /** * 获取利用反射获取类里面的值和名称 * @param obj * @return java.util.Map<java.lang.String,java.lang.Object> * @author wh * @date 2021/2/5 */
        private static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException { 
            Map<String, Object> map = new HashMap<>();
            Class<?> clazz = obj.getClass();
            System.out.println(clazz);
            for (Field field : clazz.getDeclaredFields()) { 
                field.setAccessible(true);
                String fieldName = field.getName();
                Object value = field.get(obj);
                map.put(fieldName, value);
            }
            return map;
        }
    
    }

## 使用 ##

CKOrderDetailMapperProxy ckFourProxy = new CKOrderDetailMapperProxy(ckOrderDetailMapper, clickHouseSqlSessionFactoryBean);
    CKOrderDetailMapper ckFourMapper = (CKOrderDetailMapper) ckFourProxy.getProxyInstance(sqlTestResultMapper);
    ckFourMapper.selectDrugPriceOfMode(customerId, year, month);

参考：  
https://blog.csdn.net/sdzhangshulong/article/details/104393244