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

使用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添加到SqlSessionFactory的Configuration中，并在spring.factories文件中添加AutoConfiguration

未测试性能以及是否存在未知缺陷

1、Interceptor 代码实现

package org-bi.project.other.sql.intercept;

import cn.hutool.core.util.NumberUtil;

import com-bi.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-bi.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 paramIndex = new ArrayList<>();

http:// 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) {

//处理参数

cLQkBmO 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());

//处理pageKey

pageKey.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);

}

}

@Override

public Object plugin(Object target) {

return Plugin.wrap(target, this);

}

@Override

public void setProperties(Properties properties) {

//nothing to do...

}

}

2、AutoConfiguration代码实现

package org-bi.project.autoconfig;

import com.github.pagehelper.autoconfigure.PageHelperAutoConfiguration;

import org.apache.ibatis.session.SqlSessionFactory;

import org-bi.project.other.sql.intercept.ParamInterceptor;

import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;

import org.springframework.beans.factocLQkBmOry.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 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));

}http://

}

}

@Override

public Object plugin(Object target) {

return Plugin.wrap(target, this);

}

@Override

public void setProperties(Properties properties) {

}

}

添加xml配置

//-插件

dialect=hsqldb

property-key=property-value

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。