SQL表达式转Java对象：单正则替换多正则的优化咨询

我开发了一个转换器，用来把SQL SELECT子句（不含SELECT和FROM关键字）转换成Java的AliasObj对象。输入是带AS别名的SQL表达式（比如case when、cast这类），需要从中提取表达式部分和别名部分。目前代码用了两个不同的正则表达式实现，希望重构为单一正则表达式，同时获取代码优化的可行方案和建议。

输入示例 #

1. case when nvl(document_type_name,'') in ('-','') then 1 else 0 end as err_status
2. 'nationkey' as nationkey, 'name' as country
3. cast(attr1 as string) as alias1, cast int(*) as alias2
4. 'fp_purpouse_criteria_id' AS fp_purpouse_criteria_id, 'fp_purpouse_criteria_name' AS fp_purpouse_criteria_name, 'fp_purpouse_criteria_version_id' AS fp_purpouse_criteria_version_id, 'dwh_unp_epbs_fp_id' AS dwh_unp_epbs_fp_id, 'dwh_fp_purposecriteria_level_id' AS dwh_fp_purposecriteria_level_id, 'dwh_unp_mu_fp_purposes_parent_id' AS dwh_unp_mu_fp_purposes_parent_id, 'fed_project_id' AS fed_project_id, 'fp_purposecriteria_level_id' AS fp_purposecriteria_level_id

预期输出 #

[AliasExpr(alias=err_status, expr=case when nvl(document_type_name,'') in ('-','') then 1 else 0 end)]
[AliasExpr(alias=nationkey, expr='nationkey'), AliasExpr(alias=country, expr='name')]
[AliasExpr(alias=alias1, expr=cast(attr1 as string)), AliasExpr(alias=alias2, expr=cast int(*))]
[AliasExpr(alias=fp_purpouse_criteria_id, expr='fp_purpouse_criteria_id'), AliasExpr(alias=fp_purpouse_criteria_name, expr='fp_purpouse_criteria_name'), AliasExpr(alias=fp_purpouse_criteria_version_id, expr='fp_purpouse_criteria_version_id'), AliasExpr(alias=dwh_unp_epbs_fp_id, expr='dwh_unp_epbs_fp_id'), AliasExpr(alias=dwh_fp_purposecriteria_level_id, expr='dwh_fp_purposecriteria_level_id'), AliasExpr(alias=dwh_unp_mu_fp_purposes_parent_id, expr='dwh_unp_mu_fp_purposes_parent_id'), AliasExpr(alias=fed_project_id, expr='fed_project_id'), AliasExpr(alias=fp_purposecriteria_level_id, expr='fp_purposecriteria_level_id')]

现有代码 #

String input1 = "case when nvl(document_type_name,'') in ('-','') then 1 else 0 end as err_status".toLowerCase();
String input2 = "'nationkey' as nationkey, 'name' as country".toLowerCase();
String input3 = "cast(attr1 as string) as alias1, cast int(*) as alias2".toLowerCase();
String input4 = "'fp_purpouse_criteria_id' AS fp_purpouse_criteria_id, 'fp_purpouse_criteria_name' AS fp_purpouse_criteria_name, 'fp_purpouse_criteria_version_id' AS fp_purpouse_criteria_version_id, 'dwh_unp_epbs_fp_id' AS dwh_unp_epbs_fp_id, 'dwh_fp_purposecriteria_level_id' AS dwh_fp_purposecriteria_level_id, 'dwh_unp_mu_fp_purposes_parent_id' AS dwh_unp_mu_fp_purposes_parent_id, 'fed_project_id' AS fed_project_id, 'fp_purposecriteria_level_id' AS fp_purposecriteria_level_id".toLowerCase();

public static List<AliasExpr> converter(String input) {
    List<AliasExpr> alias = new ArrayList<>();
    Pattern pattern = Pattern.compile("\\s+as\\s+(?![^(]*\\))(\\w+)");
    Matcher matcher = pattern.matcher(input);
    if (matcher.find()) {
        List<String> split = Arrays.asList(input.split(String.valueOf(pattern)));

        if (split.size() > 1) {
            Pattern multiple_regex = Pattern.compile("(?i)(.+?)\\s+AS\\s+(\\w+)");
            Matcher matcher1 = multiple_regex.matcher(input);
            while (matcher1.find()) {
                alias.add(
                        new AliasExpr(
                                matcher1.group(2).replaceAll("^,|,$", "").trim(),
                                matcher1.group(1).replaceAll("^,|,$", "").trim()));
            }

        } else {
            String firstPart = input.substring(0, matcher.start()).trim();
            String secondPart = matcher.group(1);
            alias.add(new AliasExpr(
                    secondPart.replaceAll("^,|,$", "").trim(),
                    firstPart.replaceAll("^,|,$", "").trim()));
        }
    }
    return alias;
}

优化方案与实现 #

1. 单一正则表达式设计 #

核心思路是匹配完整的表达式 AS 别名单元，同时规避括号内的AS、引号内的逗号干扰，兼容多单元逗号分隔场景：

(?<!,)\s*((?:[^,'()]|'[^']*'|\([^()]*\))+?)\s+(?i)as\s+(\w+)(?:\s*,|$)

正则解释：

• (?<!,)\s*：跳过逗号后的空格，确保匹配的单元是独立的
• ((?:[^,'()]|'[^']*'|\([^()]*\))+?)：匹配表达式部分，支持单引号字符串、括号嵌套内容，非贪婪匹配避免过度延伸
• \s+(?i)as\s+：匹配不区分大小写的AS，前后允许任意空格
• (\w+)：匹配别名（假设别名由单词字符组成）
• (?:\s*,|$)：匹配单元结尾的逗号或字符串结束，不捕获该部分

2. 重构后的代码 #

// 预编译正则，提升重复调用性能
private static final Pattern ALIAS_PATTERN = Pattern.compile("(?<!,)\\s*((?:[^,'()]|'[^']*'|\\([^()]*\\))+?)\\s+(?i)as\\s+(\\w+)(?:\\s*,|$)");

public static List<AliasExpr> converter(String input) {
    List<AliasExpr> aliasList = new ArrayList<>();
    if (input == null || input.isBlank()) {
        return aliasList;
    }
    
    Matcher matcher = ALIAS_PATTERN.matcher(input.trim());
    while (matcher.find()) {
        String expr = matcher.group(1).trim();
        String alias = matcher.group(2).trim();
        aliasList.add(new AliasExpr(alias, expr));
    }
    return aliasList;
}

3. 代码优化建议 #

• 正则预编译：将Pattern定义为类静态常量，避免每次调用重复编译正则，提升性能
• 空输入防御：添加输入为空/空白的判断，直接返回空列表，避免无意义的匹配操作
• 鲁棒性增强：可添加别名合法性校验（比如是否符合Java标识符规则）、表达式非空校验，避免无效数据进入结果
• 语法兼容性扩展：如果需要支持带特殊字符的别名（比如双引号包裹的别名），可将(\w+)修改为([^\s,]+)，同时结合SQL语法调整匹配规则
• 测试覆盖：针对嵌套括号的case when、带特殊字符的字符串、别名含下划线、逗号分隔前后空格变化等边界场景编写测试用例，确保转换器稳定性

内容的提问来源于stack exchange，提问作者Nina Fidarova