笔记总结：Web后端——MyBatisPlus

MyBatisPlus

一、配置文件

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.3.1</version>
</dependency>

注意：尽量不要与mybatis配置文件同时出现

二、用法

1.mapper

@Mapper
public interface UserMapper extends BaseMapper<User> {
    
}

继承接口，自动编写所有CRUD方法

2.service

public interface IUserService extends IService<User> {
    
}

实现类：

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {

}

• 批处理

  for循环每次都预编译SQL语句，而MP批处理只预编译了一次

  //mp批处理
  @Test
  void testSaveBatch(){
      long b = System.currentTimeMillis();
      List<User> list = new ArrayList<>(1000);
      for(int i = 1; i <= 100000; ++i){
          list.add(buildUser(i));
          //每次批处理1000条记录
          if(i % 1000 == 0){
              userService.saveBatch(list);
              list.clear();
          }
      }
      long e = System.currentTimeMillis();
      System.out.println("耗时：" + (e - b) + "ms");
  }
  

  每次为SQL语句添加参数

  配置JDBC参数，在数据库链接后添加：

  rewriteBatchedStatements=true
  

  修改了SQL语句，使得语句从添加参数变为values(xxx),(xxx)

• LambdaQuery

  @Test
  void testLambdaQuery(){
      User user = userService.lambdaQuery().eq(User::getUsername, "Rose").one();
      System.out.println(user);
      List<User> users = userService.lambdaQuery().like(User::getUsername, "o").list();
      users.forEach(System.out::println);
      Long count = userService.lambdaQuery().like(User::getUsername, "o").count();
      System.out.println(count);
  }
  

  动态SQL

  public List<User> queryUsers(String username, Integer status, Long min, Long max){
      return userService.lambdaQuery().like(username != null, User::getUsername, username)
          .eq(status != null, User::getStatus, status)
          .gt(min != null, User::getBalance, min)
          .lt(max != null, User::getBalance, max)
          .list();
  }
  

• LambdaUpdate

  public void updateBalance(Long balance, Long id, String username){
      if(id == null && username == null){
          throw new RuntimeException("更新条件不能为空");
      }
      userService.lambdaUpdate()
          .set(User::getBalance, balance)
          //当余额为0时，冻结账户
          .set(balance == 0, User::getStatus, 2)
          .eq(id != null, User::getId, id)
          .eq(username != null, User::getUsername, username)
          .update();  //执行update语句
  }
  

• Db静态工具

  • 解决注入Service时发生循环依赖的问题，使用静态工具不需要注入Bean对象
  • Db可以通过传入的实体类型，自动查找对应实体，完成数据库操作
  • Service获取Mapper还可以通过getBaseMapper方法获取，此办法适用于需要自定义SQL的场景

  @Test
  public void testUpdate(){
      User user = new User();
      //动态SQL
      user.setId(6L);
      user.setName("狂神说");
      //参数是一个对象
      int i = Db.updateById(user);
      System.out.println(i);
  }
  

三、常用注解

1.@TableName

• 类注解，将类名映射数据库表名

2.@TableId

• 主键注解，标注属性主键
• 设置主键生成策略

3.@TableField

• 属性注解，属性名映射数据库字段名
• 使用场景：
  • 变量名与字段名不一致
  • isXXX成员变量，MP识别自动删除is，需要手动映射
  • 变量名与数据库关键字冲突，手动添加转义

三、CRUD扩展

1.insert

@Test
void testInsert(){
    User user = new User();
    user.setName("小明");
    user.setAge(18);
    user.setEmail("123456@qq.com");

    int result = userMapper.insert(user);   //自动生成id，默认雪花算法
    System.out.println(result); //返回受影响的行数
    System.out.println(user);   //自动回填
}

• 主键生成策略：uuid、自增id、雪花算法、redis、zookeeper

• 雪花算法：分布式系统唯一id生成。

  使用41bit作为毫秒数，10bit作为机器ID（5bit数据中心、5bit机器ID），12bit作为毫秒内的流水号，最后为符号位0。

  可以保证几乎全球唯一。

• @TableId注解

  设置主键生成策略

  public class User {
      @TableId(type = IdType.ASSIGN_ID)	
      private Long id;
      private String name;
      private Integer age;
      private String email;
  }
  

  public enum IdType {
      AUTO(0),	//主键自增，数据库对应也一定是自增
      NONE(1),	//未设置主键
      INPUT(2),	//手动输入,不会自动添加
      ASSIGN_ID(3),	//分配Id，默认雪花算法
      ASSIGN_UUID(4);	//UUID
  }
  

2.update

