java正则替换sql中的参数实例代码

2024-04-02 19:04:59 240人浏览八月长安

Python 官方文档：入门教程 => 点击学习

摘要

目录前言要求：分析：代码：测试：总结：前言在处理sql参数的时候，替换圆括号里面只处理了一种情况。而没有从整体上进行处理！！！这是一个思考问题上严重的偏向。考虑问题时候，要先从

前言

在处理sql参数的时候，替换圆括号里面只处理了一种情况。而没有从整体上进行处理！！！

这是一个思考问题上严重的偏向。考虑问题时候，要先从整体开始考虑，逐步分解，再细分到局部！

要求：

替换sql里面的参数：

SELECT a.table_id, a.table_title, a.table_name, a.table_serial, b.STAFF_NAME , 
b.REGioN_NAME,  b.operatingsystem, b.renduoxuan 
FROM table_basic a, table_177 b WHERE a.table_id = b.table_id 
AND a.table_serial in ($fORMSerial) and  a.create_date >= $createStartDate
 AND a.create_date <= $createEndDate  or a.state like '%$state%' 
AND b.tickettitle in ($tickettitle) 
GROUP BY a.table_id ORDER BY state_date DESC

参数：

Map<String, Object> sqlParam = new HashMap<>();
sqlParam.put("state", "A,F");
sqlParam.put("tableSerial", "COM, tt");

对于参数里面有的值进行替换，没有的值，替换为空。

分析：

sql 里面支持大小写，替换的时候，要处理大小写。

常见的sql条件里面有： where、in、or、like

如果where 后面的条件都为空设置为 1 = 1

\s	匹配任何空白字符，包括空格、制表符、换页符等等。等价于 [ \f\n\r\t\v]。

前面条件的正则：

\\s+(where|WHERE|and|AND|or|OR)(\\s+[a-zA-Z0-9_.]+)(\\s*(IN|in|=|>=|>|<|<=|like|LIKE)+)

 String condition = "\\s+(where|WHERE|and|AND|or|OR)(\\s+[a-zA-Z0-9_.]+)(\\s*(IN|in|=|>=|>|<|<=|like|LIKE)+)";

后面条件的正则：

带like: \s*$*'%\$([a-zA-Z_.]*)%'$*

其它： \s*$*\$([a-zA-Z_.]*)$*

两个不好合并在一起处理，就分开来。

代码：

    public static String replaceConditionStr(String content, Map<String, Object> param) {
        String likeCondition = "\\s*\\(*\'%\\$([a-zA-Z_.]*)%\'\\)*";
        content = replaceOtherConditionStr(content, param, likeCondition);
        String otherCondition = "\\s*\\(*\\$([a-zA-Z_.]*)\\)*";
        return replaceOtherConditionStr(content, param, otherCondition);
    }
 
    public static String replaceOtherConditionStr(String content, Map<String, Object> param, String paramConfition) {
        String condition = "\\s+(where|WHERE|and|AND|or|OR)(\\s+[a-zA-Z0-9_.]+)(\\s*(IN|in|=|>=|>|<|<=|like|LIKE)+)";
        String pattern = condition + paramConfition;
        Pattern p = Pattern.compile(pattern);
        Matcher m = p.matcher(content);
        while (m.find()) {
            String group = m.group();
            String operator = m.group(4);
            String key = m.group(5);
            if (key.contains(".")) {
                key = StringUtils.substringAfter(key, ".");
            }
            String value = MapUtils.getString(param, key);
            if (StringUtils.isBlank(value)) {
                if (group.contains("WHERE") || group.contains("where")) {
                    content = content.replace(group, " where 1 = 1 ");
                }
                content = content.replace(group, "");
            } else {
                if ("like".equals(operator) || "LIKE".equals(operator)) {
                    if (!group.contains("%")) {
                        value = ("\'%").concat(value).concat("%\'");
                    }
                } else {
                    if ("in".equals(operator) || "IN".equals(operator)) {
                        List<String> valueArr = Arrays.asList(value.split(","));
                        value = ListUtils.emptyIfNull(valueArr).stream().map(s -> "\'" + s.trim() + "\'")
                                .collect(Collectors.joining(","));
                    } else {
                        value = ("\'").concat(value).concat("\'");
                    }
 
                }
 
                content = content.replace("$" + key, value);
            }
        }
        return content;
    }

测试：

public static void main(String[] args) {
        testSqlEmpty();
        testSqlReplace();
    }
 
    public static void testSqlEmpty() {
        String sqlContent = "SELECT a.table_id, a.table_title, a.table_name, a.table_serial, b.STAFF_NAME , b.REGION_NAME, "
                + " b.operatingsystem, b.renduoxuan FROM table_basic a, table_177 b WHERE "
                + " a.table_serial in ($formSerial) and  a.create_date >= $createStartDate "
                + "AND a.create_date <= $createEndDate  or a.state like '%$state%' " +
                "AND b.tickettitle in ($tickettitle) GROUP BY a.table_id ORDER BY state_date DESC";
        Map<String, Object> sqlParam = new HashMap<>();
        System.out.println("empty param: "+replaceConditionStr(sqlContent, sqlParam));
    }
 
    public static void testSqlReplace() {
        String sqlContent = "SELECT a.table_id, a.table_title, a.table_name, a.table_serial, b.STAFF_NAME , b.REGION_NAME, "
                + " b.operatingsystem, b.renduoxuan FROM table_basic a, table_177 b WHERE a.table_id = b.table_id "
                + "AND a.table_serial in ($formSerial) and  a.create_date >= $createStartDate "
                + "AND a.create_date <= $createEndDate  or a.state like '%$state%' " +
                "AND b.tickettitle in ($tickettitle)  GROUP BY a.table_id ORDER BY state_date DESC";
        Map<String, Object> sqlParam = new HashMap<>();
        sqlParam.put("state", "A,F");
        sqlParam.put("tableSerial", "COM, tt");
        System.out.println("have param: "+replaceConditionStr(sqlContent, sqlParam));
    }

empty param: SELECT a.table_id, a.table_title, a.table_name, a.table_serial, b.STAFF_NAME , b.REGION_NAME,  b.operatingsystem, b.renduoxuan FROM table_basic a, table_177 b where 1 = 1  GROUP BY a.table_id ORDER BY state_date DESC
 
have param: SELECT a.table_id, a.table_title, a.table_name, a.table_serial, b.STAFF_NAME , b.REGION_NAME,  b.operatingsystem, b.renduoxuan FROM table_basic a, table_177 b WHERE a.table_id = b.table_id  or a.state like '%A,F%'  GROUP BY a.table_id ORDER BY state_date DESC