Implementation of mybatis and mybatis plus Fuzzy Query Statement Special Character Escape Interceptor
- 2021-12-11 17:54:22
- OfStack
Directory 1. Use the interceptor provided by mybatis to intercept all query requests.
2. Define SQL statement escape template to process Map and Object objects respectively
1. Use the interceptor provided by mybatis to intercept all query requests.
2. Define SQL statement escape template to process Map and Object objects respectively
mybatis/mybatis-plus Fuzzy Query Statement Special Character Escape Interceptor
In development, we usually encounter such a situation. The user entered '%' when entering the information, but the query cannot accurately match '%'. The reason is that '%' is the keyword of MySQL, and if we want to accurately match '%', we need to escape it.
1. Use the interceptor provided by mybatis to intercept all query requests.
The specific implementation is annotated in the code
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.util.*;
/**
* mybatis/mybatis-plus Fuzzy query statement special character escape interceptor
*
* @author lieber
*/
@Intercepts({@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
@Slf4j
public class MybatisLikeSqlInterceptor implements Interceptor {
/**
* SQL Statement like
*/
private final static String SQL_LIKE = " like ";
/**
* SQL Statement placeholder
*/
private final static String SQL_PLACEHOLDER = "?";
/**
* SQL Statement placeholder separation
*/
private final static String SQL_PLACEHOLDER_REGEX = "\\?";
/**
* All escapers
*/
private static Map<Class, AbstractLikeSqlConverter> converterMap = new HashMap<>(4);
static {
converterMap.put(Map.class, new MapLikeSqlConverter());
converterMap.put(Object.class, new ObjectLikeSqlConverter());
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
MappedStatement statement = (MappedStatement) args[0];
Object parameterObject = args[1];
BoundSql boundSql = statement.getBoundSql(parameterObject);
String sql = boundSql.getSql();
this.transferLikeSql(sql, parameterObject, boundSql);
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties arg0) {
}
/**
* Modify contains like Adj. SQL Statement
*
* @param sql SQL Statement
* @param parameterObject Parameter object
* @param boundSql Binding SQL Object
*/
private void transferLikeSql(String sql, Object parameterObject, BoundSql boundSql) {
if (!isEscape(sql)) {
return;
}
sql = sql.replaceAll(" {2}", " ");
// Gets the number of keywords (de-duplication)
Set<String> fields = this.getKeyFields(sql, boundSql);
if (fields == null) {
return;
}
// You can enhance here , More than support Map Object ,Map Object is used only for passing in the condition that Map Or use @Param The incoming object is Mybatis Converted Map
AbstractLikeSqlConverter converter;
// Special character "cleaning" is carried out on keywords. If there are special characters, escape characters are added before the special characters ( \ )
if (parameterObject instanceof Map) {
converter = converterMap.get(Map.class);
} else {
converter = converterMap.get(Object.class);
}
converter.convert(sql, fields, parameterObject);
}
/**
* Do you need escape
*
* @param sql SQL Statement
* @return true/false
*/
private boolean isEscape(String sql) {
return this.hasLike(sql) && this.hasPlaceholder(sql);
}
/**
* Judge SQL Statement contains like Keyword
*
* @param str SQL Statement
* @return true/false
*/
private boolean hasLike(String str) {
if (StringUtils.isBlank(str)) {
return false;
}
return str.toLowerCase().contains(SQL_LIKE);
}
/**
* Judge SQL Statement contains SQL Placeholder
*
* @param str SQL Statement
* @return true/false
*/
private boolean hasPlaceholder(String str) {
if (StringUtils.isBlank(str)) {
return false;
}
return str.toLowerCase().contains(SQL_PLACEHOLDER);
}
/**
* Gets a collection of all fields to be replaced
*
* @param sql Intact SQL Statement
* @param boundSql Bound SQL Object
* @return Field collection list
*/
private Set<String> getKeyFields(String sql, BoundSql boundSql) {
String[] params = sql.split(SQL_PLACEHOLDER_REGEX);
Set<String> fields = new HashSet<>();
for (int i = 0; i < params.length; i++) {
if (this.hasLike(params[i])) {
String field = boundSql.getParameterMappings().get(i).getProperty();
fields.add(field);
}
}
return fields;
}
}
2. Define SQL statement escape template to process Map and Object objects respectively
2.1 Defining AbstractLikeSqlConverter
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Set;
/**
* 包含like的SQL语句转义模板
*
* @author lieber
*/
@Slf4j
public abstract class AbstractLikeSqlConverter<T> {
/**
* SQL语句like使用关键字%
*/
private final static String LIKE_SQL_KEY = "%";
/**
* SQL语句需要转义的关键字
*/
private final static String[] ESCAPE_CHAR = new String[]{LIKE_SQL_KEY, "_", "\\"};
/**
* mybatis-plus中like的SQL语句样式
*/
private final static String MYBATIS_PLUS_LIKE_SQL = " like ?";
/**
* mybatis-plus中参数前缀
*/
private final static String MYBATIS_PLUS_WRAPPER_PREFIX = "ew.paramNameValuePairs.";
/**
* mybatis-plus中参数键
*/
final static String MYBATIS_PLUS_WRAPPER_KEY = "ew";
/**
* mybatis-plus中参数分隔符
*/
final static String MYBATIS_PLUS_WRAPPER_SEPARATOR = ".";
/**
* mybatis-plus中参数分隔符替换器
*/
final static String MYBATIS_PLUS_WRAPPER_SEPARATOR_REGEX = "\\.";
/**
* 已经替换过的标记
*/
final static String REPLACED_LIKE_KEYWORD_MARK = "replaced.keyword";
/**
* 转义特殊字符
*
* @param sql SQL语句
* @param fields 字段列表
* @param parameter 参数对象
*/
public void convert(String sql, Set<String> fields, T parameter) {
for (String field : fields) {
if (this.hasMybatisPlusLikeSql(sql)) {
if (this.hasWrapper(field)) {
// 第1种情况：在业务层进行条件构造产生的模糊查询关键字,使用QueryWrapper,LambdaQueryWrapper
this.transferWrapper(field, parameter);
} else {
// 第2种情况：未使用条件构造器，但是在service层进行了查询关键字与模糊查询符`%`手动拼接
this.transferSelf(field, parameter);
}
} else {
// 第3种情况：在Mapper类的注解SQL中进行了模糊查询的拼接
this.transferSplice(field, parameter);
}
}
}
/**
* 转义条件构造的特殊字符
* 在业务层进行条件构造产生的模糊查询关键字,使用QueryWrapper,LambdaQueryWrapper
*
* @param field 字段名称
* @param parameter 参数对象
*/
public abstract void transferWrapper(String field, T parameter);
/**
* 转义自定义条件拼接的特殊字符
* 未使用条件构造器，但是在service层进行了查询关键字与模糊查询符`%`手动拼接
*
* @param field 字段名称
* @param parameter 参数对象
*/
public abstract void transferSelf(String field, T parameter);
/**
* 转义自定义条件拼接的特殊字符
* 在Mapper类的注解SQL中进行了模糊查询的拼接
*
* @param field 字段名称
* @param parameter 参数对象
*/
public abstract void transferSplice(String field, T parameter);
/**
* 转义通配符
*
* @param before 待转义字符串
* @return 转义后字符串
*/
String escapeChar(String before) {
if (StringUtils.isNotBlank(before)) {
before = before.replaceAll("\\\\", "\\\\\\\\");
before = before.replaceAll("_", "\\\\_");
before = before.replaceAll("%", "\\\\%");
}
return before;
}
/**
* 是否包含需要转义的字符
*
* @param obj 待判断的对象
* @return true/false
*/
boolean hasEscapeChar(Object obj) {
if (!(obj instanceof String)) {
return false;
}
return this.hasEscapeChar((String) obj);
}
/**
* 处理对象like问题
*
* @param field 对象字段
* @param parameter 对象
*/
void resolveObj(String field, Object parameter) {
if (parameter == null || StringUtils.isBlank(field)) {
return;
}
try {
PropertyDescriptor descriptor = new PropertyDescriptor(field, parameter.getClass());
Method readMethod = descriptor.getReadMethod();
Object param = readMethod.invoke(parameter);
if (this.hasEscapeChar(param)) {
Method setMethod = descriptor.getWriteMethod();
setMethod.invoke(parameter, this.escapeChar(param.toString()));
} else if (this.cascade(field)) {
int index = field.indexOf(MYBATIS_PLUS_WRAPPER_SEPARATOR) + 1;
this.resolveObj(field.substring(index), param);
}
} catch (IntrospectionException | IllegalAccessException | InvocationTargetException e) {
log.error("反射 {} 的 {} get/set方法出现异常", parameter, field, e);
}
}
/**
* 判断是否是级联属性
*
* @param field 字段名
* @return true/false
*/
boolean cascade(String field) {
if (StringUtils.isBlank(field)) {
return false;
}
return field.contains(MYBATIS_PLUS_WRAPPER_SEPARATOR) && !this.hasWrapper(field);
}
/**
* 是否包含mybatis-plus的包含like的SQL语句格式
*
* @param sql 完整SQL语句
* @return true/false
*/
private boolean hasMybatisPlusLikeSql(String sql) {
if (StringUtils.isBlank(sql)) {
return false;
}
return sql.toLowerCase().contains(MYBATIS_PLUS_LIKE_SQL);
}
/**
* 判断是否使用mybatis-plus条件构造器
*
* @param field 字段
* @return true/false
*/
private boolean hasWrapper(String field) {
if (StringUtils.isBlank(field)) {
return false;
}
return field.contains(MYBATIS_PLUS_WRAPPER_PREFIX);
}
/**
* 判断字符串是否含有需要转义的字符
*
* @param str 待判断的字符串
* @return true/false
*/
private boolean hasEscapeChar(String str) {
if (StringUtils.isBlank(str)) {
return false;
}
for (String s : ESCAPE_CHAR) {
if (str.contains(s)) {
return true;
}
}
return false;
}
}
2.2 Define how MapLikeSqlConverter handles Map type parameters
import com.baomidou.mybatisplus.core.conditions.AbstractWrapper;
import java.util.Map;
import java.util.Objects;
/**
* The parameter object is Map Converter of
*
* @author lieber
* @create_date 2020/1/21 12:28
*/
public class MapLikeSqlConverter extends AbstractLikeSqlConverter<Map> {
@Override
public void transferWrapper(String field, Map parameter) {
AbstractWrapper wrapper = (AbstractWrapper) parameter.get(MYBATIS_PLUS_WRAPPER_KEY);
parameter = wrapper.getParamNameValuePairs();
String[] keys = field.split(MYBATIS_PLUS_WRAPPER_SEPARATOR_REGEX);
// ew.paramNameValuePairs.param1 After intercepting the string, get the 3 Which is the parameter name
String paramName = keys[2];
String mapKey = String.format("%s.%s", REPLACED_LIKE_KEYWORD_MARK, paramName);
if (parameter.containsKey(mapKey) && Objects.equals(parameter.get(mapKey), true)) {
return;
}
if (this.cascade(field)) {
this.resolveCascadeObj(field, parameter);
} else {
Object param = parameter.get(paramName);
if (this.hasEscapeChar(param)) {
String paramStr = param.toString();
parameter.put(keys[2], String.format("%%%s%%", this.escapeChar(paramStr.substring(1, paramStr.length() - 1))));
}
}
parameter.put(mapKey, true);
}
@Override
public void transferSelf(String field, Map parameter) {
if (this.cascade(field)) {
this.resolveCascadeObj(field, parameter);
return;
}
Object param = parameter.get(field);
if (this.hasEscapeChar(param)) {
String paramStr = param.toString();
parameter.put(field, String.format("%%%s%%", this.escapeChar(paramStr.substring(1, paramStr.length() - 1))));
}
}
@Override
public void transferSplice(String field, Map parameter) {
if (this.cascade(field)) {
this.resolveCascadeObj(field, parameter);
return;
}
Object param = parameter.get(field);
if (this.hasEscapeChar(param)) {
parameter.put(field, this.escapeChar(param.toString()));
}
}
/**
* Handling Cascading Attributes
*
* @param field Cascading field name
* @param parameter Parameter Map Object
*/
private void resolveCascadeObj(String field, Map parameter) {
int index = field.indexOf(MYBATIS_PLUS_WRAPPER_SEPARATOR);
Object param = parameter.get(field.substring(0, index));
if (param == null) {
return;
}
this.resolveObj(field.substring(index + 1), param);
}
}
2.3 Defining how ObjectLikeSqlConverter handles Object type parameters
import lombok.extern.slf4j.Slf4j;
/**
* Converter of general parameters
*
* @author lieber
*/
@Slf4j
public class ObjectLikeSqlConverter extends AbstractLikeSqlConverter<Object> {
@Override
public void transferWrapper(String field, Object parameter) {
// This situation has not been found yet
}
@Override
public void transferSelf(String field, Object parameter) {
// This situation has not been found yet
}
@Override
public void transferSplice(String field, Object parameter) {
this.resolveObj(field, parameter);
}
}
3. Register in Spring
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* mybatis/mybatis-plus Fuzzy Query Statement Special Character Escape Configuration
*
* @author lieber
*/
@Configuration
public class MybatisLikeSqlConfig {
@Bean
public MybatisLikeSqlInterceptor mybatisSqlInterceptor() {
return new MybatisLikeSqlInterceptor();
}
}
At this point, you can accurately match keywords.