@Test
public void testUpdate(){
    User user = new User();
    //动态SQL
    user.setId(6L);
    user.setName("狂神说");
    //参数是一个对象
    int i = userMapper.updateById(user);
    System.out.println(i);
}

• 自动填充create_time、modified_time

• 注解@TableField

  @TableField(fill = FieldFill.INSERT)
  private LocalDateTime createTime;
  @TableField(fill = FieldFill.INSERT_UPDATE)
  private LocalDateTime updateTime;
  

• 设置自动填充处理器

  @Slf4j
  //把处理器加入到IOC容器中
  @Component
  public class MyMetaObjectHandler implements MetaObjectHandler {
  
      @Override
      public void insertFill(MetaObject metaObject) {
          log.info("start insert fill...");
          this.setFieldValByName("createTime", LocalDateTime.now(), metaObject);
          this.setFieldValByName("updateTime", LocalDateTime.now(), metaObject);
      }
  
      @Override
      public void updateFill(MetaObject metaObject) {
          log.info("start update fill..");
          this.setFieldValByName("updateTime", LocalDateTime.now(), metaObject);
      }
  }
  

• 乐观锁

  乐观锁：总是认为不会出现问题，无论干什么都不去上锁，当出现了问题，再次更新值测定。

  悲观锁：总是认为会出现问题，无论干什么都上锁。

  用于线程安全处理

  # A线程
  update user set name = "aaa", version = version + 1 where id = 2 and version = 1;
  # B线程
  update user set name = "aaa", version = version + 1 where id = 2 and version = 1;
  

  当B线程抢先对version+1，A线程将更新失败。

  MP乐观锁插件：

  //添加乐观锁注解
  @Version
  private Integer version;
  

  注册乐观锁插件：

  //自动管理事物
  @EnableTransactionManagement
  @Configuration
  public class MyBatisPlusConfig {
      //注册乐观锁插件(MP内置拦截器，用于在与数据库交互前拦截数据，执行逻辑)
      @Bean
      public MybatisPlusInterceptor mybatisPlusInterceptor() {
          MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
          interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
          return interceptor;
      }
  }
  

  测试模拟多线程：

  //测试乐观锁
  @Test
  public void testOptimisticLockerInnerInterceptor(){
      //线程1
      User user = userMapper.selectById(1);
      user.setName("张无忌");
  
      //另外一个线程进行插队
      User user2 = userMapper.selectById(1);
      user2.setName("刘邦");
      userMapper.updateById(user2);
  
      //被乐观锁阻断了
      userMapper.updateById(user);
  }
  

3.select

//测试查询
@Test
public void testSelectById(){
    User user = userMapper.selectById(1L);
    System.out.println(user);
}

//批量查询
@Test
public void testSelectByBatchIds(){
    List<User> users = userMapper.selectBatchIds(Arrays.asList(1,2,3));
    users.forEach(System.out::println);
}

//按条件查询
@Test
public void testSelectByMap(){
    HashMap<String, Object> map = new HashMap<>();
    map.put("name", "Jack");
    List<User> users = userMapper.selectByMap(map);
    users.forEach(System.out::println);
}

• 分页查询

  插件配置：

  @Bean
  public MybatisPlusInterceptor mybatisPlusInterceptor() {
      MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
      PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
      //设置最大分页数
      paginationInnerInterceptor.setMaxLimit(1000L);
      interceptor.addInnerInterceptor(paginationInnerInterceptor);
      return interceptor;
  }
  

  分页查询测试：

  @Test
  void testPage(){
      int pageNo = 1, pageSize = 5;
      //分页条件
      Page<User> p = Page.of(pageNo, pageSize);
      //排序条件,true升序，false降序
      p.addOrder(new OrderItem("balance", false));、
      //分页查询
      Page<User> page = userService.page(p);
      //分页结果
      long total = page.getTotal();
      System.out.println("total = " + total);
      long pages = page.getPages();
      System.out.println("pages = " + pages);
      List<User> records = page.getRecords();
      records.forEach(System.out::println);
  }
  

4.delete

//测试删除
@Test
public void testDeleteById(){
    userMapper.deleteById(1703042369650462724L);
}

//批量删除
@Test
public void testDeleteByBatchIds(){
    userMapper.deleteBatchIds(Arrays.asList(2, 3));
}

//条件删除
@Test
public void testDeleteMap(){
    HashMap<String, Object> hashMap = new HashMap<>();
    hashMap.put("name", "张三");
    userMapper.deleteByMap(hashMap);
}

