Mybatis-Plus 分页、逻辑删除、乐观锁快速入门

前置配置

表结构

数据库使用Oracle数据库

-- ----------------------------
-- Table structure for T_USER
-- ----------------------------
DROP TABLE "SCOTT"."T_USER";
CREATE TABLE "SCOTT"."T_USER" (
  "ID" VARCHAR2(19 BYTE) NOT NULL,
  "NAME" VARCHAR2(50 BYTE) NOT NULL,
  "AGE" NUMBER(3,0) NOT NULL,
  "DELETED" NUMBER(1,0) DEFAULT 0,
  "VERSION" NUMBER(10,0) DEFAULT 1
)
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
STORAGE (
  INITIAL 65536 
  NEXT 1048576 
  MINEXTENTS 1
  MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT
)
PARALLEL 1
NOCACHE
DISABLE ROW MOVEMENT
;
COMMENT ON COLUMN "SCOTT"."T_USER"."ID" IS '主键';
COMMENT ON COLUMN "SCOTT"."T_USER"."NAME" IS '姓名';
COMMENT ON COLUMN "SCOTT"."T_USER"."AGE" IS '年龄';
COMMENT ON COLUMN "SCOTT"."T_USER"."DELETED" IS '逻辑删除 （0未删除,1已删除）';
COMMENT ON COLUMN "SCOTT"."T_USER"."VERSION" IS '版本控制（乐观锁）';
COMMENT ON TABLE "SCOTT"."T_USER" IS '用户表';

-- ----------------------------
-- Records of T_USER
-- ----------------------------
INSERT INTO "SCOTT"."T_USER" VALUES ('1', 'xy', '19', '0', '1');
INSERT INTO "SCOTT"."T_USER" VALUES ('2', 'jack', '20', '0', '1');
INSERT INTO "SCOTT"."T_USER" VALUES ('3', 'lily', '21', '0', '1');
INSERT INTO "SCOTT"."T_USER" VALUES ('4', 'scott', '19', '0', '1');
INSERT INTO "SCOTT"."T_USER" VALUES ('5', 'smith', '20', '0', '1');

-- ----------------------------
-- Primary Key structure for table T_USER
-- ----------------------------
ALTER TABLE "SCOTT"."T_USER" ADD CONSTRAINT "PK_T_USER_ID" PRIMARY KEY ("ID");

-- ----------------------------
-- Checks structure for table T_USER
-- ----------------------------
ALTER TABLE "SCOTT"."T_USER" ADD CONSTRAINT "SYS_C0011157" CHECK ("ID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "SCOTT"."T_USER" ADD CONSTRAINT "SYS_C0011158" CHECK ("NAME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;
ALTER TABLE "SCOTT"."T_USER" ADD CONSTRAINT "SYS_C0011159" CHECK ("AGE" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE;

domain类

@TableName(value ="T_USER")
@Data
public class User implements Serializable {
    /**
     * 主键
     */
    @TableId
    private String id;

    /**
     * 姓名
     */
    private String name;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 逻辑删除 （0未删除,1已删除）
     */
    @TableField(select = false) // 查询时不会出现在select语句中
    private Integer deleted;

    /**
     * 版本控制（乐观锁）
     */
    @TableField(select = false) // 查询时不会出现在select语句中
    private Integer version;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;

    @Override
    public String toString() {
        return "User{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

mapper类

@Mapper
public interface UserMapper extends BaseMapper<User> {

}

开启分页

添加分页拦截器

@Configuration
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        // 添加分页拦截器
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}

测试代码

@Test
void testSelectPage() {
    Page<User> page = new Page<>(1, 2);
    userMapper.selectPage(page, null);
    System.out.println("page.getCurrent() = " + page.getCurrent());
    System.out.println("page.getSize() = " + page.getSize());
    System.out.println("page.getPages() = " + page.getPages());
    System.out.println("page.getTotal() = " + page.getTotal());
    System.out.println("page.getRecords() = " + page.getRecords());
}

查询结果:

开启逻辑删除

配置逻辑删除

• 方式一 在user.java 中的delete字段下添加如下注解

/**
 * 逻辑删除 （0未删除,1已删除）
 */
@TableField(select = false) // 查询时不会出现在select语句中
@TableLogic(delval = "1",value = "0")
private Integer deleted;

• 方式二 在 application.yml 配置文件中添加以下配置：

mybatis-plus:
  global-config:
    db-config:
      logic-delete-field: deleted
      logic-delete-value: 1
      logic-not-delete-value: 0

测试代码

@Test
void testLogicDelete() {
    userMapper.selectList(null).forEach(System.out::println);
    userMapper.deleteById(1);
    userMapper.selectList(null).forEach(System.out::println);
}

查询结果:

查询所有数据方法

该方法不一定满足复杂业务逻辑，如需复杂操作，请自定义sql查询

@Test
void testSelectLogicDeleteAndNotDeleted() {
    LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
    queryWrapper.last("or deleted = 1");
    userMapper.selectList(queryWrapper);
}

查询结果：

开启乐观锁

添加乐观锁拦截器

@Configuration
public class MyBatisPlusConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        // 添加分页拦截器
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        // 添加乐观锁拦截器
        mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return mybatisPlusInterceptor;
    }
}

开启乐观锁注解

在user.java 中的delete字段下添加如下注解

/**
 * 版本控制（乐观锁）
 */
@Version
private Integer version;

测试代码

@Test
void testVersion() {
    User user1 = userMapper.selectById(2);
    User user2 = userMapper.selectById(2);
    user1.setName("xy");
    System.out.println("userMapper.updateById(user) = " + userMapper.updateById(user1));
    user2.setName("xy");
    System.out.println("userMapper.updateById(user) = " + userMapper.updateById(user2));
}

查询结果