公司有一个服务用的sqlserver数据库，由于对数据的准确性要求不高，所以修改了事务的隔离级别
主要就是通过mybatis的拦截器实现的
被拦截的类型如下4种,本文主要实现的是StatementHandler
Executor：拦截执行器的方法。
ParameterHandler：拦截参数的处理。
ResultHandler：拦截结果集的处理。
StatementHandler：拦截Sql语法构建的处理
作用如下:
1、分页查询
2、多租户添加条件过滤
3、对返回结果，过滤掉审计字段，敏感字段
4、对返回结果中的加密数据进行解密
5、对新增数据自动添加创建人，创建时间，更新时间，更新人 ，对更新数据自动新增更新时间，更新人
实现代码
import cn.hutool.core.util.ReflectUtil; import lombok.extern.slf4j.Slf4j; import org.apache.ibatis.executor.statement.RoutingStatementHandler; import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.BoundSql; import org.apache.ibatis.plugin.*; import org.springframework.stereotype.Component; import java.sql.Connection; import java.util.Properties; @Slf4j @Component @Intercepts({@Signature(method = "prepare", type = StatementHandler.class, args = {Connection.class, Integer.class}) }) @SuppressWarnings("unchecked") public class MyExecutor implements Interceptor { @Override public Object plugin(Object target) { // 调用插件 return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { } @Override public Object intercept(Invocation invocation) throws Exception { // 该方法写入自己的逻辑 if (invocation.getTarget() instanceof StatementHandler) { // judge dataSource type ,because sqlite can't use internal.core_project this express // so we need to add "" for it or delete this 'internal.' RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget(); StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(handler, "delegate"); BoundSql boundSql = delegate.getBoundSql(); String sql = boundSql.getSql(); // 修改事务级别为读未提交 StringBuilder sqlResult=new StringBuilder("set transaction isolation level read uncommitted;"); sqlResult.append(sql); ReflectUtil.setFieldValue(boundSql, "sql", sqlResult); } // SQL execute start time long startTimeMillis = System.currentTimeMillis(); // get execute result Object proceedReslut = invocation.proceed(); // SQL execute end time long endTimeMillis = System.currentTimeMillis(); log.debug("<< ==== sql execute runnung time：{} millisecond ==== >>", (endTimeMillis - startTimeMillis)); return proceedReslut; } }
监控sql耗时
import org.apache.ibatis.executor.statement.StatementHandler; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.plugin.Interceptor; 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.MetaObject; import org.apache.ibatis.reflection.SystemMetaObject; import org.apache.ibatis.session.ResultHandler; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Component; import java.sql.Statement; import java.util.Properties; /** * Sql执行时间记录拦截器 * * @author tanxu * */ @Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}), @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}), @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class})}) @Component public class MyExecutor implements Interceptor { private static Logger logger = LoggerFactory.getLogger(MyExecutor.class); @Override public Object intercept(Invocation invocation) throws Throwable { Object target = invocation.getTarget(); long startTime = System.currentTimeMillis(); StatementHandler statementHandler = (StatementHandler) target; try { return invocation.proceed(); } finally { long endTime = System.currentTimeMillis(); long timeCount = endTime - startTime; MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler); MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement"); //获取此sql的mapper方法(id) String mapperId = mappedStatement.getId(); logger.info("执行方法>>>>>>>>>>>[ {} ]SQL,执行耗时[ {} ms]", mapperId, timeCount); } } @Override public Object plugin(Object target) { return Plugin.wrap(target, this); } @Override public void setProperties(Properties properties) { } }
