MyBatis Like查詢處理%_符號

如果我們資料庫中存的欄位包含有"%_"這兩個like查詢的萬用字元，那麼在查詢的時候把"%_"當作關鍵字是查詢不出來的，因為mybatis會把這兩個字元當作萬用字元。解決方法是要能加轉義字元

1.定義一個攔截器，如果要查詢的字串中包含"%_"則增加一個轉義字元

@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class,
                RowBounds.class, ResultHandler.class})})
public class QueryExecutorInterceptor implements Interceptor {

    private static final ObjectFactory DEFAULT_OBJECT_FACTORY = new DefaultObjectFactory();
    private static final ObjectWrapperFactory DEFAULT_OBJECT_WRAPPER_FACTORY = new DefaultObjectWrapperFactory();
    private static final ReflectorFactory DEFAULT_OBJECT_REFLECTOR_FACTORY = new DefaultReflectorFactory();
    private static final String ROOT_SQL_NODE = "sqlSource.rootSqlNode";


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object parameter = invocation.getArgs()[1];
        MappedStatement statement = (MappedStatement) invocation.getArgs()[0];
        MetaObject metaMappedStatement = MetaObject.forObject(statement, DEFAULT_OBJECT_FACTORY, DEFAULT_OBJECT_WRAPPER_FACTORY, DEFAULT_OBJECT_REFLECTOR_FACTORY);
        BoundSql boundSql = statement.getBoundSql(parameter);
        if (metaMappedStatement.hasGetter(ROOT_SQL_NODE)) {
            //修改引數值
            SqlNode sqlNode = (SqlNode) metaMappedStatement.getValue(ROOT_SQL_NODE);
            getBoundSql(statement.getConfiguration(), boundSql.getParameterObject(), sqlNode);
        }
        return invocation.proceed();
    }

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

    @Override
    public void setProperties(Properties properties) {
    }


    public static BoundSql getBoundSql(Configuration configuration, Object parameterObject, SqlNode sqlNode) {
        DynamicContext context = new DynamicContext(configuration, parameterObject);
        sqlNode.apply(context);
        String countextSql = context.getSql();
        SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);
        Class<?> parameterType = parameterObject == null ? Object.class : parameterObject.getClass();
        String sql = modifyLikeSql(countextSql, parameterObject);
        SqlSource sqlSource = sqlSourceParser.parse(sql, parameterType, context.getBindings());

        BoundSql boundSql = sqlSource.getBoundSql(parameterObject);
        for (Map.Entry<String, Object> entry : context.getBindings().entrySet()) {
            boundSql.setAdditionalParameter(entry.getKey(), entry.getValue());
        }
        return boundSql;
    }

    public static String modifyLikeSql(String sql, Object parameterObject) {
        if (parameterObject instanceof Map) {
        } else {
            return sql;
        }
        if (!sql.toLowerCase().contains("like"))
            return sql;
        String reg = "\\bLIKE\\b.*\\#\\{\\b.*\\}";
        Pattern pattern = Pattern.compile(reg, Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);

        List<String> replaceFiled = new ArrayList<String>();

        while (matcher.find()) {
            int n = matcher.groupCount();
            for (int i = 0; i <= n; i++) {
                String output = matcher.group(i);
                if (output != null) {
                    String key = getParameterKey(output);
                    if (replaceFiled.indexOf(key) < 0) {
                        replaceFiled.add(key);
                    }
                }
            }
        }
        //修改引數
        Map<String, Object> paramMab = (Map) parameterObject;
        for (String key : replaceFiled) {
            Object val = paramMab.get(key);
            if (val != null && val instanceof String && (val.toString().contains("%") || val.toString().contains("_"))) {
                val = val.toString().replaceAll("%", "/%").replaceAll("_", "/_");
                paramMab.replace(key.toString(), val);
            }

        }
        return sql;
    }

    private static String getParameterKey(String input) {
        String key = "";
        String[] temp = input.split("#");
        if (temp.length > 1) {
            key = temp[1];
            key = key.replace("{", "").replace("}", "").split(",")[0];
        }
        return key.trim();
    }

 

2. 對面的查詢mapper like後面要加escape '/'

<select id="getList" resultMap="MultiResultMap" parameterType="java.util.Map">
        SELECT * FROM SYS_TEST T
        WHERE 1=1
        <if test="_parameter.containsKey('key')">
            AND UPPER(CONCAT(T.ROLE_NAME,T.ROLE_INFO)) LIKE UPPER (CONCAT(CONCAT('%', #{key, jdbcType=VARCHAR}),'%')) ESCAPE '/'
        </if>
    </select>

最好的做法是可以直接攔截SQL，然後在SQL後面自動加上ESCAPE '/'，但還沒有找到合適的方法