纯净、安全、绿色的下载网站

首页|软件分类|下载排行|最新软件|IT学院

当前位置:首页IT学院IT技术

sql字段解析器 sql字段解析器的实现示例

等你归去来   2021-06-23 我要评论
想了解sql字段解析器的实现示例的相关内容吗等你归去来在本文为您仔细讲解sql字段解析器的相关知识和一些Code实例欢迎阅读和指正我们先划重点:sql字段解析器下面大家一起来学习吧

用例:有一段sql语句我们需要从中截取出所有字段部分以便进行后续的类型推断或者别名字段抽取定义请给出此解析方法

想来很简单吧因为 sql 中的字段列表使用方式有限比如 a as b, a, a b...

1. 解题思路

  如果不想做复杂处理最容易想到的就是直接用某个特征做分割即可比如先截取出 字段列表部分然后再用逗号',' 分割就可以得到一个个的字段了然后再要细分其实只需要用 as 进行分割就可以了

  看起来好像可行但是存在许多漏洞首先这里面有太多的假设:各种截取部分要求必须符合要求必须没有多余的逗号必须要有as 等等这明显不符合要求了

  其二我们可以换一种转换方式比如先截取到field部分然后先以 as 分割再以逗号分割然后取最后一个词作为field

  看起来好像更差了截取到哪里已经完全不知道了即原文已经被破坏殆尽而且同样要求要有 as 转换标签而且对于函数觊觎有 as 的场景就完全错误了

  其三最好还是自行一个个单词地解析field 字段无外乎几种情况1. 普通字段如 select a; 2. 带as的普通字段如 select a as b; 3. 带函数的字段如 select coalesce(a, b); 4. 带函数且带as的字段如 select coalesce(a, b) ab; 5. 函数内带as的字段如 select cast(a as string) b; ...   我们只需依次枚举对应的情况就可以将字段解析出来了

  看起来是个不错的想法但是具体实现如何?

2. 具体解析实现

  主要分两个部分1. 需要定义一个解析后的结果数据结构以便清晰描述字段信息 2. 分词解析sql并以结构体返回

  我们先来看看整个算法核心:

/**
 * 功能描述: 简单sql字段解析器
 *
 *        样例如1:
 *          select COALESCE(t1.xno, t2.xno, t3.xno) as xno,
 *             case when t1.no is not null then 1 else null end as xxk001,
 *             case when t2.no is not null then 1 else null end as xxk200,
 *             case when t3.xno is not null then 1 else null end as xx3200
 *             from xxk001 t1
 *               full join xxkj100 t2 on t1.xno = t2.xno
 *               full join xxkj200 t3 on t1.xno = t3.xno;
 *
 *        样例如2:
 *          select cast(a as string) as b from ccc;
 *
 *        样例如3:
 *          with a as(select cus,x1 from b1), b as (select cus,x2 from b2)
 *              select a.cus as a_cus from a join b on a.cus=b.cus where xxx;
 *
 *        样例如4:
 *         select a.xno,b.xx from a_tb as a join b_tb as b on a.id = b.id
 *
 *        样例如5:
 *          select cast  \t(a as string) a_str, cc (a as double) a_double from x
 *
 */
public class SimpleSqlFieldParser {

