编写一个可以自动添加过滤条件的mybatis插件（类似 分页插件PageHelper） 这样在某些业务下就不用在xml sql语句中添加过滤条件了 也不用在实体类中添加与我们业务无关的字段了

 原理：

原理类似mybatis分页插件   在使用时添加过滤条件即可  整个难点是改写原本sql 因为可能这个sql很复杂  我写的改写算法可能有bug

 

步骤

1 编写存放我们过滤条件的类  

package com.yinhai.domain.common.plug;

import com.github.pagehelper.util.MetaObjectUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;

import java.util.*;


/**
 * @author
 * @discription;
 * @time 2020/10/9 10:13
 */
@Slf4j
public class Condition {
    private Map<String,Object> filterData=new HashMap<>();
    private UnionTableModel unionTable=null;
    private SqlResove sqlResove=new DefaultSqlResove();

    public Object processParameterObject(MappedStatement ms, Object parameterObject, BoundSql boundSql, CacheKey pageKey) {
            Map<String, Object> paramMap = null;
            if (parameterObject == null) {
                paramMap = new HashMap();
            } else if (parameterObject instanceof Map) {
                paramMap = new HashMap();
                paramMap.putAll((Map)parameterObject);
            } else {
                paramMap = new HashMap();
                boolean hasTypeHandler = ms.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass());
                MetaObject metaObject = MetaObjectUtil.forObject(parameterObject);
                if (!hasTypeHandler) {
                    String[] var9 = metaObject.getGetterNames();
                    int var10 = var9.length;

                    for(int var11 = 0; var11 < var10; ++var11) {
                        String name = var9[var11];
                        paramMap.put(name, metaObject.getValue(name));
                    }
                }

                if (boundSql.getParameterMappings() != null && boundSql.getParameterMappings().size() > 0) {
                    Iterator var13 = boundSql.getParameterMappings().iterator();

                    ParameterMapping parameterMapping;
                    String name;
                    do {
                        do {
                            do {
                                do {
                                    if (!var13.hasNext()) {
                                        return   getParam(ms,boundSql,paramMap);
                                    }

                                    parameterMapping = (ParameterMapping)var13.next();
                                    name = parameterMapping.getProperty();
                                } while(false);
                            } while(false);
                        } while(paramMap.get(name) != null);
                    } while(!hasTypeHandler && !parameterMapping.getJavaType().equals(parameterObject.getClass()));

                    paramMap.put(name, parameterObject);
                }
            }

          return   getParam(ms,boundSql,paramMap);

    }

    public Object getParam(MappedStatement ms,BoundSql boundSql,Map paramMap){


        paramMap.putAll(filterData);

        List<ParameterMapping> newParameterMappings = new ArrayList(boundSql.getParameterMappings());
        if (boundSql.getParameterMappings() != null) {
            filterData.forEach((k, v) -> {
                String newK=k;
                if(!k.contains("_")){
                    //驼峰转换
                    StringBuilder stringBuilder=new StringBuilder();
                    char[] chars = k.toCharArray();
                    for (int i = 0; i < chars.length; i++) {
                        char c=chars[i];
                        if(c>=65&&c<=89){
                            stringBuilder.append("_");
                            stringBuilder.append(c+32);
                        }else {
                            stringBuilder.append(c);
                        }
                    }
                    newK=stringBuilder.toString();
                }
                newParameterMappings.add((new ParameterMapping.Builder(ms.getConfiguration(), newK, v!=null?v.getClass():null)).build());

            });
        }

        MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
        metaObject.setValue("parameterMappings", newParameterMappings);

        return paramMap;


    }


    public void addQueryFilter(String column,Object data){
        filterData.put(column,data);
    }
    public void addUnionTable(String unionExpression,String unionTableName,String unionColumn){
        log.error("对于自动联表操作  暂时未支持 请在xml语句中自行添加");
        unionTable=new UnionTableModel();
        unionTable.setColumn(unionColumn);
        unionTable.setTableName(unionTableName);
        unionTable.setUnionExpression(unionExpression);
    }
    public boolean isNeedUnionTable(){
        return  unionTable!=null;
    }
    public Map<String, Object> getFilterData(){
        return filterData;
    }

    public UnionTableModel getUnionTable() {
        return unionTable;
    }

    public SqlResove getSqlResove() {
        return sqlResove;
    }

    public void setSqlResove(SqlResove sqlResove) {
        this.sqlResove = sqlResove;
    }

    public String filterSql(BoundSql boundSql, Object parameter) {

     return       sqlResove.filterSql(this,boundSql,parameter);
    }

    public static class UnionTableModel{

        private String unionExpression;
        private String tableName;
        private String column;

        public String getUnionExpression() {
            return unionExpression;
        }

        public void setUnionExpression(String unionExpression) {
            this.unionExpression = unionExpression;
        }

        public String getTableName() {
            return tableName;
        }

        public void setTableName(String tableName) {
            this.tableName = tableName;
        }

        public String getColumn() {
            return column;
        }

        public void setColumn(String column) {
            this.column = column;
        }
    }
}

