利用druid数据源的SQL解析器构建数据权限模块

最新推荐文章于 2024-11-02 17:00:00 发布

转载最新推荐文章于 2024-11-02 17:00:00 发布 · 962 阅读

2 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/weiguangyue/p/9965662.html

文章标签：

#数据库

本文介绍了一种在Oracle SQL查询中动态添加权限条件的方法，通过定义特定的SQL表达式构造器，可以针对指定的表和字段添加IN或NOT IN类型的过滤条件。此方法避免了硬编码SQL语句，提高了SQL查询的灵活性和安全性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

关键的几个类:

项目依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.0</version>
        </dependency>

public class WhereInSqlExprBuilder extends AbstractConditionSqlExprBuilder{
    

    private String columnName;

    private String[] arrayInValue;
    
    private boolean not;

    public WhereInSqlExprBuilder(String tableName,String columnName, String[] arrayInValue,boolean not) {
        super(tableName);
        this.columnName = columnName;
        this.arrayInValue = arrayInValue;
        this.not = not;
    }

    @Override
    public SQLExpr build(String tableNameAlias) {
        SQLExpr targetExpr = new SQLIdentifierExpr(tableNameAlias + "." + this.columnName);
        SQLInListExpr condition = new SQLInListExpr();
        condition.setExpr(targetExpr);
        List<SQLExpr> sqlExprList = new ArrayList<SQLExpr>();
        for (String v : arrayInValue) {
            SQLExpr se = new SQLCharExpr(v);
            sqlExprList.add(se);
        }
        condition.setTargetList(sqlExprList);
        condition.setNot(not);
        return condition;
    }
}

public class SqlVisitor extends OracleSchemaStatVisitor {

    private static final Logger log = LoggerFactory.getLogger(SqlVisitor.class);
    
    private ConditionSqlExprBuilder conditionSqlExprBuilder;

    private Set<ProcessedPairKey> processed;// 已经处理过的集合，避免无限递归不退出
    
    private Set<SQLExpr> processedTagSQLExpr;//追加tag的表达式

    public SqlVisitor(ConditionSqlExprBuilder conditionSqlExprBuilder, Set<SQLExpr> processedTagSQLExpr) {
        this.processed = new HashSet<ProcessedPairKey>();
        this.conditionSqlExprBuilder = conditionSqlExprBuilder;
        this.processedTagSQLExpr = processedTagSQLExpr;
    }

    @Override
    public void endVisit(OracleSelectTableReference x) {

        SQLExpr sqlExpr = x.getExpr();
        if (sqlExpr instanceof SQLIdentifierExpr) {

            SQLIdentifierExpr sqlIdentifierExpr = (SQLIdentifierExpr) sqlExpr;
            String tableName = sqlIdentifierExpr.getName();
            // 若已经处理过了，直接跳过
            SQLObject sqlObject = x.getParent();
            ProcessedPairKey pairKey = new ProcessedPairKey(sqlObject, sqlIdentifierExpr);
            if (processed.contains(pairKey)) {
                return;
            }
            // 如果发现匹配的表，就处理
            boolean accept = conditionSqlExprBuilder.accept(tableName);

            if (accept) {
                if (log.isTraceEnabled()) {
                    log.trace("found target table[" + tableName + "]");
                }
                String tableNameAlias = x.getAlias();
                if (tableNameAlias == null) {
                    tableNameAlias = tableName;
                } else {
                    if (log.isTraceEnabled()) {
                        log.trace("use table alias[" + tableNameAlias + "]");
                    }
                }
                if (sqlObject instanceof SQLSelectQueryBlock) {// 父节点是查询块

                    SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock) sqlObject;
                    this.appendWhereCondition(sqlSelectQueryBlock, tableNameAlias);

                } else if (sqlObject instanceof OracleSelectJoin) {// 父节点是连接查询

                    OracleSelectJoin oracleSelectJoin = (OracleSelectJoin) sqlObject;
                    this.processOracleSelectJoin(oracleSelectJoin, tableNameAlias);

                } else {
                    log.warn("未处理语句情况，请联系基础平台部开发人员协助处理!!!");
                }
                // 已经处理过的集合
                this.processed.add(pairKey);
            }
        }
    }
    
    private void processOracleSelectJoin(OracleSelectJoin oracleSelectJoin, String tableNameAlias) {

        SQLObject par = oracleSelectJoin.getParent();
        if (par instanceof SQLSelectQueryBlock) {

            SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock) par;
            this.appendWhereCondition(sqlSelectQueryBlock, tableNameAlias);

        } else if (par instanceof OracleSelectJoin) {

            OracleSelectJoin join = (OracleSelectJoin) par;
            this.processOracleSelectJoin(join, tableNameAlias);
        }
    }

    private void appendWhereCondition(SQLSelectQueryBlock sqlSelectQueryBlock, String tableNameAlias) {
        SQLExpr sqlExpr = conditionSqlExprBuilder.build(tableNameAlias);
        SQLExpr oldWhereSqlExpr =  sqlSelectQueryBlock.getWhere();
        if(oldWhereSqlExpr != null) {
            SQLExpr newWhereSqlExpr = new SQLBinaryOpExpr(oldWhereSqlExpr, SQLBinaryOperator.BooleanAnd,sqlExpr);            
            sqlSelectQueryBlock.setWhere(newWhereSqlExpr);
        }else {
            sqlSelectQueryBlock.setWhere(sqlExpr);
        }
        this.processedTagSQLExpr.add(sqlExpr);
    }
    
    private static class ProcessedPairKey{
        
        private SQLObject parent;
        private SQLIdentifierExpr sqlIdentifierExpr;
        public ProcessedPairKey(SQLObject parent, SQLIdentifierExpr sqlIdentifierExpr) {
            super();
            this.parent = parent;
            this.sqlIdentifierExpr = sqlIdentifierExpr;
        }
        @Override
        public int hashCode() {
            final int prime = 31;
            int result = 1;
            result = prime * result + ((parent == null) ? 0 : parent.hashCode());
            result = prime * result + ((sqlIdentifierExpr == null) ? 0 : sqlIdentifierExpr.hashCode());
            return result;
        }
        @Override
        public boolean equals(Object obj) {
            if (this == obj)
                return true;
            if (obj == null)
                return false;
            if (getClass() != obj.getClass())
                return false;
            ProcessedPairKey other = (ProcessedPairKey) obj;
            if (parent == null) {
                if (other.parent != null)
                    return false;
            } else if (!parent.equals(other.parent))
                return false;
            if (sqlIdentifierExpr == null) {
                if (other.sqlIdentifierExpr != null)
                    return false;
            } else if (!sqlIdentifierExpr.equals(other.sqlIdentifierExpr))
                return false;
            return true;
        }
        @Override
        public String toString() {
            return "PairKey [parent=" + parent + ", sqlIdentifierExpr=" + sqlIdentifierExpr + "]";
        }
    }
}