    /**
     * 解析一段次标签sql 中的字段列表
     *
     * @param sql 原始sql, 需如 select xx from xxx join ... 格式
     * @return 字段列表
     */
    public static List<SelectFieldClauseDescriptor> parse(String sql) {
        String columnPart = adaptFieldPartSql(sql);
        int deep = 0;
        List<StringBuilder> fieldTokenSwap = new ArrayList<>();
        StringBuilder currentTokenBuilder = new StringBuilder();
        List<SelectFieldClauseDescriptor> fieldList = new ArrayList<>();
        fieldTokenSwap.add(currentTokenBuilder);
        int len = columnPart.length();
        char[] columnPartChars = columnPart.toCharArray();
        for(int i = 0; i < len; i++) {
            // 空格忽略换行忽略tab忽略
            // 字符串相接
            // 左(号入栈++deep
            // 右)号出栈--deep
            // deep>0 忽略所有其他直接拼接
            // as 则取下一个值为fieldName
            // case 则直接取到end为止
            //,号则重置token构建结果集
            char currentChar = columnPartChars[i];
            switch (currentChar) {
                case '(':
                    ++deep;
                    currentTokenBuilder.append(currentChar);
                    break;
                case ')':
                    --deep;
                    currentTokenBuilder.append(currentChar);
                    break;
                case ',':
                    if(deep == 0) {
                        addNewField(fieldList, fieldTokenSwap, true);
                        fieldTokenSwap = new ArrayList<>();
                        currentTokenBuilder = new StringBuilder();
                        fieldTokenSwap.add(currentTokenBuilder);
                        break;
                    }
                    currentTokenBuilder.append(currentChar);
                    break;
                case ' ':
                case '\t':
                case '\r':
                case '\n':
                    if(deep > 0) {
                        currentTokenBuilder.append(currentChar);
                        continue;
                    }
                    if(currentTokenBuilder.length() == 0) {
                        continue;
                    }
                    // original_name as   --> alias
                    if(i + 1 < len) {
                        int j = i + 1;
                        // 收集连续的空格
                        StringBuilder spaceHolder = new StringBuilder();
                        boolean isNextLeftBracket = false;
                        do {
                            char nextChar = columnPart.charAt(j++);
                            if(nextChar == ' ' || nextChar == '\t'
                                    || nextChar == '\r' || nextChar == '\n') {
                                spaceHolder.append(nextChar);
                                continue;
                            }
                            if(nextChar == '(') {
                                isNextLeftBracket = true;
                            }
                            break;
                        } while (j < len);
                        if(isNextLeftBracket) {
                            currentTokenBuilder.append(currentChar);
                        }
                        if(spaceHolder.length() > 0) {
                            currentTokenBuilder.append(spaceHolder);
                            i += spaceHolder.length();
                        }
                        if(isNextLeftBracket) {
                            // continue next for, function begin
                            continue;
                        }
                    }
                    if(fieldTokenSwap.size() == 1) {
                        if(fieldTokenSwap.get(0).toString().equalsIgnoreCase("case")) {
                            String caseWhenPart = CommonUtil.readSplitWord(
                                    columnPartChars, i, " ", "end");
                            currentTokenBuilder.append(caseWhenPart);
                            if(caseWhenPart.length() <= 0) {
                                throw new BizException("语法错误未找到case..when的结束符");
                            }
                            i += caseWhenPart.length();
                        }
                    }
                    addNewField(fieldList, fieldTokenSwap, false);
                    currentTokenBuilder = new StringBuilder();
                    fieldTokenSwap.add(currentTokenBuilder);
                    break;
                    // 空格忽略
                default:
                    currentTokenBuilder.append(currentChar);
                    break;
            }

        }
        // 处理剩余尚未存储的字段信息
        addNewField(fieldList, fieldTokenSwap, true);
        return fieldList;
    }

    /**
     * 新增一个字段描述
     *
     * @param fieldList 字段容器
     * @param fieldTokenSwap 候选词
     */
    private static void addNewField(List<SelectFieldClauseDescriptor> fieldList,
                                    List<StringBuilder> fieldTokenSwap,
                                    boolean forceAdd) {
        int ts = fieldTokenSwap.size();
        if(ts == 1 && forceAdd) {
            // db.original_name,
            String fieldName = fieldTokenSwap.get(0).toString();
            String alias = fieldName;
            if(fieldName.contains(".")) {
                alias = fieldName.substring(fieldName.lastIndexOf('.') + 1);
            }
            fieldList.add(new SelectFieldClauseDescriptor(fieldName, alias));
            return;
        }
        if(ts < 2) {
            return;
        }
        if(ts == 2) {
            // original_name alias,
            if(fieldTokenSwap.get(1).toString().equalsIgnoreCase("as")) {
                return;
            }
            fieldList.add(new SelectFieldClauseDescriptor(
                    fieldTokenSwap.get(0).toString(),
                    fieldTokenSwap.get(1).toString()));
        }
        else if(ts == 3) {
            // original_name as alias,
            fieldList.add(new SelectFieldClauseDescriptor(
                    fieldTokenSwap.get(0).toString(),
                    fieldTokenSwap.get(2).toString()));
        }
        else {
            throw new BizException("字段语法解析错误超过3个以字段描述信息:" + ts);
        }
    }

    // 截取适配 field 字段信息部分
    private static String adaptFieldPartSql(String fullSql) {
        int start = fullSql.lastIndexOf("select ");
        int end = fullSql.lastIndexOf(" from");
        String columnPart = fullSql.substring(start + "select ".length(), end);
        return columnPart.trim();
    }

}

  应该说是比较简单的一个for, 一个 switch 就搞定了其他的更多的是逻辑判定

  下面我们来看看字段描述类的写法其实就是两个字段源字段和别名

/**
 * 功能描述: sql字段描述 select 字段描述类
 *
 */
public class SelectFieldClauseDescriptor {
    private String fieldName;
    private String alias;

    public SelectFieldClauseDescriptor(String fieldName, String alias) {
        this.fieldName = fieldName;
        this.alias = alias;
    }

    public String getFieldName() {
        return fieldName;
    }

    public String getAlias() {
        return alias;
    }


    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        SelectFieldClauseDescriptor that = (SelectFieldClauseDescriptor) o;
        return Objects.equals(fieldName, that.fieldName) &&
                Objects.equals(alias, that.alias);
    }

    @Override
    public int hashCode() {
        return Objects.hash(fieldName, alias);
    }

    @Override
    public String toString() {
        return "SelectFieldClauseDescriptor{" +
                "fieldName='" + fieldName + '\'' +
                ", alias='" + alias + '\'' +
                '}';
    }
}

它存在的意义仅仅是为了使用方更方便取值以为更进一步的解析提供了依据

3. 单元测试

  其实像写这种工具类单元测试最是方便简单因为最初的结果我们早已预料以测试驱动开发最合适不过了而且基本上一出现不符合预期的值时很快速就定位问题了

