搜索模糊匹配% _ 等特殊字符转义工具

mysql 和达梦数据库不同的解决方式：
mysql 数据库 解决搜索框传入%等特殊字符查询全部数据的问题：

/**
 *@author liuxingying
 *@description 搜索转义工具类
 *@since 2023/11/30
 */
public class EscapeUtil {

    /**
     * sql的模糊查询时特殊字符转义(条件查询%或者_查询所有问题)
     */
    public static String escapeChar(String str){
        if(StringUtils.isNotBlank(str)){
            if (str.contains("/")){
                str = str.replaceAll("/", "\\\\/");
            }
            if (str.contains("%")){
                str = str.replaceAll("%", "\\\\%");
            }
            if (str.contains("_")){
                str = str.replaceAll("_", "\\\\_");
            }
        }
        return str.trim();
    }
}

达梦 数据库 解决搜索框传入%等特殊字符查询全部数据的问题：
加入mybatis拦截器处理

package com.dx.radar.data;

import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.List;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author liuxingying
 * @since 2024/3/28
 */
@Component
@Intercepts(
        {
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
                @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),
        }
)
public class MybatisEscapeInterceptor implements Interceptor {

    static final String REG_EXP_LIKE_CONTAIN = "^.*(like\\s*\\?|like\\s*concat\\s*[(]\\s*'%'\\s*,\\s*concat\\s*[(]\\s*\\?\\s*,\\s*'%'\\s*[)]\\s*[)]).*$";
    static final String REG_EXP_PROCESSED_PARAM = "^.*(\\\\%|\\\\_).*$";
    static final String REG_EXP_UNPROCESSED_PARAM = "^.*(%|_).*$";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement mappedStatement = (MappedStatement) args[0];
        Object parameter = args[1];
        RowBounds rowBounds = (RowBounds) args[2];
        ResultHandler resultHandler = (ResultHandler) args[3];
        Executor executor = (Executor) invocation.getTarget();
        CacheKey cacheKey;
        BoundSql boundSql;
        if (args.length == 4) {
            boundSql = mappedStatement.getBoundSql(parameter);
            cacheKey = executor.createCacheKey(mappedStatement, parameter, rowBounds, boundSql);
        } else {
            cacheKey = (CacheKey) args[4];
            boundSql = (BoundSql) args[5];
        }
        String sql = boundSql.getSql();
        String newSql = modifyLikeSql(sql, parameter, boundSql, mappedStatement);

        Field field = boundSql.getClass().getDeclaredField("sql");
        field.setAccessible(true);
        field.set(boundSql, newSql);

        return executor.query(mappedStatement, parameter, rowBounds, resultHandler, cacheKey, boundSql);
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {

    }

    public static String modifyLikeSql(String sql, Object parameterObject, BoundSql boundSql, MappedStatement mappedStatement) {
        if (!matches(REG_EXP_LIKE_CONTAIN, sql) || sql.contains(" ESCAPE '\\\\' ")) {
            return sql;
        }
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings != null) {

            int j = 0;
            Matcher matcher = getMatcher("\\?", sql);
            StringBuffer sb = new StringBuffer();
            while (matcher.find()) {
                matcher.appendReplacement(sb, "#{" + j + "}");
                j++;
            }
            matcher.appendTail(sb);
            sql = sb.toString();

            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                        if (value instanceof String) {
                            String val = (String) value;
                            if (val.equals("%%")) {
                                continue;
                            }
                            if (!matches(REG_EXP_PROCESSED_PARAM, val) && matches(REG_EXP_UNPROCESSED_PARAM, val) && matches(getRegExpLikeContain(i), sql)) {
                                val = resetParam(val);
                                boundSql.setAdditionalParameter(propertyName, val);
                                value = val;
                            }
                        }
                    } else if (parameterObject == null) {
                        value = null;
                    } else if (mappedStatement.getConfiguration().getTypeHandlerRegistry().hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else {
                        MetaObject metaObject = mappedStatement.getConfiguration().newMetaObject(parameterObject);
                        value = metaObject.getValue(propertyName);
                        if (value instanceof String) {
                            String val = (String) value;
                            if (val.equals("%%")) {
                                continue;
                            }
                            if (!matches(REG_EXP_PROCESSED_PARAM, val) && matches(REG_EXP_UNPROCESSED_PARAM, val) && matches(getRegExpLikeContain(i), sql)) {
                                val = resetParam(val);
                                metaObject.setValue(propertyName, val);
                                value = val;
                            }
                        }
                    }
                    if (value instanceof String) {
                        if (matches(REG_EXP_PROCESSED_PARAM, ((String) value))) {
                            sql = matchesReplace(getRegExpLike(i), sql, " LIKE ? ESCAPE '\\\\' ");
                        }
                    }
                }
            }
        }
        sql = matchesReplace("#\\{\\d+\\}", sql, " ? ");
        return sql;
    }

    private static String resetParam(String val) {
        if (!"%".equals(val) && !"%%".equals(val) && !"_".equals(val) && !"__".equals(val)) {
            if (val.startsWith("%") || val.startsWith("_")) {
                val = val.substring(1);
            }
            if (val.endsWith("%") || val.endsWith("_")) {
                val = val.substring(0, val.length() - 1);
            }
        }
        val = val.replaceAll("%", "\\\\%");
        val = val.replaceAll("_", "\\\\_");
        val = "%" + val + "%";
        return val;
    }

    private static String getRegExpLike(int i) {
        return "like\\s*#\\{" + i + "\\}|like\\s*concat\\s*[(]\\s*'%'\\s*,\\s*concat\\s*[(]\\s*#\\{" + i + "\\}\\s*,\\s*'%'\\s*[)]\\s*[)]";
    }

    private static String getRegExpLikeContain(int i) {
        return "^.*(like\\s*#\\{" + i + "\\}|like\\s*concat\\s*[(]\\s*'%'\\s*,\\s*concat\\s*[(]\\s*#\\{" + i + "\\}\\s*,\\s*'%'\\s*[)]\\s*[)]).*$";
    }

    private static String matchesReplace(String regExp, String input, String replaceStr) {
        Matcher matcher = getMatcher(regExp, input);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, replaceStr);
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    private static boolean matches(String regExp, String input) {
        return getMatcher(regExp, input).matches();
    }

    private static Matcher getMatcher(String regExp, String input) {
        Pattern pattern = Pattern.compile(regExp, Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
        return pattern.matcher(input);
    }
}