使用druid解析sql，如表名，字段等

最新推荐文章于 2024-08-31 22:09:01 发布

he3more

最新推荐文章于 2024-08-31 22:09:01 发布

阅读量4.7k

点赞数 2

分类专栏： Java

本文链接：https://blog.csdn.net/he3more/article/details/119106838

版权

Java 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

测试代码

 /**
     * druid sql解析
     */
    public void getTableNamesFromSql(){
        String sql = "select gender,email,phone,address from table1 where id = 3 and name = 'dave'";
        //String sql = "selec gender,email,phone,address from table1";
        //选择数据源类型，这里以mySql为例
        //String dbType = JdbcConstants.ODPS;
        String dbType = JdbcConstants.MYSQL;
        try {
            List<String> tableNameList = new ArrayList<>();
            //格式化输出
            String sqlResult = SQLUtils.format(sql, dbType);
            log.info("格式化后的sql:[{}]",sqlResult);

            List<SQLStatement> stmtList = null;
            try {
                stmtList = SQLUtils.parseStatements(sql, dbType);
            } catch (ParserException e) {
                log.error("sql语法有误，请检查sql:{}",e.getMessage());
               return;
               // e.printStackTrace();
            }
            if (CollectionUtils.isEmpty(stmtList)) {
                log.info("stmtList为空无需获取");
            }
            //解析sql，获取表名
            for (SQLStatement sqlStatement : stmtList) {
                //mysql为例，若为其他数据源请换成对应的SchemaStatVisitor
                MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
               // OdpsSchemaStatVisitor visitor = new OdpsSchemaStatVisitor();
                sqlStatement.accept(visitor);
                //获取表名
                Map<TableStat.Name, TableStat> tables = visitor.getTables();
                log.info("druid解析sql的结果集:[{}]",tables);
                Set<TableStat.Name> tableNameSet = tables.keySet();
                for (TableStat.Name name : tableNameSet) {
                    String tableName = name.getName();
                    if (StringUtils.isNotBlank(tableName)) {
                        tableNameList.add(tableName);
                    }
                }
                //获取where条件
                List<TableStat.Condition> conditions = visitor.getConditions();
                log.info("解析sql后的查询条件：{}",conditions);
                //获取查询字段
                Collection<TableStat.Column> columns = visitor.getColumns();
                log.info("解析sql后的字段：{}",columns);

            }

            //解析sql中表名
            log.info("解析sql后的表名:[{}]",tableNameList);
        } catch (Exception e) {
            log.error("**************异常SQL:[{}]*****************\\n",sql);
            log.error(e.getMessage(),e);
        }
    }

运行结果

在这里插入图片描述