2  编写 DbFilterHelper  功能类似PageHelper 使用时以这个类为入口

public class DbFilterHelper {

    private static  ThreadLocal<Condition> CONDITON=new ThreadLocal<>();

    public static void startFilter(Condition con){

        CONDITON.set(con);

    }

    public static  void clearCondition(){

        CONDITON.remove();

    }

    public static Condition getCondition(){
        return CONDITON.get();
    }

}

3  编写mybatis 拦截器 拦截我们的sql查询语句 看看是否需要添加过滤条件

@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}
)})
@Slf4j
public class DbFilterInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
           Condition condition=null;
        if((condition= DbFilterHelper.getCondition())!=null){

            try {
                Object[] args = invocation.getArgs();
                MappedStatement ms = (MappedStatement) args[0];
                Object parameter = args[1];
                RowBounds rowBounds = (RowBounds) args[2];
                ResultHandler resultHandler = (ResultHandler) args[3];
                Executor executor = (Executor) invocation.getTarget();
                CacheKey cacheKey;
                BoundSql boundSql;
                if (args.length == 4) {
                    boundSql = ms.getBoundSql(parameter);
                    cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
                } else {
                    cacheKey = (CacheKey) args[4];
                    boundSql = (BoundSql) args[5];
                }
                List<Object> objects = DbFilterExecutorUtil.filterQuery(condition, executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);

                return objects;
            }finally {
                DbFilterHelper.clearCondition();
            }
        }else {
            return invocation.proceed();
        }




    }

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

    @Override
    public void setProperties(Properties properties) {
      log.info("sql 过滤器插件启动成功");
    }
}

4 编写 DbFilterExecutorUtil  这是一个工具类  它可以帮我们新增一个BoundSql  我们就用这个工具类获得改写的sql 并生成新的BoundSql 传给mybatis执行器  获得执行结果

public class DbFilterExecutorUtil {
    private static Field additionalParametersField;
    public static MappedStatement getExistedMappedStatement(Configuration configuration, String msId) {
        MappedStatement mappedStatement = null;

        try {
            mappedStatement = configuration.getMappedStatement(msId, false);
        } catch (Throwable var4) {
        }

        return mappedStatement;
    }

    public static <E> List<E> filterQuery(Condition condition,Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql, CacheKey cacheKey) throws SQLException {

            parameter=condition.processParameterObject(ms, parameter, boundSql, cacheKey);
            String filterSqlStr=condition.filterSql(boundSql,parameter);
            BoundSql filterSql = new BoundSql(ms.getConfiguration(), filterSqlStr, boundSql.getParameterMappings(), parameter);
            Map<String, Object> additionalParameters = getAdditionalParameter(boundSql);
            Iterator var12 = additionalParameters.keySet().iterator();

            while(var12.hasNext()) {
                String key = (String)var12.next();
                filterSql.setAdditionalParameter(key, additionalParameters.get(key));
            }

            return executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, filterSql);

    }

    public static Map<String, Object> getAdditionalParameter(BoundSql boundSql) {
        try {
            return (Map)additionalParametersField.get(boundSql);
        } catch (IllegalAccessException var2) {
            throw new PageException("获取 BoundSql 属性值 additionalParameters 失败: " + var2, var2);
        }
    }

    static {
        try {
            additionalParametersField = BoundSql.class.getDeclaredField("additionalParameters");
            additionalParametersField.setAccessible(true);
        } catch (NoSuchFieldException var1) {
            throw new PageException("获取 BoundSql 属性 additionalParameters 失败: " + var1, var1);
        }
    }
}

