前言

      最近发现原兄弟部门的交付底座，接口执行查询的结果会跟自己预想的不一致，最后发现是做了sql的统一拦截处理。

一、怎么实现

      说一千道一万，还是基于Interceptor拦截器，我们这里用的mybatis，为了做数据隔离，根据当前登录用户来，service层省略传参。

二、使用

      最近有点忙，没有时间废话，直接上码。

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
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.DefaultReflectorFactory;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;
import org.apache.ibatis.reflection.wrapper.DefaultObjectWrapperFactory;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.springframework.stereotype.Component;


@Slf4j
@Component
@Intercepts(@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class,
    RowBounds.class, ResultHandler.class}))
public class MybatisInterceptor implements Interceptor {

	//一个本地缓存对象，登录成功后会将用户相关信息放在里面，任何地方都可以直接取到
    @Resource
    LinkappUserContextProducer linkappUserContextProducer;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs(); //方法参数
        MappedStatement mappedStatement = (MappedStatement) args[0];
        String sqlKey = mappedStatement.getId();
        // 获取sql
        String sql = getSqlByInvocation(invocation);
        if (StringUtils.isBlank(sql)) {
            return invocation.proceed();
        }
        // 全局查询放弃拦截
        if (sqlKey.endsWith("Global")) {
            return invocation.proceed();
        }

        for (String tableName : invcationTables()) {
            if (sql.indexOf(tableName) != -1) {
                LinkappUser linkappUser = linkappUserContextProducer.getCurrent();
                if (linkappUser != null) {
          			log.info("before:" + sql);
                    for (String id : sqlId()) {
                        if (sqlKey.endsWith(id)) {
                            sql = sql.replaceAll(tableName, tableName + " modifyTable ");
                            break;
                        }
                    }
                    //sql = sql.replaceAll(tableName, "(select * from " + tableName + " where tenant_id = '" + linkappUser.getTenantId() + "') ");

                    //针对用户类型做sql的处理
                    if ("1".equals(linkappUser.getType())) {
                        sql = sql.replaceAll(tableName, "(select * from " + tableName + " where tenant_id = '" + linkappUser.getTenantId() + "') ");
                    } else {
                        //其他逻辑
                    }
          			log.info("after:" + sql);
                }
            }
        }
        // 包装sql后，重置到invocation中
        resetSql2Invocation(invocation, sql);
        // 返回，继续执行
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object obj) {
        return Plugin.wrap(obj, this);
    }

    @Override
    public void setProperties(Properties arg0) {
        // doSomething
    }

    private static List<String> invcationTables() {
        //拦截的查询表名
        List<String> list = new ArrayList<>();
        
        list.add("linkapp_device ");
        
        return list;
    }


    private static List<String> sqlId() {
    	//拦截的查询方法（也就是要补充参数过来的方法）
        List<String> list = new ArrayList<>();
        list.add("selectById");
        list.add("selectBatchIds");
        list.add("selectByMap");
        list.add("selectOne");
        list.add("selectList");
        list.add("selectMaps");
        list.add("selectObjs");
        list.add("selectPage");
        list.add("selectMapsPage");
        list.add("selectCount");
        return list;
    }

    /**
     * 获取sql语句
     */
    private String getSqlByInvocation(Invocation invocation) {
        final Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object parameterObject = args[1];
        BoundSql boundSql = ms.getBoundSql(parameterObject);
        return boundSql.getSql();
    }

    /**
     * 包装sql后，重置到invocation中
     */
    private void resetSql2Invocation(Invocation invocation, String sql) throws SQLException {
        final Object[] args = invocation.getArgs();
        MappedStatement statement = (MappedStatement) args[0];
        Object parameterObject = args[1];
        BoundSql boundSql = statement.getBoundSql(parameterObject);
        MappedStatement newStatement = newMappedStatement(statement, new BoundSqlSqlSource(boundSql));
        MetaObject msObject = MetaObject.forObject(newStatement, new DefaultObjectFactory(),
            new DefaultObjectWrapperFactory(), new DefaultReflectorFactory());
        msObject.setValue("sqlSource.boundSql.sql", sql);
        args[0] = newStatement;
    }

    private MappedStatement newMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(), ms.getId(), newSqlSource,
            ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null && ms.getKeyProperties().length != 0) {
            StringBuilder keyProperties = new StringBuilder();
            for (String keyProperty : ms.getKeyProperties()) {
                keyProperties.append(keyProperty).append(",");
            }
            keyProperties.delete(keyProperties.length() - 1, keyProperties.length());
            builder.keyProperty(keyProperties.toString());
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.resultSetType(ms.getResultSetType());
        builder.cache(ms.getCache());
        builder.flushCacheRequired(ms.isFlushCacheRequired());
        builder.useCache(ms.isUseCache());

        return builder.build();
    }

    // 定义一个内部辅助类，作用是包装sq
    class BoundSqlSqlSource implements SqlSource {

        private BoundSql boundSql;

        public BoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }

        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }
    }
}

没啥特别的，应该都能看懂，也有注释。

总结

• 怎么说能，没没这时候不得不夸赞下spring，这里这个拦截器的也是跟spring的生命周期有关。
• 关于数据存库、查询的加密解密也可以用这个思路实现。
• 这里其实也有一定的缺点，比如这里相当于增加了一层子查询，那么就会有性能的损失。
• 其次也会影响数据库优化器的优化，索引的命中。

这里除了分享，也是自己备忘。真的有点忙，就不多说，最近写博文都少了，55555