/**
 * 功能描述: sql字段解析器测试
 **/
public class SimpleSqlFieldParserTest {

    @Test
    public void testParse() {
        String sql;
        List<SelectFieldClauseDescriptor> parsedFieldList;
        sql = "select COALESCE(t1.xno, t2.xno, t3.xno) as xno,\n" +
                "   case when t1.xno is not null then 1 else null end as xxk001,\n" +
                "   case when t2.xno is not null then 1 else null end as xxk200,\n" +
                "   case when t3.xno is not null then 1 else null end as xx3200\n" +
                "   from xxk001 t1\n" +
                "     full join xxkj100 t2 on t1.xno = t2.xno\n" +
                "     full join xxkj200 t3 on t1.xno = t3.xno;";
        parsedFieldList = SimpleSqlFieldParser.parse(sql);
        System.out.println("result:");
        parsedFieldList.forEach(System.out::println);
        Assert.assertEquals("字段个数解析不正确",
                4, parsedFieldList.size());
        Assert.assertEquals("字段别名解析不正确",
                "xno", parsedFieldList.get(0).getAlias());
        Assert.assertEquals("字段别名解析不正确",
                "xx3200", parsedFieldList.get(3).getAlias());

        sql = "select cast(a as string) as b from ccc;";
        parsedFieldList = SimpleSqlFieldParser.parse(sql);
        System.out.println("result:");
        parsedFieldList.forEach(System.out::println);
        Assert.assertEquals("字段个数解析不正确",
                1, parsedFieldList.size());
        Assert.assertEquals("字段别名解析不正确",
                "b", parsedFieldList.get(0).getAlias());

        sql = "with a as(select cus,x1 from b1), b as (select cus,x2 from b2)\n" +
                "    select a.cus as a_cus, cast(a \nas string) as a_cus2, " +
                "b.x2 b2 from a join b on a.cus=b.cus where xxx;";
        parsedFieldList = SimpleSqlFieldParser.parse(sql);
        System.out.println("result:");
        parsedFieldList.forEach(System.out::println);
        Assert.assertEquals("字段个数解析不正确",
                3, parsedFieldList.size());
        Assert.assertEquals("字段别名解析不正确",
                "a_cus", parsedFieldList.get(0).getAlias());
        Assert.assertEquals("字段别名解析不正确",
                "b2", parsedFieldList.get(2).getAlias());

        sql = "select a.xno,b.xx,qqq from a_tb as a join b_tb as b on a.id = b.id";
        parsedFieldList = SimpleSqlFieldParser.parse(sql);
        System.out.println("result:");
        parsedFieldList.forEach(System.out::println);
        Assert.assertEquals("字段个数解析不正确",
                3, parsedFieldList.size());
        Assert.assertEquals("字段别名解析不正确",
                "xno", parsedFieldList.get(0).getAlias());
        Assert.assertEquals("字段别名解析不正确",
                "qqq", parsedFieldList.get(2).getAlias());

        sql = "select cast (a.a_int as string) a_str, b.xx, coalesce  \n( a, b, c) qqq from a_tb as a join b_tb as b on a.id = b.id";
        parsedFieldList = SimpleSqlFieldParser.parse(sql);
        System.out.println("result:");
        parsedFieldList.forEach(System.out::println);
        Assert.assertEquals("字段个数解析不正确",
                3, parsedFieldList.size());
        Assert.assertEquals("字段别名解析不正确",
                "a_str", parsedFieldList.get(0).getAlias());
        Assert.assertEquals("字段原始名解析不正确",
                "cast (a.a_int as string)", parsedFieldList.get(0).getFieldName());
        Assert.assertEquals("字段别名解析不正确",
                "qqq", parsedFieldList.get(2).getAlias());
        Assert.assertEquals("字段原始名解析不正确",
                "coalesce  \n( a, b, c)", parsedFieldList.get(2).getFieldName());
    }
}

至此一个简单的字段解析器完成小工具供参考!


相关文章

猜您喜欢

  • OpenCV直线检测并消除 OpenCV实现直线检测并消除

    想了解OpenCV实现直线检测并消除的相关内容吗Leonwenbin在本文为您仔细讲解OpenCV直线检测并消除的相关知识和一些Code实例欢迎阅读和指正我们先划重点:OpenCV直线检测,OpenCV直线消除,OpenCV检测消除下面大家一起来学习吧..
  • springBoot访问静态资源 springBoot快速访问工程目录下的静态资源

    想了解springBoot快速访问工程目录下的静态资源的相关内容吗Dkwestworld在本文为您仔细讲解springBoot访问静态资源的相关知识和一些Code实例欢迎阅读和指正我们先划重点:springBoot访问静态资源,springBoot目录静态资源下面大家一起来学习吧..

网友评论

Copyright 2020 www.fresh-weather.com 【世纪下载站】 版权所有 软件发布

声明:所有软件和文章来自软件开发商或者作者 如有异议 请与本站联系 点此查看联系方式