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

目录

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添加到sqlssionfactory的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.rviceexception;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.ssion.resulthandler;import org.apache.ibatis.ssion.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.ca_innsitive);private final static string replace_txt = "(lect * 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);} el {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.keyt()) {companyperiodboundsql.tadditionalparameter(key, additionalparameter我的同学s.get(key));}return executor.query(ms, parameterobject, rowbounds.default, resulthandler, cachekey, companyperiodboundsql);}public object processparameterobject(mappedstatement ms, object parameterobject, boundsql boundsql, cac关于月亮的谜语hekey pagekey, arraylist<integer> paramindex) {//处理参数map<string, object> parammap = null;if (parameterobject == null) {parammap = new hashmap<>();} el if (parameterobject instanceof map) {//解决不可变map的情况parammap = new hashmap<>();parammap.putall((map) parameterobject);} el {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(provid小常识ermethodargumentnames[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());//处理pagekeypagekey.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());}el{newparametermappings.add(new parametermapping.builder(ms.getconfiguration(), globalperiod, string.class).build());}}metaobject metaobject = metaobjectutil.forobject(boundsql);metaobject.tvalue("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;}el if(period.contains(q)){return period.substring(0, 4) + integer.parint(period.substring(5)) * 3;}el if(period.contains(h)){return period.substring(0, 4) + integer.parint(period.substring(5)) * 6;}el{throw new rviceexception("非法期间:" + period);}}@overridepublic object plugin(object target) {return plugin.wrap(target, this);}@overridepublic void tproperties(properties properties) {//nothing to do...}}

2、autoconfiguration代码实现

package org.cnbi.project.autoconfig;import com.github.pagehelper.autoconfigure.pagehelperautoconfiguration;import org.apache.ibatis.ssion.sqlssionfactory;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<sqlssionfactory> sqlssionfactorylist;public paramintecepterautoconfiguration() {}@postconstructpublic void addparaminterceptor() {paraminterceptor interceptor = new paraminterceptor();iterator var3 = this.sqlssionfactorylist.iterator();while(var3.hasnext()) {sqlssionfactory sqlssionfactory = (sqlssionfactory)var3.next();sqlssionfactory.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);@overridepublic 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("urbamapper")){if(methodname.equals("urbamapper.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 ar马关条约的主要内容raylist<?>){list<?> list = (arraylist<?>)returnvalue;for(object val: list){if(val instanceof pasnger){/////todo}logger.info("mybatis decrypt result interceptor, result object: {}", tostringbuilder.reflectiontostring(val));}}}@overridepublic object plugin(object target) {return plugin.wrap(target, this);}@overridepublic void tproperties(properties properties) {}}

添加xml配置

<bean id="sqlssionfactory" class="org.mybatis.spring.sqlssionfactorybean"><property name="typealiaspackage" 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>

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