9.MyBatis-Plus

1、前期准备

a. 创建数据库

CREATE TABLE USER
(
  id BIGINT(20)NOT NULL COMMENT '主键ID',
  NAME VARCHAR(30)NULL DEFAULT NULL COMMENT '姓名',
  age INT(11)NULL DEFAULT NULL COMMENT '年龄',
  email VARCHAR(50)NULL DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (id)
);
 
INSERT INTO user (id, name, age, email)VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');

b. 配置项目环境

配置Java编译器：

项目和文件的编码：

c. 添加依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
 
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
 
    <!--mybatis-plus-->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.3.1</version>
    </dependency>
 
    <!--mysql依赖-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <!--lombok用来简化实体类-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

d. 配置文件

# 配置MySQL
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/syt_mp?serverTimezone=GMT%2B8
    username: root
    password: 0903he0419

• 这里的 url 使用了 ?serverTimezone=GMT%2B8 后缀，因为8.0版本的jdbc驱动需要添加这个后缀，否则运行测试用例报告如下错误：java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more
• 这里的 driver-class-name 使用了 com.mysql.cj.jdbc.Driver ，在 jdbc 8 中 建议使用这个驱动，否则运行测试用例的时候会有 WARN 信息

e. 启动类

@SpringBootApplication
@MapperScan("com.lemon.demomp.mapper")
public class DemompApplication {
 
    public static void main(String[] args) {
 
        SpringApplication.run(DemompApplication.class, args);
    }
 
}

• 在启动类添加注解：@MapperScan("com.lemon.demomp.mapper")

f. 添加实体

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

g. 添加mapper

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

• 为了避免报错，可以在dao 层 的接口上添加 @Repository 注

h. 查看 sql 输出日志

# mybatis日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

2、主键策略

插入操作：

//添加
@Test
public void testAdd() {
    User user = new User();
    user.setName("lucy");
    user.setAge(20);
    user.setEmail("1243@qq.com");
    int insert = userMapper.insert(user);
    System.out.println(insert);
}

a. ASSIGN_ID

MyBatis-Plus默认的主键策略是：ASSIGN_ID （使用了雪花算法）

@TableId(type = IdType.ASSIGN_ID)
private Long id;

b. AUTO 自增策略

需要在创建数据表的时候设置主键自增

实体字段中配置@TableId(type = IdType.AUTO)

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

要想影响所有实体的配置，可以设置全局主键配置

#全局设置主键生成策略
mybatis-plus.global-config.db-config.id-type=auto

3、自动填充

注意：update时生成的sql自动是动态sql：UPDATE user SET age=? WHERE id=?

更新操作：

// 修改
@Test
public void testUpdate() {
    User user = new User();
    user.setId(1722996412305653764L);
    user.setName("朱棣");
    int count = userMapper.updateById(user);
    System.out.println(count);
}

需求描述：项目中经常会遇到一些数据，每次都使用相同的方式填充，例如记录的创建时间，更新时间等。我们可以使用MyBatis Plus的自动填充功能，完成这些字段的赋值工作

a. 修改数据库

在User表中添加datetime类型的新的字段 create_time、update_time

b. 修改实体类

实体上增加字段并添加自动填充注解:

@TableField(fill = FieldFill.INSERT)
private Date createTime;  //create_time
 
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime; //update_time

c. 实现元对象处理接口

src/hander/MyMetaObjectHandler

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    // mp执行添加操作，这个方法执行
    @Override
    public void insertFill(MetaObject metaObject) {
        this.setFieldValByName("createTime", new Date(), metaObject);
        this.setFieldValByName("updateTime", new Date(), metaObject);
    }
 
    // mp 执行修改操作，这个方法执行
    @Override
    public void updateFill(MetaObject metaObject) {
        this.setFieldValByName("updateTime", new Date(), metaObject);
    }
}

4、乐观锁

主要适用场景：当要更新一条记录的时候，希望这条记录没有被别人更新，也就是说实现线程安全的数据更新。

乐观锁实现方式：

• 取出记录时，获取当前version
• 更新时，带上这个version
• 执行更新时，set version = newVersion where version = oldVersion
• 如果version不对，就更新失败

乐观锁实现流程：

a. 修改数据库

在user表中新增version字段数据类型为int，给其设定一个默认值（假设为1）

