MyBatis-Plus 02- 深入学习

水深无声 2023-02-11 03:07 16阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70_pic_center]

> 公司从 MyBatis 升级为MyBatis-Plus，所以开始学习，在这里留下学习总结，也希望能带给读者一些帮助，同时文章中有什么不对的地方欢迎指出。  
> [HERE 直达 MyBatis-Plus 官网][HERE _ MyBatis-Plus]  
> [HERE 直达 MyBatis-Plus 01- 整合SpringBoot][HERE _ MyBatis-Plus 01- _SpringBoot]

### 文章目录 ###

*   *  一、CRUD接口
     *   *  这里是表结构及测试数据
         *  UserService
         *  UserServiceImpl
         *  UserController
     *  二、条件构造器
     *   *  UserService
         *  UserServiceImpl
         *  UserController
         *  其他条件参数说明
     *  三、分页插件
     *   *  UserMapper.java
         *  UserMapper.xml
         *  UserService.java
         *  UserServiceImpl.java
         *  UserController

在开始之前需要补充下，如果是跟着 [MyBatis-Plus 01- 整合SpringBoot (超详细)][HERE _ MyBatis-Plus 01- _SpringBoot] 这篇博客进来的，有两个地方需要做修改：

1.将数据库User表的ID字段改为varchar(32)类型。  
2.将实体类User表中的ID字段改为String，并且使用注解 `@TableId(type = IdType.UUID)` 设置ID生成策略为UUID。

如图。  
![在这里插入图片描述][20200521163357172.png]

## 一、CRUD接口 ##

> 这里只列举了比较常用的一些mapper层通用CRUD的方法，查看更多 [进入官网 CRUD接口][CRUD]。

### 这里是表结构及测试数据 ###

DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '主键ID',
      `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
      `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
      `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `user` VALUES ('1', 'Jone', 35, 'test1@baomidou.com');
    INSERT INTO `user` VALUES ('2', 'Jack', 20, 'test2@baomidou.com');
    INSERT INTO `user` VALUES ('3', 'Tom', 28, 'test3@baomidou.com');
    INSERT INTO `user` VALUES ('4', 'Sandy', 21, 'test4@baomidou.com');
    INSERT INTO `user` VALUES ('5', 'Billie', 24, 'test5@baomidou.com');
    INSERT INTO `user` VALUES ('6', '国服安琪拉', 18, 'anqila@163.com');
    INSERT INTO `user` VALUES ('7', '哪吒', 9, 'nezha@qq.com');
    INSERT INTO `user` VALUES ('8', '盲僧', 30, 'mangseng@163.com');
    INSERT INTO `user` VALUES ('9', '小乔', 29, 'xiaoqiao123@163.com');
    INSERT INTO `user` VALUES ('10', '小悟空', 35, 'wukong521@qq.com');

### UserService ###

package com.demo.kathy.domain.service;
    
    import com.baomidou.mybatisplus.core.conditions.Wrapper;
    import com.baomidou.mybatisplus.core.metadata.IPage;
    import com.demo.kathy.domain.entity.User;
    import com.baomidou.mybatisplus.extension.service.IService;
    import com.demo.kathy.dto.request.GetUserReq;
    
    import java.util.List;
    import java.util.Map;
    
    /**
     * <p>
     *  服务类
     * </p>
     *
     * @author kathy
     * @since 2020-05-20
     */
    public interface UserService extends IService<User> {
    
        /**
         * 添加单个用户
         * @param user 用户对象
         * @return
         */
        int insertUser(User user);
    
        /**
         * 删除（根据ID 批量删除）
         * @param idList 主键ID列表(不能为 null 以及 empty)
         * @return
         */
        int deleteUserByBatchIds(List<String> idList);
    
        /**
         * 根据id删除单条记录
         * @param id 主键ID
         * @return
         */
        int deleteUserById(String id);
    
        /**
         * 根据columnMap 条件，删除记录
         * @param columnMap 表字段 map 对象
         * @return
         */
        int deleteUserByMap(Map<String, Object> columnMap);
    
        /**
         * 根据id更新
         * @param user
         * @return
         */
        int updateUserById(User user);
    
        /**
         * 根据 ID 查询
         * @param id
         * @return
         */
        User selectUserById(String id);
    
        /**
         * 查询（根据ID 批量查询）
         * @param idList
         * @return
         */
        List<User> selectUserByBatchIds(List<String> idList);
    
        /**
         * 查询（根据 columnMap 条件）
         * @param columnMap
         * @return
         */
        List<User> selectUserByMap(Map<String, Object> columnMap);
    }

### UserServiceImpl ###

package com.demo.kathy.domain.service.impl;
    
    import com.baomidou.mybatisplus.core.conditions.Wrapper;
    import com.demo.kathy.domain.entity.User;
    import com.demo.kathy.domain.mapper.UserMapper;
    import com.demo.kathy.domain.service.UserService;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    import java.util.Map;
    
    /**
     * <p>
     * 服务实现类
     * </p>
     *
     * @author kathy
     * @since 2020-05-20
     */
    @Service
    public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
    
        @Override
        public int insertUser(User user) {
            return baseMapper.insert(user);
        }
    
        @Override
        public int deleteUserByBatchIds(List<String> idList) {
            return baseMapper.deleteBatchIds(idList);
        }
    
        @Override
        public int deleteUserById(String id) {
            return baseMapper.deleteById(id);
        }
    
        @Override
        public int deleteUserByMap(Map<String, Object> columnMap) {
            return baseMapper.deleteByMap(columnMap);
        }
    
        @Override
        public int updateUserById(User user) {
            return baseMapper.updateById(user);
        }
    
        @Override
        public User selectUserById(String id) {
            return baseMapper.selectById(id);
        }
    
        @Override
        public List<User> selectUserByBatchIds(List<String> idList) {
            return baseMapper.selectBatchIds(idList);
        }
    
        @Override
        public List<User> selectUserByMap(Map<String, Object> columnMap) {
            return baseMapper.selectByMap(columnMap);
        }
    }

### UserController ###

package com.demo.kathy.controller;
    
    
    import com.demo.kathy.domain.entity.User;
    import com.demo.kathy.domain.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.stream.Collectors;
    import java.util.stream.Stream;
    
    /**
     * <p>
     *  前端控制器
     * </p>
     *
     * @author kathy
     * @since 2020-05-20
     */
    @RestController
    @RequestMapping("/user")
    public class UserController{
    
        @Autowired
        private UserService userService;
    
        @GetMapping("/insertUser")
        public int insertUser() {
            return userService.insertUser(new User("国服安琪拉",18,"anqila@163.com"));
        }
    
        @GetMapping("/deleteUserByBatchIds")
        public int deleteUserByBatchIds() {
            //批量删除ID 为1和2的用户
            return userService.deleteUserByBatchIds(Stream.of("1", "2").collect(Collectors.toList()));
        }
    
        @GetMapping("/deleteUserById")
        public int deleteUserById() {
            //删除ID为3的用户
            return userService.deleteUserById("3");
        }
    
        @GetMapping("/deleteUserByMap")
        public int deleteUserByMap() {
            //删除姓名为 "Sandy"的用户
            Map<String, Object> columnMap=new HashMap<>();
            columnMap.put("name","Sandy");
            return userService.deleteUserByMap(columnMap);
        }
    
        @GetMapping("/updateUserById")
        public int updateUserById() {
            //根据id修改用户信息
            return userService.updateUserById(new User("5","update-Billie",20,"billie@qq.com"));
        }
    
        @GetMapping("/selectUserById")
        public User selectUserById() {
            //查询ID为5的用户
            return userService.selectUserById("5");
        }
    
        @GetMapping("/selectUserByBatchIds")
        public List<User> selectUserByBatchIds() {
            //查询ID为1和2的用户返回List
            return userService.selectUserByBatchIds(Stream.of("1", "2").collect(Collectors.toList()));
        }
    
        @GetMapping("/selectUserByMap")
        public List<User> selectUserByMap() {
            Map<String, Object> columnMap=new HashMap<>();
            //查询姓名为"安琪拉"并且年龄为18岁的用户
            columnMap.put("name","安琪拉");
            columnMap.put("age",18);
            return userService.selectUserByMap(columnMap);
        }
    }

## 二、条件构造器 ##

> 在第一节中调用了一些通用的比较基础的CRUD方法，但是在实际开发中因为业务的复杂性，一些简单的操作是无法满足产品需求的，不过不要慌，MyBatis-Plus为我们提供了强大的条件构造器，用于处理sql条件拼接、排序等等。  
> [这里进入官网 -条件构造器][-]

条件构造器这里需要注意的是  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 1]

### UserService ###

/**
       * 使用条件构造器查询用户列表
       * @param columnMap
       * @return
       */
     List<User> selectUserByCon(Map<String,Object> columnMap);

### UserServiceImpl ###

@Override
        public List<User> selectUserByCon(Map<String,Object> columnMap){
            //将查询入参转换为对象
            User queryUser=BeanUtil.mapToBean(columnMap,User.class,true);
            //创建条件构造器
            QueryWrapper<User> userQueryWrapper=new QueryWrapper<>();
    
            //1.查询姓名=xxx
            /*userQueryWrapper.lambda().eq(ObjectUtils.isNotEmpty(queryUser.getName()),User::getName,queryUser.getName());*/
            //2.查询姓名=xxx 或者 邮箱包含xxx
            /*userQueryWrapper.lambda().eq(ObjectUtils.isNotEmpty(queryUser.getName()),User::getName,queryUser.getName())
                    .or()
                    .like(User::getEmail,queryUser.getEmail());*/
            //3.查询年龄不等于<>xxx 并且在18-30之间的
            //userQueryWrapper.lambda().ne(ObjectUtils.isNotNull(queryUser.getAge()),User::getAge,queryUser.getAge())
                    //大于
                    /*.gt(ObjectUtils.isNotNull(queryUser.getAge()),User::getAge,queryUser.getAge())
                    //小于
                    .lt(ObjectUtils.isNotNull(queryUser.getAge()),User::getAge,queryUser.getAge())
                    //大于等于
                    .ge(ObjectUtils.isNotNull(queryUser.getAge()),User::getAge,queryUser.getAge())
                    //小于等于
                    .le(ObjectUtils.isNotNull(queryUser.getAge()),User::getAge,queryUser.getAge())*/
                    //.between(ObjectUtils.isNotNull(queryUser.getAge()),User::getAge,18,30);
    
            //4.查询姓名包含xxx 并且 年龄大于xx
            /*userQueryWrapper.lambda().like(ObjectUtils.isNotEmpty(queryUser.getName()),User::getName,queryUser.getName()).
                    gt(ObjectUtils.isNotNull(queryUser.getAge()),User::getAge,queryUser.getAge());*/
    
            //5.查询（姓名包含xxx并且年龄大于等于xx的）或者 （邮箱包含xxx的）
            /*userQueryWrapper.lambda().like(ObjectUtils.isNotEmpty(queryUser.getName()),User::getName,queryUser.getName()).
                    ge(ObjectUtils.isNotNull(queryUser.getAge()),User::getAge,queryUser.getAge())
                    .or()
                    .like(ObjectUtils.isNotEmpty(queryUser.getEmail()),User::getEmail,queryUser.getEmail());*/
    
            //6.查询（姓名包含xxx并且年龄小于等于xx的）或者 （邮箱包含xxx并且id=1的）
            userQueryWrapper.lambda().like(ObjectUtils.isNotEmpty(queryUser.getName()),User::getName,queryUser.getName()).
                    le(ObjectUtils.isNotNull(queryUser.getAge()),User::getAge,queryUser.getAge())
                    .or()
                    .like(ObjectUtils.isNotEmpty(queryUser.getEmail()),User::getEmail,queryUser.getEmail())
                    .eq(ObjectUtils.isNotEmpty(queryUser.getId()),User::getId,queryUser.getId());
    
             //根据id升序排序
            userQueryWrapper.lambda().orderByAsc(User::getId);
            //先根据id降序排序,再根据年龄降序排列
            userQueryWrapper.orderByDesc("id","age");
            return baseMapper.selectList(userQueryWrapper);
        }

### UserController ###

@GetMapping("/selectUserByCon")
        public List<User> selectUserByCon(){
            Map<String, Object> columnMap=new HashMap<>();
            columnMap.put("name","小");
            columnMap.put("age",35);
    
            columnMap.put("email","test");
            columnMap.put("id","1");
            userService.selectUserByCon(columnMap).forEach(System.out::println);
            return userService.selectUserByCon(columnMap);
        }

### 其他条件参数说明 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 3]

> 图片来自： 程序员不弃  
> https://blog.csdn.net/kye055947/article/details/83692476

## 三、分页插件 ##

> 这里需要导入分页插件，并且需要注入分页拦截器，如果是从[MyBatis-Plus系列-01][HERE _ MyBatis-Plus 01- _SpringBoot] 进入的，那我们之前已经配置过了，无需再配置。

<dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper-spring-boot-starter</artifactId>
        <version>1.2.10</version>
    </dependency>

@EnableTransactionManagement
    @Configuration
    @MapperScan("com.demo.kathy.domain.mapper")
    public class MybatisConfiguration {
    
        @Bean
        public PaginationInterceptor paginationInterceptor(){
            return new PaginationInterceptor();
        }
    }

### UserMapper.java ###

public interface UserMapper extends BaseMapper<User> {
    
        IPage<User> selectUserByPage(@Param("page") Page<User> page, @Param("queryUser") User queryUser);
    }

**这里需要注意的是：分页返回的对象与传入的对象是同一个**

### 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.demo.kathy.domain.mapper.UserMapper">
    
        <select id="selectUserByPage" resultType="com.demo.kathy.domain.entity.User">
            SELECT
            id,
            `name`,
            age,
            email
            FROM
            `user`
            <where>
                <if test="queryUser.name!=null and queryUser.name!=''">
                    AND `name` LIKE CONCAT('%',#{queryUser.name},'%')
                </if>
                <if test="queryUser.age!=null">
                    AND age >#{queryUser.age}
                </if>
            </where>
        </select>
    </mapper>

### UserService.java ###

/**
         * 分页条件查询
         * @param page
         * @param user
         * @return
         */
        IPage<User> selectUserByPage(Page<User> page, User user);

### UserServiceImpl.java ###

/**
         * 分页条件查询
         * @param page
         * @param user
         * @return
         */
        @Override
        public IPage<User> selectUserByPage(Page<User> page, User user) {
           //方式1:自己手写条件并注入然后让MyBatisPlus帮助我们分页
           //return userMapper.selectUserByPage(page,user);
    
            //方式2:使用条件构造器进行条件注入并分页，与方式一同等效果
            QueryWrapper<User> queryWrapper = new QueryWrapper();
            queryWrapper.lambda().like(ObjectUtils.isNotEmpty(user.getName()),User::getName,user.getName())
                    .gt(ObjectUtils.isNotEmpty(user.getAge()),User::getAge,user.getAge());
            return baseMapper.selectPage(page,queryWrapper);
        }

### UserController ###

@GetMapping("/selectUserByPage")
        public IPage<User> selectUserByPage(){
            User queryUser=new User();
            queryUser.setName("安琪拉");
            queryUser.setAge(18);
            return userService.selectUserByPage(new Page<User>(1,5),queryUser);
        }

> 关注微信公众号【程序媛琬淇】，专注分享Java干货，给你意想不到的收获。  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70_pic_center 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200521103540587.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ==,size_16,color_FFFFFF,t_70#pic_center
[HERE _ MyBatis-Plus]: https://mp.baomidou.com/
[HERE _ MyBatis-Plus 01- _SpringBoot]: https://blog.csdn.net/weixin_43094361/article/details/106237893
[20200521163357172.png]: https://img-blog.csdnimg.cn/20200521163357172.png
[CRUD]: https://mybatis.plus/guide/crud-interface.html#service-crud-%E6%8E%A5%E5%8F%A3
[-]: https://mybatis.plus/guide/wrapper.html#abstractwrapper
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200522101638557.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200522101710534.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200522152750249.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200522152825867.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/2020052613161360.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzA5NDM2MQ==,size_16,color_FFFFFF,t_70#pic_center