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`, `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 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 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 batchInsert(@RequestBody List 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 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 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> selectAll() {
        List users = userService.selectAll();
        if (users != null) {
            return Response.success("查询成功", users);
        }
        return Response.fail("查询失败");
    }

    
    @ApiOperation("通过实体作为筛选条件查询")
    @RequestMapping(value = "selectList", method = RequestMethod.GET)
    public Response> selectList(User user) {
        List users = userService.selectList(user);
        if (users != null) {
            return Response.success("查询成功", users);
        }
        return Response.fail("查询失败");
    }

    
    @ApiOperation("分页查询")
    @RequestMapping(value = "selectPage", method = RequestMethod.GET)
    public Response> selectPage(Integer start, Integer limit) {
        List 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 selectPage(int start, int limit);

    
    List selectAll();

    
    List selectList(User user);

    
    int insert(User user);

    
    int batchInsert(List 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 selectPage(int start, int limit) {
        return this.userMapper.selectPage(start, limit);
    }

    
    @Override
    public List selectAll() {
        return this.userMapper.selectAll();
    }

    
    @Override
    public List selectList(User user) {
        return this.userMapper.selectList(user);
    }

    
    @Override
    public int insert(User user) {
        return this.userMapper.insert(user);
    }

    
    @Override
    public int batchInsert(List 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 selectPage(@Param("start") int start, @Param("limit") int limit);

    
    List selectAll();

    
    List selectList(User user);

    
    int insert(User user);

    
    int batchInsert(List users);

    
    int update(User user);

    
    int deleteById(Object id);

    
    int count();
}

UserMapper.xml




    
    
        
        
        
        
        
    

    
    
        id, name, sex, age, born    

    
    
        select
        
        from user
        limit #{start},#{limit}
    

    
    
        select
        
        from user
        
            
                and id = #{id}
            
            
                and name like concat('%',#{name},'%')
            
            
                and sex = #{sex}
            
            
                and age = #{age}
            
            
                and born = #{born}
            
        
    

    
    
        insert into user(id, name, sex, age, born)
        values ( #{id}, #{name}, #{sex}, #{age}, #{born})
    

    
    
        insert into user(id, name, sex, age, born)
        values
        
            (
            #{item.id}, #{item.name}, #{item.sex}, #{item.age}, #{item.born} )
        
    

    
    
        update category.user
        
            
                name = #{name},
            
            
                sex = #{sex},
            
            
                age = #{age},
            
            
                born = #{born},
            
        
        where id = #{id}
    

    
    
        delete from user where id = #{id}