使用druid的sql解析,编写java程序,实现血缘关系解析,包含表依赖关系和字段依赖关系
时间: 2024-02-04 15:04:16 浏览: 209
获取表血缘关系以及相关sql语句,输出形式,附带shell脚本及建表语句
实现血缘关系解析需要分别解析出表之间的依赖关系和字段之间的依赖关系。以下是一个Java程序示例,使用Druid解析SQL语句,输出表依赖关系和字段依赖关系:
```
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLName;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLAggregateExpr;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOperator;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLPropertyExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import java.util.*;
public class BloodRelationParser {
private Map<String, List<String>> tableRelations;
private Map<String, List<String>> columnRelations;
public BloodRelationParser() {
tableRelations = new HashMap<>();
columnRelations = new HashMap<>();
}
public void parse(String sql) {
SQLStatementParser parser = new MySqlStatementParser(sql);
SQLStatement stmt = parser.parseStatement();
if (stmt instanceof SQLSelectStatement) {
SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;
SQLSelect select = selectStmt.getSelect();
SQLSelectQuery query = select.getQuery();
if (query instanceof SQLSelectQueryBlock) {
SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) query;
SQLTableSource from = queryBlock.getFrom();
// 解析表之间的依赖关系
parseTableSource(from, "");
// 解析字段之间的依赖关系
List<SQLSelectItem> selectList = queryBlock.getSelectList();
for (SQLSelectItem selectItem : selectList) {
SQLExpr expr = selectItem.getExpr();
parseExpr(expr);
}
}
}
// 输出表之间的依赖关系
System.out.println("Table Relations:");
for (String table : tableRelations.keySet()) {
System.out.println(table + " -> " + tableRelations.get(table));
}
// 输出字段之间的依赖关系
System.out.println("Column Relations:");
for (String column : columnRelations.keySet()) {
System.out.println(column + " -> " + columnRelations.get(column));
}
}
private void parseTableSource(SQLTableSource tableSource, String alias) {
if (tableSource instanceof SQLExprTableSource) {
SQLExprTableSource exprTableSource = (SQLExprTableSource) tableSource;
SQLExpr expr = exprTableSource.getExpr();
if (expr instanceof SQLIdentifierExpr) {
String tableName = ((SQLIdentifierExpr) expr).getSimpleName();
// 添加表之间的依赖关系
if (!tableRelations.containsKey(tableName)) {
tableRelations.put(tableName, new ArrayList<>());
}
if (!alias.isEmpty()) {
tableRelations.get(tableName).add(alias);
}
}
} else if (tableSource instanceof SQLJoinTableSource) {
SQLJoinTableSource joinTableSource = (SQLJoinTableSource) tableSource;
parseTableSource(joinTableSource.getLeft(), joinTableSource.getLeft().getAlias());
parseTableSource(joinTableSource.getRight(), joinTableSource.getRight().getAlias());
}
}
private void parseExpr(SQLExpr expr) {
if (expr instanceof SQLBinaryOpExpr) {
SQLBinaryOpExpr binaryOpExpr = (SQLBinaryOpExpr) expr;
SQLBinaryOperator operator = binaryOpExpr.getOperator();
SQLExpr left = binaryOpExpr.getLeft();
SQLExpr right = binaryOpExpr.getRight();
parseExpr(left);
parseExpr(right);
} else if (expr instanceof SQLAggregateExpr) {
// 聚合函数,忽略
} else if (expr instanceof SQLPropertyExpr) {
SQLPropertyExpr propertyExpr = (SQLPropertyExpr) expr;
SQLExpr owner = propertyExpr.getOwner();
SQLName columnName = propertyExpr.getName();
String column = columnName.getSimpleName();
String table = "";
if (owner instanceof SQLIdentifierExpr) {
table = ((SQLIdentifierExpr) owner).getSimpleName();
} else if (owner instanceof SQLPropertyExpr) {
SQLPropertyExpr ownerPropertyExpr = (SQLPropertyExpr) owner;
table = ownerPropertyExpr.getName().getSimpleName();
}
// 添加字段之间的依赖关系
String columnFullName = table + "." + column;
if (!columnRelations.containsKey(columnFullName)) {
columnRelations.put(columnFullName, new ArrayList<>());
}
List<String> dependencies = getColumnDependencies(expr);
for (String dependency : dependencies) {
if (!dependency.equals(columnFullName)) {
columnRelations.get(columnFullName).add(dependency);
}
}
}
}
private List<String> getColumnDependencies(SQLExpr expr) {
List<String> dependencies = new ArrayList<>();
if (expr instanceof SQLBinaryOpExpr) {
SQLBinaryOpExpr binaryOpExpr = (SQLBinaryOpExpr) expr;
SQLBinaryOperator operator = binaryOpExpr.getOperator();
SQLExpr left = binaryOpExpr.getLeft();
SQLExpr right = binaryOpExpr.getRight();
dependencies.addAll(getColumnDependencies(left));
dependencies.addAll(getColumnDependencies(right));
} else if (expr instanceof SQLPropertyExpr) {
SQLPropertyExpr propertyExpr = (SQLPropertyExpr) expr;
SQLExpr owner = propertyExpr.getOwner();
SQLName columnName = propertyExpr.getName();
String column = columnName.getSimpleName();
String table = "";
if (owner instanceof SQLIdentifierExpr) {
table = ((SQLIdentifierExpr) owner).getSimpleName();
} else if (owner instanceof SQLPropertyExpr) {
SQLPropertyExpr ownerPropertyExpr = (SQLPropertyExpr) owner;
table = ownerPropertyExpr.getName().getSimpleName();
}
dependencies.add(table + "." + column);
}
return dependencies;
}
}
```
这个程序接受一个SQL语句作为输入,解析出语句中涉及到的表和字段之间的依赖关系,并输出到控制台。你可以将具体的SQL语句作为程序的输入,来实现血缘关系的解析。
阅读全文