b. 修改实体类

添加 @Version 注解

@Version
private Integer version;

c. 创建配置类

创建包config，创建文件MybatisPlusConfig.java

此时可以删除主类中的 @MapperScan 扫描注解

@Configuration
@MapperScan("com.lemon.demomp.mapper")
public class MpConfig {
    /**
     * 乐观锁插件
     */
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor() {
        return new OptimisticLockerInterceptor();
    }
}

d. 测试方法

根据输出结果可以看到，首先经过查询，user的version是1，然后在更改user的name属性，此时version变为2。自此乐观锁已经生效。

@Test
public void testOptimisticLocker() {
    // 1. 根据id进行查询
    User user = userMapper.selectById(1722996412305653768L);
    user.setName("张三");
    int count = userMapper.updateById(user);
    System.out.println(count);
}

5、查询

a. 批量查询

通过多个id进行批量查询

SELECT id,name,age,email,create_time,update_time,version FROM user WHERE id IN ( ? , ? , ? )

==> Parameters: 1(Integer), 2(Integer), 3(Integer)

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

b. 简单条件查询

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

==> Parameters: Jack(String), 20(Integer)

/*
* 简单条件查询
* */
@Test
public void testSelectByMap() {
    Map<String, Object> columnMap = new HashMap<>();
    columnMap.put("name", "Jack");
    columnMap.put("age", 20);
    List<User> users = userMapper.selectByMap(columnMap);
    System.out.println(users);
}

6、分页查询

a. 添加分页插件

在配置类中注册分页插件：src/config/MpConfig

/**
 * 分页插件
 */
@Bean
public PaginationInterceptor paginationInterceptor() {
    return new PaginationInterceptor();
}

b. 测试selectPage

Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user LIMIT ?,?

==> Parameters: 0(Long), 3(Long)

/*
* 测试selectPage分页
* */
@Test
public void testSelectPage() {
    Page<User> page = new Page<>(1, 3);
    Page<User> userPage = userMapper.selectPage(page, null);
    // 返回对象得到分页所有数据
    long pages = userPage.getPages(); //总页数
    long current = userPage.getCurrent(); //当前页
    List<User> records = userPage.getRecords();//查询数据集合
    long total = userPage.getTotal();//总记录数
    boolean hasNext = userPage.hasNext();//是否有下一页
    boolean hasPrevious = userPage.hasPrevious();//是否有上一页
 
    System.out.println(pages);
    System.out.println(current);
    System.out.println(records);
    System.out.println(total);
    System.out.println(hasNext);
    System.out.println(hasPrevious);
 
}

c. 测试selectMapsSelect

Preparing: SELECT id,name,age,email,create_time,update_time,version FROM user LIMIT ?,?

==> Parameters: 0(Long), 5(Long)

/*
* 测试selectMapsPage
* */
@Test
public void testSelectMapsPage() {
    //Page不需要泛型
    Page<Map<String, Object>> page = new Page<>(1, 5);
    Page<Map<String, Object>> pageParam = userMapper.selectMapsPage(page, null);
    List<Map<String, Object>> records = pageParam.getRecords();
    records.forEach(System.out::println);
    System.out.println(pageParam.getCurrent());
    System.out.println(pageParam.getPages());
    System.out.println(pageParam.getSize());
    System.out.println(pageParam.getTotal());
    System.out.println(pageParam.hasNext());
    System.out.println(pageParam.hasPrevious());
}

7、删除

a. 根据id删除记录

Preparing: DELETE FROM user WHERE id=?

==> Parameters: 1(Long)

/*
* 根据id删除记录
* */
@Test
public void testDelById() {
    int isDeleted = userMapper.deleteById(1L);
    System.out.println(isDeleted);
}

b. 批量删除

Preparing: DELETE FROM user WHERE id IN ( ? , ? , ? )

==> Parameters: 2(Integer), 3(Integer), 4(Integer)

/*
* 批量删除
* */
@Test
public void testDelBatchByIds() {
    int isDeleted = userMapper.deleteBatchIds(Arrays.asList(2, 3, 4));
    System.out.println(isDeleted);
}

c. 简单条件删除

Preparing: DELETE FROM user WHERE name = ? AND age = ?

==> Parameters: Billie(String), 24(Integer)