• 逻辑删除

  物理删除：从数据库直接移除

  逻辑删除：在数据库中没有移除，而是通过变量来让他失效，查询不到他，设置deleted = 1

  管理员可以查看被删除的数据，类似于回收站

• 配置application.yml

  mybatis-plus:
    global-config:
      db-config:
        logic-delete-field: flag # 全局逻辑删除的实体字段名(since 3.3.0,配置后可以忽略不配置步骤2)
        logic-delete-value: 1 # 逻辑已删除值(默认为 1)
        logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
  

• 为实体类的逻辑删除属性添加@TableLogic注解

• 查询操作会自动拼接上delete条件

  SELECT id,name,age,email,version,deleted,create_time,update_time FROM user WHERE id=? AND deleted=0
  

四、条件构造器Wrapper

@Test
void contextLoads() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //设置查询的字段和条件
    queryWrapper.isNotNull("name").isNotNull("email").ge("age", 12).select("name", "email", "age");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

@Test
void test1(){
    //查询一个记录
    userMapper.selectOne(new QueryWrapper<User>().eq("name", "Jack"));
}

@Test
void test2(){
    //查询结果数
    Long count = userMapper.selectCount(new QueryWrapper<User>().between("age", 20, 30));
    System.out.println(count);
}

//模糊查询
@Test
void test3(){
    List<Map<String, Object>> maps = userMapper.selectMaps(new QueryWrapper<User>().notLike("name", "e")
                                                           //右边以t开头
                                                           .likeRight("email", "t"));
    maps.forEach(System.out::println);
}

@Test
void test4(){
    //使用sql语句进行联表子查询
    List<Object> objects = userMapper.selectObjs(new QueryWrapper<User>().inSql("id", "select id from user where id < 3"));
    objects.forEach(System.out::println);
}

@Test
void test5(){
    //通过id进行降序排序
    List<Object> objects = userMapper.selectObjs(new QueryWrapper<User>().orderByDesc("id"));
    objects.forEach(System.out::println);
}

@Test
void test6(){
    User user = new User();
    user.setAge(64);
    //更新记录
    userMapper.update(user, new QueryWrapper<User>().eq("name", "赵四"));
}

//基于UpdateWrapper
@Test
void test7(){
    //设置set语句
    userMapper.update(null, new UpdateWrapper<User>().setSql("age = age - 1")
                      .in("id", Arrays.asList(1, 2, 3)));
}

//基于LambdaQueryWrapper
@Test
void test8(){
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //防止直接写入属性名的硬编码，利用方法反射获取属性名
    wrapper.lambda().select(User::getId, User::getName, User::getAge)
        .like(User::getName, "赵");
    userMapper.selectList(wrapper);
}

自定义sql语句

@Test
void testCustomSql(){
    int amount = 2;
    QueryWrapper<User> wrapper = new QueryWrapper<User>().in("id", Arrays.asList(1, 2, 3)).eq("deleted", 0);
    //注意：逻辑删除失效了！手动添加
    userMapper.updateAgeByWrapper(amount, wrapper);
}

//注入wrapper生成的条件语句
@Update("UPDATE user set age = age - #{amount} ${ew.customSqlSegment}")
void updateAgeByWrapper(@Param("amount") int amount, @Param("ew") QueryWrapper<User> wrapper);

联表查询：

查询给定用户id值，且地址在北京的记录

@Test
void testMultiTableQuery() {
    List<Long> ids = List.of(1L, 2L, 4L);
    String city = "北京";
    QueryWrapper<User> queryWrapper = new QueryWrapper<User>().in("u.id", ids)
        .eq("a.city", city);
    List<User> users = userMapper.queryUsersByWrapper(queryWrapper);
    users.forEach(System.out::println);
}

List<User> queryUsersByWrapper(@Param("ew") QueryWrapper<User> wrapper);

xml配置文件

<select id="queryUsersByWrapper" resultType="com.itheima.po.User">
    select u.*
    from user u
    inner join address a on u.id = a.user_id
    ${ew.customSqlSegment}
</select>

五、其他处理器

1.枚举类型处理器

在数据库中填入枚举类型的实际值

1. 在枚举类型中为值变量添加注解@EnumValue

   @Getter
   public enum UserStatus {
   
       NORMAL(1, "正常"),
       FREEZE(2, "冻结"),
       ;
   
       @EnumValue
       private final int value;
       private final String decs;
   
       //私有的
       UserStatus(int value, String decs) {
           this.value = value;
           this.decs = decs;
       }
   }
   

2. 配置全局枚举处理器

   mybatis-plus:
       # 修改 mybatis 的 DefaultEnumTypeHandler
       configuration:
           default-enum-type-handler: com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler
   

