1.使用jsqlparser解析
- 引入jsqlparser包:
<!--jsqlparser-->
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.6</version>
</dependency>
- 使用工具类解析
/**
* SQL语句解析
*
* @param sql 原SQL
* @return select:[table1,table2]
*/
public static Map<String, Object> sqlParser(String sql) {
Map<String, Object> result = new HashMap<>();
if (StringUtils.isEmpty(sql)) {
return result;
}
try {
String operation = "";
Set<String> tableList = new HashSet<>();
Statement statement = CCJSqlParserUtil.parse(new StringReader(sql));
if (statement instanceof Select) {
operation = "SELECT";
Select select = (Select) statement;
} else if (statement instanceof Delete) {
operation = "DELETE";
Delete delete = (Delete) statement;
Table table = delete.getTable();
String tableName = table.getName();
tableList.add(tableName);
} else if (statement instanceof Update) {
operation = "UPDATE";
Update update = (Update) statement;
Table table = update.getTable();
String tableName = table.getName();
tableList.add(tableName);
} else if (statement instanceof Insert) {
operation = "INSERT";
Insert insert = (Insert) statement;
Table table = insert.getTable();
String tableName = table.getName();
tableList.add(tableName);
} else if (statement instanceof Merge) {
operation = "Merge";
Merge merge = (Merge) statement;
Table table = merge.getTable();
String tableName = table.getName();
tableList.add(tableName);
}
result.put("operation", operation);
result.put("tableList", tableList);
return result;
} catch (JSQLParserException e) {
log.error("SQL解析异常,语句为{},原因:", sql, e);
return result;
}
}
/**
* 对select语句解析
*
* @param result 表名集合
* @param selectBody select体
*/
public static void getSelectTables(Set<String> result, SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
PlainSelect plainSelect = (PlainSelect) selectBody;
FromItem fromItem = plainSelect.getFromItem();
// 单表
if (fromItem instanceof Table) {
Table table = (Table) fromItem;
String tableName = table.getName();
result.add(tableName);
}
// 子查询
else if (fromItem instanceof SubSelect) {
SubSelect subSelect = (SubSelect) fromItem;
getSelectTables(result, subSelect.getSelectBody());
}
// 多表连接查询
List<Join> joinList = plainSelect.getJoins();
if (joinList != null) {
for (Join joinJoin : joinList) {
FromItem rightFromItem = joinJoin.getRightItem();
if (!(rightFromItem instanceof Table)) {
continue;
}
Table table = (Table) rightFromItem;
String tableName = table.getName();
result.add(tableName);
}
}
}
// union连接
else if (selectBody instanceof SetOperationList) {
SetOperationList setOperationList = (SetOperationList) selectBody;
List<SelectBody> selectBodyList = setOperationList.getSelects();
selectBodyList.forEach(e -> getSelectTables(result, e));
}
}
2.使用Druid解析
- 引入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
- 工具方法解析
public static Map<String, Object> parser(String sql) {
Map<String, Object> result = new HashMap<>();
MySqlStatementParser parser = new MySqlStatementParser(sql);
// 是否保留注释
parser.setKeepComments(false);
SQLStatement stmt = parser.parseStatement();
if (stmt instanceof SQLCreateProcedureStatement) {
SQLCreateProcedureStatement procStmt = (SQLCreateProcedureStatement) stmt;
SQLStatement blockStmt = procStmt.getBlock();
if (blockStmt instanceof SQLBlockStatement) {
SQLBlockStatement sqlBlockStatement = (SQLBlockStatement) blockStmt;
List<SQLStatement> sqlStatementList = sqlBlockStatement.getStatementList();
for (SQLStatement statement : sqlStatementList) {
List<String> tableList = getTableList(statement);
}
}
// List<SQLStatement> statementList = blockStmt.getStatementList();
// for (SQLStatement sqlStatement : statementList) {
// if (sqlStatement instanceof SQLSelectStatement) {
// SQLSelectStatement selectStatement = (SQLSelectStatement) sqlStatement;
// SQLSelectQuery query = selectStatement.getSelect().getQuery();
//
// // 处理SELECT语句
// System.out.println(query.toString());
// }
// }
}
return result;
}
public static List<String> getTableList(SQLStatement statement) {
List<String> result = new ArrayList<>();
if (statement instanceof SQLSelectStatement) {
SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) statement;
SQLSelect sqlSelect = sqlSelectStatement.getSelect();
SQLSelectQuery sqlSelectQuery = sqlSelect.getQuery();
if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
MySqlSelectQueryBlock mySqlSelectQueryBlock = (MySqlSelectQueryBlock) sqlSelectQuery;
SQLTableSource sqlTableSource = mySqlSelectQueryBlock.getFrom();
result.add(sqlTableSource.toString());
}
}
return result;
}