使用mybatis的interceptor修改执行sql以及传入参数方式使用mybatis的interceptor修改执行

mybatis interceptor修改执行sql以及传入参数

总体思路
1、Interceptor 代码实现
2、AutoConfiguration代码实现

mybatis interceptor 处理查询参数及查询结果

拦截器：拦截update，query方法
添加xml配置

mybatis interceptor修改执行sql以及传入参数项目中途遇到业务需求更改，在查询某张表时需要增加条件，由于涉及的sql语句多而且依赖其他服务的jar，逐个修改sql语句和接口太繁杂。项目使用mybatis框架，因此借鉴PageHelper插件尝试使用mybatis的Interceptor来实现改需求。

总体思路

从BoundSql中获取sql，通过正则匹配替换表名为子查询REPLACE_TXT
添加子查询REPLACE_TXT 中需要用到的参数到mybatis参数列表中
添加参数与占位符映射，即添加ParameterMapping对象到ParameterMappings中，由于statement在执行时是按照ParameterMappings的元素索引定位占位符封装参数（即ParameterMappings中的第一个参数会封装到第一个占位符上），因此ParameterMappings中的参数顺序需要和占位符保持一致。其次ParameterMappings的元素个数需要和占位符个数保持一致。
为了保证该intercept在最后执行，使用AutoConfiguration将intercept添加到SqlSessionFactory的Configuration中，并在spring.factories文件中添加AutoConfiguration
未测试性能以及是否存在未知缺陷

1、Interceptor 代码实现