2.JSON处理器

数据库中以JSON格式存储的数据导出为实体对象

1. 在实体对象属性上添加注解@TableField(typeHandler = JacksonTypeHandler.class)

   @Data
   @NoArgsConstructor
   @AllArgsConstructor(staticName = "of")
   public class UserInfo {
       private Integer age;
       private String intro;
       private String gender;
   }
   

   @TableField(typeHandler = JacksonTypeHandler.class)
   private UserInfo info;
   

2. 导入json依赖

   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-json</artifactId>
   </dependency>
   

3. 注意对象嵌套需要设置参数

   @TableName(autoResultMap = true)
   

六、配置加密

MP拥有基于AES算法的加密工具

1.获取密钥与加密

@Test
void contextLoads() {
    // 生成 16 位随机 AES 密钥
    String randomKey = AES.generateRandomKey();
    System.out.println("randomKey = " + randomKey);

    // 利用密钥对用户名加密
    String username = AES.encrypt("root", randomKey);
    System.out.println("username = " + username);

    // 利用密钥对密码加密
    String password = AES.encrypt("MySQL123", randomKey);
    System.out.println("password = " + password);

}

2.修改配置

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/mp?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: mpw:QWWVnk1Oal3258x5rVhaeQ== # 密文要以 mpw:开头
    password: mpw:EUFmeH3cNAzdRGdOQcabWg== # 密文要以 mpw:开头

3.配置参数

@SpringBootTest(args = "--mpw.key=6234633a66fb399f")

七、分页查询高级应用实例

1.实体类

/**
 * 分页查询请求实体类
 */
//贫血模型：无方法
//充血模型：有方法
@Data
public class PageQuery {

    private Integer pageNo = 1;
    private Integer pageSize = 5;
    private String sortBy;
    private Boolean isAsc = false;

    //根据前端的请求实体自动生成Page对象，手动传入默认排序方法
    public <T> Page<T> toMpPage(OrderItem ... defaultOrderItem){
        //分页条件
        Page<T> p = Page.of(pageNo, pageSize);
        //排序条件,true升序，false降序
        //既不能为null,也不能为空
        if(StrUtil.isNotBlank(sortBy)){
            p.addOrder(new OrderItem(sortBy, isAsc));
        }
        else {
            for (OrderItem orderItem : defaultOrderItem) {
                p.addOrder(orderItem);
            }
        }
        return p;
    }

}

/**
 * 返回前端的User实体类
 */
@Data
public class UserVO {

    private Long id;
    private String username;
    private UserInfo info;
    private UserStatus status;
    private Long balance;
}

/**
 * 通用分页查询结果类
 * @param <T>
 */
@Data
@AllArgsConstructor(staticName = "of")
@NoArgsConstructor
public class PageVo<T> {

    private Long total;
    private Long pages;
    private List<T> list;

    //自动查询转换器，将mp的page实体转换为分页查询结果类封装
    public <P> PageVo(Page<P> page, Class<T> clazz) {
        this.total = page.getTotal();
        this.pages = page.getPages();
        List<P> records = page.getRecords();
        if(CollUtil.isEmpty(records)){
            this.list = Collections.emptyList();
            return;
        }
        this.list = BeanUtil.copyToList(records, clazz);
    }
    
    public <P> PageVo(Page<P> page, Function<P, T> convertor) {
        this.total = page.getTotal();
        this.pages = page.getPages();
        List<P> records = page.getRecords();
        if(CollUtil.isEmpty(records)){
            this.list = Collections.emptyList();
            return;
        }
        this.list = records.stream().map(convertor).collect(Collectors.toList());
    }
}

2.controller

@RestController
@RequestMapping("/users")
//自动生成参数构造器
@RequiredArgsConstructor
public class UserController {

    //final必须加入构造器
    private final IUserService userService;
    private final IAddressService addressService;

    @GetMapping("/page")
    public PageVo<UserVO> queryUserByPage(@RequestBody PageQuery pageQuery){
        return userService.queryUserByPage(pageQuery);
    }
}

3.service

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService {

    @Override
    public PageVo<UserVO> queryUserByPage(PageQuery pageQuery) {
        Page<User> p = pageQuery.toMpPage(new OrderItem("updateTime", false));
        //分页查询
        Page<User> page = page(p);
        //分页结果，将用户名的最后四个字符隐藏
        return new PageVo<>(page, user -> {
            UserVO userVO = BeanUtil.copyProperties(user, UserVO.class);
            String username = userVO.getUsername();
            userVO.setUsername((username.substring(0, username.length() - 4) + "****"));
            return userVO;
        });
    }
}