Mybatis 拦截器实现 Like 通配符转义

Mybatis 拦截器实现 Like 通配符转义

mysql中like查询通配符问题描述

如果在MySQL中使用如下查询，将检索出全部数据

select * from t_user where name like '%%%';
select * from t_user where name like '%_%';
select * from t_user where name like concat('%','%','%');
select * from t_user where name like concat('%','_','%');

所以我们要对其做一个转义处理
拦截目标为 like 查询语句

定义mybatis拦截器

import com.github.pagehelper.util.MetaObjectUtil;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@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 LikeStringEscapeInterceptor implements Interceptor {


    private static final Pattern LIKE_PARAM_PATTERN = Pattern.compile("like\\s+['\"%_]*\\?", Pattern.CASE_INSENSITIVE);

    private static final Pattern LIKE_CONCAT_PARAM_PATTERN = Pattern.compile("like\\s+concat\\s*\\(\\s*'%'\\s*,\\s*\\?,\\s*'%'\\s*\\)", Pattern.CASE_INSENSITIVE);

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

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement ms = (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 = ms.getBoundSql(parameter);
            cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
        } else {
            cacheKey = (CacheKey) args[4];
            boundSql = (BoundSql) args[5];
        }

        SqlCommandType sqlCommandType = ms.getSqlCommandType();
        StatementType statementType = ms.getStatementType();
        if (sqlCommandType == SqlCommandType.SELECT && statementType == StatementType.PREPARED) {
            escapeParameterIfContainingLike(boundSql);
            return executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
        }
        return invocation.proceed();
    }

    void escapeParameterIfContainingLike(BoundSql boundSql) {
        if (boundSql == null) {
            return;
        }
        String prepareSql = boundSql.getSql();
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();

        Map<Integer, Boolean> position = findLikeParam(prepareSql);
        if (position == null || position.size() == 0) {
            return;
        }

        Map<ParameterMapping, Boolean> likeParameterMappings = new LinkedHashMap<>();

        MetaObject metaObject = MetaObjectUtil.forObject(boundSql.getParameterObject());
        for (int i = 0; i < parameterMappings.size(); i++) {
            ParameterMapping pm = parameterMappings.get(i);
            String property = pm.getProperty();
            if (metaObject.hasGetter(property)) {
                boundSql.setAdditionalParameter(property, metaObject.getValue(property));
                if (position.containsKey(i)) {
                    likeParameterMappings.put(pm, position.get(i));
                }
            }
        }

        delegateMetaParameterForEscape(boundSql, likeParameterMappings);
    }

    void delegateMetaParameterForEscape(BoundSql boundSql, Map<ParameterMapping, Boolean> likeParameterMappings) {

        for (ParameterMapping mapping : likeParameterMappings.keySet()) {
            String property = mapping.getProperty();

            MetaObject metaObject = MetaObjectUtil.forObject(boundSql.getParameterObject());
            Object value = metaObject.getValue(property);
            if (value instanceof String) {
                boundSql.setAdditionalParameter(property, escapeLike((String) value, likeParameterMappings.get(mapping)));
            }
        }
    }

    String escapeLike(String value, Boolean hasConcat) {
        if (value != null) {
            if (hasConcat) {
                return value.replaceAll("%", "\\\\%");
            }
            //去除首尾%如果有的话
            if (value.startsWith("%")) {
                value = value.substring(1);
            }
            if (value.endsWith("%")) {
                value = value.substring(0, value.length() - 1);
            }
            if (StringUtils.isEmpty(value)) {
                return null;
            }
            return "%" + value.replaceAll("%", "\\\\%") + "%";
        }
        return null;
    }


    Map<Integer, Boolean> findLikeParam(String prepareSql) {
        Matcher matcher = LIKE_PARAM_PATTERN.matcher(prepareSql);
        Map<Integer, Boolean> indexes = new LinkedHashMap<>();
        while (matcher.find()) {
            int start = matcher.start();
            int index = StringUtils.countMatches(prepareSql.substring(0, start), "?");
            indexes.put(index, false);
        }
        matcher = LIKE_CONCAT_PARAM_PATTERN.matcher(prepareSql);
        while (matcher.find()) {
            int start = matcher.start();
            int index = StringUtils.countMatches(prepareSql.substring(0, start), "?");
            indexes.put(index, true);
        }
        return indexes;
    }
}

使用拦截器

    @Bean
    public LikeStringEscapeInterceptor likeStringEscapeInterceptor() {
        return new LikeStringEscapeInterceptor();
    }