将SQL中的占位符替换成参数

描述

此方法是将SQL中的${}或#{}替换为直接拼接到SQL中或直接替换为?的形式。具体详情看下面代码。

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author HuYu
 * @date 2023-09-21
 * @since 1.0
 **/
public class Test {
    public static void main(String[] args) throws Exception {
        String sql = "SELECT * FROM t_user WHERE id = #{id} AND username =#{username} GROUP BY username ORDER BY id ;";
        Map<String, Object> params = new HashMap<>();
        params.put("id", 1);
        params.put("username", "admin");
        Map<String, Object> map = replaceSqlPlaceholder(sql, "#{", "}", params);
        System.out.println(map);

    }


    /**
     * 功能描述：将带有占位符的SQL解析成可执行SQL,openToken传 #{ 则以?占位符方式,openToken传${则采用字符串拼接的方式,
     * 如果 params 中的参数是数值类型会直接拼接,如果是字符串类型,会填充为 'params.get(key)' 的模式。
     * @param sql        sql字符串
     * @param openToken  开始标记token 只能是#{或${
     * @param closeToken 结束标记 }
     * @param params     参数map,比如 sql中包含 #{id} ，那么会从该map中查找 key为id的值填充到SQL中作为参数
     * @return java.util.Map<java.lang.String, java.lang.Object>
     * @author compass
     * @date 2024/7/21 12:16
     * @since 1.0
     **/
    public static Map<String, Object> replaceSqlPlaceholder(String sql, String openToken, String closeToken, Map<String, Object> params) {

        Map<String, Object> resultMap = new HashMap<>();
        String fill = "#";
        String placeholder = "$";
        String type = openToken.contains(fill) ? fill : placeholder;


        if (sql==null || sql.trim().length()<=0){
            throw  new IllegalArgumentException("sql 不能为空!");
        }
        
        if (!sql.contains("$") || !sql.contains("#")){
            resultMap.put("sql",sql);
            return resultMap;
        }
        
        if ( (sql.contains("$") || sql.contains("#") )&& (params == null || params.size() <= 0)){
            throw  new IllegalArgumentException("发现SQL中存在占位符 但是 params 中未发现填充参数!");
        }
        
        if (!Arrays.asList("#{","${").contains(openToken)){
            throw  new IllegalArgumentException("openToken 只能是 #{或 ${!");
        }

        if (!"}".equals(closeToken)){
            throw  new IllegalArgumentException("closeToken 只能是}!");
        }

        char[] openTokeChars = openToken.toCharArray();
        List<Object> dataList = new ArrayList<>();
        String regx = openTokeChars[0] + "\\\\" + openTokeChars[1] + "([^" + closeToken + "]*)}";
        regx = regx.replace("\\\\", "\\");
        Pattern pattern = Pattern.compile(regx);
        Matcher matcher = pattern.matcher(sql);
        StringBuffer result = new StringBuffer();

        while (matcher.find()) {
            String matcherContent = matcher.group(1);
            if (placeholder.equals(type)) {
                Object arg = params.get(matcherContent);
                String paramValue = "";
                if (arg instanceof String) {
                    paramValue = "'" + arg + "'";
                } else {
                    paramValue = arg.toString();
                }
                matcher.appendReplacement(result, paramValue);
            } else {
                matcher.appendReplacement(result, "?");
                dataList.add(params.get(matcherContent));
            }
        }
        matcher.appendTail(result);
        resultMap.put("sql", result.toString());
        resultMap.put("args", dataList);
        return resultMap;
    }
}