package org.cnbi.project.other.sql.intercept; import cn.hutool.core.util.NumberUtil; import com.cnbi.cloud.common.core.exception.ServiceException; import com.github.pagehelper.Page; import com.github.pagehelper.util.ExecutorUtil; import com.github.pagehelper.util.MetaObjectUtil; import org.apache.ibatis.builder.annotation.ProviderSqlSource; import org.apache.ibatis.cache.CacheKey; import org.apache.ibatis.executor.Executor; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.mapping.ParameterMapping; import org.apache.ibatis.plugin.*; import org.apache.ibatis.reflection.MetaObject; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.session.RowBounds; import org.cnbi.project.other.sql.aop.PeriodHolder; import java.util.*; import java.util.regex.Matcher; import java.util.regex.Pattern; /** * @ClassName ParamInterceptor * @Description 修改接口太繁琐，直接用mybatis拦截器对查询sql进行拦截，将期间参数注入sql * @Author Wangjunkai * @Date 2019/10/23 11:36 **/@Intercepts({@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})})public class ParamInterceptor implements Interceptor {private final static Pattern DW_DIMCOMPANY = Pattern.compile("dw_dimcompany", Pattern.CASE_INSENSITIVE); private final static String REPLACE_TXT = "(select * from dw_dimcompany where cisdel = '0' and START_PERIOD <= ? and END_PERIOD > ?)"; @Overridepublic Object intercept(Invocation invocation) throws Throwable {Object[] args = invocation.getArgs(); MappedStatement ms = (MappedStatement) args[0]; Object parameter = args[1]; RowBounds rowBounds = (RowBounds) args[2]; ResultHandler resultHandler = (ResultHandler) args[3]; Executor executor = (Executor) invocation.getTarget(); CacheKey cacheKey; BoundSql boundSql; if(args.length == 4){boundSql = ms.getBoundSql(parameter); } else {boundSql = (BoundSql) args[5]; }//获取sql语句，使用正则忽略大小写匹配String sql = boundSql.getSql(); Matcher matcher = DW_DIMCOMPANY.matcher(sql); //没有需要替换的表名则放行if(!matcher.find()){return invocation.proceed(); }//收集占位符个数（即paramIndex 的size）以及占位符次序（slot：即参数在ParameterMappings中的顺序）int index = 0; ArrayList paramIndex = new ArrayList<>(); while(matcher.find(index)){index = matcher.end(); String sqlPart = sql.substring(0, index); int slot = index - sqlPart.replace("?", "").length() + paramIndex.size() ; paramIndex.add(slot); paramIndex.add(slot + 1); }//替换子查询String companyPeriodSql = matcher.replaceAll(REPLACE_TXT); cacheKey = args.length == 4 ? executor.createCacheKey(ms, parameter, rowBounds, boundSql) : (CacheKey) args[4]; //处理参数Object parameterObject = processParameterObject(ms, parameter, boundSql, cacheKey, paramIndex); BoundSql companyPeriodBoundSql = new BoundSql(ms.getConfiguration(), companyPeriodSql, boundSql.getParameterMappings(), parameterObject); Map additionalParameters = ExecutorUtil.getAdditionalParameter(boundSql); //设置动态参数for (String key : additionalParameters.keySet()) {companyPeriodBoundSql.setAdditionalParameter(key, additionalParameters.get(key)); }return executor.query(ms, parameterObject, RowBounds.DEFAULT, resultHandler, cacheKey, companyPeriodBoundSql); }public Object processParameterObject(MappedStatement ms, Object parameterObject, BoundSql boundSql, CacheKey pageKey, ArrayList paramIndex) {//处理参数Map paramMap = null; if (parameterObject == null) {paramMap = new HashMap<>(); } else if (parameterObject instanceof Map) {//解决不可变Map的情况paramMap = new HashMap<>(); paramMap.putAll((Map) parameterObject); } else {paramMap = new HashMap<>(); // sqlSource为ProviderSqlSource时，处理只有1个参数的情况if (ms.getSqlSource() instanceof ProviderSqlSource) {String[] providerMethodArgumentNames = ExecutorUtil.getProviderMethodArgumentNames((ProviderSqlSource) ms.getSqlSource()); if (providerMethodArgumentNames != null && providerMethodArgumentNames.length == 1) {paramMap.put(providerMethodArgumentNames[0], parameterObject); paramMap.put("param1", parameterObject); }}//动态sql时的判断条件不会出现在ParameterMapping中，但是必须有，所以这里需要收集所有的getter属性//TypeHandlerRegistry可以直接处理的会作为一个直接使用的对象进行处理boolean hasTypeHandler = ms.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass()); MetaObject metaObject = MetaObjectUtil.forObject(parameterObject); //需要针对注解形式的MyProviderSqlSource保存原值if (!hasTypeHandler) {for (String name : metaObject.getGetterNames()) {paramMap.put(name, metaObject.getValue(name)); }}//下面这段方法，主要解决一个常见类型的参数时的问题if (boundSql.getParameterMappings() != null && boundSql.getParameterMappings().size() > 0) {for (ParameterMapping parameterMapping : boundSql.getParameterMappings()) {String name = parameterMapping.getProperty(); if (!name.equals(GLOBALPERIOD)&& paramMap.get(name) == null) {if (hasTypeHandler|| parameterMapping.getJavaType().equals(parameterObject.getClass())) {paramMap.put(name, parameterObject); break; }}}}}return processPageParameter(ms, paramMap, boundSql, pageKey, paramIndex); }private final static String GLOBALPERIOD = "globalPeriod"; public Object processPageParameter(MappedStatement ms, Map paramMap, BoundSql boundSql, CacheKey pageKey, ArrayList paramIndex) {paramMap.put(GLOBALPERIOD, getPeriod()); //处理pageKeypageKey.update(getPeriod()); //处理参数配置handleParameter(boundSql, ms, paramIndex); return paramMap; }protected void handleParameter(BoundSql boundSql, MappedStatement ms,ArrayList paramIndex) {if (boundSql.getParameterMappings() != null) {List newParameterMappings = new ArrayList<>(boundSql.getParameterMappings()); for (Integer index : paramIndex) {if(index < newParameterMappings.size()) {newParameterMappings.add(index, new ParameterMapping.Builder(ms.getConfiguration(), GLOBALPERIOD, String.class).build()); }else{newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), GLOBALPERIOD, String.class).build()); }}MetaObject metaObject = MetaObjectUtil.forObject(boundSql); metaObject.setValue("parameterMappings", newParameterMappings); }}private final static String Q = "Q"; private final static String H = "H"; private String getPeriod(){//使用threadlocal保存从request中获取的参数，此处不再描述String period = PeriodHolder.getPeriod(); if(NumberUtil.isNumber(period)){return period; }else if(period.contains(Q)){return period.substring(0, 4) + Integer.parseInt(period.substring(5)) * 3; }else if(period.contains(H)){return period.substring(0, 4) + Integer.parseInt(period.substring(5)) * 6; }else{throw new ServiceException("非法期间:" + period); }}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this); }@Overridepublic void setProperties(Properties properties) {//nothing to do...}}

2、AutoConfiguration代码实现

package org.cnbi.project.autoconfig; import com.github.pagehelper.autoconfigure.PageHelperAutoConfiguration; import org.apache.ibatis.session.SqlSessionFactory; import org.cnbi.project.other.sql.intercept.ParamInterceptor; import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.autoconfigure.AutoConfigureAfter; import org.springframework.context.annotation.Configuration; import javax.annotation.PostConstruct; import java.util.Iterator; import java.util.List; /** * @ClassName ParamIntecepterAutoConfiguration * @Description * @Author Wangjunkai * @Date 2019/10/23 15:41 **/@AutoConfigureAfter({MybatisAutoConfiguration.class, PageHelperAutoConfiguration.class})@Configurationpublic class ParamIntecepterAutoConfiguration {@Autowiredprivate List sqlSessionFactoryList; public ParamIntecepterAutoConfiguration() {}@PostConstructpublic void addParamInterceptor() {ParamInterceptor interceptor = new ParamInterceptor(); Iterator var3 = this.sqlSessionFactoryList.iterator(); while(var3.hasNext()) {SqlSessionFactory sqlSessionFactory = (SqlSessionFactory)var3.next(); sqlSessionFactory.getConfiguration().addInterceptor(interceptor); }}}

【使用mybatis的interceptor修改执行sql以及传入参数方式】
mybatis interceptor 处理查询参数及查询结果
拦截器：拦截update，query方法
处理查询参数及返回结果。

/** * Created by windwant on 2017/1/12. */@Intercepts({@Signature(type=Executor.class,method="update",args={MappedStatement.class,Object.class}),@Signature(type=Executor.class,method="query",args={MappedStatement.class,Object.class,RowBounds.class,ResultHandler.class})})public class EncryptInterceptor implements Interceptor {public static final Logger logger = LoggerFactory.getLogger(EncryptInterceptor.class); @Overridepublic Object intercept(Invocation invocation) throws Throwable {dealParameter(invocation); Object returnValue = https://www.it610.com/article/invocation.proceed(); dealReturnValue(returnValue); return returnValue; } //查询参数加密处理private void dealParameter(Invocation invocation) {MappedStatement statement = (MappedStatement) invocation.getArgs()[0]; String mapperl = ConfigUtils.get("mybaits.mapper.path"); String methodName = statement.getId().substring(statement.getId().indexOf(mapperl) + mapperl.length() + 1); if (methodName.startsWith("UserBaseMapper")){if(methodName.equals("UserBaseMapper.updateDriver")){((Driver) invocation.getArgs()[1]).encrypt(); }}logger.info("Mybatis Encrypt parameters Interceptor, method: {}, args: {}", methodName, invocation.getArgs()[1]); } //查询结果解密处理private void dealReturnValue(Object returnValue){if(returnValue instanceof ArrayList){List list = (ArrayList)returnValue; for(Object val: list){if(val instanceof Passenger){/////TODO}logger.info("Mybatis Decrypt result Interceptor, result object: {}", ToStringBuilder.reflectionToString(val)); }}} @Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this); } @Overridepublic void setProperties(Properties properties) { }}

添加xml配置