写一个Java代码,使用Druid实现sql解析,并分析字段血缘关系
时间: 2024-02-26 19:59:06 浏览: 388
以下是一个使用Druid解析SQL并分析字段血缘关系的Java代码示例:
```
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.*;
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 DruidSqlParser {
public static void main(String[] args) {
String sql = "SELECT t1.id, t2.name FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id WHERE t1.age > 18";
List<ColumnInfo> columnInfoList = analyzeSql(sql);
for (ColumnInfo columnInfo : columnInfoList) {
System.out.println(columnInfo);
}
}
public static List<ColumnInfo> analyzeSql(String sql) {
List<ColumnInfo> columnInfoList = new ArrayList<>();
SQLStatementParser parser = new MySqlStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
for (SQLStatement statement : statementList) {
if (statement instanceof SQLSelectStatement) {
SQLSelectStatement selectStatement = (SQLSelectStatement) statement;
SQLSelectQueryBlock queryBlock = selectStatement.getSelect().getQueryBlock();
analyzeSelectItems(queryBlock.getSelectList(), columnInfoList);
analyzeWhere(queryBlock.getWhere(), columnInfoList);
analyzeJoin(queryBlock.getFrom(), columnInfoList);
}
}
return columnInfoList;
}
private static void analyzeSelectItems(List<SQLSelectItem> selectItems, List<ColumnInfo> columnInfoList) {
for (SQLSelectItem selectItem : selectItems) {
SQLExpr expr = selectItem.getExpr();
String columnName = null;
String tableName = null;
if (expr instanceof SQLPropertyExpr) {
SQLPropertyExpr propertyExpr = (SQLPropertyExpr) expr;
columnName = propertyExpr.getName();
tableName = propertyExpr.getOwner().getName();
} else if (expr instanceof SQLIdentifierExpr) {
SQLIdentifierExpr identifierExpr = (SQLIdentifierExpr) expr;
columnName = identifierExpr.getName();
}
if (columnName != null) {
ColumnInfo columnInfo = new ColumnInfo(columnName, tableName, true);
if (!columnInfoList.contains(columnInfo)) {
columnInfoList.add(columnInfo);
}
}
}
}
private static void analyzeWhere(SQLExpr whereExpr, List<ColumnInfo> columnInfoList) {
if (whereExpr instanceof SQLBinaryOpExpr) {
SQLBinaryOpExpr binaryOpExpr = (SQLBinaryOpExpr) whereExpr;
analyzeWhere(binaryOpExpr.getLeft(), columnInfoList);
analyzeWhere(binaryOpExpr.getRight(), columnInfoList);
} else if (whereExpr instanceof SQLBinaryOpExprGroup) {
SQLBinaryOpExprGroup binaryOpExprGroup = (SQLBinaryOpExprGroup) whereExpr;
for (SQLExpr sqlExpr : binaryOpExprGroup.getItems()) {
analyzeWhere(sqlExpr, columnInfoList);
}
} else if (whereExpr instanceof SQLIdentifierExpr) {
SQLIdentifierExpr identifierExpr = (SQLIdentifierExpr) whereExpr;
String columnName = identifierExpr.getName();
ColumnInfo columnInfo = new ColumnInfo(columnName, null, false);
if (!columnInfoList.contains(columnInfo)) {
columnInfoList.add(columnInfo);
}
}
}
private static void analyzeJoin(SQLTableSource tableSource, List<ColumnInfo> columnInfoList) {
if (tableSource instanceof SQLJoinTableSource) {
SQLJoinTableSource joinTableSource = (SQLJoinTableSource) tableSource;
analyzeJoin(joinTableSource.getLeft(), columnInfoList);
analyzeJoin(joinTableSource.getRight(), columnInfoList);
SQLExpr onExpr = joinTableSource.getCondition();
if (onExpr instanceof SQLBinaryOpExpr) {
SQLBinaryOpExpr binaryOpExpr = (SQLBinaryOpExpr) onExpr;
SQLExpr leftExpr = binaryOpExpr.getLeft();
SQLExpr rightExpr = binaryOpExpr.getRight();
if (leftExpr instanceof SQLPropertyExpr && rightExpr instanceof SQLPropertyExpr) {
SQLPropertyExpr leftPropertyExpr = (SQLPropertyExpr) leftExpr;
SQLPropertyExpr rightPropertyExpr = (SQLPropertyExpr) rightExpr;
String leftColumnName = leftPropertyExpr.getName();
String leftTableName = leftPropertyExpr.getOwner().getName();
String rightColumnName = rightPropertyExpr.getName();
String rightTableName = rightPropertyExpr.getOwner().getName();
ColumnInfo leftColumnInfo = new ColumnInfo(leftColumnName, leftTableName, false);
ColumnInfo rightColumnInfo = new ColumnInfo(rightColumnName, rightTableName, false);
if (columnInfoList.contains(leftColumnInfo)) {
int index = columnInfoList.indexOf(leftColumnInfo);
columnInfoList.get(index).setTableName(leftTableName);
}
if (columnInfoList.contains(rightColumnInfo)) {
int index = columnInfoList.indexOf(rightColumnInfo);
columnInfoList.get(index).setTableName(rightTableName);
}
}
}
} else if (tableSource instanceof SQLSubqueryTableSource) {
SQLSubqueryTableSource subqueryTableSource = (SQLSubqueryTableSource) tableSource;
SQLSelect subSelect = subqueryTableSource.getSelect();
analyzeSelectItems(subSelect.getQueryBlock().getSelectList(), columnInfoList);
analyzeWhere(subSelect.getQueryBlock().getWhere(), columnInfoList);
analyzeJoin(subSelect.getQueryBlock().getFrom(), columnInfoList);
} else if (tableSource instanceof SQLExprTableSource) {
SQLExprTableSource exprTableSource = (SQLExprTableSource) tableSource;
String tableName = null;
if (exprTableSource.getAlias() != null) {
tableName = exprTableSource.getAlias();
} else if (exprTableSource.getExpr() instanceof SQLIdentifierExpr) {
tableName = ((SQLIdentifierExpr) exprTableSource.getExpr()).getName();
}
for (ColumnInfo columnInfo : columnInfoList) {
if (columnInfo.getTableName() == null) {
columnInfo.setTableName(tableName);
}
}
}
}
}
class ColumnInfo {
private String columnName;
private String tableName;
private boolean isSelectItem;
public ColumnInfo(String columnName, String tableName, boolean isSelectItem) {
this.columnName = columnName;
this.tableName = tableName;
this.isSelectItem = isSelectItem;
}
public String getColumnName() {
return columnName;
}
public void setColumnName(String columnName) {
this.columnName = columnName;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public boolean isSelectItem() {
return isSelectItem;
}
public void setSelectItem(boolean selectItem) {
isSelectItem = selectItem;
}
@Override
public String toString() {
return "ColumnInfo{" +
"columnName='" + columnName + '\'' +
", tableName='" + tableName + '\'' +
", isSelectItem=" + isSelectItem +
'}';
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
ColumnInfo that = (ColumnInfo) o;
return Objects.equals(columnName, that.columnName) &&
Objects.equals(tableName, that.tableName);
}
@Override
public int hashCode() {
return Objects.hash(columnName, tableName);
}
}
```
这个代码实现了对SELECT、WHERE和JOIN三个部分的分析,并且能够正确地识别出每个字段所属的表和是否是SELECT中的选择项。你可以根据自己的需求对这个代码进行扩展和修改。
阅读全文