MyBatis如何通過攔截修改SQL

更新時(shí)間：2022年11月18日 09:43:53 作者：澄風(fēng)

這篇文章主要介紹了MyBatis如何通過攔截修改SQL問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

假如我們想實(shí)現(xiàn)多租戶，或者在某些SQL后面自動(dòng)拼接查詢條件。在開發(fā)過程中大部分場(chǎng)景可能都是一個(gè)查詢寫一個(gè)SQL去處理，我們?nèi)绻胄薷淖罱KSQL可以通過修改各個(gè)mapper.xml中的SQL來處理。

但實(shí)際過程中我們可能穿插著ORM和SQL的混合使用，隱藏在代碼中不容易被發(fā)現(xiàn)，還有假如項(xiàng)目中有很多很多的SQL我們不可能一一的去修改解決。

這個(gè)時(shí)候我們就需要通過mybatis攔截SQL并且最終修改SQL。

maven依賴

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>2.0.4</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-core</artifactId>
    <version>5.2.4.RELEASE</version>
    <scope>compile</scope>
</dependency>

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

<!-- 大家最好是使用jsqlparser去解析sql提供的功能很多 -->
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>4.2</version>
</dependency>

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>2.0.4</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus</artifactId>
    <version>2.3.3</version>
</dependency>
<!-- 核心包 -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.4</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.6</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

配置部分

這部分是傳統(tǒng)mybatis的xml配置，如果是Springboot項(xiàng)目或者使用JavaConfig配置的請(qǐng)查看官方文檔配置方式。

無非就是Springboot封裝了mybatis-xxx-stater包將部分配置都轉(zhuǎn)為了參數(shù)控制以及部分autoconfig，大同小異這里不做過多討論。

<?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>

    <properties resource="db.properties"></properties>
    <settings>
        <!-- 打印查詢語句 -->
        <setting name="logImpl" value="STDOUT_LOGGING" />
        <!-- 控制全局緩存（二級(jí)緩存）-->
        <setting name="cacheEnabled" value="false"/>
        <!-- 延遲加載的全局開關(guān)。當(dāng)開啟時(shí)，所有關(guān)聯(lián)對(duì)象都會(huì)延遲加載。默認(rèn) false  -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 當(dāng)開啟時(shí)，任何方法的調(diào)用都會(huì)加載該對(duì)象的所有屬性。默認(rèn) false，可通過select標(biāo)簽的 fetchType來覆蓋-->
        <setting name="aggressiveLazyLoading" value="false"/>
        <!--  Mybatis 創(chuàng)建具有延遲加載能力的對(duì)象所用到的代理工具，默認(rèn)JAVASSIST -->
        <!--<setting name="proxyFactory" value="CGLIB" />-->
        <!-- STATEMENT級(jí)別的緩存，使一級(jí)緩存，只針對(duì)當(dāng)前執(zhí)行的這一statement有效 -->
        <!-- <setting name="localCacheScope" value="STATEMENT"/>-->
        <setting name="localCacheScope" value="SESSION"/>
    </settings>

	<!--<typeAliases>-->
	<!--   <typeAlias alias="blog" type="com.allens.mybatis.model.model" />-->
	<!--</typeAliases>-->

    <!--    <typeHandlers>
            <typeHandler handler="com.wuzz.type.MyTypeHandler"></typeHandler>
        </typeHandlers>-->

    <!-- 對(duì)象工廠 -->
    <!--    <objectFactory type="com.wuzz.objectfactory.GPObjectFactory">
            <property name="wuzz" value="666"/>
        </objectFactory>-->

   <!-- 配置攔截器，本文的重點(diǎn) -->
    <plugins>
        <plugin interceptor="com.allens.mybatis.interceptor.SQLInterceptor"/>
        <plugin interceptor="com.allens.mybatis.interceptor.SQLParamInterceptor"/>
    </plugins>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/><!-- 單獨(dú)使用時(shí)配置成MANAGED沒有事務(wù) -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mappers/UserMapper.xml"/>
    </mappers>

</configuration>

整個(gè)攔截調(diào)用鏈路流程圖：

① mybatis在這一層包裝了StatementHandler返回代理對(duì)象，下一步調(diào)用prepare的時(shí)候會(huì)先調(diào)用增強(qiáng)攔截器。

Configuration.newStatementHandler

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);
	// 對(duì)statementhandler進(jìn)行代理
	statementHandler = (StatementHandler) interceptorChain.pluginAll(statementHandler);
	return statementHandler;
}

InterceptorChain.pluginAll

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

Interceptor.plugin

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

包裝成代理對(duì)象

Plugin.wrap

public static Object wrap(Object target, Interceptor interceptor) {
	Map<Class<?>, Set<Method>> signatureMap = getSignatureMap(interceptor);
	Class<?> type = target.getClass();
	// 獲取target對(duì)象的所有接口類型
	Class<?>[] interfaces = getAllInterfaces(type, signatureMap);
	if (interfaces.length > 0) {
	// 學(xué)習(xí)過JDK動(dòng)態(tài)代理的同學(xué)對(duì)這段代碼肯定很熟，這里是生成一個(gè)代理對(duì)象
	return Proxy.newProxyInstance(
	       type.getClassLoader(), // 類加載器
	       interfaces, // JDK動(dòng)態(tài)代理必須要有接口
	       new Plugin(target, interceptor, signatureMap));
	 }
	 return target;
}