/*
*
* 简单条件删除
* */
@Test
public void testDelByMap() {
    Map<String, Object> map = new HashMap<>();
    map.put("name", "Billie");
    map.put("age", 24);
    int isDeleted = userMapper.deleteByMap(map);
    System.out.println(isDeleted);
}

8、逻辑删除

物理删除：真实删除，将对应数据从数据库中删除，之后查询不到此条被删除数据

逻辑删除：假删除，将对应数据中代表是否被删除字段状态修改为“被删除状态”，之后在数据库中仍旧能看到此条数据记录

逻辑删除的使用场景：

• 可以进行数据恢复
• 有关联数据，不便删除

a. 修改数据库

添加逻辑删除字段

ALTER TABLE `user` ADD COLUMN deleted BOOLEAN DEFAULT FALSE COMMENT '逻辑删除'

b. 修改实体类

添加deleted 字段，并加上 @TableLogic 注解

@TableLogic
private Integer deleted;

c. 配置文件（可选）

application.properties 加入以下配置，此为默认值，如果你的默认值和mp默认的一样,该配置可无

mybatis-plus:
  global-config:
    db-config:
      id-type: auto
      logic-delete-value: 1
      logic-not-delete-value: 0

d. 测试逻辑删除

Preparing: UPDATE user SET deleted=1 WHERE id=? AND deleted=0

==> Parameters: 6(Long)

测试后发现，数据并没有被删除，deleted字段的值由0变成了1

测试后分析打印的sql语句，是一条update

注意：被删除前，数据的deleted 字段的值必须是 0，才能被选取出来执行逻辑删除的操作

/*
* 逻辑删除
* */
@Test
public void testLogicDelete() {
    int isDeleted = userMapper.deleteById(6L);
    System.out.println(isDeleted);
}

e. 测试逻辑删除后的查询

MyBatis Plus中查询操作也会自动添加逻辑删除字段的判断

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

/*
* 测试逻辑删除后的查询
* */
@Test
public void testLogicDeleteSelect() {
    List<User> users = userMapper.selectList(null);
    users.forEach(System.out::println);
}

9、条件构造器

a. ge、gt、le、lt、isNull、isNotNull

UPDATE user SET deleted=1 WHERE deleted=0 AND (name IS NOT NULL AND age >= ? AND email IS NOT NULL)

==> Parameters: 50(Integer)

/*
* ge、gt、le、lt、isNull、isNotNull
* delete
* */
@Test
public void testQuery() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.isNotNull("name")
            .ge("age",50)
            .isNotNull("email");
    int deleted = userMapper.delete(queryWrapper);
    System.out.println("deleted return count= " + deleted);
}

b. eq、ne

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

==> Parameters: 张三(String)

注意：seletOne()返回的是一条实体记录，当出现多条时会报错

/*
* eq、ne
* selectOne
* */
@Test
public void testSelectOne() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("name", "张三");
    User user = userMapper.selectOne(queryWrapper);
    System.out.println(user);
}

c. between、notBetween

SELECT COUNT( 1 ) FROM user WHERE deleted=0 AND (age BETWEEN ? AND ?)

==> Parameters: 60(Integer), 80(Integer)

/*
* between,notBetween
* selectCount
* */
@Test
public void testSelectCount() {
    QueryWrapper<User> queryWrapper  = new QueryWrapper<>();
    queryWrapper.between("age", 60, 80);
    Integer count = userMapper.selectCount(queryWrapper);
    System.out.println(count);
}

d. like、notLike、likeLeft、likeRight

SELECT name,age FROM user WHERE deleted=0 AND (name LIKE ? AND email LIKE ?)

==> Parameters: %e%(String), 5%(String)

selectMaps()返回Map集合列表，通常配合select()使用

/*
* like,notLike,likeLeft,likeRight
* selectMaps
* */
@Test
public void testSelectMaps() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.select("name", "age")
            .like("name", "e")
            .likeRight("email", "5");
    List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
    maps.forEach(System.out::println);
}

e. orderBy、orderByDesc、orderByAsc

SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 ORDER BY age DESC,id DESC

/*
* orderBy、orderByDesc、orderByAsc
* selectList
* */
@Test
public void testSelectList() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.orderByDesc("age", "id");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

10、查询方式