Mybatis特殊字符转义查询实现
靖节先生 人气:01. 问题描述
MyBatis作为目前最常用的ORM数据库访问持久层框架，其本身支持动态SQL存储映射等高级特性也非常优秀，通过Mapper文件采用动态代理模式使SQL与业务代码相解耦，日常开发中使用也非常广泛。
正常模糊匹配查询时是没有什么问题的，但是如果需要模糊查询字段含有特殊字符比如% _ / 等时就会出现查询不准确的问题。本文就是通过mybatis拦截器实现特殊字符转义实现mybatis特殊字符查询问题。
2. 解决方案
MybatisLikeSqlInterceptor：
通过 @Intercepts 注解指定拦截器插件的属性：分别指定了拦截器类型 Executor, 拦截方法名 query （共有2个query方法）。
拦截方法参数（方法1）MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class
拦截方法参数（方法2）MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class，CacheKey.class, BoundSql.class
@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})
MyBatis 允许使用插件来拦截的方法调用包括：
Executor 、ParameterHandler、ResultSetHandler 、StatementHandler ，方法时序如下：
3. 设计实现
3.1 环境准备
-- 创建用户表
CREATE TABLE `user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID主键',
`name` varchar(64) DEFAULT NULL COMMENT '姓名',
`sex` varchar(8) DEFAULT NULL COMMENT '性别',
`age` int(4) DEFAULT NULL COMMENT '年龄',
`born` date DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='用户表';
-- 查询用户表
select * from user;
-- 新增数据
INSERT INTO `category`.`user`(`id`, `name`, `sex`, `age`, `born`) VALUES (1, '%张三%', '男', 18, '2022-04-22');
INSERT INTO `category`.`user`(`id`, `name`, `sex`, `age`, `born`) VALUES (2, '李四', '女', 27, '2022-04-01');
-- 执行sql
select id, name, sex, age, born from user WHERE name like concat('%','%','%');
select id, name, sex, age, born from user WHERE name like concat('%','','%');
select id, name, sex, age, born from user WHERE name like concat('%','/','%');
select id, name, sex, age, born from user WHERE name like concat('%','张','%');
3.2 代码实现
UserController
package com.jerry.market.controller;
import com.jerry.market.entity.User;
import com.jerry.market.service.UserService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;
import com.jerry.market.entity.Response;
import java.util.List;
import javax.annotation.Resource;
/**
* 用户表(User)控制层
*
* @author makejava
* @since 2022-04-22 15:31:00
*/
@RestController
@RequestMapping("/user")
@Api(tags = "UserController", description = "用户控制器")
public class UserController {
/**
* 服务对象
*/
@Resource
private UserService userService;
/**
* 通过主键查询单条数据
*
* @param user 参数对象
* @return 单条数据
*/
@ApiOperation("通过主键查询单条数据")
@RequestMapping(value = "get", method = RequestMethod.GET)
public Response<User> selectOne(User user) {
User result = userService.selectById(user.getId());
if (result != null) {
return Response.success("查询成功", result);
}
return Response.fail("查询失败");
}
/**
* 新增一条数据
*
* @param user 实体类
* @return Response对象
*/
@ApiOperation("新增一条数据")
@RequestMapping(value = "insert", method = RequestMethod.POST)
public Response<User> insert(@RequestBody User user) {
int result = userService.insert(user);
if (result > 0) {
return Response.success("新增成功", user);
}
return Response.fail("新增失败");
}
/**
* 批量新增
*
* @param users 实例对象的集合
* @return 影响行数
*/
@ApiOperation("批量新增")
@RequestMapping(value = "batchInsert", method = RequestMethod.POST)
public Response<Integer> batchInsert(@RequestBody List<User> users) {
int result = userService.batchInsert(users);
if (result > 0) {
return Response.success("新增成功", result);
}
return Response.fail("新增失败");
}
/**
* 修改一条数据
*
* @param user 实体类
* @return Response对象
*/
@ApiOperation("修改一条数据")
@RequestMapping(value = "update", method = RequestMethod.PUT)
public Response<User> update(@RequestBody User user) {
User result = userService.update(user);
if (result != null) {
return Response.success("修改成功", result);
}
return Response.fail("修改失败");
}
/**
* 删除一条数据
*
* @param user 参数对象
* @return Response对象
*/
@ApiOperation("删除一条数据")
@RequestMapping(value = "delete", method = RequestMethod.DELETE)
public Response<User> delete(User user) {
int result = userService.deleteById(user.getId());
if (result > 0) {
return Response.success("删除成功", null);
}
return Response.fail("删除失败");
}
/**
* 查询全部
*
* @return Response对象
*/
@ApiOperation("查询全部")
@RequestMapping(value = "selectAll", method = RequestMethod.GET)
public Response<List<User>> selectAll() {
List<User> users = userService.selectAll();
if (users != null) {
return Response.success("查询成功", users);
}
return Response.fail("查询失败");
}
/**
* 通过实体作为筛选条件查询
*
* @return Response对象
*/
@ApiOperation("通过实体作为筛选条件查询")
@RequestMapping(value = "selectList", method = RequestMethod.GET)
public Response<List<User>> selectList(User user) {
List<User> users = userService.selectList(user);
if (users != null) {
return Response.success("查询成功", users);
}
return Response.fail("查询失败");
}
/**
* 分页查询
*
* @param start 偏移
* @param limit 条数
* @return Response对象
*/
@ApiOperation("分页查询")
@RequestMapping(value = "selectPage", method = RequestMethod.GET)
public Response<List<User>> selectPage(Integer start, Integer limit) {
List<User> users = userService.selectPage(start, limit);
if (users != null) {
return Response.success("查询成功", users);
}
return Response.fail("查询失败");
}
}
UserService
package com.jerry.market.service;
import com.jerry.market.entity.User;
import java.util.List;
import java.util.Map;
/**
* 用户表(User)表服务接口
*
* @author makejava
* @since 2022-04-22 15:31:01
*/
public interface UserService {
/**
* 通过ID查询单条数据
*
* @param id 主键
* @return 实例对象
*/
User selectById(Object id);
/**
* 分页查询
*
* @param start 查询起始位置
* @param limit 查询条数
* @return 对象列表
*/
List<User> selectPage(int start, int limit);
/**
* 查询全部
*
* @return 对象列表
*/
List<User> selectAll();
/**
* 通过实体作为筛选条件查询
*
* @param user 实例对象
* @return 对象列表
*/
List<User> selectList(User user);
/**
* 新增数据
*
* @param user 实例对象
* @return 影响行数
*/
int insert(User user);
/**
* 批量新增
*
* @param users 实例对象的集合
* @return 影响行数
*/
int batchInsert(List<User> users);
/**
* 修改数据
*
* @param user 实例对象
* @return 修改
*/
User update(User user);
/**
* 通过主键删除数据
*
* @param id 主键
* @return 影响行数
*/
int deleteById(Object id);
/**
* 查询总数据数
*
* @return 数据总数
*/
int count();
}
UserServiceImpl
package com.jerry.market.service.impl;
import com.jerry.market.entity.User;
import com.jerry.market.mapper.UserMapper;
import com.jerry.market.service.UserService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
/**
* 用户表(User表)服务实现类
*
* @author makejava
* @since 2022-04-22 15:31:01
*/
@Service("userService")
public class UserServiceImpl implements UserService {
@Resource
private UserMapper userMapper;
/**
* 通过ID查询单条数据
*
* @param id 主键
* @return 实例对象
*/
@Override
public User selectById(Object id) {
return this.userMapper.selectById(id);
}
/**
* 分页查询
*
* @param start 查询起始位置
* @param limit 查询条数
* @return 对象列表
*/
@Override
public List<User> selectPage(int start, int limit) {
return this.userMapper.selectPage(start, limit);
}
/**
* 查询所有
*
* @return 实例对象的集合
*/
@Override
public List<User> selectAll() {
return this.userMapper.selectAll();
}
/**
* 根据条件查询
*
* @return 实例对象的集合
*/
@Override
public List<User> selectList(User user) {
return this.userMapper.selectList(user);
}
/**
* 新增数据
*
* @param user 实例对象
* @return 实例对象
*/
@Override
public int insert(User user) {
return this.userMapper.insert(user);
}
/**
* 批量新增
*
* @param users 实例对象的集合
* @return 生效的条数
*/
@Override
public int batchInsert(List<User> users) {
return this.userMapper.batchInsert(users);
}
/**
* 修改数据
*
* @param user 实例对象
* @return 实例对象
*/
@Override
public User update(User user) {
this.userMapper.update(user);
return this.selectById(user.getId());
}
/**
* 通过主键删除数据
*
* @param id 主键
* @return 是否成功
*/
@Override
public int deleteById(Object id) {
return this.userMapper.deleteById(id);
}
/**
* 查询总数据数
*
* @return 数据总数
*/
@Override
public int count() {
return this.userMapper.count();
}
}
UserMapper
package com.jerry.market.mapper;
import com.jerry.market.entity.User;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
/**
* 用户表(User)表数据库访问层
*
* @author makejava
* @since 2022-04-22 15:31:00
*/
public interface UserMapper {
/**
* 通过ID查询单条数据
*
* @param id 主键
* @return 实例对象
*/
User selectById(Object id);
/**
* 分页查询
*
* @param start 查询起始位置
* @param limit 查询条数
* @return 对象列表
*/
List<User> selectPage(@Param("start") int start, @Param("limit") int limit);
/**
* 查询全部
*
* @return 对象列表
*/
List<User> selectAll();
/**
* 通过实体作为筛选条件查询
*
* @param user 实例对象
* @return 对象列表
*/
List<User> selectList(User user);
/**
* 新增数据
*
* @param user 实例对象
* @return 影响行数
*/
int insert(User user);
/**
* 批量新增
*
* @param users 实例对象的集合
* @return 影响行数
*/
int batchInsert(List<User> users);
/**
* 修改数据
*
* @param user 实例对象
* @return 影响行数
*/
int update(User user);
/**
* 通过主键删除数据
*
* @param id 主键
* @return 影响行数
*/
int deleteById(Object id);
/**
* 查询总数据数
*
* @return 数据总数
*/
int count();
}
UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jerry.market.mapper.UserMapper">
<!-- 结果集 -->
<resultMap type="com.jerry.market.entity.User" id="UserMap">
<result property="id" column="id" jdbcType="VARCHAR"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="sex" column="sex" jdbcType="VARCHAR"/>
<result property="age" column="age" jdbcType="INTEGER"/>
<result property="born" column="born" jdbcType="VARCHAR"/>
</resultMap>
<!-- 基本字段 -->
<sql id="Base_Column_List">
id, name, sex, age, born </sql>
<!-- 查询单个 -->
<select id="selectById" resultMap="UserMap">
select
<include refid="Base_Column_List"/>
from user
where id = #{id}
</select>
<!-- 分页查询 -->
<select id="selectPage" resultMap="UserMap">
select
<include refid="Base_Column_List"/>
from user
limit #{start},#{limit}
</select>
<!-- 查询全部 -->
<select id="selectAll" resultMap="UserMap">
select
<include refid="Base_Column_List"/>
from user
</select>
<!--通过实体作为筛选条件查询-->
<select id="selectList" resultMap="UserMap">
select
<include refid="Base_Column_List"/>
from user
<where>
<if test="id != null">
and id = #{id}
</if>
<if test="name != null and name != ''">
and name like concat('%',#{name},'%')
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="age != null">
and age = #{age}
</if>
<if test="born != null">
and born = #{born}
</if>
</where>
</select>
<!-- 新增所有列 -->
<insert id="insert" keyProperty="id" useGeneratedKeys="true">
insert into user(id, name, sex, age, born)
values ( #{id}, #{name}, #{sex}, #{age}, #{born})
</insert>
<!-- 批量新增 -->
<insert id="batchInsert">
insert into user(id, name, sex, age, born)
values
<foreach collection="users" item="item" index="index" separator=",">
(
#{item.id}, #{item.name}, #{item.sex}, #{item.age}, #{item.born} )
</foreach>
</insert>
<!-- 通过主键修改数据 -->
<update id="update">
update category.user
<set>
<if test="name != null and name != ''">
name = #{name},
</if>
<if test="sex != null and sex != ''">
sex = #{sex},
</if>
<if test="age != null">
age = #{age},
</if>
<if test="born != null">
born = #{born},
</if>
</set>
where id = #{id}
</update>
<!--通过主键删除-->
<delete id="deleteById">
delete from user where id = #{id}
</delete>
<!-- 总数 -->
<select id="count" resultType="int">
select count(*) from user
</select>
</mapper>
3.3 拦截器实现
1 MybatisLikeSqlInterceptor.java mybatis拦截器
package com.jerry.market.config;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
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.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
/**
* Mybatis/mybatis-plus fuzzy query statement special character escape interceptor
*
* @author zrj
* @since 2022/4/22
**/
@Slf4j
@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 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 separated
*/
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) {
System.out.println("aaaaaa");
}
/**
* Modify the SQL statement that contains like
*
* @param sql SQL statement
* @param parameterObject parameter object
* @param boundSql bound SQL object
*/
private void transferLikeSql(String sql, Object parameterObject, BoundSql boundSql) {
if (!isEscape(sql)) {
return;
}
sql = sql.replaceAll(" {2}", "");
//Get the number of keywords (de-duplication)
Set<String> fields = this.getKeyFields(sql, boundSql);
if (fields == null) {
return;
}
//This can be enhanced, not only to support the Map object, the Map object is only used for the incoming condition is Map or the object passed in using @Param is converted to Map by Mybatis
AbstractLikeSqlConverter converter;
//"Clean" keywords with special characters. If there are special characters, add an escape character (\) 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 to escape
*
* @param sql SQL statement
* @return true/false
*/
private boolean isEscape(String sql) {
return this.hasLike(sql) && this.hasPlaceholder(sql);
}
/**
* Determine whether the SQL statement contains the 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);
}
/**
* Determine whether the SQL statement contains SQL placeholders
*
* @param str SQL statement
* @return true/false
*/
private boolean hasPlaceholder(String str) {
if (StringUtils.isBlank(str)) {
return false;
}
return str.toLowerCase().contains(SQL_PLACEHOLDER);
}
/**
* Get a collection of all fields that need to be replaced
*
* @param sql complete 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 AbstractLikeSqlConverter.java 转换器抽象类
package com.jerry.market.config;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Set;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
/**
* SQL statement escape template containing like
*
* @author zrj
* @since 2022/4/22
**/
@Slf4j
public abstract class AbstractLikeSqlConverter<T> {
/**
* SQL statement like uses keyword%
*/
private final static String LIKE_SQL_KEY = "%";
/**
* Keywords that need to be escaped in SQL statements
*/
private final static String[] ESCAPE_CHAR = new String[]{LIKE_SQL_KEY, "_", "\\"};
/**
* SQL statement style like in mybatis-plus
*/
private final static String MYBATIS_PLUS_LIKE_SQL = "like ?";
/**
* Parameter prefix in mybatis-plus
*/
private final static String MYBATIS_PLUS_WRAPPER_PREFIX = "ew.paramNameValuePairs.";
/**
* Parameter key in mybatis-plus
*/
final static String MYBATIS_PLUS_WRAPPER_KEY = "ew";
/**
* Parameter separator in mybatis-plus
*/
final static String MYBATIS_PLUS_WRAPPER_SEPARATOR = ".";
/**
* Parameter separator replacer in mybatis-plus
*/
final static String MYBATIS_PLUS_WRAPPER_SEPARATOR_REGEX = "\\.";
/**
* Marks that have been replaced
*/
final static String REPLACED_LIKE_KEYWORD_MARK = "replaced.keyword";
/**
* Escape special characters
*
* @param sql SQL statement
* @param fields field list
* @param parameter parameter object
*/
public void convert(String sql, Set<String> fields, T parameter) {
for (String field : fields) {
if (this.hasMybatisPlusLikeSql(sql)) {
if (this.hasWrapper(field)) {
//The first case: use QueryWrapper, LambdaQueryWrapper for fuzzy query keywords generated by conditional construction in the business layer
this.transferWrapper(field, parameter);
} else {
//The second case: The condition constructor is not used, but the query keyword and fuzzy query symbol `%` are manually spliced in the service layer
this.transferSelf(field, parameter);
}
} else {
//The third case: Fuzzy queries are spliced in the annotation SQL of the Mapper class
this.transferSplice(field, parameter);
}
}
}
/**
* Special characters constructed by escape conditions
* Use QueryWrapper, LambdaQueryWrapper for fuzzy query keywords generated by conditional construction in the business layer
*
* @param field field name
* @param parameter parameter object
*/
public abstract void transferWrapper(String field, T parameter);
/**
* Escape special characters spliced by custom conditions
* The condition constructor is not used, but the query keyword and fuzzy query symbol `%` are manually spliced in the service layer
*
* @param field field name
* @param parameter parameter object
*/
public abstract void transferSelf(String field, T parameter);
/**
* Escape special characters spliced by custom conditions
* Fuzzy queries are spliced in the annotation SQL of the Mapper class
*
* @param field field name
* @param parameter parameter object
*/
public abstract void transferSplice(String field, T parameter);
/**
* Escape wildcard
*
* @param before the string to be escaped
* @return escaped string
*/
String escapeChar(String before) {
if (StringUtils.isNotBlank(before)) {
before = before.replaceAll("\\\\", "\\\\\\\\");
before = before.replaceAll("_", "\\\\_");
before = before.replaceAll("%", "\\\\%");
}
return before;
}
/**
* Whether it contains characters that need to be escaped
*
* @param obj the object to be judged
* @return true/false
*/
boolean hasEscapeChar(Object obj) {
if (!(obj instanceof String)) {
return false;
}
return this.hasEscapeChar((String) obj);
}
/**
* Deal with object like issues
*
* @param field object field
* @param parameter object
*/
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("Reflected {} {} get/set method is abnormal", parameter, field, e);
}
}
/**
* Determine whether it is a cascade attribute
*
* @param field field name
* @return true/false
*/
boolean cascade(String field) {
if (StringUtils.isBlank(field)) {
return false;
}
return field.contains(MYBATIS_PLUS_WRAPPER_SEPARATOR) && !this.hasWrapper(field);
}
/**
* Whether to include the SQL statement format of mybatis-plus containing like
*
* @param sql complete SQL statement
* @return true/false
*/
private boolean hasMybatisPlusLikeSql(String sql) {
if (StringUtils.isBlank(sql)) {
return false;
}
return sql.toLowerCase().contains(MYBATIS_PLUS_LIKE_SQL);
}
/**
* Determine whether to use mybatis-plus conditional constructor
*
* @param field
* @return true/false
*/
private boolean hasWrapper(String field) {
if (StringUtils.isBlank(field)) {
return false;
}
return field.contains(MYBATIS_PLUS_WRAPPER_PREFIX);
}
/**
* Determine whether the string contains characters that need to be escaped
*
* @param str String to be judged
* @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;
}
}
3 MapLikeSqlConverter.java 转换器类
package com.jerry.market.config;
import lombok.extern.slf4j.Slf4j;
import java.lang.reflect.Method;
import java.util.Map;
import java.util.Objects;
/**
* The parameter object is Map converter
*
* @author zrj
* @since 2022/4/22
**/
@Slf4j
public class MapLikeSqlConverter extends AbstractLikeSqlConverter<Map> {
@Override
public void transferWrapper(String field, Map parameter) {
Object wrapper = parameter.get(MYBATIS_PLUS_WRAPPER_KEY);
try {
Method m = wrapper.getClass().getDeclaredMethod("getParamNameValuePairs");
parameter = (Map<String, Object>) m.invoke(wrapper);
} catch (Exception e) {
log.error("反射异常", e);
return;
}
String[] keys = field.split(MYBATIS_PLUS_WRAPPER_SEPARATOR_REGEX);
//ew.paramNameValuePairs.param1, after intercepting the string, get the third one, 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 cascade 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);
}
}
4 ObjectLikeSqlConverter.java 转换器类
package com.jerry.market.config;
import lombok.extern.slf4j.Slf4j;
/**
* Universal parameter converter
*
* @author zrj
* @since 2022/4/22
**/
@Slf4j
public class ObjectLikeSqlConverter extends AbstractLikeSqlConverter<Object> {
@Override
public void transferWrapper(String field, Object parameter) {
//No such situation
}
@Override
public void transferSelf(String field, Object parameter) {
//No such situation
}
@Override
public void transferSplice(String field, Object parameter) {
this.resolveObj(field, parameter);
}
}
5 MybatisLikeSqlConfig.java mybatis拦截器注入配置类
package com.jerry.market.config;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import org.springframework.context.annotation.Lazy;
import org.springframework.core.annotation.Order;
import java.util.List;
/**
* Mybatis/mybatis-plus fuzzy query statement special character escape configuration
*
* @author zrj
* @since 2022/4/22
**/
@Configuration
@Lazy(false)
@Order
//@DependsOn("pageHelperProperties")
public class MybatisLikeSqlConfig implements InitializingBean {
@Autowired
private List<SqlSessionFactory> sqlSessionFactoryList;
public MybatisLikeSqlInterceptor mybatisSqlInterceptor() {
return new MybatisLikeSqlInterceptor();
}
@Override
public void afterPropertiesSet() throws Exception {
Interceptor interceptor = mybatisSqlInterceptor();
for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {
org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();
List<Interceptor> list = configuration.getInterceptors();
if (!containsInterceptor(configuration, interceptor)) {
configuration.addInterceptor(interceptor);
}
}
}
private boolean containsInterceptor(org.apache.ibatis.session.Configuration configuration,
Interceptor interceptor) {
try {
return configuration.getInterceptors().contains(interceptor);
} catch (Exception var4) {
return false;
}
}
}
4. 测试验证
mybatis特殊符号处理前,同样的参数查询出多条数据。
正常mybatis特殊符号未做转义，导致全部查询出来
[nio-8080-exec-1] c.j.market.mapper.UserMapper.selectList : ==> Preparing: select id, name, sex, age, born from user WHERE name like concat('%',?,'%')
[nio-8080-exec-1] c.j.market.mapper.UserMapper.selectList : ==> Parameters: %(String)
[nio-8080-exec-1] c.j.market.mapper.UserMapper.selectList : <== Total: 2
mybatis特殊符号处理后
通过mybatis拦截器将特殊符号过滤后，%作为转义字符串正常查询
[nio-8080-exec-1] c.j.market.mapper.UserMapper.selectList : ==> Preparing: select id, name, sex, age, born from user WHERE name like concat('%',?,'%')
[nio-8080-exec-1] c.j.market.mapper.UserMapper.selectList : ==> Parameters: \%(String)
[nio-8080-exec-1] c.j.market.mapper.UserMapper.selectList : <== Total: 1
加载全部内容