mybatis plus intercept修改sql

2024-03-28 16:04
文章标签 sql 修改 mybatis plus database intercept

本文主要是介绍mybatis plus intercept修改sql，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

原始需求

在SQL语句前面加上一个request-id

问题描述

今天收到业务同学反馈，说接入某个SDK后，request-id本地debug发现sql已经修改了，但打印的sql中却没有request-id信息

在这里插入图片描述

在这里插入图片描述

看了下代码，发现用户的代码其实就是下方 方案一代码，取不到的原因也在代码中注释了

方案一

package com.jiankunking.mybatisplus;import static org.apache.commons.lang3.StringUtils.isNotBlank;import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.jiankunking.utils.JkkLogUtil;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.Properties;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;@Intercepts({@Signature(type = StatementHandler.class, method = "prepare",args = {Connection.class, Integer.class}),@Signature(type = StatementHandler.class, method = "getBoundSql", args = {}),@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}),@Signature(type = Executor.class, method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class,CacheKey.class, BoundSql.class}),}
)
@Slf4j
public class MybatisPlusRequestIdInterceptor extends MybatisPlusInterceptor {private Boolean enabled = true;@Overridepublic Object intercept(Invocation invocation) throws Throwable {Object target = invocation.getTarget();Object[] args = invocation.getArgs();BoundSql boundSql = null;if (target instanceof Executor) {Object parameter = args[1];boolean isUpdate = args.length == 2;MappedStatement ms = (MappedStatement) args[0];if (!isUpdate && ms.getSqlCommandType() == SqlCommandType.SELECT) {RowBounds rowBounds = (RowBounds) args[2];if (args.length == 4) {boundSql = ms.getBoundSql(parameter);} else {// 几乎不可能走进这里面,除非使用Executor的代理对象调用query[args[6]]boundSql = (BoundSql) args[5];}}} else {StatementHandler statementHandler = (StatementHandler) target;boundSql = statementHandler.getBoundSql();}if (enabled && boundSql != null) {String sql = boundSql.getSql();String requestId = JkkLogUtil.getCurrentRequestId();StringBuilder sb = new StringBuilder("/*");if (isNotBlank(requestId) && isNotBlank(sql)) {sb.append(" Jkk-request-id:").append(requestId).append(" ");}String armsTraceId = JkkLogUtil.getTraceId();if (isNotBlank(armsTraceId) && isNotBlank(sql)) {sb.append(" trace-id:").append(armsTraceId).append(" ");}String armsRpcId = JkkLogUtil.getRpcId();if (isNotBlank(armsRpcId)) {sb.append(" rpc-id:").append(armsRpcId).append(" ");}sb.append(" */");sb.append(sql);// 通过反射修改sql语句Field field = boundSql.getClass().getDeclaredField("sql");field.setAccessible(true);field.set(boundSql, sb.toString());// 这里如果不调用 return invocation.proceed();// 而是执行父类intercept的话，会导致反射修改的SQL无效// 因为父类获取sql还是基于参数再次拼接的没有直接使用boundSql}return super.intercept(invocation);}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties) {enabled = Boolean.valueOf(properties.getProperty("enabled", "true"));}
}

方案二

直接修改mybatisplus的Statement

package com.jiankunking.mybatisplus;import static org.apache.commons.lang3.StringUtils.isNotBlank;import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.jiankunking.utils.JkkLogUtil;import java.lang.reflect.Field;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;@Intercepts({@Signature(type = StatementHandler.class, method = "prepare",args = {Connection.class, Integer.class}),@Signature(type = StatementHandler.class, method = "getBoundSql", args = {}),@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}),@Signature(type = Executor.class, method = "query",args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class,CacheKey.class, BoundSql.class}),}
)
@Slf4j
public class MybatisPlusRequestIdInterceptor extends MybatisPlusInterceptor {private Boolean enabled = true;@Overridepublic Object intercept(Invocation invocation) throws Throwable {Object target = invocation.getTarget();Object[] args = invocation.getArgs();BoundSql boundSql = null;if (target instanceof Executor) {Object parameter = args[1];boolean isUpdate = args.length == 2;MappedStatement ms = (MappedStatement) args[0];if (!isUpdate && ms.getSqlCommandType() == SqlCommandType.SELECT) {RowBounds rowBounds = (RowBounds) args[2];if (args.length == 4) {boundSql = ms.getBoundSql(parameter);} else {// 几乎不可能走进这里面,除非使用Executor的代理对象调用query[args[6]]boundSql = (BoundSql) args[5];}}} else {StatementHandler statementHandler = (StatementHandler) target;boundSql = statementHandler.getBoundSql();}if (enabled && boundSql != null) {String sql = boundSql.getSql();String requestId = JkkLogUtil.getCurrentRequestId();StringBuilder sb = new StringBuilder("/*");if (isNotBlank(requestId) && isNotBlank(sql)) {sb.append(" Jkk-request-id:").append(requestId).append(" ");}String armsTraceId = JkkLogUtil.getTraceId();if (isNotBlank(armsTraceId) && isNotBlank(sql)) {sb.append(" trace-id:").append(armsTraceId).append(" ");}String armsRpcId = JkkLogUtil.getRpcId();if (isNotBlank(armsRpcId)) {sb.append(" rpc-id:").append(armsRpcId).append(" ");}sb.append(" */");sb.append(sql);// 通过反射修改sql语句//Field field = boundSql.getClass().getDeclaredField("sql");//field.setAccessible(true);//field.set(boundSql, sb.toString());if (target instanceof Executor) {setCurrentSql(invocation, sb.toString());} else {StatementHandler statementHandler = (StatementHandler) target;MetaObject metaObject = SystemMetaObject.forObject(statementHandler);metaObject.setValue("delegate.boundSql.sql", sb.toString());}}return super.intercept(invocation);}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties) {enabled = Boolean.valueOf(properties.getProperty("enabled", "true"));}private static final int MAPPED_STATEMENT_INDEX = 0;private static final int PARAM_OBJ_INDEX = 1;private void setCurrentSql(Invocation invocation, String sql) {Object[] args = invocation.getArgs();Object paramObj = args[PARAM_OBJ_INDEX];MappedStatement mappedStatement = (MappedStatement) args[MAPPED_STATEMENT_INDEX];BoundSql boundSql = mappedStatement.getBoundSql(paramObj);MappedStatement newMappedStatement = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(boundSql));MetaObject metaObject = MetaObject.forObject(newMappedStatement,new DefaultObjectFactory(), new DefaultObjectWrapperFactory(),new DefaultReflectorFactory());metaObject.setValue("sqlSource.boundSql.sql", sql);args[MAPPED_STATEMENT_INDEX] = newMappedStatement;}private class BoundSqlSqlSource implements SqlSource {BoundSql boundSql;public BoundSqlSqlSource(BoundSql boundSql) {this.boundSql = boundSql;}public BoundSql getBoundSql(Object parameterObject) {return boundSql;}}@SuppressWarnings({"unchecked", "rawtypes"})private MappedStatement copyFromMappedStatement(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());// setStatementTimeout()builder.timeout(ms.getTimeout());// setParameterMap()builder.parameterMap(ms.getParameterMap());// setStatementResultMap()List<ResultMap> resultMaps = new ArrayList<ResultMap>();String id = "-inline";if (ms.getResultMaps() != null) {id = ms.getResultMaps().get(0).getId() + "-inline";}ResultMap resultMap = new ResultMap.Builder(null, id, Long.class,new ArrayList()).build();resultMaps.add(resultMap);builder.resultMaps(resultMaps);builder.resultSetType(ms.getResultSetType());// setStatementCache()builder.cache(ms.getCache());builder.flushCacheRequired(ms.isFlushCacheRequired());builder.useCache(ms.isUseCache());return builder.build();}//private MappedStatement getMappedStatement(Invocation invo) {//    Object[] args = invo.getArgs();//    Object mappedStatement = args[MAPPED_STATEMENT_INDEX];//    return (MappedStatement) mappedStatement;//}
}

方案三

package com.jiankunking.mybatisplus;import static org.apache.commons.lang3.StringUtils.isNotBlank;import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import com.jiankunking.utils.JkkLogUtil;
import java.sql.Connection;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.statement.StatementHandler;@Slf4j
public class MybatisPlusRequestIdInterceptor implements InnerInterceptor {@Overridepublic void beforePrepare(StatementHandler sh, Connection connection, Integer transactionTimeout) {try {PluginUtils.MPStatementHandler mpSh = PluginUtils.mpStatementHandler(sh);PluginUtils.MPBoundSql mpBs = mpSh.mPBoundSql();String sql = mpBs.sql();if (StringUtils.isBlank(sql)) {return;}StringBuilder sb = new StringBuilder("/*");String requestId = JkkLogUtil.getCurrentRequestId();if (isNotBlank(requestId)) {sb.append(" Jkk-request-id:").append(requestId).append(" ");}String armsTraceId = JkkLogUtil.getTraceId();if (StringUtils.isNotBlank(armsTraceId)) {sb.append(" trace-id:").append(armsTraceId).append(" ");}String armsRpcId = JkkLogUtil.getRpcId();if (StringUtils.isNotBlank(armsRpcId)) {sb.append(" rpc-id:").append(armsRpcId).append(" ");}sb.append(" */");sb.append(sql);mpBs.sql(sb.toString());} catch (Exception e) {log.error("beforePrepare has error", e);}}
}

这篇关于mybatis plus intercept修改sql的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/856061。

相关文章

MySQL 主从复制部署及验证(示例详解)

MySQL 主从复制部署及验证(示例详解)

《MySQL主从复制部署及验证(示例详解)》本文介绍MySQL主从复制部署步骤及学校管理数据库创建脚本,包含表结构设计、示例数据插入和查询语句,用于验证主从同步功能,感兴趣的朋友一起看看吧... 目录mysql 主从复制部署指南部署步骤1.环境准备2. 主服务器配置3. 创建复制用户4. 获取主服务器状态5
阅读更多...
SpringBoot中六种批量更新Mysql的方式效率对比分析

SpringBoot中六种批量更新Mysql的方式效率对比分析

《SpringBoot中六种批量更新Mysql的方式效率对比分析》文章比较了MySQL大数据量批量更新的多种方法,指出REPLACEINTO和ONDUPLICATEKEY效率最高但存在数据风险,MyB... 目录效率比较测试结构数据库初始化测试数据批量修改方案第一种 for第二种 case when第三种
阅读更多...
MyBatis-Plus通用中等、大量数据分批查询和处理方法

MyBatis-Plus通用中等、大量数据分批查询和处理方法

《MyBatis-Plus通用中等、大量数据分批查询和处理方法》文章介绍MyBatis-Plus分页查询处理,通过函数式接口与Lambda表达式实现通用逻辑,方法抽象但功能强大,建议扩展分批处理及流式... 目录函数式接口获取分页数据接口数据处理接口通用逻辑工具类使用方法简单查询自定义查询方法总结函数式接口
阅读更多...
MySql基本查询之表的增删查改+聚合函数案例详解

MySql基本查询之表的增删查改+聚合函数案例详解

《MySql基本查询之表的增删查改+聚合函数案例详解》本文详解SQL的CURD操作INSERT用于数据插入（单行/多行及冲突处理）,SELECT实现数据检索（列选择、条件过滤、排序分页）,UPDATE... 目录一、Create1.1 单行数据 + 全列插入1.2 多行数据 + 指定列插入1.3 插入否则更
阅读更多...
MySQL深分页进行性能优化的常见方法

MySQL深分页进行性能优化的常见方法

《MySQL深分页进行性能优化的常见方法》在Web应用中,分页查询是数据库操作中的常见需求,然而,在面对大型数据集时,深分页（deeppagination）却成为了性能优化的一个挑战,在本文中,我们将... 目录引言：深分页，真的只是“翻页慢”那么简单吗？一、背景介绍二、深分页的性能问题三、业务场景分析四、
阅读更多...
MySQL 迁移至 Doris 最佳实践方案(最新整理)

MySQL 迁移至 Doris 最佳实践方案(最新整理)

《MySQL迁移至Doris最佳实践方案(最新整理)》本文将深入剖析三种经过实践验证的MySQL迁移至Doris的最佳方案,涵盖全量迁移、增量同步、混合迁移以及基于CDC(ChangeData... 目录一、China编程JDBC Catalog 联邦查询方案（适合跨库实时查询）1. 方案概述2. 环境要求3.
阅读更多...
SQL server数据库如何下载和安装

SQL server数据库如何下载和安装

《SQLserver数据库如何下载和安装》本文指导如何下载安装SQLServer2022评估版及SSMS工具,涵盖安装配置、连接字符串设置、C#连接数据库方法和安全注意事项,如混合验证、参数化查... 目录第一步：打开官网下载对应文件第二步：程序安装配置第三部：安装工具SQL Server Manageme
阅读更多...
C#连接SQL server数据库命令的基本步骤

C#连接SQL server数据库命令的基本步骤

《C#连接SQLserver数据库命令的基本步骤》文章讲解了连接SQLServer数据库的步骤,包括引入命名空间、构建连接字符串、使用SqlConnection和SqlCommand执行SQL操作,... 目录建议配合使用：如何下载和安装SQL server数据库-CSDN博客1. 引入必要的命名空间2.
阅读更多...
MyBatis中$与#的区别解析

MyBatis中$与#的区别解析

《MyBatis中$与#的区别解析》文章浏览阅读314次，点赞4次，收藏6次。MyBatis使用#{}作为参数占位符时，会创建预处理语句（PreparedStatement），并将参数值作为预处理语句... 目录一、介绍二、sql注入风险实例一、介绍#（井号）：MyBATis使用#{}作为参数占位符时，会
阅读更多...
全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

全面掌握 SQL 中的 DATEDIFF函数及用法最佳实践

《全面掌握SQL中的DATEDIFF函数及用法最佳实践》本文解析DATEDIFF在不同数据库中的差异,强调其边界计算原理,探讨应用场景及陷阱,推荐根据需求选择TIMESTAMPDIFF或inte... 目录1. 核心概念：DATEDIFF 究竟在计算什么？2. 主流数据库中的 DATEDIFF 实现2.1
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2