MyBatis-Plus的扩展

MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射，而实际开发中，我们都会选择使用 MyBatisPlus，它是对 MyBatis 框架的进一步增强，能够极大地简化我们的持久层代码，下面就一起来看看 MyBatisPlus 中的一些奇淫巧技吧。

说明：本篇文章需要一定的 MyBatis 与 MyBatisPlus 基础

1、分页

1.1、容器注入分页插件

package com.wnx.mybatisplusdemo.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@MapperScan("com.wnx.mybatisplusdemo.modules.*.mapper")
public class MyBatisConfig {
    

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }


}

1.2、Page方法

   @GetMapping("/page")
    public CommonResult page(){
        Page<TblEmployee> page = new Page<>(1,2);
        employeeService.page(page, null);
        List<TblEmployee> employeeList = page.getRecords();
        employeeList.forEach(System.out::println);
        System.out.println("获取总条数:" + page.getTotal());
        System.out.println("获取当前页码:" + page.getCurrent());
        System.out.println("获取总页码:" + page.getPages());
        System.out.println("获取每页显示的数据条数:" + page.getSize());
        System.out.println("是否有上一页:" + page.hasPrevious());
        System.out.println("是否有下一页:" + page.hasNext());
        return CommonResult.success(page);
    }
    @GetMapping("/findPageByCondition")
    public CommonResult findPageByCondition(){
        Page<TblEmployee> page = new Page<>(1,2);
        QueryWrapper<TblEmployee> example = new QueryWrapper<>();
        example.between("age",20,50);
        example.eq("gender",1);
        employeeService.page(page,example);
        return CommonResult.success(page);
    }

1.3、底层Sql

2022-01-05 12:48:17.251 DEBUG 17784 --- [nio-8080-exec-6] c.w.m.m.p.m.T.selectPage                 : ==>  Preparing: SELECT COUNT(1) FROM tbl_employee WHERE is_deleted = 0 
2022-01-05 12:48:17.252 DEBUG 17784 --- [nio-8080-exec-6] c.w.m.m.p.m.T.selectPage                 : ==> Parameters: 
2022-01-05 12:48:17.280 DEBUG 17784 --- [nio-8080-exec-6] c.w.m.m.p.m.T.selectPage                 : ==>  Preparing: SELECT id,last_name,email,gender,age,gmt_create,gmt_modified,is_deleted AS deleted FROM tbl_employee WHERE is_deleted=0 LIMIT ?,? 
2022-01-05 12:48:17.280 DEBUG 17784 --- [nio-8080-exec-6] c.w.m.m.p.m.T.selectPage                 : ==> Parameters: 0(Long), 2(Long)
2022-01-05 12:48:17.291 DEBUG 17784 --- [nio-8080-exec-6] c.w.m.m.p.m.T.selectPage                 : <==      Total: 2

2、逻辑删除

2.1、表字段

alter table tbl_employee add column is_deleted tinyint not null;

2.2、实体配置

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("tbl_employee")
@ApiModel(value="TblEmployee对象", description="")
public class TblEmployee implements Serializable {

    private static final long serialVersionUID=1L;

    private Long id;

    private String lastName;

    private String email;

    private String gender;

    private Integer age;

    @TableField(fill = FieldFill.INSERT) // 插入的时候自动填充
    private LocalDateTime gmtCreate;
    @TableField(fill = FieldFill.INSERT_UPDATE) // 插入和更新的时候自动填充
    private LocalDateTime gmtModified;
    /**
     * 逻辑删除属性
     */
    @TableLogic
    @TableField("is_deleted")
    private Boolean deleted;

}

2.3、所有的删除方法都变成更新！

    @GetMapping("/delete")
    public CommonResult delete(){
        boolean b = employeeService.removeById(3);
        return CommonResult.success("");
    }

2.4、底层Sql

2022-01-05 12:50:42.451 DEBUG 13288 --- [nio-8080-exec-1] c.w.m.m.p.m.T.deleteById                 : ==>  Preparing: UPDATE tbl_employee SET is_deleted=1 WHERE id=? AND is_deleted=0 
2022-01-05 12:50:42.467 DEBUG 13288 --- [nio-8080-exec-1] c.w.m.m.p.m.T.deleteById                 : ==> Parameters: 3(Integer)
2022-01-05 12:50:42.473 DEBUG 13288 --- [nio-8080-exec-1] c.w.m.m.p.m.T.deleteById                 : <==    Updates: 0

3、时间自动填充

3.1、表字段

alter table tbl_employee add column gmt_create datetime not null;
alter table tbl_employee add column gmt_modified datetime not null;

3.2、实体配置

package com.wnx.mybatisplusdemo.modules.pms.model;

import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableLogic;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.time.LocalDateTime;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;


