首页 > 数据库 >获取SQL中的所有字段

获取SQL中的所有字段

时间:2022-11-07 18:57:54浏览次数:44  
标签:instanceof expression 所有 param 获取 getExpressionColumns SQL Expression columns

public static void main(String[] args) {
        String SQL = "SELECT * FROM table";

        Statement statement = SQLParserUtil.getStatement(SQL);
        SelectBody selectBody = ((Select) statement).getSelectBody();
        Set<String> columns = new HashSet<>();
        getSQLAllColumns(selectBody, columns);

        for(String s : columns){
            System.out.println(s);
        }
    }




    /**
     * 获取SQL中的所有字段
     * @param selectBody
     * @param columns
     */
    public static void getSQLAllColumns(SelectBody selectBody, Set<String> columns){
        if(null != selectBody){
            // 1.提取Select中的字段
            List<SelectItem> selectItems = ((PlainSelect) selectBody).getSelectItems();
            if(null != selectItems && selectItems.size() > 0){
                getSelectColumns(selectItems, columns);
            }
            // 2.提取Join中的字段
            List<Join> joinsList = ((PlainSelect) selectBody).getJoins();
            if(null != joinsList && 0 != joinsList.size()){
                getJoinColumns(joinsList, columns);
            }
            // 3.提取Where中的字段
            Expression where = ((PlainSelect) selectBody).getWhere();
            if(null != where){
                getWhereColumns(where, columns);
            }
        }
    }

    /**
     * 获取Select下所有字段
     * @param selectItems
     * @param columns
     */
    public static void getSelectColumns(List<SelectItem> selectItems, Set<String> columns){
        for (SelectItem selectItem : selectItems){
            Expression expression = ((SelectExpressionItem) selectItem).getExpression();
            getExpressionColumns(expression, columns);
        }
    }

    /**
     * 获取JOIN下所有字段
     * @param joinsList
     * @param columns
     */
    public static void getJoinColumns(List<Join> joinsList, Set<String> columns){
        if(0 != joinsList.size()){
            for(Join join : joinsList){
                Collection<Expression> onExpressions = join.getOnExpressions();
                for (Expression onExpression : onExpressions){
                    getEqualsExpressionColumns(onExpression, columns);
                }
                FromItem rightItem = join.getRightItem(); // join 里面是子查询
                if(rightItem instanceof SubSelect){
                    List<SelectBody> joinSubSelects = SQLParserUtil.getJoinSubSelects(join);
                    for (SelectBody subSelectBody : joinSubSelects) {
                        getSQLAllColumns(subSelectBody, columns);
                    }
                }
            }
        }
    }

    /**
     * 获取Where下所有字段
     * @param expression
     * @param columns
     */
    public static void getWhereColumns(Expression expression, Set<String> columns){
        if(expression instanceof AndExpression){ // 判断where后面的条件是否包含And
            Expression leftExpression = ((AndExpression) expression).getLeftExpression();   // And左侧表达式,一大堆
            Expression rightExpression = ((AndExpression) expression).getRightExpression(); // And右侧表达式,只有一个
            getExpressionColumns(rightExpression, columns);  // 判断右侧表达
            if(leftExpression instanceof AndExpression){ // 遍历左侧一大堆表达式
                getWhereColumns(leftExpression, columns);
            }
        } else { // 判断where后面的条件是否只有一个条件
            getExpressionColumns(expression, columns);
        }
    }

    /**
     * 解析SQL中的表达式
     * @param expression
     * @param columns
     */
    public static void getExpressionColumns(Expression expression, Set<String> columns){
        if(expression instanceof EqualsTo){ // 判断表达式是否为 =
            getEqualsExpressionColumns(expression, columns);
        } else if(expression instanceof GreaterThanEquals){ // 判断表达式是否为 >=
            getGreaterThanEqualsExpressionColumns(expression, columns);
        } else if(expression instanceof MinorThan){ // 判断表达式是否为 <
            getMinorThanExpressionColumns(expression, columns);
        }  else if(expression instanceof NotEqualsTo){ // 判断表达式是否为 <>
            getNotEqualsToExpressionColumns(expression, columns);
        } else if(expression instanceof GeometryDistance){ // 判断表达式是否为 <->
            getGeometryDistanceExpressionColumns(expression, columns);
        } else if(expression instanceof GreaterThan){ // 判断表达式是否为 >
            getGreaterThanExpressionColumns(expression, columns);
        } else if(expression instanceof MinorThanEquals){ // 判断表达式是否为 <=
            getMinorThanEqualsExpressionColumns(expression, columns);
        } else if(expression instanceof ExistsExpression){  // 判断表达式是否为 Exists
            getExsitsExpressionColumns(expression, columns);
        } else if(expression instanceof InExpression){  // 判断表达式是否为 In
            getInExpressionColumns(expression, columns);
        } else if(expression instanceof Subtraction){  // 判断表达式是否为 -
            getSubtractionExpressionColumns(expression, columns);
        } else if(expression instanceof Addition){  // 判断表达式是否为 +
            getAdditionExpressionColumns(expression, columns);
        } else if(expression instanceof Function){  // 判断表达式是否为 函数
            getFunctionExpressionColumns(expression, columns);
        } else if(expression instanceof BitwiseAnd){  // 判断表达式是否为 +
            getBitwiseAndExpressionColumns(expression, columns);
        } else if(expression instanceof BitwiseLeftShift){  // 判断表达式是否为 <<
            getBitwiseLeftShiftExpressionColumns(expression, columns);
        } else if(expression instanceof BitwiseOr){  // 判断表达式是否为 |
            getBitwiseOrExpressionColumns(expression, columns);
        } else if(expression instanceof BitwiseRightShift){  // 判断表达式是否为 >>
            getBitwiseRightShiftExpressionColumns(expression, columns);
        } else if(expression instanceof BitwiseXor){  // 判断表达式是否为 ^
            getBitwiseRightShiftExpressionColumns(expression, columns);
        } else if(expression instanceof Concat){  // 判断表达式是否为 ||
            getConcatExpressionColumns(expression, columns);
        } else if(expression instanceof Division){  // 判断表达式是否为 /
            getDivisionExpressionColumns(expression, columns);
        } else if(expression instanceof Modulo){  // 判断表达式是否为 %
            getModuloExpressionColumns(expression, columns);
        } else if(expression instanceof Multiplication){  // 判断表达式是否为 *
            getMultiplicationExpressionColumns(expression, columns);
        } else if(expression instanceof Column){  // 判断表达式是否为 字段
            getColumns(expression, columns);
        }
    }

    /**
     * 解析 * 左右两侧
     * @param expression
     * @param columns
     */
    private static void getMultiplicationExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof Multiplication){
            Expression leftExpression = ((Multiplication) expression).getLeftExpression();
            Expression rightExpression = ((Multiplication) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }

    /**
     * 解析 % 左右两侧
     * @param expression
     * @param columns
     */
    private static void getModuloExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof Modulo){
            Expression leftExpression = ((Modulo) expression).getLeftExpression();
            Expression rightExpression = ((Modulo) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }

    /**
     * 解析 / 左右两侧
     * @param expression
     * @param columns
     */
    private static void getDivisionExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof Division){
            Expression leftExpression = ((Division) expression).getLeftExpression();
            Expression rightExpression = ((Division) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }

    /**
     * 解析 || 左右两侧
     * @param expression
     * @param columns
     */
    private static void getConcatExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof Concat){
            Expression leftExpression = ((Concat) expression).getLeftExpression();
            Expression rightExpression = ((Concat) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }


    /**
     * 解析 ^ 左右两侧
     * @param expression
     * @param columns
     */
    private static void getBitwiseXorExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof BitwiseXor){
            Expression leftExpression = ((BitwiseXor) expression).getLeftExpression();
            Expression rightExpression = ((BitwiseXor) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }

    /**
     * 解析 >> 左右两侧
     * @param expression
     * @param columns
     */
    private static void getBitwiseRightShiftExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof BitwiseRightShift){
            Expression leftExpression = ((BitwiseRightShift) expression).getLeftExpression();
            Expression rightExpression = ((BitwiseRightShift) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }

    /**
     * 解析 | 左右两侧
     * @param expression
     * @param columns
     */
    private static void getBitwiseOrExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof BitwiseOr){
            Expression leftExpression = ((BitwiseOr) expression).getLeftExpression();
            Expression rightExpression = ((BitwiseOr) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }

    /**
     * 解析 << 左右两侧
     * @param expression
     * @param columns
     */
    private static void getBitwiseLeftShiftExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof BitwiseLeftShift){
            Expression leftExpression = ((BitwiseLeftShift) expression).getLeftExpression();
            Expression rightExpression = ((BitwiseLeftShift) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }

    /**
     * 解析 & 左右两侧
     * @param expression
     * @param columns
     */
    private static void getBitwiseAndExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof BitwiseAnd){
            Expression leftExpression = ((BitwiseAnd) expression).getLeftExpression();
            Expression rightExpression = ((BitwiseAnd) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }

    /**
     * 解析 - 左右两侧
     * @param expression
     * @param columns
     */
    private static void getAdditionExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof Addition){
            Expression leftExpression = ((Addition) expression).getLeftExpression();
            Expression rightExpression = ((Addition) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }

    /**
     * 解析函数内字段
     * @param expression
     * @param columns
     */
    private static void getFunctionExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof Function){
            ExpressionList expressionList = ((Function) expression).getParameters();
            List<Expression> expressions = expressionList.getExpressions();
            for(Expression colExpression : expressions){
                getExpressionColumns(colExpression, columns);
            }
        }
    }

    /**
     * 解析 - 左右两侧
     * @param expression
     * @param columns
     */
    private static void getSubtractionExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof Subtraction){
            Expression leftExpression = ((Subtraction) expression).getLeftExpression();
            Expression rightExpression = ((Subtraction) expression).getRightExpression();
            getExpressionColumns(rightExpression, columns);
            getExpressionColumns(leftExpression, columns);
        }
    }

    /**
     * 解析 <= 左右两侧
     * @param expression
     * @param columns
     */
    private static void getMinorThanEqualsExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof MinorThanEquals){
            Expression leftExpression = ((MinorThanEquals) expression).getLeftExpression();
            Expression rightExpression = ((MinorThanEquals) expression).getRightExpression();
            getExpressionColumns(leftExpression, columns);
            getExpressionColumns(rightExpression, columns);
        }
    }

    /**
     * 解析 <-> 左右两侧
     * @param expression
     * @param columns
     */
    private static void getGeometryDistanceExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof GeometryDistance){
            Expression leftExpression = ((GeometryDistance) expression).getLeftExpression();
            Expression rightExpression = ((GeometryDistance) expression).getRightExpression();
            getExpressionColumns(leftExpression, columns);
            getExpressionColumns(rightExpression, columns);
        }
    }

    /**
     * 解析 > 左右两侧
     * @param expression
     * @param columns
     */
    private static void getGreaterThanExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof GreaterThan){
            Expression leftExpression = ((GreaterThan) expression).getLeftExpression();
            Expression rightExpression = ((GreaterThan) expression).getRightExpression();
            getExpressionColumns(leftExpression, columns);
            getExpressionColumns(rightExpression, columns);
        }
    }

    /**
     * 解析 <> 左右两侧
     * @param expression
     * @param columns
     */
    private static void getNotEqualsToExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof NotEqualsTo){
            Expression leftExpression = ((NotEqualsTo) expression).getLeftExpression();
            Expression rightExpression = ((NotEqualsTo) expression).getRightExpression();
            getExpressionColumns(leftExpression, columns);
            getExpressionColumns(rightExpression, columns);
        }
    }

    /**
     * 解析 < 左右两侧
     * @param expression
     * @param columns
     */
    private static void getMinorThanExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof MinorThan){
            Expression leftExpression = ((MinorThan) expression).getLeftExpression();
            Expression rightExpression = ((MinorThan) expression).getRightExpression();
            getExpressionColumns(leftExpression, columns);
            getExpressionColumns(rightExpression, columns);
        }
    }

    /**
     * 解析 >= 左右两侧
     * @param expression
     * @param columns
     */
    private static void getGreaterThanEqualsExpressionColumns(Expression expression, Set<String> columns) {
        if(expression instanceof GreaterThanEquals){
            Expression leftExpression = ((GreaterThanEquals) expression).getLeftExpression();
            Expression rightExpression = ((GreaterThanEquals) expression).getRightExpression();
            getExpressionColumns(leftExpression, columns);
            getExpressionColumns(rightExpression, columns);
        }
    }

    /**
     * 解析 In 左右两侧
     * @param expression
     * @param columns
     */
    public static void getInExpressionColumns(Expression expression, Set<String> columns){
        if(expression instanceof InExpression) {
            Expression InLeftExpression = ((InExpression) expression).getLeftExpression();
            Expression InRightExpression = ((InExpression) expression).getRightExpression();
            if (InLeftExpression instanceof Column) {  // 解析In左侧的字段
                String columnName = ((Column) InLeftExpression).getColumnName();
                columns.add(columnName);
            }
            if (InRightExpression instanceof SubSelect) {  // 判断In 中是否为子查询
                SelectBody selectBody = ((SubSelect) InRightExpression).getSelectBody();
                getSQLAllColumns(selectBody, columns);
            }
        }
    }

    /**
     * 解析 Exsits 左右两侧
     * @param expression
     * @param columns
     */
    public static void getExsitsExpressionColumns(Expression expression, Set<String> columns){
        if(expression instanceof ExistsExpression) {
            Expression ExsitsRightExpression = ((ExistsExpression) expression).getRightExpression();
            if (ExsitsRightExpression instanceof SubSelect) {
                SelectBody selectBody = ((SubSelect) ExsitsRightExpression).getSelectBody();
                getSQLAllColumns(selectBody, columns);
            }
        }
    }

    /**
     * 解析 = 左右两侧
     * @param expression
     * @param columns
     */
    public static void getEqualsExpressionColumns(Expression expression, Set<String> columns){
        if(expression instanceof EqualsTo){
            Expression leftExpression = ((EqualsTo) expression).getLeftExpression();
            Expression rightExpression = ((EqualsTo) expression).getRightExpression();
            getExpressionColumns(leftExpression, columns);
            getExpressionColumns(rightExpression, columns);
        }
    }

    /**
     * 解析字段
     * @param expression
     * @param columns
     */
    public static void getColumns(Expression expression, Set<String> columns){
        if(expression instanceof Column){ // 将字段取出
            String columnName = ((Column) expression).getColumnName();
            columns.add(columnName);
        }
    }

     * 获取Join中的子查询
     * @return
     */
    public static List<SelectBody> getJoinSubSelects(Join join){
        FromItem rightItem = join.getRightItem();
        List<SelectBody> subSelects = null;
        if(rightItem instanceof SubSelect){
            SelectBody subSelectBody = ((SubSelect) rightItem).getSelectBody();
            if(subSelectBody instanceof SetOperationList){
                subSelects = ((SetOperationList) subSelectBody).getSelects();
            } else {
                subSelects = new ArrayList<>();
                subSelects.add(subSelectBody);
            }
        }
        return subSelects;
    }
 

 

