Mybatis特殊字符转义查询实现_开发_开发者

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 ，方法时序如下：

Mybatis特殊字符转义查询实现

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`,php `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(Ushttp://www.devze.comer 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;
        }
        ZtjzZYkxereturn 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
     *
     * js@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;
        }
        Str开发者_JS学习ing[] 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特殊符号处理后

通过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特殊字符转义查询实现