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

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 > ?)";
    @override
    public 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<integer> 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<string, object> 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<integer> paramindex) {
        //处理参数
        map<string, object> 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<string, object> parammap, boundsql boundsql, cachekey pagekey, arraylist<integer> paramindex) {
        parammap.put(globalperiod, getperiod());
        //处理pagekey
        pagekey.update(getperiod());
        //处理参数配置
        handleparameter(boundsql, ms, paramindex);
        return parammap;
    }
    protected void handleparameter(boundsql boundsql, mappedstatement ms,  arraylist<integer> paramindex) {
        if (boundsql.getparametermappings() != null) {
            list<parametermapping> 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);
        }
    }
    @override
    public object plugin(object target) {
        return plugin.wrap(target, this);
    }
    @override
    public 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})
@configuration
public class paramintecepterautoconfiguration {
    @autowired
    private list<sqlsessionfactory> sqlsessionfactorylist;
    public paramintecepterautoconfiguration() {
    }
    @postconstruct
    public 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 处理查询参数及查询结果

拦截器：拦截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);
 
 
    @override
    public object intercept(invocation invocation) throws throwable {
        dealparameter(invocation);
        object returnvalue = 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));
            }
        }
    }
 
    @override
    public object plugin(object target) {
        return plugin.wrap(target, this);
    }
 
    @override
    public void setproperties(properties properties) {
 
    }
}

添加xml配置

<bean id="sqlsessionfactory" class="org.mybatis.spring.sqlsessionfactorybean">
              <property name="typealiasespackage" value="com.xx.model"/>
              <property name="datasource" ref="datasource"/>
              <!-- 自动扫描mapping.xml文件 -->
              <property name="mapperlocations" value="classpath*:mybatis/*.xml"></property>
              <property name="plugins">//拦截器插件
                     <array>
                            <bean class="com.github.pagehelper.pagehelper">
                                   <property name="properties">
                                          <value>dialect=hsqldb</value>
                                   </property>
                            </bean>
                            <bean class="com.xx.interceptor.encryptinterceptor">
                                   <property name="properties">
                                          <value>property-key=property-value</value>
                                   </property>
                            </bean>
                     </array>
              </property>
       </bean>

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。