标签:instanceof,expression,所有,param,获取,getExpressionColumns,SQL,Expression,columns
From: https://www.cnblogs.com/chuijingjing/p/16867008.html

相关文章

  • MS Sql 连接字符串
    WebApp访问Docker容器中的连接字符串:"ConnectionStrings":{"DockerConnection":"Server=host.docker.internal,1433;uid=sa;pwd=Adminxyz22#;database=Aps;M......
  • php获取当前时间最近的整数时间
    比如当前时间是03那么获取应该是05,如果是07获取应该是10//当前时间的分钟数2020-07-2515:07:36$time=date('i');//最近的5分钟就除以5,同理10分钟就除10$aa=(i......
  • 配置master无密码登陆所有的slave
    1.SSH无密码原理master(NameNode|JobTracker)作为客户端,要实现无密码公钥认证,连接到服务器salve(DataNode|Tasktracker)上时,需要在master上生成一个密钥对,包括一个公钥和一......
  • java 获取IP地址 无法获取到真实的IP地址springboot 获取访问接口的请求的IP地址
    工具类:springboot获取访问接口的请求的IP地址问题:无法获取到真实IP地址  获取出来全是 192.xxx.xxx.xxx开头或者 172.xxx.xxx.xxx 开头 解决方案:nginx代理需......
  • SQL刷题 力扣
    力扣584.寻找用户推荐人:selectnamefromcustomerwherereferee_id!=2orreferee_idisnotNULL;null值无法与确定的值作比较,用isNULL或者isnotNULL判断 ......
  • web前端开发中获取数据失败回调函数弹出提示的重要性
    前几天下班后跟踪一个系统的投产,测试环境和准生产环境都上了,确定没有问题,但是投产后,系统老是不请求数据渲染页面。先是排查后端那边部署的文件是否齐全,打印日志发现后端的......
  • Python获取MySQL的创建表sql到文件中
    importosimportpymysqlpath=r'D:\Projects\sql'ifnotos.path.isdir(path):os.mkdir(path)conn=pymysql.connect(host='192.168.1.17',user='root',......
  • Flask-SQLAlchemy
    认识Flask-SQLAlchemyFlask-SQLAlchemy是一个为Flask应用增加SQLAlchemy支持的扩展。它致力于简化在Flask中SQLAlchemy的使用。SQLAlchemy是目前python中最......
  • MySQL_流程控制_分支结构
    1If结构功能:实现简单的双分支语法:IF(表达式1,表达式2,表达式3)执行顺序:如果表达式1成立,则IF函数返回表达式2的值,否则返回表达式3的值应用:任何地方 2case结构情况1......
  • 在MAC上安装mysql
    安装Mysql使用可视化界面安装方式。1、登录Mysql的官网,mysql下载的官网2、下载“社区版的Mysql”,点击下图的MysqlCommunity(GPL)Downloads。 3、然后选择MysqlCom......