解析SQL 获取表、字段及SQL查询参数
- 1. 执行效果
- 2. 使用
- 2.1 引入依赖
- 2.2 相关实体
- 2.3 工具类
1. 执行效果
2. 使用
2.1 引入依赖
<!-- sql 解析处理-->
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.4</version>
</dependency>
2.2 相关实体
- TableDescVo.java
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
/**
* @author:
* @version: 1.0
* @className: TableDesc
* @description: 表描述信息
* @date: 2023/05/23 11:36
*/
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
public class TableDescVo {
/**
* 表名
*/
private String name;
/**
* 备注
*/
private String remark;
/**
* 数据库名称
*/
private String dbName;
/**
* 表原始名称
*/
private String originName;
}
- TableFieldVo.java
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
/**
* @author:
* @version: 1.0
* @className: TableField
* @description: 字段信息
* @date: 2023/05/23 11:41
*/
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
public class TableFieldVo {
/**
* 字段名称
*/
private String fieldName;
/**
* 字段别名
*/
private String aliasName;
/**
* 表名称
*/
private String tableName;
}
2.3 工具类
getTablesBySQL: 用于获得SQL中携带的表及数据库信息
getColumnNameBySQL: 用于获得SQL中携带的列字段信息, 如果为select * from xxx，则返回fieldName为* ，若为 select a.* from xx a，则放回 fieldName为*, tableName为 a
getRequestParamsBySQL: 用于获得请求参数,例如 select * from xxx where a = :a，则会提取a为请求参数
import com.sdews.bdpLite.vo.sqlparser.TableDescVo;
import com.sdews.bdpLite.vo.sqlparser.TableFieldVo;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Alias;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.ExpressionVisitorAdapter;
import net.sf.jsqlparser.expression.JdbcNamedParameter;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import java.util.ArrayList;
import java.util.List;
/**
* @author
* @version 1.0
* @className: SqlParserUtils
* @description: SQL解析工具类
* @date: 2022/12/11 16:10
**/
public final class SqlParserUtils {
/**
* 获得所有表名+别名
*
* @param sql
* @return
* @throws JSQLParserException
*/
public static List<TableDescVo> getTablesBySQL(String sql) throws JSQLParserException {
Select select = (Select) CCJSqlParserUtil.parse(sql);
List<TableDescVo> tableList = new ArrayList<>();
SelectBody selectBody = select.getSelectBody();
PlainSelect plainSelect = (PlainSelect) selectBody;
Table table = (Table) plainSelect.getFromItem();
TableDescVo tableDesc = new TableDescVo();
tableDesc.setOriginName(table.getName());
tableDesc.setName(table.getName());
tableDesc.setDbName(table.getDatabase().getDatabaseName());
if (table.getAlias() != null) {
tableDesc.setName(table.getAlias().getName());
}
tableList.add(tableDesc);
if (plainSelect.getJoins() != null) {
for (Join join : plainSelect.getJoins()) {
Table joinTable = (Table) join.getRightItem();
TableDescVo joinTableDesc = new TableDescVo();
joinTableDesc.setOriginName(joinTable.getName());
joinTableDesc.setName(joinTable.getName());
joinTableDesc.setDbName(joinTable.getDatabase().getDatabaseName());
if (joinTable.getAlias() != null) {
joinTableDesc.setName(joinTable.getAlias().getName());
}
tableList.add(joinTableDesc);
}
}
return tableList;
}
/**
* 通过sql获得所有返回参数结果
*
* @param sql
* @return columnList: SQL列
* @throws JSQLParserException
*/
public static List<TableFieldVo> getColumnNameBySQL(String sql) throws JSQLParserException {
Select select = (Select) CCJSqlParserUtil.parse(sql);
SelectBody selectBody = select.getSelectBody();
List<TableFieldVo> columnList = new ArrayList<>();
List<SelectItem> selectItems = ((PlainSelect) selectBody).getSelectItems();
for (SelectItem item : selectItems) {
TableFieldVo tableField = new TableFieldVo();
String column = null;
if (item instanceof AllColumns) {
column = item.toString();
}
if (item instanceof AllTableColumns) {
Table table = ((AllTableColumns) item).getTable();
tableField.setTableName(table.getName());
column = "*";
}
if (item instanceof SelectExpressionItem) {
Alias alias = ((SelectExpressionItem) item).getAlias();
Expression expression = ((SelectExpressionItem) item).getExpression();
if (alias != null) {
// 原名称
column = expression.toString();
// 别名
String aliasName = alias.getName();
tableField.setAliasName(aliasName);
} else if (expression != null) {
column = expression.toString();
if (column.contains(".")) {
String[] columnArr = column.split("\\.");
tableField.setTableName(columnArr[0]);
column = columnArr[1];
}
}
}
tableField.setFieldName(column);
columnList.add(tableField);
}
return columnList;
}
/**
* 获得jdbcName参数形式请求参数
*
* @param sql
* @return
* @throws JSQLParserException
*/
public static List<TableFieldVo> getRequestParamsBySQL(String sql) throws JSQLParserException {
Select select = (Select) CCJSqlParserUtil.parse(sql);
SelectBody selectBody = select.getSelectBody();
List<TableFieldVo> requestParamList = new ArrayList<>();
Expression where = ((PlainSelect) selectBody).getWhere();
if (where != null) {
where.accept(new ExpressionVisitorAdapter() {
@Override
public void visit(JdbcNamedParameter namedParameter) {
TableFieldVo tableField = new TableFieldVo();
tableField.setFieldName(namedParameter.getName());
requestParamList.add(tableField);
}
});
}
Offset offset = ((PlainSelect) selectBody).getOffset();
if (offset != null && offset.getOffset() != null) {
offset.getOffset().accept(new ExpressionVisitorAdapter() {
@Override
public void visit(JdbcNamedParameter namedParameter) {
TableFieldVo tableField = new TableFieldVo();
tableField.setFieldName(namedParameter.getName());
requestParamList.add(tableField);
}
});
}
Limit limit = ((PlainSelect) selectBody).getLimit();
if (limit != null) {
Expression rowCount = limit.getRowCount();
if (rowCount != null) {
rowCount.accept(new ExpressionVisitorAdapter() {
@Override
public void visit(JdbcNamedParameter namedParameter) {
TableFieldVo tableField = new TableFieldVo();
tableField.setFieldName(namedParameter.getName());
requestParamList.add(tableField);
}
});
}
}
return requestParamList;
}
}
