1.获取表名
sql为数据表语句 select id as id2 from testDb.testTable where id = ${id}
//数据类型可以输入其他的
DbType dbType = DbType.valueOf("MYSQL".toLowerCase());
//sql为数据表语句 select id as id2 from testDb.testTable where id = ${id}
List<SQLStatement> statementList = SQLUtils.parseStatements(sql, "MYSQL".toLowerCase());
for (SQLStatement statement : statementList) {
if (statement instanceof SQLSelectStatement) {
SchemaStatVisitor visitor = new SchemaStatVisitor(dbType);
statement.accept(visitor);
//解析表名
SQLSelectStatement selectStatement = (SQLSelectStatement) statement;
SQLSelectQueryBlock queryBlock = selectStatement.getSelect().getFirstQueryBlock();
Map<TableStat.Name, TableStat> tables = visitor.getTables();
tables.forEach((tableName, tableStat) -> {
//判断sql语句
if ("Select".equals(tableStat.toString())) {
//这里获取表名
tableNameList.add(tableName.getName().contains(".") ? tableName.getName().split("\\.")[1] : tableName.getName());
}
});
}
2.获取查询字段
//查询字段
List<SQLSelectItem> selectList = queryBlock.getSelectList();
if (CollectionUtils.isNotEmpty(selectList)) {
selectList.stream().forEach(sqlSelectItem -> {
String outStr = sqlSelectItem.toString().trim();
//去除字段前面的 表的别名
if (outStr.contains(".")) {
outStr = outStr.split("\\.", -1)[1];
}
if (outStr.contains("AS")) {
outStr = outStr.split("AS", -1)[0].trim();
//获取字段名
outputParamDto.setEnName(outStr);
//获取别名
outputParamDto.setAliasName(sqlSelectItem.getAlias());
} else {
//获取字段名
outputParamDto.setEnName(outStr);
}
});
}
3.获取过滤条件
//查询过滤条件
List<TableStat.Condition> conditions = visitor.getConditions();
if (CollectionUtils.isNotEmpty(conditions)) {
conditions.stream().forEach(row -> {
//获取过滤条件 id=12 获取 id
String columnName = row.getColumn().getName();
//获取操作符 id=12 获取 = 号
String operator = row.getOperator();
});
}
4.获取排序条件
//查询order by
List<TableStat.Column> orderByColumns = visitor.getOrderByColumns();
if (CollectionUtils.isNotEmpty(orderByColumns)) {
orderByColumns.stream().forEach(orderByColumn -> {
if (outputParamDto.getEnName().equals(orderByColumn.getName())) {
if (BizConstants.ASC_PARAM.equals(orderByColumn.getAttributes().get("orderBy.type"))) {
outputParamDto.setSort(BizConstants.ASC_PARAM);
} else {
outputParamDto.setSort(BizConstants.DESC_PARAM);
}
}
});
}