@Data
@EqualsAndHashCode(callSuper = false)
@TableName("tbl_employee")
@ApiModel(value="TblEmployee对象", description="")
public class TblEmployee implements Serializable {

    private static final long serialVersionUID=1L;

    private Long id;

    private String lastName;

    private String email;

    private String gender;

    private Integer age;

    @TableField(fill = FieldFill.INSERT) // 插入的时候自动填充
    private LocalDateTime gmtCreate;
    @TableField(fill = FieldFill.INSERT_UPDATE) // 插入和更新的时候自动填充
    private LocalDateTime gmtModified;
    /**
     * 逻辑删除属性
     */
    @TableLogic
    @TableField("is_deleted")
    private Boolean deleted;

}

3.3、自定义填充规则Handler

package com.wnx.mybatisplusdemo.config;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.time.LocalDateTime;

@Component
@Slf4j
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        boolean hasGmtCreate = metaObject.hasSetter("gmtCreate");
        boolean hasGmtModified = metaObject.hasSetter("gmtModified");
        if (hasGmtCreate) {
            Object gmtCreate = this.getFieldValByName("gmtCreate", metaObject);
            if (gmtCreate == null) {
                this.strictInsertFill(metaObject, "gmtCreate", LocalDateTime.class, LocalDateTime.now());
            }
        }
        if (hasGmtModified) {
            Object gmtModified = this.getFieldValByName("gmtModified", metaObject);
            if (gmtModified == null) {
                this.strictInsertFill(metaObject, "gmtModified", LocalDateTime.class, LocalDateTime.now());
            }
        }
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        boolean hasGmtModified = metaObject.hasSetter("gmtModified");
        if (hasGmtModified) {
            Object gmtModified = this.getFieldValByName("gmtModified", metaObject);
            if (gmtModified == null) {
                this.strictInsertFill(metaObject, "gmtModified", LocalDateTime.class, LocalDateTime.now());
            }
        }
    }
}

3.4、插入会都更新时间，修改更新修改时间

  @GetMapping("/insert")
    public CommonResult insert(){
        TblEmployee employee = new TblEmployee();
        employee.setLastName("lisa");
        employee.setEmail("lisa@qq.com");
        employee.setAge(20);
        employee.setDeleted(false);
        employeeService.save(employee);

        return CommonResult.success(employee);
    }
    @GetMapping("/update")
    public CommonResult update(){
        TblEmployee employee = new TblEmployee();
        employee.setId(1478548108547170306L);
        employee.setAge(50);
        employee.setDeleted(false);
        // 设置创建时间
        employee.setGmtModified(LocalDateTime.now());
        employeeService.updateById(employee);
        return CommonResult.success(employee);
    }

3.5、底层Sql

2022-01-05 13:01:05.204 DEBUG 19020 --- [nio-8080-exec-1] c.w.m.m.p.m.TblEmployeeMapper.insert     : ==>  Preparing: INSERT INTO tbl_employee ( last_name, email, age, gmt_create, gmt_modified, is_deleted ) VALUES ( ?, ?, ?, ?, ?, ? ) 
2022-01-05 13:01:05.219 DEBUG 19020 --- [nio-8080-exec-1] c.w.m.m.p.m.TblEmployeeMapper.insert     : ==> Parameters: lisa(String), lisa@qq.com(String), 20(Integer), 2022-01-05T13:01:05.192(LocalDateTime), 2022-01-05T13:01:05.194(LocalDateTime), false(Boolean)
2022-01-05 13:01:05.236 DEBUG 19020 --- [nio-8080-exec-1] c.w.m.m.p.m.TblEmployeeMapper.insert     : <==    Updates: 1
2022-01-05 13:01:31.142 DEBUG 19020 --- [nio-8080-exec-2] c.w.m.m.p.m.T.updateById                 : ==>  Preparing: UPDATE tbl_employee SET age=?, gmt_modified=? WHERE id=? AND is_deleted=0 
2022-01-05 13:01:31.144 DEBUG 19020 --- [nio-8080-exec-2] c.w.m.m.p.m.T.updateById                 : ==> Parameters: 50(Integer), 2022-01-05T13:01:31.132(LocalDateTime), 1478548108547170306(Long)
2022-01-05 13:01:31.160 DEBUG 19020 --- [nio-8080-exec-2] c.w.m.m.p.m.T.updateById                 : <==    Updates: 1

4、雪花算法-分布式ID

分布式ID一般用于多个数据库之间，保证ID唯一性。如果你是只有一个数据库，ID自增。可在yaml全局设置

mybatis-plus:
  global-config:
    db-config:
      id-type: auto

等价于：

 @TableId(value = "id",type = IdType.AUTO)
    private Long id;

5、QueryWrapper