如果大家覺得這篇文章寫的還可以請(qǐng)關(guān)注我，我后續(xù)會(huì)出mybatis的源碼解析。

調(diào)用代碼

這部分代碼負(fù)責(zé)調(diào)用mybatis，如果使用springboot這部分就是你的rest接口。

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創(chuàng)建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        // 獲取到SqlSession
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // 調(diào)用Mapper中的指定方法 com.wyh.mapper.UserMapper.queryAll是statementId
        Map<String, Object> map = new HashMap<>();
        map.put("pageSize", 1);
        map.put("desc", "desc");
        map.put("name", "Allens");
        List<User> userList = sqlSession.selectList("com.allens.mybatis.mappers.UserMapper.selectUsers", map);
        System.out.println("++++++++++++++++++++++");
        userList.forEach(System.out::println);
    }
}

攔截器的代碼實(shí)現(xiàn)

這使用了Druid的SQLParser進(jìn)行解析SQL，如果不想使用druid可以使用sqlparser包進(jìn)行sql解析。不管用什么樣的工具把SQL修改掉就行了，形式不限。

如果想使用sqlparser進(jìn)行解析sql可以看我的下一篇文章

JSqlparser 使用攻略（高效的SQL解析工具）

/**
 * MyBatis 允許你在映射語句執(zhí)行過程中的某一點(diǎn)進(jìn)行攔截調(diào)用。默認(rèn)情況下，MyBatis 允許使用插件來攔截的方法調(diào)用包括：
 * <p>
 * Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
 * ParameterHandler (getParameterObject, setParameters)
 * ResultSetHandler (handleResultSets, handleOutputParameters)
 * StatementHandler (prepare, parameterize, batch, update, query)
 * 這些類中方法的細(xì)節(jié)可以通過查看每個(gè)方法的簽名來發(fā)現(xiàn)，或者直接查看 MyBatis 發(fā)行包中的源代碼。 如果你想做的不僅僅是監(jiān)控方法的調(diào)用，那么你最好相當(dāng)了解要重寫的方法的行為。 因?yàn)樵谠噲D修改或重寫已有方法的行為時(shí)，很可能會(huì)破壞 MyBatis 的核心模塊。 這些都是更底層的類和方法，所以使用插件的時(shí)候要特別當(dāng)心。
 * <p>
 * 通過 MyBatis 提供的強(qiáng)大機(jī)制，使用插件是非常簡(jiǎn)單的，只需實(shí)現(xiàn) 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<String> getInsertPropertiesName(List<Map<String, Object>> properties) {
        List<String> 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<Map<String, Object>> 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<Map<String, Object>> insertProperties = new ArrayList<>();
        Map<String, Object> nameProperties = new HashMap<>();
        nameProperties.put("columnName", "name");
        nameProperties.put("columnValue", "baby");
        nameProperties.put("expr", "and");
        insertProperties.add(nameProperties);

        Map<String, Object> 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();

        // 獲取傳入的參數(shù)
        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<String, Object> 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 通過在這里打斷點(diǎn)看對(duì)象我們可以看出這是一個(gè)樹的結(jié)構(gòu)
            SQLSelect sqlselect = selectStmt.getSelect();
            SQLSelectQueryBlock query = (SQLSelectQueryBlock) sqlselect.getQuery();
            SQLExpr whereExpr = query.getWhere();

            // 修改where表達(dá)式
            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;
    }
}

可以看到上面使用了反射去設(shè)置boundsql.sql，有可能有同學(xué)會(huì)問這樣會(huì)不會(huì)觸發(fā)JVM優(yōu)化修改不了這個(gè)final String屬性。

這里我下一個(gè)結(jié)論是可以的，為了解釋這個(gè)問題我寫了端代碼幫助理解：

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");

        // 構(gòu)造函數(shù)初始化
        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);
    }
}

最終輸出結(jié)果為：

可以看到如果是直接賦值進(jìn)行初始化final屬性的話，會(huì)被JVM給優(yōu)化掉，如果使用的是構(gòu)造函數(shù)進(jìn)行初始化屬性是不是觸發(fā)JVM優(yōu)化的。

我們?cè)倏匆幌耣oundsql類的屬性定義，很顯然sql屬性是在構(gòu)造函數(shù)中進(jìn)行初始化的。我們可以大膽的去modify sql，但一定要注意不能修改成錯(cuò)誤的SQL和一定要考慮安全問題，mybatis沒有提供sql的修改方法也是考慮這一點(diǎn)，可能會(huì)不安全。

public class BoundSql {

  // 我們要修改的SQL屬性
  private final String sql;
  private final List<ParameterMapping> parameterMappings;
  private final Object parameterObject;
  private final Map<String, Object> additionalParameters;
  private final MetaObject metaParameters;

  public BoundSql(Configuration configuration, String sql, List<ParameterMapping> 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<ParameterMapping> 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);
  }
}

UserMapper.xml

很簡(jiǎn)單的一個(gè)SQL

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.allens.mybatis.mappers.UserMapper">
    <select id="selectUsers" parameterType="int" resultType="com.allens.mybatis.model.User">
        select * from user where id = 1 limit ${pageSize}
    </select>
</mapper>