为所有的查询sql统一添加查询条件 AnyLine

为所有的查询sql统一添加查询条件

最后更新:2023-09-29 10:01:28 | 状态:未完成

注意，这种方式并不适合用于多租户场景。

anyline
Mybatis
全部展开

【anyline】

可以通过QueryInterceptor拦截器

import org.anyline.data.interceptor.QueryInterceptor;
import org.anyline.data.jdbc.ds.JDBCRuntime;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.prepare.RunPrepare;
import org.anyline.data.prepare.auto.TablePrepare;
import org.anyline.metadata.ACTION;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;

@Component("interceptor.type")
public class TypeInterceptor implements QueryInterceptor {
    @Override
    public ACTION.SWITCH prepare(JDBCRuntime runtime, RunPrepare prepare, ConfigStore configs, String... conditions) {
        if(prepare instanceof TablePrepare){//如果是自动生成的SQL比较好处理
            configs.and("TYPE_CODE", type);
        }else{//如果是JAVA中写的SQL会可能比较复杂需要统一的约定
              //如果要精细控制SQL可以用jsqlparser解析一下SQL，不过对性能有影响，有条件的话还是统一约定比较好
            configs.and("m","TYPE_CODE", type);
        }
        return ACTION.SWITCH.CONTINUE;
    }
}

注意如果需要HttpServletRequest不要注入,可以通过 RequestContextHolder获取
HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();

【Mybatis】

Mybatis也提供了相关的拦截器

import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.springframework.stereotype.Component;
import net.sf.jsqlparser.statement.Statement;

import java.sql.Connection;

/**
 * @author
 * @description 当表无该字段查询条件时增加默认查询条件
 */
@Intercepts({@Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class, Integer.class})})
@Slf4j
@Component
public class MybatisDataFilterInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        RoutingStatementHandler handler = (RoutingStatementHandler) invocation.getTarget();
        //获取StatementHandler构造器
        StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(handler, "delegate");
        // 通过反射获取delegate父类BaseStatementHandler的mappedStatement属性
        MappedStatement mappedStatement = (MappedStatement) ReflectUtil.getFieldValue(delegate, "mappedStatement");
        SqlCommandType commandType = mappedStatement.getSqlCommandType();
        if (SqlCommandType.SELECT.equals(commandType)) {
            String sqlId = mappedStatement.getId();
            BoundSql boundSql = delegate.getBoundSql();
            String sql = boundSql.getSql();
            Statement statement = CCJSqlParserUtil.parse(sql);
            Select select = (Select) statement;
            PlainSelect selectBody = (PlainSelect) select.getSelectBody();
            addWhere(selectBody);
            ReflectUtil.setFieldValue(boundSql, "sql", statement.toString());

        }
        return invocation.proceed();

    }

    //增加条件
    private void addWhere(PlainSelect selectBody){
        try{
            Table fromItem = (Table) selectBody.getFromItem();
           	String name = fromItem.getName();
            if (name.indexOf("表名") != -1) {
                String stringExpression = "";
                try{
                    EqualsTo where = (EqualsTo) selectBody.getWhere();
                    stringExpression = where.getStringExpression();
                }catch (Exception e){
                    stringExpression =  selectBody.getWhere().toString();
                }
                //如果字段搜索条件为空则搜索字段为空或指定数据
                StringBuilder sqlFilter = new StringBuilder(128);
                if (stringExpression.indexOf("字段名") == -1) {
                    sqlFilter.append("(表名.字段名!='1' or 表名.字段名 is null) ");
                    buildWhereClause(selectBody, sqlFilter.toString());
                }
            }
        }catch (Exception e){
        //多表查询时由于不是最后一层，获取不到Table，继续获取子表
            SubSelect ss = (SubSelect)selectBody.getFromItem();
            PlainSelect subSelect = (PlainSelect) ss.getSelectBody();
            addWhere(subSelect);
        }
    }



    private void buildWhereClause(PlainSelect select, String dataFilter) throws JSQLParserException {
        if (select.getWhere() == null) {
            select.setWhere(CCJSqlParserUtil.parseCondExpression(dataFilter));
        } else {
            AndExpression and = new AndExpression(
                    CCJSqlParserUtil.parseCondExpression(dataFilter), select.getWhere());
            select.setWhere(and);
        }
    }
}