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

谁践踏了优雅 2022-08-31 15:17 470阅读 0赞

# 测试代码 #

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

# 运行结果 #

![在这里插入图片描述][2e3922585d384a74b657e0bcff45652a.png]

[2e3922585d384a74b657e0bcff45652a.png]: /images/20220829/1dde4dc110c646f9bfafdaea68283a2b.png