5  根据原始sql 和新增的过滤条件 改写sql  这里我们用下策略模式  因为可能 换个数据库就不一样了 我这里只实现了mysql的

 

接口类

public interface SqlResove {

    String filterSql(Condition condition, BoundSql boundSql, Object parameter);
}

实现类

@Slf4j
public class DefaultSqlResove implements SqlResove {

    @Override
    public String filterSql(Condition condition, BoundSql boundSql, Object parameter) {
        String sourceSql = boundSql.getSql();
        StringBuilder stringBuilder=new StringBuilder(sourceSql);
        if(condition.isNeedUnionTable()){

            log.error("对于联表操作  暂时未支持 请在xml语句中自行添加");

        }else {//不需要连表操作  直接加条件即可


            String convert = SqlUtil.convert(sourceSql, condition.getFilterData());

            return  convert;
        }



        return sourceSql;
    }





}

6  sql工具类  根据原始类 和条件map 改写原始sql 添加过滤器  比如原先为 where a=？ 改写后 为 where a=? and b=? (map里面为（b,value）)

public class SqlUtil {
    public static String convert(String s, Map<String,Object> map){

        String s1 = s.replaceAll("\\s{2,}", " ");
        Set<String> strings = map.keySet();
        StringBuilder stringBuilder=new StringBuilder(s1);

        int order_by = containStr(stringBuilder, "order by");
        int group_by = containStr(stringBuilder, "group by");
        int limit = containStr(stringBuilder, "limit");
        boolean o = order_by != -1;
        boolean g = group_by != -1;
        boolean l = limit != -1;
        int start = g ? group_by : o ? order_by : l ? limit : -1;
        boolean first = true;
        boolean isAppend=start==-1;
        //包含where
        if(containStr(stringBuilder,"where")!=-1){

            String addStr="";
            int len=0;
            for (String k : strings) {
                addStr=format("and " + k + "=?");
                if(isAppend){
                    stringBuilder.append(format(addStr));
                }else {
                    stringBuilder.insert(start, addStr);
                }

            }

            len=addStr.length();

        }else {//不含where
            String addStr="";
            int len=0;
            for (String k : strings) {

                if (first) {
                    addStr=format("where " + k + "=?");
                    if(isAppend){
                        stringBuilder.append(format(addStr));
                    }else {
                        stringBuilder.insert(start-len, addStr);
                    }
                } else {
                    addStr=format("and " + k + "=?");
                    if(isAppend){
                        stringBuilder.append(addStr);
                    }else {
                        stringBuilder.insert(start+len, addStr);
                    }

                }
                if (first) {
                    first = false;
                }
                len=addStr.length();
            }

        }
        return stringBuilder.toString();

    }
    private static   int containStr(StringBuilder s,String ch){
        int i = s.indexOf(ch);
        if(i==-1){
            i=s.indexOf(ch.toUpperCase());
        }
        return i;
    }

    public static String format(String s){
        return " "+s+" ";
    }
}

7 添加进mybatis sqlSessionFactory (视情况而定  我这里是springboot环境)

Configuration
public class DbFilterAutoConfig {

    @Autowired
    private List<SqlSessionFactory> sqlSessionFactoryList;

    @PostConstruct
    public void addPageInterceptor() {
        DbFilterInterceptor interceptor = new DbFilterInterceptor();
        Iterator var3 = this.sqlSessionFactoryList.iterator();

        while(var3.hasNext()) {
            SqlSessionFactory sqlSessionFactory = (SqlSessionFactory)var3.next();
            sqlSessionFactory.getConfiguration().addInterceptor(interceptor);
        }

    }

}

 

使用过程：

在我们的业务类添加 红色代码段：

@Override
public Optional<Page<XtTbEduPlanPo>> queryCoursePlanAllByCondition(CoursePlanDTO coursePlanDTO) {
    
    Condition condition=new Condition();
    condition.addQueryFilter("way","1");
    DbFilterHelper.startFilter(condition);
    
    Page<XtTbEduPlanPo> xtTbEduPlanPoPage = cousePlanReadService.queryCondition(courseConverterUtil.byCoursePlanDTO(coursePlanDTO).orElse(null));
    return Optional.ofNullable(xtTbEduPlanPoPage);

}