5.1、链式编程构造单表复杂条件

    @GetMapping("/queryWrapper2")
    public CommonResult queryWrapper2(){
        // 查询名字中包含'j'，年龄大于20岁，邮箱不为空的员工信息
        QueryWrapper<TblEmployee> wrapper = new QueryWrapper<>();
        wrapper.like("last_name", "j")
                .gt("age", 20)
                .isNotNull("email");
        List<TblEmployee> list = employeeService.list(wrapper);
        return CommonResult.success(list);

    }

5.2、Lambda编程解决字段硬编码

   @GetMapping("/queryWrapper3")
    public CommonResult queryWrapper3(){
        // 查询名字中包含'j'，年龄大于20岁，邮箱不为空的员工信息
        LambdaQueryWrapper<TblEmployee> wrapper = new LambdaQueryWrapper<TblEmployee>();
        wrapper.like(TblEmployee::getLastName, "j")
                .gt(TblEmployee::getAge, 20)
                .isNotNull(TblEmployee::getEmail);
        List<TblEmployee> list = employeeService.list(wrapper);
        return CommonResult.success(list);
    }

5.3、底层Sql

2022-01-05 13:05:39.195 DEBUG 19020 --- [nio-8080-exec-4] c.w.m.m.p.m.T.selectList                 : ==>  Preparing: SELECT id,last_name,email,gender,age,gmt_create,gmt_modified,is_deleted AS deleted FROM tbl_employee WHERE is_deleted=0 AND (last_name LIKE ? AND age > ? AND email IS NOT NULL) 
2022-01-05 13:05:39.196 DEBUG 19020 --- [nio-8080-exec-4] c.w.m.m.p.m.T.selectList                 : ==> Parameters: %j%(String), 20(Integer)
2022-01-05 13:05:39.207 DEBUG 19020 --- [nio-8080-exec-4] c.w.m.m.p.m.T.selectList                 : <==      Total: 2

6、UpdateWrapper

6.1、根据条件修改记录

@GetMapping("/UpdateWrapper")
public CommonResult updateWrapper(){
    TblEmployee byId = employeeService.getById(1);
    byId.setEmail("827376239");
    LambdaUpdateWrapper<TblEmployee> updateWrapper = new LambdaUpdateWrapper<TblEmployee> ();
    updateWrapper.like(TblEmployee::getLastName,"j")
                 .ge(TblEmployee::getAge,20);
    employeeService.update(byId,updateWrapper);
    return CommonResult.success("");

}

6.1.1、底层Sql

2022-01-05 13:58:42.628 DEBUG 10532 --- [nio-8080-exec-4] c.w.m.m.p.m.TblEmployeeMapper.update     : ==>  Preparing: UPDATE tbl_employee SET last_name=?, email=?, gender=?, age=?, gmt_create=?, gmt_modified=? WHERE is_deleted=0 AND (last_name LIKE ? AND age >= ?) 
2022-01-05 13:58:42.630 DEBUG 10532 --- [nio-8080-exec-4] c.w.m.m.p.m.TblEmployeeMapper.update     : ==> Parameters: jack(String), 827376239(String), 1(String), 50(Integer), 2022-01-05T10:12:56(LocalDateTime), 2022-01-05T10:12:56(LocalDateTime), %j%(String), 20(Integer)
2022-01-05 13:58:42.635 DEBUG 10532 --- [nio-8080-exec-4] c.w.m.m.p.m.TblEmployeeMapper.update     : <==    Updates: 2

6.2、根据条件修改某些字段

    @GetMapping("/UpdateWrapper2")
    public CommonResult updateWrapper2(){
        UpdateWrapper<TblEmployee> wrapper = new UpdateWrapper<TblEmployee>()
                .set("age", 50)
                .set("email", "emp@163.com")
                .like("last_name", "j")
                .gt("age", 20);
        employeeService.update(null, wrapper);
        return CommonResult.success("");

6.1.2、底层Sql

2022-01-05 14:00:30.848 DEBUG 10532 --- [io-8080-exec-10] c.w.m.m.p.m.TblEmployeeMapper.update     : ==>  Preparing: UPDATE tbl_employee SET age=?,email=? WHERE is_deleted=0 AND (last_name LIKE ? AND age > ?) 
2022-01-05 14:00:30.849 DEBUG 10532 --- [io-8080-exec-10] c.w.m.m.p.m.TblEmployeeMapper.update     : ==> Parameters: 50(Integer), emp@163.com(String), %j%(String), 20(Integer)
2022-01-05 14:00:30.868 DEBUG 10532 --- [io-8080-exec-10] c.w.m.m.p.m.TblEmployeeMapper.update     : <==    Updates: 2

7、乐观锁

7.1、注入乐观锁插件

@Configuration
public class MyBatisConfig {

    /**
     * 注册插件
     * @return
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 分页插件
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        // 乐观锁插件
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

7.2、注解

@Data
public class Shop {

    private Long id;
    private String name;
    private Integer price;
    @Version // 声明版本号属性
    private Integer version;
}