SQL语句解析器

小卡雷尔

已于 2024-02-01 09:18:07 修改

阅读量421

点赞数 9

文章标签： sql 数据库

于 2024-02-01 09:16:54 首次发布

本文链接：https://blog.csdn.net/littlekard/article/details/135966185

版权

该代码片段展示了如何使用正则表达式解析不包含嵌套的SQL语句，提取SELECT、FROM、WHERE、GROUPBY、ORDERBY和LIMIT部分。

摘要由CSDN通过智能技术生成

SQL语句结构解析：仅适用于不包含嵌套的普通sql语句

package com.xingtu.bi.common.util;

import org.apache.commons.lang3.StringUtils;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SQLParse {
    private String selectPart;
    private String fromPart;
    private String wherePart;
    private String groupByPart;
    private String orderByPart;
    private String limitPart;

    public SQLParse(String sql) {
        //正则表示，大小通配
        Pattern pattern = Pattern.compile(
                "SELECT (.+?) FROM (.+?)(?: WHERE (.*?))?(?: GROUP BY (.*?))?(?: ORDER BY (.*?))?(?: LIMIT (.*?))?$",
                Pattern.CASE_INSENSITIVE
        );
        sql = sql.replace("\n", " ");
        Matcher matcher = pattern.matcher(sql);

        if (matcher.find()) {
            selectPart = matcher.group(1);
            fromPart = matcher.group(2);
            wherePart = matcher.group(3);
            groupByPart = matcher.group(4);
            orderByPart = matcher.group(5);
            limitPart = matcher.group(6);
        }
    }

    public String getSelect() {
        //解析select项
        return "SELECT " + selectPart;
    }

    public String getFrom() {
        //解析from
        return " FROM " + fromPart;
    }

    public String getWhere() {
        //解析where

        if (StringUtils.isNotBlank(wherePart)) {
            return " WHERE " + wherePart + " ";
        } else {
            return "";
        }
    }

    //group by ... having
    public String getGroupBy() {
        if (StringUtils.isNotBlank(groupByPart)) {
            return " GROUP BY " + groupByPart + " ";
        } else {
            return "";
        }
    }

    //解析order by
    public String getOrderBy() {
        if (StringUtils.isNotBlank(orderByPart)) {
            return " ORDER BY " + orderByPart + " ";
        } else {
            return "";
        }
    }

    //limit
    public String getLimit() {
        if (StringUtils.isNotBlank(limitPart)) {
            return " LIMIT  " + limitPart + " ";
        } else {
            return "";
        }
    }

    public static void main(String[] args) {
        String sql = "SELECT\n" +
                "\tb.train_model 列车型号,\n" +
                "\ta.loco_no 列车编号,\n" +
                "\tsum( a.d1zqyzdlsjz )+ sum( a.d2zqyzdlsjz )+ sum( a.d3zqyzdlsjz )+ sum( a.d4zqyzdlsjz ) 牵引力和 \n" +
                "FROM\n" +
                "\t(\n" +
                "\tSELECT\n" +
                "\t\t* \n" +
                "\tFROM\n" +
                "\t\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tT_TYPE_ID,\n" +
                "\t\t\tloco_no,\n" +
                "\t\t\tab,\n" +
                "\t\t\tcast( d1zqyzdlsjz AS DOUBLE ) d1zqyzdlsjz,\n" +
                "\t\t\tcast( d2zqyzdlsjz AS DOUBLE ) d2zqyzdlsjz,\n" +
                "\t\t\tcast( d3zqyzdlsjz AS DOUBLE ) d3zqyzdlsjz,\n" +
                "\t\t\tcast( d4zqyzdlsjz AS DOUBLE ) d4zqyzdlsjz,\n" +
                "\t\t\tROW_NUMBER() over ( PARTITION BY T_TYPE_ID, loco_no, ab ORDER BY rqsj DESC ) px \n" +
                "\t\tFROM\n" +
                "\t\t\tPHM.DTHX_TCMS_FQ \n" +
                "\t\tWHERE\n" +
                "\t\tcj_date = to_char ( CURRENT_DATE - 1, 'yyyyMMdd' )) ORDER BY rqsj DESC\n" +
                "\tWHERE\n" +
                "\t\tpx = '1' \n" +
                "\t) a\n" +
                "\tLEFT JOIN phm.dthx_cxmb b ON a.t_type_id = b.t_type_id \n" +
                "GROUP BY\n" +
                "\tb.train_model,\n" +
                "\ta.loco_no";
        SQLParse sqlParse2 = new SQLParse(sql);
        System.out.println("Select:    " + sqlParse2.getSelect());
        System.out.println("From:    " + sqlParse2.getFrom());
        System.out.println("Where:     " + sqlParse2.getWhere());
        System.out.println("GroupBy:     " + sqlParse2.getGroupBy());
        System.out.println("OrderBy:     " + sqlParse2.getOrderBy());
        System.out.println("Limit:     " + sqlParse2.getLimit());
        System.out.println("sql:     " + sqlParse2.getSelect() + sqlParse2.getFrom() + sqlParse2.getWhere() + sqlParse2.getGroupBy() + sqlParse2.getOrderBy() + sqlParse2.getLimit());
        System.out.println("newSql:     " + sqlParse2.getSelect() + sqlParse2.getFrom() + sqlParse2.getWhere() + sqlParse2.getGroupBy() + sqlParse2.getOrderBy() + sqlParse2.getLimit());
    }
}

代码从项目中提取，原创链接找不到了，可联系备注 \(^o^)/~