需求
最近在整理项目的时候提到两个需求,分别是在过滤数据时,自动将字符串内容的前后空格去掉 以及 实现全局 不区分英文大小写的模糊查询
方案
然后跟团队小伙伴讨论如何实现时,考虑到不去一个一个从接口层去改了(项目呢已成型,改动量大)于是就有了从数据查询底层处理的方式
代码实现
首先创建一个类,实现 org.apache.ibatis.plugin.Interceptor 的interface;
具体如下:
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.binding.MapperMethod.ParamMap;
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.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.springframework.stereotype.Component;
import java.lang.reflect.Field;
import java.util.Iterator;
import java.util.Map;
import java.util.regex.Pattern;
/**
* 仅对查询查询语句做处理
*/
@Slf4j
@Component
@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 MybatisQueryInterceptor implements Interceptor {
// 匹配单词like,不区分大小写
private static final String regex = "(?i)\\blike\\b";
private static final Pattern pattern = Pattern.compile(regex);
@Override
public Object intercept(Invocation invocation) throws Throwable {
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
String sqlId = mappedStatement.getId();
SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
Object parameter = invocation.getArgs()[1];
//判断sql语句类型是否为select
if (parameter == null || SqlCommandType.SELECT != sqlCommandType) {
return invocation.proceed();
}
//将参数内容逐个取出,并替换
if (parameter instanceof ParamMap) {
ParamMap map = (ParamMap) parameter;
Iterator<Map.Entry<String, Object>> iterator = map.entrySet().iterator();
while (iterator.hasNext()) {
Map.Entry<String, Object> next = iterator.next();
if (next.getValue() instanceof String) {
next.setValue(((String) next.getValue()).trim());
}
}
}
//将原sql取出,判断是否包含like 然后全局 将like 替换为 ilike(当然可能存在特殊字符串匹配上的问题,如果其他小伙伴有更好的方案 麻烦提点一二)
if (invocation.getArgs().length >= 6) {
BoundSql boundSql = (BoundSql)invocation.getArgs()[5];
String sql = boundSql.getSql();
// sql中包含like
if (pattern.matcher(sql).find()) {
// 修改sql将所有的LIKE改成ILIKE
sql = sql.replaceAll(regex, "ILIKE");
Field field = boundSql.getClass().getDeclaredField("sql");
field.setAccessible(true);
field.set(boundSql, sql);
}
}
return invocation.proceed();
}
}
以上仅为个人拙见,若其他小伙伴有更好方式 或者建议 ,能交流一下 就更好啦~~ Ending