Mybatis Interceptor接口的使用
关于Mybatis中插件的声明需要在configuration的配置文件中进行配置，配置文件的位置使用configLocation属性指定。
测试中使用的config文件内容如下
| 1 2 3 4 5 6 7 8 |
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--plugins插件之 分页拦截器 -->
<plugins>
<plugin interceptor="com.interceptors.LogInterceptor" ></plugin>
</plugins>
</configuration>
|
在配置文件中配置了一个Interceptor的实现类
LogInterceptor的代码如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
public class LogInterceptor implements Interceptor{
@Override
public Object intercept(Invocation invocation) throws Throwable {
System.out.println("LogInterceptor : intercept");
return null;
}
@Override
public Object plugin(Object target) {
if(target instanceof StatementHandler){
RoutingStatementHandler handler = (RoutingStatementHandler)target;
//打印出当前执行的sql语句
System.out.println(handler.getBoundSql().getSql());
}
return target;
}
@Override
public void setProperties(Properties properties) {
System.out.println("LogInterceptor : setProperties");
}
}
|
在工程的配置文件中，在配置SqlSessionFactoryBean时需要指明config配置文件的位置，配置文件如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<bean id="user1" class="com.beans.User">
<property name="userName" value="zhuyuqiang"/>
</bean>
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="name" value="mysql"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/world"/>
<property name="username" value="root"/>
<property name="password" value="https://suowo.cn"/>
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="mapperLocations" value="classpath:mybatis/*.xml"/>
<property name="typeAliasesPackage" value="com.entities"/>
<property name="configLocation" value="classpath:config/configuration.xml"/>
</bean>
<bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.interfaces"/>
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<context:component-scan base-package="com"/>
</beans>
|
基本上这样配置以后，在工程中声明的plugin就已经生效了，实际中打印出来的log如下：
| 1 |
SELECT * FROM city WHERE ID=?
|
执行的CityMapper如下：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.interfaces.CityMapper">
<resultMap id="BaseResultMap" type="com.entities.City">
<id column="ID" jdbcType="INTEGER" property="id"/>
<result column="Name" jdbcType="CHAR" property="name"/>
<result column="CountryCode" jdbcType="CHAR" property="countrycode"/>
<result column="District" jdbcType="CHAR" property="district"/>
<result column="Population" jdbcType="INTEGER" property="population"/>
</resultMap>
<select id="selectCityById" parameterType="int" resultType="com.entities.City">
SELECT * FROM city WHERE ID=#{id,jdbcType=INTEGER}
</select>
</mapper>
|
简单的测试了一下，可以打印出sql语句。在使用mybatis提供的plugin接口时需要注意，在构建ParameterHandler 、ResultSetHandler 、StatementHandler 和Executor 的时候都会调用在项目中实现的插件接口，一般情况下，如果只是为了打印显示当前执行的sql语句，可以只在当target为StatementHandler类型的时候再进行处理即可。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
public ParameterHandler newParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql) {
ParameterHandler parameterHandler = mappedStatement.getLang().createParameterHandler(mappedStatement, parameterObject, boundSql);
parameterHandler = (ParameterHandler) interceptorChain.pluginAll(parameterHandler);
return parameterHandler;
}
public ResultSetHandler newResultSetHandler(Executor executor, MappedStatement mappedStatement, RowBounds rowBounds, ParameterHandler parameterHandler,
ResultHandler resultHandler, BoundSql boundSql) {
ResultSetHandler resultSetHandler = new DefaultResultSetHandler(executor, mappedStatement, parameterHandler, resultHandler, boundSql, rowBounds);
resultSetHandler = (ResultSetHandler) interceptorChain.pluginAll(resultSetHandler);
return resultSetHandler;
}
public StatementHandler newStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
System.out.println("newStatementHandler......");
StatementHandler statementHandler = new RoutingStatementHandler(executor, mappedStatement, parameterObject, rowBounds, resultHandler, boundSql);
statementHandler = (StatementHandler) interceptorChain.pluginAll(statementHandler);
return statementHandler;
}
public Executor newExecutor(Transaction transaction) {
return newExecutor(transaction, defaultExecutorType);
}
public Executor newExecutor(Transaction transaction, ExecutorType executorType) {
executorType = executorType == null ? defaultExecutorType : executorType;
executorType = executorType == null ? ExecutorType.SIMPLE : executorType;
Executor executor;
if (ExecutorType.BATCH == executorType) {
executor = new BatchExecutor(this, transaction);
} else if (ExecutorType.REUSE == executorType) {
executor = new ReuseExecutor(this, transaction);
} else {
executor = new SimpleExecutor(this, transaction);
}
if (cacheEnabled) {
executor = new CachingExecutor(executor);
}
executor = (Executor) interceptorChain.pluginAll(executor);
return executor;
}
|
在实际测操作中，在项目中定义实现的plugin都会被添加保存到interceptorChain对象的一个集合中，在内部会对集合里的对象进行遍历，分别调用每个插件的plugin方法。
其中target就是在调用pluginAll传入的具体对象。。。
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 代码实现
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 |
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代码实现
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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);
}
}
}
|