解析SQL 获取表、字段及SQL查询参数

1. 执行效果

2. 使用

2.1 引入依赖

	<!-- sql 解析处理-->
        <dependency>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
            <version>4.4</version>
        </dependency>

2.2 相关实体

1. 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;
}

2. 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 net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.ComparisonOperator;
import net.sf.jsqlparser.expression.operators.relational.InExpression;
import net.sf.jsqlparser.expression.operators.relational.LikeExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.example.vo.TableDescVo;
import org.example.vo.TableFieldVo;

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) {
            extractedRequestParams(where, requestParamList);
        }
        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;
    }

    /**
     * 提取查询参数: 解析查询语法树，提取嵌套子查询查询条件
     *
     * @param where
     * @param requestParamList
     */
    private static void extractedRequestParams(Expression where, List<TableFieldVo> requestParamList) {
        // 这里需要语法树反向解析
        if (where instanceof AndExpression || where instanceof OrExpression) {
            // 说明where为and、or连接
            Expression leftExpression = ((BinaryExpression) where).getLeftExpression();
            extractedRequestParams(leftExpression, requestParamList);
            Expression rightExpression = ((BinaryExpression) where).getRightExpression();
            extractedRequestParams(rightExpression, requestParamList);
        }else if (where instanceof ComparisonOperator || where instanceof InExpression || where instanceof LikeExpression) {
            // where为比较符,取右侧元素
            Expression rightExpression = where;
            if(where instanceof ComparisonOperator){
                rightExpression = ((ComparisonOperator) where).getRightExpression();
            }else if(where instanceof InExpression){
                rightExpression = ((InExpression) where).getRightExpression();
            }else{
                rightExpression = ((LikeExpression) where).getRightExpression();
            }
            if (rightExpression instanceof SubSelect) {
                // 说明为子嵌套查询
                PlainSelect subSelectBody = (PlainSelect) ((SubSelect) rightExpression).getSelectBody();
                // 提取查询条件
                Expression childWhere = subSelectBody.getWhere();
                extractedRequestParams(childWhere, requestParamList);
            }else{
                where.accept(new ExpressionVisitorAdapter() {
                    @Override
                    public void visit(JdbcNamedParameter namedParameter) {
                        TableFieldVo tableField = new TableFieldVo();
                        tableField.setFieldName(namedParameter.getName());
                        requestParamList.add(tableField);
                    }
                });
            }
        }

    }
}