自定义插件解决MyBatis-Plus like查询遇
作者:mmseoamin日期:2023-12-25

我们使用MyBatis-Plus执行LIKE模糊查询时,若预处理参数包含_ % \等字符(欢迎补充),会查询出所有结果,这不是我们需要的。

不论写法是自定义SQL

xxx like concat('%',#{fuzzyName},'%')

还是Wrapper(本质上也是生成like SQL语句)

final LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.like(CharSequenceUtil.isNotBlank(fuzzyName), XxxPo::getName, fuzzyName);

因为SQL中LIKE中_ % \这些符号是通配符,若要作为正常参数查询需要转译。

\转译为\\
_转译为\_
%转译为\%

1、每处like查询替换特殊字符(不推荐)

照前文所述,我们只需定义一个替换方法,在调用like的地方把参数处理一下。

    /**
     * 转译 \ % _
     * 禁止与escape 同时使用
     */
    public static String convertToSqlSafeValue(String str) {
        if (CharSequenceUtil.isEmpty(str)) {
            return str;
        }
        return str.replace("\\", "\\\\")
                .replace("%", "\\%")
                .replace("_", "\\_");
    }

但是,这种做法有诸多缺点:

  1. 侵入性太强,需要每处like参数进行处理,而且有些参数在对象内,可能会改变属性值

  1. 业务庞杂的系统,修改容易遗漏,且下次写like时容易忘记加这个方法,项目交接也不易

  1. 太不优雅了,写代码不应这样

  1. 若公司有多个项目,每个项目每个like都需要写这个东西,建议使用下面的方法,并集成进公司/项目组的基础组件中

2、自定义插件解决普通查询的like特殊字符问题

MyBatis-Plus的核心插件MybatisPlusInterceptor代理了Executor#query和Executor#update和 StatementHandler#prepare方法。

允许我们通过实现InnerInterceptor接口,创建MybatisPlusInterceptor对象,注入Bean中生效。以MyBatis-Plus提供的扩展“分页插件PaginationInnerInterceptor”为例:

    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        final MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }

阅读MybatisPlusInterceptor的源码可知,当执行SELECT的SQL时,会执行各InnerInterceptor实现类的beforeQuery方法(同理UPDATE时会执行beforeUpdate方法),源码如下截图:

因此,创建一个类实现InnerInterceptor接口,在beforeQuery中将“_%\”等特殊字符做转译替换。我将其命名为LikeStringEscapeInterceptor,读者可自行命名。

import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.sql.SQLException;
/**
 * Like 转义插件:
 * 在MyBatis-Plus配置此插件使用;MyBatis-Plus插件使用机制,优先使用原始参数进行条件查询。
 * 1、如果 count 记录为0 时,name将不再执行任何before query 调用;
 * 2、如果 count 结果非0 时,执行插件业务逻辑。
 *
 * @author 陨·落
 * @date 2023/1/31 14:49
 */
public class LikeStringEscapeInterceptor implements InnerInterceptor {
    @Override
    public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds,
                            ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        // 为了在分页插件中复用,此处抽取出静态方法
        MybatisUtil.escapeParameterIfContainingLike(ms, boundSql);
        InnerInterceptor.super.beforeQuery(executor, ms, parameter, rowBounds, resultHandler, boundSql);
    }
}

为了后文中分页插件中也复用这个like特殊字符替换方法,将具体实现抽取出静态方法MybatisUtil#escapeParameterIfContainingLike。其中加一层判断只处理有参数的查询语句,以跳过其他没必要处理的SQL,并通过正则表达式定位like concat('%',?,'%')所在位置。

import cn.hutool.core.text.CharSequenceUtil;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
 * @author 陨·落
 * @date 2023/1/31 14:55
 */
public class MybatisUtil {
    private MybatisUtil() {
    }
    /**
     * 检查sql中,是否含有like查询
     */
    public static final Pattern LIKE_PARAM_PATTERN =
            Pattern.compile("like\\s(concat)?[\\w'\"\\(\\)\\%,\\s\\n\\t]*\\?", Pattern.CASE_INSENSITIVE);
    public static void escapeParameterIfContainingLike(MappedStatement ms, BoundSql boundSql) {
        final SqlCommandType sqlCommandType = ms.getSqlCommandType();
        final StatementType statementType = ms.getStatementType();
        // 只处理 有参数的查询语句
        if (SqlCommandType.SELECT == sqlCommandType && StatementType.PREPARED == statementType) {
            escapeParameterIfContainingLike(boundSql);
        }
    }
    public static void escapeParameterIfContainingLike(BoundSql boundSql) {
        if (null == boundSql) {
            return;
        }
        final String prepareSql = boundSql.getSql();
        final List parameterMappings = boundSql.getParameterMappings();
        // 找到 like 后面的参数
        final List positions = findLikeParam(prepareSql);
        if (positions.isEmpty()) {
            return;
        }
        final List likeParameterMappings = new ArrayList<>(parameterMappings.size());
        // 复制
        final MetaObject metaObject = SystemMetaObject.forObject(boundSql.getParameterObject());
        for (ParameterMapping parameterMapping : parameterMappings) {
            final String property = parameterMapping.getProperty();
            if (!metaObject.hasGetter(property)) {
                continue;
            }
            boundSql.setAdditionalParameter(property, metaObject.getValue(property));
        }
        for (Integer position : positions) {
            final ParameterMapping parameterMapping = parameterMappings.get(position);
            likeParameterMappings.add(parameterMapping);
        }
        // 覆盖 转译字符
        delegateMetaParameterForEscape(boundSql, likeParameterMappings);
    }
    /**
     * @param boundSql              原BoundSql
     * @param likeParameterMappings 需要转义的参数
     */
    private static void delegateMetaParameterForEscape(BoundSql boundSql,
        List likeParameterMappings) {
        final MetaObject metaObject = SystemMetaObject.forObject(boundSql.getParameterObject());
        for (ParameterMapping mapping : likeParameterMappings) {
            final String property = mapping.getProperty();
            if (!metaObject.hasGetter(property)) {
                continue;
            }
            final Object value = metaObject.getValue(property);
            if (value instanceof String) {
                final String saveValue = convertToSqlSafeValue((String) value);
                boundSql.setAdditionalParameter(property, saveValue);
            }
        }
    }
    /**
     * 匹配like 位置, 如
     * like concat('%',?,'%')
     * like CONCAT('%',?,'%')
     * LIKE CONCAT('%', ?,'%')
     * lIKE conCAT('%', ?,'%')
     * like ?
     */
    private static List findLikeParam(String prepareSql) {
        if (CharSequenceUtil.isBlank(prepareSql)) {
            return Collections.emptyList();
        }
        final Matcher matcher = LIKE_PARAM_PATTERN.matcher(prepareSql);
        if (!matcher.find()) {
            return Collections.emptyList();
        }
        matcher.reset();
        int pos = 0;
        final List indexes = new ArrayList<>();
        while (matcher.find(pos)) {
            final int start = matcher.start();
            final int index = CharSequenceUtil.count(prepareSql.substring(0, start), '?');
            indexes.add(index);
            pos = matcher.end();
        }
        return indexes;
    }
    /**
     * 转译 \ % _
     * 禁止与escape 同时使用
     */
    public static String convertToSqlSafeValue(String str) {
        if (CharSequenceUtil.isEmpty(str)) {
            return str;
        }
        return str.replace("\\", "\\\\")
                .replace("%", "\\%")
                .replace("_", "\\_");
    }
}

