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

星梦天河

已于 2023-12-18 12:13:14 修改

阅读量6.6k

点赞数 3

分类专栏： java mybatis 文章标签： sql 数据库

于 2021-07-12 16:37:18 首次发布

本文链接：https://blog.csdn.net/mxskymx/article/details/118678296

版权

java 同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

mybatis

3 篇文章 1 订阅

订阅专栏

该代码段展示了如何解析SQL查询语句并生成QueryModelInfo对象。它首先根据数据库类型解析SQL语句，然后获取表名、查询列和过滤条件，并将这些信息封装到QueryModelInfo对象中，用于后续的数据操作。

摘要由CSDN通过智能技术生成

/**
 * 解析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;
    }
}