Mybatis SQL数量限制插件的实现_Java

插件背景

起因是一次线上事故，现有项目对数据库的操作依赖编码者自己的行为规范，有可能出现考虑不当对全表进行查询，数据量极大的情况会引发频繁gc带来一系列的问题
为了避免该问题，基于mybatis开发一个插件，默认限制查询的条数，默认开启该功能，默认1000条。

插件功能

支持灵活配置插件

可以通过配置application.properties文件中的配置，灵活控制插件，如果有接入动态配置中心，同样可以把一下配置添加进去，这样可以达到运行时灵活控制插件的效果，参数详情见上文：

mybatis.limit.size.enable:是否开启插件功能。
mybatis.limit.size:插件限制查询数量，如果不配置使用默认：1000条。

支持个别特殊方法插件效果不生效

该插件加载之后，默认会拦截所有查询语句，同时会过滤掉已经存在limit或者如count等统计类的sql，但是仍然有极个别业务场景需要绕开此拦截，因此提供了注解，支持在全局开启limit插件的情况下，特殊接口不走拦截。

@notsupportdefaultlimit -> 该注解在插件开启的情况下，可以针对某个不需要查询限制的接口单独设置屏蔽此功能

注意
现有已知的不兼容的场景：
代码中使用rowbounds进行逻辑分页，接口会报错，因为mybatis的rowbounds是一次性将数据查出来，在内存中进行分页的，而mybatis插件是无法区分该种形式，也就无法兼容。

插件代码

1、添加一个配置类

@configuration
@propertysource(value = {"classpath:application.properties"},encoding = "utf-8")
public class limitproperties {
    /**
     * 默认限制数量
     */
    private final static int defaultlimitsize = 1000;
    /**
     * 插件的开关
     */
    @value("${mybatis.limit.size.enable}")
    private boolean enable;
    /**
     * 配置限制数量
     */
    @value("${mybatis.limit.size}")
    private integer size;

    public limitproperties() {
    }

    public boolean isoffline() {
        return this.enable != null && !this.enable;
    }

    public int limit() {
        return this.size != null && this.size > 0 ? this.size : defaultlimitsize;
    }

    public void setsize(integer size) {
        this.size = size;
    }
}

2、定义sql处理器，用来修改sql

public class sqlhandler {
    private static final string limit_sql_template = "%s limit %s;";
    private static final list<string> key_word = arrays.aslist("count", "limit", "sum", "avg", "min", "max");
    private boundsql boundsql;
    private string originsql;
    private boolean needoverride;
    private string newsql;

    public static sqlhandler build(boundsql boundsql, int size) {
        string originsql = boundsql.getsql().tolowercase();
        sqlhandler handler = new sqlhandler(boundsql, originsql);
        if (!containskeyword(handler.getoriginsql())) {
            handler.setneedoverride(boolean.true);
            string newsql = string.format(limit_sql_template, originsql.replace(";", ""), size);
            handler.setnewsql(newsql);
        }

        return handler;
    }

    private sqlhandler(boundsql boundsql, string originsql) {
        this.needoverride = boolean.false;
        this.boundsql = boundsql;
        this.originsql = originsql;
    }

    public boolean needoverride() {
        return this.needoverride;
    }

    public static boolean containskeyword(string sql) {
        iterator var1 = key_word.iterator();

        string keyword;
        do {
            if (!var1.hasnext()) {
                return boolean.false;
            }

            keyword = (string)var1.next();
        } while(!sql.contains(keyword));

        return boolean.true;
    }

    public boundsql getboundsql() {
        return this.boundsql;
    }

    public void setboundsql(boundsql boundsql) {
        this.boundsql = boundsql;
    }

    public string getoriginsql() {
        return this.originsql;
    }

    public void setoriginsql(string originsql) {
        this.originsql = originsql;
    }

    public boolean getneedoverride() {
        return this.needoverride;
    }

    public void setneedoverride(boolean needoverride) {
        this.needoverride = needoverride;
    }

    public string getnewsql() {
        return this.newsql;
    }

