Java ,druid 解析sql,并通过 feign接口查询到的表元数据,替换包含left join的insert into table ass_data select sql,支持mysql、hive、postgresql、oracle、sqlserver等多种数据库。需要写出代码
时间: 2023-11-22 18:53:33 浏览: 162
以下是一个示例代码,解析 SQL 并替换其中的 left join:
```java
import java.sql.*;
import java.util.*;
import java.util.regex.*;
import com.alibaba.druid.pool.*;
import com.alibaba.druid.sql.*;
import com.alibaba.druid.sql.ast.*;
import com.alibaba.druid.sql.ast.expr.*;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.visitor.*;
import com.alibaba.druid.sql.dialect.oracle.visitor.*;
import com.alibaba.druid.sql.dialect.postgresql.visitor.*;
import com.alibaba.druid.sql.dialect.sqlserver.visitor.*;
import com.alibaba.druid.sql.visitor.*;
import feign.*;
import feign.jackson.*;
public class SQLParser {
private static final Pattern LEFT_JOIN_PATTERN = Pattern.compile("\\bleft join\\b", Pattern.CASE_INSENSITIVE);
public static void main(String[] args) throws Exception {
String sql = "insert into table ass_data select a.*, b.* from table_a a left join table_b b on a.id = b.a_id";
String replacedSql = replaceLeftJoin(sql);
System.out.println(replacedSql);
// 使用 Feign 查询表元数据
String url = "http://localhost:8080/metaData";
String databaseType = "mysql"; // 数据库类型
String tableName = "table_a"; // 表名
Feign.Builder builder = Feign.builder().encoder(new JacksonEncoder()).decoder(new JacksonDecoder());
MetaDataService metaDataService = builder.target(MetaDataService.class, url);
TableMetaData tableMetaData = metaDataService.getTableMetaData(databaseType, tableName);
System.out.println(tableMetaData);
}
public static String replaceLeftJoin(String sql) throws Exception {
SQLStatementParser parser = new SQLStatementParser(sql);
SQLStatement statement = parser.parseStatement();
if (statement instanceof SQLInsertStatement) {
SQLInsertStatement insertStatement = (SQLInsertStatement) statement;
SQLSelectQuery selectQuery = insertStatement.getQuery().getQuery();
if (selectQuery instanceof SQLSelectQueryBlock) {
SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) selectQuery;
List<SQLJoinTableSource> joinList = queryBlock.getJoinList();
for (SQLJoinTableSource join : joinList) {
if (join.getJoinType() == SQLJoinTableSource.JoinType.LEFT_OUTER_JOIN) {
String joinSql = SQLUtils.toMySqlString(join.getCondition());
if (LEFT_JOIN_PATTERN.matcher(joinSql).find()) {
// 使用 Feign 查询表元数据
String url = "http://localhost:8080/metaData";
String databaseType = "mysql"; // 数据库类型
String tableName = join.getRight().toString(); // 右表名
Feign.Builder builder = Feign.builder().encoder(new JacksonEncoder()).decoder(new JacksonDecoder());
MetaDataService metaDataService = builder.target(MetaDataService.class, url);
TableMetaData tableMetaData = metaDataService.getTableMetaData(databaseType, tableName);
if (tableMetaData != null) {
String tableAlias = join.getRight().getAlias();
if (tableAlias == null) {
tableAlias = join.getRight().toString();
}
String primaryKey = tableMetaData.getPrimaryKey();
StringBuilder sb = new StringBuilder();
sb.append("select a.*, b.* from ");
sb.append(join.getLeft().toString());
sb.append(" a left join ");
sb.append(join.getRight().toString());
sb.append(" b on a.");
sb.append(primaryKey);
sb.append(" = b.");
sb.append(primaryKey);
sb.append(" where ");
sb.append(join.getLeft().toString());
sb.append(".");
sb.append(primaryKey);
sb.append(" is not null");
if (tableAlias != null) {
sb.append(" and ");
sb.append(tableAlias);
sb.append(".");
sb.append(primaryKey);
sb.append(" is null");
}
queryBlock.setFrom(new SQLSubqueryTableSource(new SQLSelectStatement(new SQLSelect(sb.toString()), databaseType)));
join.setJoinType(SQLJoinTableSource.JoinType.INNER_JOIN);
join.setCondition(null);
}
}
}
}
return SQLUtils.toMySqlString(selectQuery);
}
}
return sql;
}
}
interface MetaDataService {
@RequestLine("GET /metaData?databaseType={databaseType}&tableName={tableName}")
TableMetaData getTableMetaData(@Param("databaseType") String databaseType, @Param("tableName") String tableName);
}
class TableMetaData {
private String tableName;
private List<String> columnNames;
private String primaryKey;
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public List<String> getColumnNames() {
return columnNames;
}
public void setColumnNames(List<String> columnNames) {
this.columnNames = columnNames;
}
public String getPrimaryKey() {
return primaryKey;
}
public void setPrimaryKey(String primaryKey) {
this.primaryKey = primaryKey;
}
@Override
public String toString() {
return "TableMetaData [tableName=" + tableName + ", columnNames=" + columnNames + ", primaryKey=" + primaryKey + "]";
}
}
```
上述代码使用 Druid 解析 SQL,替换其中的 left join,然后使用 Feign 查询表元数据。其中 `MetaDataService` 接口用于定义查询表元数据的 API,`TableMetaData` 类用于存储表元数据。请根据实际情况修改代码。
阅读全文