Java ,druid解析sql,并通过 feign接口查询到的表元数据,替换包含left join sql语句中的*,支持mysql、hive、postgresql、oracle等多种数据库
时间: 2023-07-16 12:13:39 浏览: 134
通过解析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.SQLAllColumnExpr;
import com.alibaba.druid.sql.ast.statement.SQLJoinTableSource;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlOutputVisitor;
import com.alibaba.druid.sql.dialect.oracle.ast.stmt.OracleSelectQueryBlock;
import com.alibaba.druid.sql.dialect.oracle.visitor.OracleOutputVisitor;
import com.alibaba.druid.sql.dialect.postgresql.ast.stmt.PGSelectQueryBlock;
import com.alibaba.druid.sql.dialect.postgresql.visitor.PGOutputVisitor;
import com.alibaba.druid.sql.dialect.sqlserver.ast.stmt.SQLServerSelectQueryBlock;
import com.alibaba.druid.sql.dialect.sqlserver.visitor.SQLServerOutputVisitor;
import com.alibaba.druid.util.JdbcConstants;
import com.google.common.collect.Lists;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cloud.openfeign.FeignClient;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
public class SqlParser {
@Autowired
private MetaDataService metaDataService;
@Autowired
private MysqlFeignClient mysqlFeignClient;
@Autowired
private HiveFeignClient hiveFeignClient;
@Autowired
private PostgresqlFeignClient postgresqlFeignClient;
@Autowired
private OracleFeignClient oracleFeignClient;
@FeignClient(name = "mysqlFeignClient", url = "${mysql.url:}", fallback = MysqlFeignClientFallback.class)
public interface MysqlFeignClient {
@GetMapping(value = "/meta/{dbName}/{tableName}")
TableMetaData getTableMetaData(@PathVariable("dbName") String dbName,
@PathVariable("tableName") String tableName);
}
@FeignClient(name = "hiveFeignClient", url = "${hive.url:}", fallback = HiveFeignClientFallback.class)
public interface HiveFeignClient {
@GetMapping(value = "/meta/{dbName}/{tableName}")
TableMetaData getTableMetaData(@PathVariable("dbName") String dbName,
@PathVariable("tableName") String tableName);
}
@FeignClient(name = "postgresqlFeignClient", url = "${postgresql.url:}", fallback = PostgresqlFeignClientFallback.class)
public interface PostgresqlFeignClient {
@GetMapping(value = "/meta/{dbName}/{tableName}")
TableMetaData getTableMetaData(@PathVariable("dbName") String dbName,
@PathVariable("tableName") String tableName);
}
@FeignClient(name = "oracleFeignClient", url = "${oracle.url:}", fallback = OracleFeignClientFallback.class)
public interface OracleFeignClient {
@GetMapping(value = "/meta/{dbName}/{tableName}")
TableMetaData getTableMetaData(@PathVariable("dbName") String dbName,
@PathVariable("tableName") String tableName);
}
public String parse(String sql, String dbType) {
List<String> tables = Lists.newArrayList();
SQLStatement stmt = SQLUtils.parseStatements(sql, dbType).get(0);
if (stmt instanceof SQLSelectStatement) {
SQLSelectStatement selectStatement = (SQLSelectStatement) stmt;
SQLSelect select = selectStatement.getSelect();
MySqlSelectQueryBlock queryBlock = null;
OracleSelectQueryBlock oracleQueryBlock = null;
PGSelectQueryBlock pgQueryBlock = null;
SQLServerSelectQueryBlock sqlServerQueryBlock = null;
if (select.getQuery() instanceof MySqlSelectQueryBlock) {
queryBlock = (MySqlSelectQueryBlock) select.getQuery();
} else if (select.getQuery() instanceof OracleSelectQueryBlock) {
oracleQueryBlock = (OracleSelectQueryBlock) select.getQuery();
} else if (select.getQuery() instanceof PGSelectQueryBlock) {
pgQueryBlock = (PGSelectQueryBlock) select.getQuery();
} else if (select.getQuery() instanceof SQLServerSelectQueryBlock) {
sqlServerQueryBlock = (SQLServerSelectQueryBlock) select.getQuery();
}
List<SQLSelectItem> items = selectBlock.getSelectList();
for (SQLSelectItem item : items) {
SQLExpr expr = item.getExpr();
if (expr instanceof SQLAllColumnExpr) {
SQLAllColumnExpr allColumnExpr = (SQLAllColumnExpr) expr;
tables.add(allColumnExpr.getOwner().getSimpleName());
}
}
List<SQLJoinTableSource> joinList = Lists.newArrayList();
if (queryBlock != null) {
joinList.addAll(queryBlock.getJoinList());
} else if (oracleQueryBlock != null) {
joinList.addAll(oracleQueryBlock.getJoinList());
} else if (pgQueryBlock != null) {
joinList.addAll(pgQueryBlock.getJoinList());
} else if (sqlServerQueryBlock != null) {
joinList.addAll(sqlServerQueryBlock.getJoinList());
}
for (SQLJoinTableSource join : joinList) {
tables.add(join.getAlias());
}
}
// 从元数据服务查询表的字段信息
for (String tableName : tables) {
TableMetaData metaData = metaDataService.getTableMetaData(tableName);
if (metaData != null) {
sql = sql.replaceAll(tableName + "\\.", metaData.getColumnNames().stream().map(columnName -> tableName + "." + columnName).collect(Collectors.joining(",")));
}
}
return sql;
}
private String formatSql(String sql, String dbType) {
SQLStatement stmt = SQLUtils.parseStatements(sql, dbType).get(0);
StringBuilder out = new StringBuilder();
if (dbType.equals(JdbcConstants.MYSQL)) {
MySqlOutputVisitor visitor = new MySqlOutputVisitor(out);
stmt.accept(visitor);
} else if (dbType.equals(JdbcConstants.ORACLE)) {
OracleOutputVisitor visitor = new OracleOutputVisitor(out);
stmt.accept(visitor);
} else if (dbType.equals(JdbcConstants.POSTGRESQL)) {
PGOutputVisitor visitor = new PGOutputVisitor(out);
stmt.accept(visitor);
} else if (dbType.equals(JdbcConstants.SQL_SERVER)) {
SQLServerOutputVisitor visitor = new SQLServerOutputVisitor(out);
stmt.accept(visitor);
}
return out.toString();
}
}
```
其中,`SQLUtils.parseStatements()` 可以将 SQL 语句解析成 `SQLStatement` 对象,然后通过访问器(如 `MySqlOutputVisitor`、`OracleOutputVisitor` 等)进行遍历和修改。同时,通过 `Feign` 客户端调用元数据服务,获取表的元数据信息,然后替换 SQL 语句中的 `*` 符号。最后,再使用格式化器(如 `MySqlOutputVisitor`、`OracleOutputVisitor` 等)将修改后的 SQL 语句格式化输出。
阅读全文