    public void setnewsql(string newsql) {
        this.newsql = newsql;
    }
}

3、第一步定义一个插件

/**
 * mybatis插件：查询数量限制
 * 使用方法详见：
 * 拦截mybatis的：executor.query()
 * @see executor#query(org.apache.ibatis.mapping.mappedstatement, java.lang.object, org.apache.ibatis.session.rowbounds, org.apache.ibatis.session.resulthandler)
 * @see executor#query(org.apache.ibatis.mapping.mappedstatement, java.lang.object, org.apache.ibatis.session.rowbounds, org.apache.ibatis.session.resulthandler, org.apache.ibatis.cache.cachekey, org.apache.ibatis.mapping.boundsql)
 */
@component
@intercepts({@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}
)})
public class limitinterceptor implements interceptor {
    @autowired
    limitproperties limitproperties;

    public limitinterceptor() {
    }

    public object intercept(invocation invocation) throws throwable {
        if (!this.limitproperties.isoffline() && !limitthreadlocal.isnotsupport()) {
            object[] args = invocation.getargs();
            mappedstatement ms = (mappedstatement) args[0];
            object parameter = args[1];
            boundsql boundsql = args.length == 4 ? ms.getboundsql(parameter) : (boundsql) args[5];
            sqlhandler sqlhandler = sqlhandler.build(boundsql, this.limitproperties.limit());
            if (!sqlhandler.needoverride()) {
                return invocation.proceed();
            } else {
                // 需要覆盖
                executor executor = (executor) invocation.gettarget();
                rowbounds rowbounds = (rowbounds) args[2];
                resulthandler resulthandler = (resulthandler) args[3];
                cachekey cachekey = args.length == 4 ? executor.createcachekey(ms, parameter, rowbounds, boundsql) : (cachekey) args[4];
                metaobject metaobject = systemmetaobject.forobject(boundsql);
                metaobject.setvalue("sql", sqlhandler.getnewsql());
                return executor.query(ms, parameter, rowbounds, resulthandler, cachekey, boundsql);
            }
        } else {
            return invocation.proceed();
        }
    }

    public object plugin(object o) {
        return plugin.wrap(o, this);
    }

    public void setproperties(properties properties) {
    }
}

4、下面定义一个注解，用来设置哪些接口不需要数量限制

@retention(retentionpolicy.runtime)
@target({elementtype.method})
@documented
public @interface notsupportdefaultlimit {
}

5、使用aop去拦截不需要数量控制限制的接口

/**
 * aop：对@notsupportdefaultlimit进行环绕通知
 * 主要作用：为当前线程set一个标志，标记当前线程不需要数量限制
 */
@aspect
@component
public class limitsupportaop {
    @autowired
    limitproperties limitproperties;

    public limitsupportaop() {
    }

    @around("@annotation(com.jkys.vitamin.rpc.mybatis.notsupportdefaultlimit)")
    public object around(proceedingjoinpoint proceedingjoinpoint) throws throwable {
        if (this.limitproperties.isoffline()) {
            return proceedingjoinpoint.proceed();
        } else {
            object var2;
            try {
                limitthreadlocal.tryacquire();
                var2 = proceedingjoinpoint.proceed();
            } finally {
                limitthreadlocal.tryrelease();
            }

            return var2;
        }
    }
}

/**
 * 记录当前线程执行sql，是否 不需要数量限制
 */
public class limitthreadlocal {
    private static final threadlocal<integer> times = new threadlocal();

    public limitthreadlocal() {
    }

    public static void tryacquire() {
        integer time = (integer)times.get();
        if (time == null || time < 0) {
            time = 0;
        }

        times.set(time + 1);
    }

    public static void tryrelease() {
        integer time = (integer)times.get();
        if (time != null && time > 0) {
            times.set(time - 1);
        }

        if ((integer)times.get() <= 0) {
            times.remove();
        }

    }

    public static boolean issupport() {
        return times.get() == null || (integer)times.get() <= 0;
    }

    public static boolean isnotsupport() {
        return times.get() != null && (integer)times.get() > 0;
    }
}