java正則替換sql中的參數實例代碼

更新時間：2022年10月09日 11:39:45 作者：天狼1222

這篇文章主要給大家介紹了關于java正則替換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