实现写完后,记得在Bean中应用MyBatis-Plus插件。

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import lombok.RequiredArgsConstructor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
 * @author 陨·落
 * @date 2023/2/17 15:00
 */
@Configuration
@RequiredArgsConstructor
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        final MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new LikeStringEscapeInterceptor());
        return interceptor;
    }
}

3、自定义插件解决分页查询的like特殊字符问题

使用MyBatis-Plus的分页功能,通常离不开官方插件PaginationInnerInterceptor的支持。

PaginationInnerInterceptor的主要功能有两个:

  1. 统计总数

  1. 拼接SQL实现分页查询

阅读源码,可知统计数量count是在PaginationInnerInterceptor插件willDoQuery方法完成的。而count结果若为0,willDoQuery方法返回false,就不会执行具体的查询方法(因为查询范围内条数都为0了没必要)。

若我们不重写willDoQuery方法,当count遇到特殊字符,返回条数为0时会直接结束查询,这不是我们想要的结果,我们是想特殊字符作为查询条件 进行分页查询。

因此,我们继承PaginationInnerInterceptor类,重写其willDoQuery方法,方法内容与官方大体不变,只需加一句替换countSql的特殊字符进行转译。

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.ParameterUtils;
import com.baomidou.mybatisplus.core.toolkit.PluginUtils;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
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.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.sql.SQLException;
import java.util.List;
/**
 * 在 分页拦截器 的基础上,分页计数sql查询条件like 处理\ % _等特殊字符
 *
 * @author 陨·落
 * @date 2023/1/31 18:13
 */
public class PaginationInnerEscapeInterceptor extends PaginationInnerInterceptor {
    @Override
    public boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
        IPage page = ParameterUtils.findPage(parameter).orElse(null);
        if (page == null || page.getSize() < 0 || !page.searchCount()) {
            return true;
        }
        BoundSql countSql;
        MappedStatement countMs = buildCountMappedStatement(ms, page.countId());
        if (countMs != null) {
            countSql = countMs.getBoundSql(parameter);
        } else {
            countMs = buildAutoCountMappedStatement(ms);
            String countSqlStr = autoCountSql(page.optimizeCountSql(), boundSql.getSql());
            PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);
            countSql = new BoundSql(countMs.getConfiguration(), countSqlStr, mpBoundSql.parameterMappings(), parameter);
            PluginUtils.setAdditionalParameter(countSql, mpBoundSql.additionalParameters());
        }
        CacheKey cacheKey = executor.createCacheKey(countMs, parameter, rowBounds, countSql);
        // 其余和官方一致,只需加这句:处理like条件\ % _等特殊字符
        MybatisUtil.escapeParameterIfContainingLike(countMs, countSql);
        List result = executor.query(countMs, parameter, rowBounds, resultHandler, cacheKey, countSql);
        long total = 0;
        if (CollectionUtils.isNotEmpty(result)) {
            // 个别数据库 count 没数据不会返回 0
            Object o = result.get(0);
            if (o != null) {
                total = Long.parseLong(o.toString());
            }
        }
        page.setTotal(total);
        return continuePage(page);
    }
} 
 

然后记得在Bean中使用重写的对象,而非官方的。

import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import lombok.RequiredArgsConstructor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
 * @author 陨·落
 * @date 2023/2/17 15:00
 */
@Configuration
@RequiredArgsConstructor
public class MybatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        final MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new LikeStringEscapeInterceptor());
        interceptor.addInnerInterceptor(new PaginationInnerEscapeInterceptor());
        return interceptor;
    }
}

至此,分页查询的count查询与主体查询,like concat('%',?,'%')处 预处理参数均经过转译处理,问题解决。

只需启动程序,以往模糊查询遇_ % \问题均被解决。