緣起
在 Mybatis 執(zhí)行 like 查詢時(shí)，如果查詢字符包含通配符‘%’或‘_’則查詢結(jié)果并不是我們預(yù)期結(jié)果。比如，一張文件表里有字段 file_name 記錄文件名，而我們需要找到以‘_’開頭的文件琅捏。查詢語(yǔ)句是這樣： select * from file where file_name like '_%' 。如果你了解mysql 就知道執(zhí)行該語(yǔ)句將返回表中所有的文件名不為空的記錄递雀。這實(shí)際是與我們目標(biāo)相悖柄延。正確的語(yǔ)句應(yīng)該是 select * from file where file_name like "\\_%"（默認(rèn)轉(zhuǎn)義字符是反斜杠）。
代碼
不止一次遇到該問題了，之前的解決辦法搜吧，是在 mapper 的參數(shù)中做轉(zhuǎn)義市俊。比如下面這段代碼（是一個(gè)帖子搜索參數(shù)的定義，我們重點(diǎn)關(guān)注 titlePrefix 這代表標(biāo)題是一個(gè)前綴匹配）
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class PostSearchParam implements Serializable {
private Integer type;
/**
* 標(biāo)題前綴
*/
private String titlePrefix;
/**
* 學(xué)院名稱
*/
private String groupName;
/**
* userId
*/
private Integer userId;
}
為了實(shí)現(xiàn)轉(zhuǎn)義滤奈，添加一個(gè)新的getter方法來(lái)獲取轉(zhuǎn)義后的標(biāo)題
....
public String getEscapeTitlePrefix(){
return likeEscape(this.titlePrefix);
}
String escapeLike(String value) {
if (value != null) {
return value
.replaceAll("_", "\\\\_")
.replaceAll("%", "\\\\%");
}
return null;
}
...
雖然能夠?qū)崿F(xiàn)預(yù)期摆昧，但是你想想，如果有很多這樣的類蜒程，那我們需要敲很多遍一樣的代碼绅你。為了“永絕后患”，可以通過mybatis攔截器去除冗余代碼昭躺。
攔截器的主要內(nèi)容：1. 識(shí)別 mapper 中的sql語(yǔ)句中包含like查詢（正則）忌锯；2. 找到like后的占位符，將該占位的字符串轉(zhuǎn)義领炫。
/**
* Like 轉(zhuǎn)義
* <p>
* 使用方法：
* <ol>
* <li> 添加插件 </li>
* </ol>
* </p>
*/
@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 {
/**
* 檢查sql中偶垮，是否含有l(wèi)ike查詢
*/
private static Pattern LIKE_PARAM_PATTERN = Pattern
.compile("like\\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;
//由于邏輯關(guān)系，只會(huì)進(jìn)入一次
if (args.length == 4) {
//4 個(gè)參數(shù)時(shí)
boundSql = ms.getBoundSql(parameter);
cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
} else {
//6 個(gè)參數(shù)時(shí)
cacheKey = (CacheKey) args[4];
boundSql = (BoundSql) args[5];
}
SqlCommandType sqlCommandType = ms.getSqlCommandType();
StatementType statementType = ms.getStatementType();
// 只處理 有參數(shù)的查詢語(yǔ)句
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();
// 找到 like 后面的參數(shù)
List<Integer> position = findLikeParam(prepareSql);
if (position == null || position.size() == 0) {
return;
}
List<ParameterMapping> likeParameterMappings = new ArrayList<>();
// 復(fù)制
MetaObject metaObject = MetaObjectUtil.forObject(boundSql.getParameterObject());
for (int i = 0; i < parameterMappings.size(); i++) {
ParameterMapping pm = parameterMappings.get(i);
String property = pm.getProperty();
// 忽略無(wú)法處理的屬性帝洪。例如 __frch_
if (metaObject.hasGetter(property)) {
boundSql.setAdditionalParameter(property, metaObject.getValue(property));
if (position.contains(i)) {
likeParameterMappings.add(pm);
}
}
}
// 覆蓋 轉(zhuǎn)義字符
delegateMetaParameterForEscape(boundSql, likeParameterMappings);
}
/**
* @param boundSql 原 boundSql
* @param likeParameterMappings 需要轉(zhuǎn)義的參數(shù)
* @return 支持轉(zhuǎn)義的 boundSql
*/
void delegateMetaParameterForEscape(BoundSql boundSql, List<ParameterMapping> likeParameterMappings) {
for (ParameterMapping mapping : likeParameterMappings) {
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));
}
}
}
String escapeLike(String value) {
if (value != null) {
return value
.replaceAll("_", "\\\\_")
.replaceAll("%", "\\\\%");
}
return null;
}
List<Integer> findLikeParam(String prepareSql) {
Matcher matcher = LIKE_PARAM_PATTERN.matcher(prepareSql);
int pos = 0;
List<Integer> indexes = new ArrayList<>();
while (matcher.find(pos)) {
int start = matcher.start();
int index = StringUtils.countMatches(prepareSql.substring(0, start), "?");
indexes.add(index);
pos = matcher.end();
}
return indexes;
}
}
使用
如果需要使用似舵，請(qǐng)?zhí)砑拥?code>mybatis的插件中
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor"/>
<plugin
interceptor="xxxxx.LikeStringEscapeInterceptor"/>
</plugins>
如有錯(cuò)誤，歡迎大家指正葱峡。
