SpringBoot 通过自定义 Mybatis 拦截器,实现 SQL 的改写
1、拦截器应用场景:
(1)分页,如com.github.pagehelper的分页插件实现。
(2)拦截sql做日志监控;
(3)统一对某些sql进行统一条件拼接,类似于分页。
2、研究初衷:
(1)做什么:通过拦截器对查询的 sql 进行改写, 让 pageHelper 执行的是改写后的 sql。
(2)怎样做:由于分页使用的是 Pagehelper ,其内部机制也是通过拦截器实现的。基于 MyBatis 拦截器链的加载机制,后加载的会先执行,也就是说我们
自定义的拦截器,必须加载在 Pagehelper之后。
3、相关配置类实现:
(1)MySqlInterceptor 类:
package spcommon.config; import org.apache.ibatis.executor.Executor; import org.apache.ibatis.mapping.*; import org.apache.ibatis.plugin.*; import org.apache.ibatis.session.ResultHandler; import org.apache.ibatis.session.RowBounds; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.Properties; /** * 自定义 MyBatis 拦截器 */ @Intercepts({@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class,ResultHandler.class})}) public class MySqlInterceptor implements Interceptor { private static final Logger logger= LoggerFactory.getLogger(MySqlInterceptor.class); /** * intercept 方法用来对拦截的sql进行具体的操作 * @param invocation * @return * @throws Throwable */ @Override public Object intercept(Invocation invocation) throws Throwable { logger.info("执行intercept方法:{}", invocation.toString()); Object[] args = invocation.getArgs(); MappedStatement ms = (MappedStatement) args[0]; Object parameterObject = args[1]; // id为执行的mapper方法的全路径名,如com.mapper.UserMapper String id = ms.getId(); // sql语句类型 select、delete、insert、update String sqlCommandType = ms.getSqlCommandType().toString(); // 仅拦截 select 查询 //if (!sqlCommandType.equals(SqlCommandType.SELECT.toString())) { // return invocation.proceed(); //} BoundSql boundSql = ms.getBoundSql(parameterObject); String origSql = boundSql.getSql(); logger.info("原始SQL: {}", origSql); // 组装新的 sql String newSql = origSql + " limit 1"; // 重新new一个查询语句对象 BoundSql newBoundSql = new BoundSql(ms.getConfiguration(), newSql, boundSql.getParameterMappings(), boundSql.getParameterObject()); // 把新的查询放到statement里 MappedStatement newMs = newMappedStatement(ms, new BoundSqlSqlSource(newBoundSql)); for (ParameterMapping mapping : boundSql.getParameterMappings()) { String prop = mapping.getProperty(); if (boundSql.hasAdditionalParameter(prop)) { newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop)); } } Object[] queryArgs = invocation.getArgs(); queryArgs[0] = newMs; logger.info("改写的SQL: {}", newSql); return invocation.proceed(); } /** * 定义一个内部辅助类,作用是包装 SQL */ class BoundSqlSqlSource implements SqlSource { private BoundSql boundSql; public BoundSqlSqlSource(BoundSql boundSql) { this.boundSql = boundSql; } public BoundSql getBoundSql(Object parameterObject) { return boundSql; } } private MappedStatement newMappedStatement (MappedStatement ms, SqlSource newSqlSource) { MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource, ms.getSqlCommandType()); builder.resource(ms.getResource()); builder.fetchSize(ms.getFetchSize()); builder.statementType(ms.getStatementType()); builder.keyGenerator(ms.getKeyGenerator()); if (ms.getKeyProperties() != null && ms.getKeyProperties().length > 0) { builder.keyProperty(ms.getKeyProperties()[0]); } builder.timeout(ms.getTimeout()); builder.parameterMap(ms.getParameterMap()); builder.resultMaps(ms.getResultMaps()); builder.resultSetType(ms.getResultSetType()); builder.cache(ms.getCache()); builder.flushCacheRequired(ms.isFlushCacheRequired()); builder.useCache(ms.isUseCache()); return builder.build(); } @Override public Object plugin(Object target) { logger.info("plugin方法:{}", target); if (target instanceof Executor) { return Plugin.wrap(target, this); } return target; } @Override public void setProperties(Properties properties) { // 获取属性 // String value1 = properties.getProperty("prop1"); logger.info("properties方法:{}", properties.toString()); } }
(2)MyBatisConfig 类:
package spcommon.config; import com.github.pagehelper.autoconfigure.PageHelperAutoConfiguration; import org.apache.ibatis.session.SqlSessionFactory; 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.List; import java.util.Properties; @Configuration @AutoConfigureAfter(PageHelperAutoConfiguration.class) public class MyBatisConfig { @Autowired private List<SqlSessionFactory> sqlSessionFactoryList; @PostConstruct public void addMySqlInterceptor() { MySqlInterceptor interceptor = new MySqlInterceptor(); for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) { // 添加自定义属性 // Properties properties = new Properties(); // properties.setProperty("prop1", "value1"); // interceptor.setProperties(properties); sqlSessionFactory.getConfiguration().addInterceptor(interceptor); } } }
(3)在 resources 目录下创建META-INF目录下,在 META-INF 目录下创建 spring.factories 文件,文件内容如下:
# Auto Configure org.springframework.boot.autoconfigure.EnableAutoConfiguration=\ com.github.pagehelper.autoconfigure.PageHelperAutoConfiguration,\ xxx.xxx.MyBatisConfig
4、测试总结
在同时使用 PageHelper 和 自定义拦截器时,以上配置可解决 spring boot 在使用 pagehelper-spring-boot-starter 后 ,自定义拦截器失效的问题。
参考链接:
https://www.jianshu.com/p/59e28ad9e738
https://www.jianshu.com/p/0a72bb1f6a21
艺无止境,诚惶诚恐, 感谢开源贡献者的努力!!