Mybatis特殊字符转义查询实现

目录

• 1. 问题描述
• 2. 解决方案
• 3. 设计实现
• 3.1 环境准备
• 3.2 代码实现
• 3.3 拦截器实现
• 4. 测试验证

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`,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拦截器将特殊符号过滤后，%作为转义字符串正常查询
[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特殊字符转义查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！