自定義Mybatis-plus插件(限制最大查詢數(shù)量)

需求背景

? 一次查詢?nèi)绻Y(jié)果返回太多（1萬或更多），往往會導(dǎo)致系統(tǒng)性能下降，有時更會內(nèi)存不足，影響系統(tǒng)穩(wěn)定性，故需要做限制。

解決思路

1.經(jīng)分析最后決定，應(yīng)限制一次查詢返回的最大結(jié)果數(shù)量不應(yīng)該超出1萬，對于一次返回結(jié)果大于限制的時候應(yīng)該拋出異常，而不應(yīng)該截取(limit 10000)最大結(jié)果(結(jié)果需求不匹配)。

2.利用mybatis攔截器技術(shù)，統(tǒng)一攔截sql,并真對大結(jié)果的查詢先做一次count查詢。

步驟一

1.1 定義攔截器PreCheckBigQueryInnerInterceptor

public class PreCheckBigQueryInnerInterceptor implements InnerInterceptor {}

1.2 重寫willDoQuery方法

 public boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        // 解析sql
        Statement stmt = CCJSqlParserUtil.parse(boundSql.getSql());
        if (stmt instanceof Select) {
            PlainSelect selectStmt = (PlainSelect) ((Select) stmt).getSelectBody();
            if (Objects.nonNull(selectStmt.getLimit())) {
                //包含limit查詢
                return true;
            }
            for (SelectItem selectItem : selectStmt.getSelectItems()) {
                //計數(shù)查詢 count();
                SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
                if (selectExpressionItem.getExpression() instanceof Function) {
                    //包含function查詢
                    return true;
                }
            }
            Long aLong = doQueryCount(executor, ms, parameter, rowBounds, resultHandler, boundSql);
            if (aLong == 0L) {
                return false;
            }
            if (aLong > 20) {
                throw new RuntimeException("單個查詢結(jié)果大于20條!!!");
            }
        }
        return true;
    }

1.3 代碼解析

1.3.1 利用CCJSqlParserUtil解析sql，并判斷sql類型，只對Select的SQL攔擊.

1.3.2 對于已有l(wèi)imit的sql查詢,直接放行.

1.3.3 對于包含function查詢（例如count（1）計算，max（）...）,直接放行.

1.3.4 否則判斷為大結(jié)果查詢，執(zhí)行（doQueryCount）與查詢數(shù)量.

1.3.5 對于大于指定數(shù)量的結(jié)果，拋出異常.

1.4 定義doQueryCount方法

private Long doQueryCount(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        MappedStatement countMs = buildAutoCountMappedStatement(ms);
        String countSqlStr = autoCountSql(true, boundSql.getSql());
        PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);
        BoundSql countSql = new BoundSql(countMs.getConfiguration(), countSqlStr, mpBoundSql.parameterMappings(), parameter);
        PluginUtils.setAdditionalParameter(countSql, mpBoundSql.additionalParameters());
        CacheKey cacheKey = executor.createCacheKey(countMs, parameter, rowBounds, countSql);
        Object result = executor.query(countMs, parameter, rowBounds, resultHandler, cacheKey, countSql).get(0);
        System.out.println(result);
        return (result == null ? 0L : Long.parseLong(result.toString()));
    }

代碼解讀：參考PaginationInnerInterceptor（mybatis-plus）分頁插件

1.4.1：構(gòu)造MappedStatement對象buildAutoCountMappedStatement(ms)，MappedStatement相當(dāng)于一個存儲 SQL 語句、輸入?yún)?shù)和輸出結(jié)果映射等信息的封裝體，它對應(yīng)一條 SQL 語句，并包含了該 SQL 語句執(zhí)行所需的所有信息。如下代碼

<mapper namespace="com.example.UserMapper">
   <select id="selectAllUsers" resultType="com.example.User">
       SELECT * FROM user
   </select>
</mapper>

注意：必須重新構(gòu)造，不能直接使用入?yún)⒅械膍s

1.4.2：autoCountSql(true, boundSql.getSql()) 定義并優(yōu)化計數(shù)查詢語句

String.format("SELECT COUNT(1) FROM (%s) TOTAL", originalSql);

1.4.3: 執(zhí)行查詢executor.query

步驟二

1.1 注冊攔截器PreCheckBigQueryInnerInterceptor

@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
    MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
    interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));//分頁插件(Mybatis-plus)
    interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());//防止全表更新(Mybatis-plus)
    interceptor.addInnerInterceptor(new PreCheckBigQueryInnerInterceptor());//防止全表查詢(自定義插件)
    return interceptor;
}

知識小結(jié):

1. MybatisPlusInterceptor

public class MybatisPlusInterceptor implements Interceptor {
    @Setter
    private List<InnerInterceptor> interceptors = new ArrayList<>();
}

? 他是基于mybatis的Interceptor接口做的攔截器,上文中我們 注冊攔截器PreCheckBigQueryInnerInterceptor的攔截器其實添加到MybatisPlusInterceptor.interceptors集合中。

2. 為啥重寫willDoQuery見代碼而不是beforeQuery

 public Object intercept(Invocation invocation) throws Throwable {
       ......
                for (InnerInterceptor query : interceptors) {
                    if (!query.willDoQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql)) {
                        return Collections.emptyList();
                    }
                    query.beforeQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql);
                }
     ......
        return invocation.proceed();
 }

2.1 willDoQuery先于beforeQuery方法，且一定會執(zhí)行文章來源地址http://www.zghlxwxcb.cn/news/detail-418378.html

到了這里，關(guān)于自定義Mybatis-plus插件(限制最大查詢數(shù)量)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！