我们使用MyBatis-Plus执行LIKE模糊查询时,若预处理参数包含_ % \等字符(欢迎补充),会查询出所有结果,这不是我们需要的。
不论写法是自定义SQL
xxx like concat('%',#{fuzzyName},'%')
还是Wrapper(本质上也是生成like SQL语句)
final LambdaQueryWrapperqueryWrapper = new LambdaQueryWrapper<>(); queryWrapper.like(CharSequenceUtil.isNotBlank(fuzzyName), XxxPo::getName, fuzzyName);
因为SQL中LIKE中_ % \这些符号是通配符,若要作为正常参数查询需要转译。
\转译为\\ _转译为\_ %转译为\%
照前文所述,我们只需定义一个替换方法,在调用like的地方把参数处理一下。
/** * 转译 \ % _ * 禁止与escape 同时使用 */ public static String convertToSqlSafeValue(String str) { if (CharSequenceUtil.isEmpty(str)) { return str; } return str.replace("\\", "\\\\") .replace("%", "\\%") .replace("_", "\\_"); }
但是,这种做法有诸多缺点:
侵入性太强,需要每处like参数进行处理,而且有些参数在对象内,可能会改变属性值
业务庞杂的系统,修改容易遗漏,且下次写like时容易忘记加这个方法,项目交接也不易
太不优雅了,写代码不应这样
若公司有多个项目,每个项目每个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 ListparameterMappings = 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; } }
使用MyBatis-Plus的分页功能,通常离不开官方插件PaginationInnerInterceptor的支持。
PaginationInnerInterceptor的主要功能有两个:
统计总数
拼接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
然后记得在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('%',?,'%')处 预处理参数均经过转译处理,问题解决。
只需启动程序,以往模糊查询遇_ % \问题均被解决。