Mybatis特殊字符转义查询实现
短信预约 -IT技能 免费直播动态提醒
1. 问题描述
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;
@RestController
@RequestMapping("/user")
@Api(tags = "UserController", description = "用户控制器")
public class UserController {
@Resource
private UserService userService;
@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("查询失败");
}
@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("新增失败");
}
@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("新增失败");
}
@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("修改失败");
}
@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("删除失败");
}
@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("查询失败");
}
@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("查询失败");
}
@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;
public interface UserService {
User selectById(Object id);
List<User> selectPage(int start, int limit);
List<User> selectAll();
List<User> selectList(User user);
int insert(User user);
int batchInsert(List<User> users);
User update(User user);
int deleteById(Object id);
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;
@Service("userService")
public class UserServiceImpl implements UserService {
@Resource
private UserMapper userMapper;
@Override
public User selectById(Object id) {
return this.userMapper.selectById(id);
}
@Override
public List<User> selectPage(int start, int limit) {
return this.userMapper.selectPage(start, limit);
}
@Override
public List<User> selectAll() {
return this.userMapper.selectAll();
}
@Override
public List<User> selectList(User user) {
return this.userMapper.selectList(user);
}
@Override
public int insert(User user) {
return this.userMapper.insert(user);
}
@Override
public int batchInsert(List<User> users) {
return this.userMapper.batchInsert(users);
}
@Override
public User update(User user) {
this.userMapper.update(user);
return this.selectById(user.getId());
}
@Override
public int deleteById(Object id) {
return this.userMapper.deleteById(id);
}
@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;
public interface UserMapper {
User selectById(Object id);
List<User> selectPage(@Param("start") int start, @Param("limit") int limit);
List<User> selectAll();
List<User> selectList(User user);
int insert(User user);
int batchInsert(List<User> users);
int update(User user);
int deleteById(Object id);
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;
@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 {
private final static String SQL_LIKE = "like ";
private final static String SQL_PLACEHOLDER = "?";
private final static String SQL_PLACEHOLDER_REGEX = "\\?";
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");
}
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);
}
private boolean isEscape(String sql) {
return this.hasLike(sql) && this.hasPlaceholder(sql);
}
private boolean hasLike(String str) {
if (StringUtils.isBlank(str)) {
return false;
}
return str.toLowerCase().contains(SQL_LIKE);
}
private boolean hasPlaceholder(String str) {
if (StringUtils.isBlank(str)) {
return false;
}
return str.toLowerCase().contains(SQL_PLACEHOLDER);
}
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;
@Slf4j
public abstract class AbstractLikeSqlConverter<T> {
private final static String LIKE_SQL_KEY = "%";
private final static String[] ESCAPE_CHAR = new String[]{LIKE_SQL_KEY, "_", "\\"};
private final static String MYBATIS_PLUS_LIKE_SQL = "like ?";
private final static String MYBATIS_PLUS_WRAPPER_PREFIX = "ew.paramNameValuePairs.";
final static String MYBATIS_PLUS_WRAPPER_KEY = "ew";
final static String MYBATIS_PLUS_WRAPPER_SEPARATOR = ".";
final static String MYBATIS_PLUS_WRAPPER_SEPARATOR_REGEX = "\\.";
final static String REPLACED_LIKE_KEYWORD_MARK = "replaced.keyword";
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);
}
}
}
public abstract void transferWrapper(String field, T parameter);
public abstract void transferSelf(String field, T parameter);
public abstract void transferSplice(String field, T parameter);
String escapeChar(String before) {
if (StringUtils.isNotBlank(before)) {
before = before.replaceAll("\\\\", "\\\\\\\\");
before = before.replaceAll("_", "\\\\_");
before = before.replaceAll("%", "\\\\%");
}
return before;
}
boolean hasEscapeChar(Object obj) {
if (!(obj instanceof String)) {
return false;
}
return this.hasEscapeChar((String) obj);
}
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);
}
}
boolean cascade(String field) {
if (StringUtils.isBlank(field)) {
return false;
}
return field.contains(MYBATIS_PLUS_WRAPPER_SEPARATOR) && !this.hasWrapper(field);
}
private boolean hasMybatisPlusLikeSql(String sql) {
if (StringUtils.isBlank(sql)) {
return false;
}
return sql.toLowerCase().contains(MYBATIS_PLUS_LIKE_SQL);
}
private boolean hasWrapper(String field) {
if (StringUtils.isBlank(field)) {
return false;
}
return field.contains(MYBATIS_PLUS_WRAPPER_PREFIX);
}
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;
@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()));
}
}
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;
@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;
@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
到此这篇关于Mybatis特殊字符转义查询实现的文章就介绍到这了,更多相关Mybatis特殊字符转义查询内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
