springboot引入druid解析sql

一、前言
在开发中，有时我们可能会需要获取SQL中的表名，那么因为不同的数据源类型SQL会存在部分差异，那么我们就可以使用alibaba 的druid包实现不同的数据源类型的sql解析。

二、引入相关maven依赖

<dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.8</version></dependency>

三、通过工具类SqlUtils实现对SQL的解析。

public class QualitySqlUtils {/** *根据sql及数据源类型获取表名 */ public static List<String> getSelectSqlTable(String sql, String dbType) {List<String> tableList = new ArrayList<>();List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);SchemaStatVisitor visitor;for (SQLStatement sqlStatement : stmtList) {if (DbType.mysql.name().equalsIgnoreCase(dbType)) {visitor = new MySqlSchemaStatVisitor();} else if (DbType.hive.name().equalsIgnoreCase(dbType)) {visitor = new HiveSchemaStatVisitor();} else if (DbType.postgresql.name().equalsIgnoreCase(dbType)) {visitor = new PGSchemaStatVisitor();} else if (DbType.oracle.name().equalsIgnoreCase(dbType)) {visitor = new OracleSchemaStatVisitor();} else {visitor = new SchemaStatVisitor(DbType.of(dbType));}sqlStatement.accept(visitor);Map<TableStat.Name, TableStat> tables = visitor.getTables();for (Map.Entry<TableStat.Name, TableStat> entry: tables.entrySet()){String value = entry.getValue().toString();if (StringUtils.isNotBlank(value)) {tableList.add(entry.getKey().getName());}}}return tableList;}/*** 根据sql获取查询的字段*/public static Map<String, String> getSelectSqlColumn(String sql, String dbType) {Map<String, String> columnMap = new HashMap<>();List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);for (int i = 0; i < stmtList.size(); i++) {SQLStatement stmt = stmtList.get(i);if (stmt instanceof SQLSelectStatement) {SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) stmt;SQLSelect select = sqlSelectStatement.getSelect();SQLSelectQueryBlock query = (SQLSelectQueryBlock) select.getQuery();List<SQLSelectItem> selectList = query.getSelectList();selectList.forEach(item->{columnMap.put(item.getExpr().toString(), item.getAlias());});}}return columnMap;}}

三、测试结果

public class testDemo{public static void main(String[] args) {String sql = "select t.* from table1 as t left join table2 t2 on t.id = t2.id";List<String> tableList =getSelectSqlTable(sql, "mysql");System.out.println("获取到的表名: "+tableList);}}

运行结果如下：

大家感兴趣可以尝试下。