druid 解析select查询sql获取表名，字段名，where条件

我不是女神ヾ 2022-10-20 03:50 885阅读 0赞

/**
     * 解析select sql生成QueryModelInfo
     * @param dbTypeName  mysql, oracle...
     * @param selectSql
     * @return
     */
    public static QueryModelInfo parse(String dbTypeName, String selectSql) {
        QueryModelInfo queryModelInfo = new QueryModelInfo();
        DbType dbType = DbType.valueOf(dbTypeName.toLowerCase());
        List<SQLStatement> statementList = SQLUtils.parseStatements(selectSql, dbType);
        //final String format = SQLUtils.format(selectSql, dbType, SQLUtils.DEFAULT_FORMAT_OPTION);
        //log.debug("selectSql format = "+format);
        for (SQLStatement statement : statementList) {
            if (statement instanceof SQLSelectStatement) {
                SchemaStatVisitor visitor = new SchemaStatVisitor(dbType);
                statement.accept(visitor);
    
                //解析表名
                SQLSelectStatement selectStatement = (SQLSelectStatement) statement;
                SQLSelectQueryBlock queryBlock =  selectStatement.getSelect().getFirstQueryBlock();
                SQLTableSource from = queryBlock.getFrom();
                SQLExprTableSource sqlExprTableSource = (SQLExprTableSource) from;
                String tableName = sqlExprTableSource.getTableName();
                queryModelInfo.setTableName(tableName);
    
                //查询列
                Collection<TableStat.Column> columns = visitor.getColumns();
                List<String> columnList = new ArrayList<>();
                columns.stream().forEach(row -> {
                    if(row.isSelect()){
                        //保存select字段
                        columnList.add(row.getName());
                    }
                });
                queryModelInfo.setColumnList(columnList);
    
                //查询过滤条件
                List<TableStat.Condition> conditions = visitor.getConditions();
                Map<String, SysQueryMethodEnum> whereMap = new HashMap<>();
                conditions.stream().forEach(row -> {
                    String columnName = row.getColumn().getName();
                    String operator = row.getOperator();
                    if("=".equalsIgnoreCase(operator)){
                        whereMap.put(columnName, SysQueryMethodEnum.eq);
                    }else if("LIKE".equalsIgnoreCase(operator)){
                        whereMap.put(columnName, SysQueryMethodEnum.like);
                    }else{
                        log.warn("un support operator "+operator+"; row = "+row);
                    }
                });
                queryModelInfo.setWhereMap(whereMap);
                break;
            }
        }
        return queryModelInfo;
    }

**//解析返回对象**

public class QueryModelInfo {
    
        private String tableName;
        private List<String> columnList;
        private Map<String, SysQueryMethodEnum> whereMap;
    
        public String getTableName() {
            return tableName;
        }
    
        public void setTableName(String tableName) {
            this.tableName = tableName;
        }
    
        public List<String> getColumnList() {
            return columnList;
        }
    
        public void setColumnList(List<String> columnList) {
            this.columnList = columnList;
        }
    
        public Map<String, SysQueryMethodEnum> getWhereMap() {
            return whereMap;
        }
    
        public void setWhereMap(Map<String, SysQueryMethodEnum> whereMap) {
            this.whereMap = whereMap;
        }
    }