MyBatis如何通过拦截修改SQL

 org.mybatismybatis-spring2.0.4 org.springframeworkspring-core5.2.4.RELEASEcompile mysqlmysql-connector-java8.0.26 com.github.jsqlparserjsqlparser4.2 org.mybatismybatis-spring2.0.4 com.baomidoumybatis-plus2.3.3 org.mybatismybatis3.5.4 com.alibabadruid-spring-boot-starter1.2.6 org.projectlomboklombok org.projectlomboklombok

public StatementHandler newStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) { StatementHandler statementHandler = new RoutingStatementHandler(executor, mappedStatement, parameterObject, rowBounds, resultHandler, boundSql); // 对statementhandler进行代理 statementHandler = (StatementHandler) interceptorChain.pluginAll(statementHandler); return statementHandler; }

public Object pluginAll(Object target) { for (Interceptor interceptor : interceptors) { target = interceptor.plugin(target); } return target; }

default Object plugin(Object target) { return Plugin.wrap(target, this); }

public static Object wrap(Object target, Interceptor interceptor) { Map[] interfaces = getAllInterfaces(type, signatureMap); if (interfaces.length > 0) { // 学习过JDK动态代理的同学对这段代码肯定很熟，这里是生成一个代理对象 return Proxy.newProxyInstance( type.getClassLoader(), // 类加载器 interfaces, // JDK动态代理必须要有接口 new Plugin(target, interceptor, signatureMap)); } return target; }

import com.allens.mybatis.model.User; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; public class TestMybatis { public static void main(String[] args) throws IOException { // 读取配置文件 InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); // 通过SqlSessionFactoryBuilder创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); // 获取到SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // 调用Mapper中的指定方法 com.wyh.mapper.UserMapper.queryAll是statementId Map map = new HashMap<>(); map.put("pageSize", 1); map.put("desc", "desc"); map.put("name", "Allens"); List userList = sqlSession.selectList("com.allens.mybatis.mappers.UserMapper.selectUsers", map); System.out.println("++++++++++++++++++++++"); userList.forEach(System.out::println); } }

/** * MyBatis 允许你在映射语句执行过程中的某一点进行拦截调用。默认情况下，MyBatis 允许使用插件来拦截的方法调用包括： * 
 * Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed) * ParameterHandler (getParameterObject, setParameters) * ResultSetHandler (handleResultSets, handleOutputParameters) * StatementHandler (prepare, parameterize, batch, update, query) * 这些类中方法的细节可以通过查看每个方法的签名来发现，或者直接查看 MyBatis 发行包中的源代码。 如果你想做的不仅仅是监控方法的调用，那么你最好相当了解要重写的方法的行为。 因为在试图修改或重写已有方法的行为时，很可能会破坏 MyBatis 的核心模块。 这些都是更底层的类和方法，所以使用插件的时候要特别当心。 * 
 * 通过 MyBatis 提供的强大机制，使用插件是非常简单的，只需实现 Interceptor 接口，并指定想要拦截的方法签名即可 */ //@Intercepts({@Signature( //        type= Executor.class, //        method = "query", //        args = {StatementHandler.class, Object.class, RowBounds.class, ResultHandler.class})}) @Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})}) public class SQLInterceptor implements Interceptor { private Properties properties = new Properties(); public List getInsertPropertiesName(List> properties) { List list = new ArrayList<>(); properties.forEach(data -> data.keySet() .stream() .filter(e -> e.equals("columnName")) .forEach(e -> list.add((String) data.get("columnName")))); return list; } public void setInsertProperties(List> properties, String columnName, Object value) { properties.forEach(element -> element.forEach((k, v) -> { if (k.equals("columnName") && element.get(k).equals(columnName)) { element.put("columnValue", value); element.put("columnName", columnName); } })); } @Override public Object intercept(Invocation invocation) throws Throwable { // implement pre processing if need List> insertProperties = new ArrayList<>(); Map nameProperties = new HashMap<>(); nameProperties.put("columnName", "name"); nameProperties.put("columnValue", "baby"); nameProperties.put("expr", "and"); insertProperties.add(nameProperties); Map namePropertiesOr = new HashMap<>(); namePropertiesOr.put("columnName", "id"); namePropertiesOr.put("columnValue", "1"); namePropertiesOr.put("expr", "or"); insertProperties.add(namePropertiesOr); // BoundSql boundSql = ((MappedStatement)invocation.getArgs()[0]).getBoundSql(invocation.getArgs()[1]); StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); // 获取传入的参数 Object parameterMappings = statementHandler.getBoundSql().getParameterObject(); if (parameterMappings instanceof Map) { Map parameterMappingsConvert = (Map) parameterMappings; getInsertPropertiesName(insertProperties).forEach(e -> { Object value = parameterMappingsConvert.get(e); if (value != null) { // 如果传参值不为空就覆盖配置值 setInsertProperties(insertProperties, e, value); } }); } MetaObject metaObject = MetaObject .forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY, SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory()); BoundSql boundSql = statementHandler.getBoundSql(); SQLStatementParser sqlParser = SQLParserUtils.createSQLStatementParser(boundSql.getSql(), DbType.MYSQL.getValue()); SQLStatement stmt = sqlParser.parseStatementList().get(0); System.out.println("Origin SQL is:" + boundSql.getSql()); System.out.println("++++++++++++++++++++++"); if (stmt instanceof SQLSelectStatement) { // ((SQLSelectStatement) stmt).addWhere(sqlExpr); // convert conditions to 'and' statement StringBuilder constraintsBuffer = new StringBuilder(); boolean first = true; for (Map data : insertProperties) { if (String.valueOf(data.get("expr")).equalsIgnoreCase("and")) { if (!first) { constraintsBuffer.append(" AND "); } constraintsBuffer.append(String.format(" %s = '%s' ", data.get("columnName"), data.get("columnValue"))); } else if (String.valueOf(data.get("expr")).equalsIgnoreCase("or")) { if (!first) { constraintsBuffer.append(" OR "); } constraintsBuffer.append(String.format(" %s = '%s' ", data.get("columnName"), data.get("columnValue"))); } first = false; } SQLExprParser constraintsParser = SQLParserUtils.createExprParser(constraintsBuffer.toString(), JdbcUtils.MYSQL); SQLExpr constraintsExpr = constraintsParser.expr(); SQLSelectStatement selectStmt = (SQLSelectStatement) stmt; // 拿到SQLSelect 通过在这里打断点看对象我们可以看出这是一个树的结构 SQLSelect sqlselect = selectStmt.getSelect(); SQLSelectQueryBlock query = (SQLSelectQueryBlock) sqlselect.getQuery(); SQLExpr whereExpr = query.getWhere(); // 修改where表达式 if (whereExpr == null) { query.setWhere(constraintsExpr); } else { SQLBinaryOpExpr newWhereExpr = new SQLBinaryOpExpr( whereExpr, SQLBinaryOperator.BooleanAnd, constraintsExpr); query.setWhere(newWhereExpr); } sqlselect.setQuery(query); String sql = sqlselect.toString(); //通过反射修改sql语句 Field field = boundSql.getClass().getDeclaredField("sql"); field.setAccessible(true); field.set(boundSql, sql); System.out.println("modify sql is:" + sql); } // implement post processing if need return invocation.proceed(); } @Override public Object plugin(Object target) { return Interceptor.super.plugin(target); } @Override public void setProperties(Properties properties) { this.properties = properties; } } 

package base; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; public class FinalPropertyModify { private final String a = "123"; private final String ab; public FinalPropertyModify(String ab) { this.ab = ab; } public static void main(String[] args) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException, NoSuchFieldException { // 直接赋值初始化 FinalPropertyModify finalPropertyModify = new FinalPropertyModify("123"); Field a = FinalPropertyModify.class.getDeclaredField("a"); a.setAccessible(true); a.set(finalPropertyModify, "12345"); // 构造函数初始化 Field ab = FinalPropertyModify.class.getDeclaredField("ab"); ab.setAccessible(true); ab.set(finalPropertyModify, "123456"); System.out.println("==================反射获取=================="); System.out.println(a.get(finalPropertyModify)); System.out.println(ab.get(finalPropertyModify)); System.out.println("==================直接获取=================="); System.out.println(finalPropertyModify.a); System.out.println(finalPropertyModify.ab); } } 

public class BoundSql { // 我们要修改的SQL属性 private final String sql; private final List parameterMappings; private final Object parameterObject; private final Map additionalParameters; private final MetaObject metaParameters; public BoundSql(Configuration configuration, String sql, List parameterMappings, Object parameterObject) { this.sql = sql; this.parameterMappings = parameterMappings; this.parameterObject = parameterObject; this.additionalParameters = new HashMap<>(); this.metaParameters = configuration.newMetaObject(additionalParameters); } public String getSql() { return sql; } public List getParameterMappings() { return parameterMappings; } public Object getParameterObject() { return parameterObject; } public boolean hasAdditionalParameter(String name) { String paramName = new PropertyTokenizer(name).getName(); return additionalParameters.containsKey(paramName); } public void setAdditionalParameter(String name, Object value) { metaParameters.setValue(name, value); } public Object getAdditionalParameter(String name) { return metaParameters.getValue(name); } } 

  select * from user where id = 1 limit ${pageSize}