快捷導(dǎo)航

java正則替換sql中的參數(shù)實(shí)例代碼

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

這篇文章主要給大家介紹了關(guān)于java正則替換sql中參數(shù)的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下

前言

在處理sql參數(shù)的時(shí)候，替換圓括號(hào)里面只處理了一種情況。而沒(méi)有從整體上進(jìn)行處理?。?！

這是一個(gè)思考問(wèn)題上嚴(yán)重的偏向。考慮問(wèn)題時(shí)候，要先從整體開(kāi)始考慮，逐步分解，再細(xì)分到局部！

要求：

替換sql里面的參數(shù)：

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

參數(shù)：

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

對(duì)于參數(shù)里面有的值進(jìn)行替換，沒(méi)有的值，替換為空。

分析：

sql 里面支持大小寫(xiě)，替換的時(shí)候，要處理大小寫(xiě)。

常見(jiàn)的sql條件里面有： where、in、or、like

如果where 后面的條件都為空設(shè)置為 1 = 1

\s	匹配任何空白字符，包括空格、制表符、換頁(yè)符等等。等價(jià)于 [ \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_.]*)$*

兩個(gè)不好合并在一起處理，就分開(kāi)來(lái)。

代碼：

    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;
    }

測(cè)試：

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