代码如下：druid版本 1.2.5 数据库sqlserver 分别为group by和order by的解析 String sql1 ="select id, data_base_name, type_name, table_name, operation_name, remark from center..ad_sql_parser where type_name = 1 group by data_base_name"; String sql2 ="select id, data_base_name, type_name, table_name, operation_name, remark from center..ad_sql_parser where type_name = 1 order by data_base_name"; Map<TableStat.Name, TableStat> tableStatMap = getTableNameBySql(sql2); for (Map.Entry<TableStat.Name, TableStat> tableStatEntry : tableStatMap.entrySet()) { //1.获取到表名 System.out.println("表名：" + tableStatEntry.getKey().getName()); //2.获取此表的操作名 System.out.println("操作名：" + tableStatEntry.getValue()); } }
public static Map<TableStat.Name, TableStat> getTableNameBySql(String sql) { // 数据源类型 DbType dbType = JdbcConstants.SQL_SERVER; Map<TableStat.Name, TableStat> tables = new HashMap<TableStat.Name, TableStat>(); try { //格式化 因为sql的关键字，大小写并不敏感，通过这个方法就可以把关键字统一变成大写 String sqlResult = SQLUtils.format(sql, dbType); logger.debug("格式化后的sql:[{}]", sqlResult); //解析生产语法树 List stmtList = SQLUtils.parseStatements(sqlResult, dbType); //判断 解析出来的语法树是否为空 if (CollectionUtils.isEmpty(stmtList)) { logger.info("语法树为空"); return Collections.emptyMap(); } //访问 解析语法树 for (SQLStatement sqlStatement : stmtList) { SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor(); sqlStatement.accept(visitor); //获取到 表名和对应的操作命令 tables = visitor.getTables(); logger.info("druid解析sql的结果集:{}", tables); } return tables; } catch (Exception e) { logger.error(e.getMessage(), e); return Collections.emptyMap(); } }
日志分别打印结果： 格式化后的sql:[SELECT id, data_base_name, type_name, table_name, operation_name, remark FROM center..ad_sql_parser WHERE type_name = 1 GROUP BY data_base_name] druid解析sql的结果集:{center..ad_sql_parser=Select} 表名：center..ad_sql_parser 操作名：Select
格式化后的sql:[SELECT id, data_base_name, type_name, table_name, operation_name, remark FROM center..ad_sql_parser WHERE type_name = 1 ORDER BY data_base_name] ERROR com.rails.app.utils.sqlParserUtils - illegal sql expr : center..ad_sql_parser, pos 8, line 1, column 7, token ..