public class OracleOutputWithTagVisitor extends OracleOutputVisitor {

    private static final String TAG_START = "  /*权限条件开始*/  ";
    private static final String TAG_END = "  /*权限条件结束*/  ";

    private boolean showTag;

    private Set<SQLExpr> processedTagSQLExpr;// 追加tag的表达式

    public OracleOutputWithTagVisitor(Appendable appender, boolean showTag, Set<SQLExpr> processedTagSQLExpr) {
        super(appender);
        this.showTag = showTag;
        this.processedTagSQLExpr = processedTagSQLExpr;
    }

    @Override
    public boolean visit(SQLInListExpr x) {
        boolean printTag = processedTagSQLExpr != null && this.processedTagSQLExpr.contains(x);
        if(showTag && printTag) {
            this.print0(TAG_START);
        }
        boolean ret = super.visit(x);
        if(showTag && printTag) {
            this.print0(TAG_END);
        }
        return ret;
    }

}

public class DataRangeHitSqlBuilder{
    
    private List<ConditionSqlExprBuilder> conditionSqlExprBuilders;
    
    private Set<SQLExpr> processedTagSQLExpr;// 已经标记tag的表达式，避免重复标记
    
    private boolean showTag;
    
    private String sql;
    
    public DataRangeHitSqlBuilder(String sql,List<ConditionSqlExprBuilder> conditionSqlExprBuilders,boolean showTag) {
        this.sql = sql;
        this.conditionSqlExprBuilders = conditionSqlExprBuilders;
        this.processedTagSQLExpr = new HashSet<SQLExpr>();
        this.showTag = showTag;
    }

    public String buildSql() {

        List<SQLStatement> stmtList = SQLUtils.parseStatements(this.sql, JdbcUtils.ORACLE);
        
        for(ConditionSqlExprBuilder cseb:conditionSqlExprBuilders) {
            
            for (SQLStatement stmt: stmtList) {
                
                if (stmt instanceof SQLSelectStatement) {
                    
                    SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) stmt;
                    
                    // 追加where条件
                    SqlVisitor vister = new SqlVisitor(cseb,processedTagSQLExpr);
                    sqlSelectStatement.accept(vister);
                }
            }
            // 追加结果到buffer中
            StringBuffer resultSqlBuffer = new StringBuffer();
            for (SQLStatement stmt: stmtList) {
                OracleOutputVisitor oracleOutputVisitor = new OracleOutputWithTagVisitor(resultSqlBuffer,showTag,processedTagSQLExpr);
                stmt.accept(oracleOutputVisitor);
            }
            this.sql = resultSqlBuffer.toString();
        }
        
        return this.sql;
    }
}

public interface ConditionSqlExprBuilder {
    
    /**
     * @description    ： 是否处理这个表
     * @param tableName 表名称
     * @return
     */
    public boolean accept(String tableName);

    /**
     * 
     * @description    ： 构建sql表达式
     * @param tableNameAlias 表别名
     * @return
     */
    public SQLExpr build(String tableNameAlias);
}

public abstract class AbstractConditionSqlExprBuilder implements ConditionSqlExprBuilder{
    
    private String tableName;
    
    public AbstractConditionSqlExprBuilder(String tableName) {
        this.tableName = tableName;
    }

    public boolean accept(String tableName) {
        return tableName.equals(this.tableName);
    }

}

下面是测试代码:

public class QuestionDemo {

    public static void main(String[] args) {

        String sql = "select * from t_user";
        
        ConditionSqlExprBuilder conditionSqlExprBuilder = new WhereInSqlExprBuilder("t_user", "id", new String[] {"1","2"}, true);
        List<ConditionSqlExprBuilder> conditionSqlExprBuilders = Arrays.asList(conditionSqlExprBuilder);
        
        DataRangeHitSqlBuilder builder = new DataRangeHitSqlBuilder(sql, conditionSqlExprBuilders,false);
        
        String newSql = builder.buildSql();
        System.out.println(newSql);
    }
}