Mybatis Plus 系列3.1 —— Mapper CRUD之查询

这一章，我们开始一起学习使用Mapper来进行增删改查！

一. 前置准备

项目结构:

.
├── build.gradle
├── gradle
│   └── wrapper
│       ├── gradle-wrapper.jar
│       └── gradle-wrapper.properties
├── gradlew
├── gradlew.bat
├── settings.gradle
└── src
    ├── main
    │   ├── java
    │   │   └── cn
    │   │       └── justmr
    │   │           └── mybatisplus
    │   │               └── demo
    │   │                   ├── DemoApplication.java
    │   │                   ├── UserRepository.java
    │   │                   ├── entity
    │   │                   │   ├── BaseEntity.java
    │   │                   │   └── User.java
    │   │                   ├── handler
    │   │                   │   ├── MyMetaObjectHandler.java
    │   │                   └── mapper
    │   │                       └── UserMapper.java
    │   └── resources
    │       ├── application.yml
    │       ├── static
    │       └── templates
    └── test
        └── java
            └── cn
                └── justmr
                    └── mybatisplus
                        └── demo
                            └── demo
                                └── UserTests.java

1. build.gradle

plugins {
    id 'org.springframework.boot' version '2.3.4.RELEASE'
    id 'io.spring.dependency-management' version '1.0.10.RELEASE'
    id 'java'
}

group = 'cn.justmr.mybatis-plus.demo'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '1.8'

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    // 为了加快速度，配置阿里镜像仓库
    maven { url "http://maven.aliyun.com/nexus/content/groups/public" }
    mavenCentral()
}

ext {
    set('mybaitsPlusVersion', "3.4.2")
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    compileOnly 'org.projectlombok:lombok'
    testCompileOnly 'org.projectlombok:lombok'
    runtimeOnly 'mysql:mysql-connector-java'
    // 引入mybatis plus
    compile "com.baomidou:mybatis-plus-boot-starter:${mybaitsPlusVersion}"
    annotationProcessor 'org.projectlombok:lombok'
    testAnnotationProcessor 'org.projectlombok:lombok'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}


test {
    useJUnitPlatform()
}

2. application.yml

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
    username: test
    password: 123456

mybatis-plus:
  configuration:
    # 开启自动驼峰命名规则（camel case）映射,默认就是true
    map-underscore-to-camel-case: true
    # 输出日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      insert-strategy: not_null
      update-strategy: not_null

3. sql语句

-- mysql 版本 5.7.24

-- 测试表结构
DROP TABLE IF EXISTS user;
CREATE TABLE user
(
    id          BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    name        VARCHAR(30)         NOT NULL DEFAULT '' COMMENT '姓名',
    age         INT(11)             NULL     DEFAULT NULL COMMENT '年龄',
    email       VARCHAR(50)         NOT NULL DEFAULT '' COMMENT '邮箱',
    sex         varchar(10)         NOT NULL DEFAULT '' COMMENT '性别',
    is_delete   TINYINT                      default 0 not null comment '是否删除',
    create_time datetime            NOT NULL comment '创建时间',
    update_time datetime            NOT NULL comment '更新时间',
    PRIMARY KEY (id)
) engine = InnoDB;

-- 初始化测试数据
INSERT INTO user (name, age, email, sex, create_time, update_time) VALUES
('Jone', 18, 'test1@baomidou.com', 'MAIL', now(), now()),
('Jack', 20, 'test2@baomidou.com', 'MAIL', now(), now()),
('Tom', 28, 'test3@baomidou.com', 'MAIL', now(), now()),
('Sandy', 21, 'test4@baomidou.com', 'FEMALE', now(), now()),
('Billie', 24, 'test5@baomidou.com', 'FEMALE', now(), now());

4. 初始数据

5. UserMapper

public interface UserMapper extends BaseMapper<User> {
}

6. MyMetaObjectHandler

/**
 * 自动填充 create_time 和 update_time 字段
 */
@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("====== start insert fill ....");
        // 严格填充,只针对非主键的字段,只有该表注解了fill 并且 字段名和字段属性 能匹配到才会进行填充(严格模式填充策略,默认有值不覆盖,如果提供的值为null也不填充)
        this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now());
        this.strictInsertFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now());
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("====== start update fill ....");
        // 严格填充,只针对非主键的字段,只有该表注解了fill 并且 字段名和字段属性 能匹配到才会进行填充(严格模式填充策略,默认有值不覆盖,如果提供的值为null也不填充)
        this.strictUpdateFill(metaObject, "updateTime",  LocalDateTime.class, LocalDateTime.now());
    }
}

7. 启动类

package cn.justmr.mybatisplus.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("cn.justmr.mybatisplus.demo.mapper")
@SpringBootApplication
public class DemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

二. 查询

首先看一下UserMapper实现的BaseMapper的接口：

1. BaseMapper#selectById

根据id查询

T selectById(Serializable id);

@Slf4j
@SpringBootTest
class UserTests {

    @Autowired
    private UserMapper userMapper;

    @Test
    @DisplayName("根据id查询")
    public void selectByIdTest() {
        User user = userMapper.selectById(1L);
        Assertions.assertNotNull(user);
    }
}

结果：

==>  Preparing: SELECT id,name,age,email,sex,is_delete,create_time,update_time FROM user WHERE id=?
==> Parameters: 1(Long)
<==    Columns: id, name, age, email, sex, is_delete, create_time, update_time
<==        Row: 1, Jone, 18, test1@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==      Total: 1

2. BaseMapper#selectBatchIds

idList 不能为 null 以及 empty，否则会报错

>List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);

@Test
@DisplayName("根据id批量查询")
public void selectBatchIdsTest() {
  List<User> user = userMapper.selectBatchIds(Lists.list(1L, 2L));
  Assertions.assertFalse(user.isEmpty());
}

结果：

==>  Preparing: SELECT id,name,age,email,sex,is_delete,create_time,update_time FROM user WHERE id IN ( ? , ? )
==> Parameters: 1(Long), 2(Long)
<==    Columns: id, name, age, email, sex, is_delete, create_time, update_time
<==        Row: 1, Jone, 18, test1@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==        Row: 2, Jack, 20, test2@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==      Total: 2

3. BaseMapper#selectByMap

查询（根据 columnMap 条件）
Params:
columnMap – 表字段 map 对象

List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);

@Test
@DisplayName("根据 columnMap 条件查询")
public void selectByMapTest() {
  Map<String, Object> param = new HashMap<>();
  param.put("name", "Jone");
  param.put("age", 18);
  List<User> user = userMapper.selectByMap(param);
  Assertions.assertFalse(user.isEmpty());
}

结果：

==>  Preparing: SELECT id,name,age,email,sex,is_delete,create_time,update_time FROM user WHERE name = ? AND age = ?
==> Parameters: Jone(String), 18(Integer)
<==    Columns: id, name, age, email, sex, is_delete, create_time, update_time
<==        Row: 1, Jone, 18, test1@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==      Total: 1

注：如果columnMap为null或者empty,则是全量查询

4. BaseMapper#selectOne

根据 entity 条件，查询一条记录
Params:
queryWrapper – 实体对象封装操作类（可以为 null）

T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

这里出现了一个新的对象Wrapper，我们先看一下这个类的继承实现关系！

如图，标记出来的是跟查询相关的Wrapper。下面我们就写一下对应的单元测试：

@Test
@DisplayName("查询一条记录1(不推荐)")
public void selectOneTest1() {
  // 这种写法不推荐，列名需要使用字符串维护，很容易写错，而且写错只能到运行时才能发现
  QueryWrapper<User> eq = new QueryWrapper<User>()
    .eq("name", "Jone")
    .eq("age", 18)
    .eq("is_delete", false); // 不能写错成 isDelete
  
  //        // 也可以使用 Wrappers 中的静态方法
  //        QueryWrapper<User> eq = Wrappers.<User>query()
  //                .eq("name", "Jone")
  //                .eq("age", 18)
  //                .eq("is_delete", false);

  User user = userMapper.selectOne(eq);
  Assertions.assertNotNull(user);
}


@Test
@DisplayName("查询一条记录2(推荐)")
public void selectOneTest2() {
  // 相比上一个例子，更推荐这个，通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错
  LambdaQueryWrapper<User> eq = new QueryWrapper<User>().lambda()
    .eq(User::getName, "Jone")
    .eq(User::getAge, 18)
    .eq(User::getIsDelete, false);
    
  //        // 也可以使用 Wrappers 中的静态方法
  //        LambdaQueryWrapper<User> eq = Wrappers.<User>lambdaQuery()
  //                .eq(User::getName, "Jone")
  //                .eq(User::getAge, 18)
  //                .eq(User::getIsDelete, false);

  User user = userMapper.selectOne(eq);
  Assertions.assertNotNull(user);
}


@Test
@DisplayName("查询一条记录3-链式(不推荐)")
public void selectOneTest3() {
  // 这种写法不推荐，列名需要使用字符串维护，很容易写错，而且写错只能到运行时才能发现
  User user = new QueryChainWrapper<>(userMapper)
    .eq("name", "Jone")
    .eq("age", 18)
    .eq("is_delete", false)
    .one();

  //        // 也可以使用 Wrappers 中的静态方法
  //        User user = ChainWrappers.queryChain(userMapper)
  //                .eq("name", "Jone")
  //                .eq("age", 18)
  //                .eq("is_delete", false)
  //                .one();
  
  Assertions.assertNotNull(user);
}


@Test
@DisplayName("查询一条记录4-链式(推荐)")
public void selectOneTest4() {
    // 相比上一个例子，更推荐这个，通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错
    User user = new LambdaQueryChainWrapper<>(userMapper)
            .eq(User::getName, "Jone")
            .eq(User::getAge, 18)
            .eq(User::getIsDelete, false)
            .one();

//        // 也可以使用 Wrappers 中的静态方法
//        User user = ChainWrappers.lambdaQueryChain(userMapper)
//                .eq(User::getName, "Jone")
//                .eq(User::getAge, 18)
//                .eq(User::getIsDelete, false)
//                .one();

    Assertions.assertNotNull(user);
}

结果:

==>  Preparing: SELECT id,name,age,email,sex,is_delete,create_time,update_time FROM user WHERE (name = ? AND age = ? AND is_delete = ?)
==> Parameters: Jone(String), 18(Integer), false(Boolean)
<==    Columns: id, name, age, email, sex, is_delete, create_time, update_time
<==        Row: 1, Jone, 18, test1@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==      Total: 1

注意：

selectOne方法查询到多条数据会出现异常！

例如：

@Test
@DisplayName("查询一条记录5-查到多条")
public void selectOneTest5() {
  Assertions.assertThrows(Exception.class, () -> new LambdaQueryChainWrapper<>(userMapper)
                          .gt(User::getAge, 20)
                          .eq(User::getIsDelete, false)
                          .one());
}

结果（单元测试通过）：

==>  Preparing: SELECT id,name,age,email,sex,is_delete,create_time,update_time FROM user WHERE (age > ? AND is_delete = ?)
==> Parameters: 20(Integer), false(Boolean)
<==    Columns: id, name, age, email, sex, is_delete, create_time, update_time
<==        Row: 3, Tom, 28, test3@baomidou.com, MAIL, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==        Row: 4, Sandy, 21, test4@baomidou.com, FEMALE, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==        Row: 5, Billie, 24, test5@baomidou.com, FEMALE, 0, 2021-02-12 22:43:00, 2021-02-12 22:43:00
<==      Total: 3

返回Optional

如果我们查询一条数据时，想直接返回Optional对象，那我可以链式操作：

@Test
@DisplayName("查询一条记录6-Optional")
public void selectOneTest6() {
  Optional<User> user = new LambdaQueryChainWrapper<>(userMapper)
    .gt(User::getAge, 100)
    .eq(User::getIsDelete, false)
    .oneOpt();

  Assertions.assertFalse(user.isPresent());
}

结果（测试通过）:

==>  Preparing: SELECT id,name,age,email,sex,is_delete,create_time,update_time FROM user WHERE (age > ? AND is_delete = ?)
==> Parameters: 100(Integer), false(Boolean)
<==      Total: 0

总结一下：

构建Wrapper建议使用Lambda表达式的方式，类型安全！

5. BaseMapper#selectCount

根据 Wrapper 条件，查询总记录数
Params:
queryWrapper – 实体对象封装操作类（可以为 null）

Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

@Test
@DisplayName("查询总记录数")
public void selectCountTest() {
    LambdaQueryWrapper<User> eq = Wrappers.<User>lambdaQuery()
            .gt(User::getAge, 20)
            .eq(User::getIsDelete, false);

    Integer integer = userMapper.selectCount(eq);
    Assertions.assertTrue(integer > 0);
}

结果：

==>  Preparing: SELECT COUNT( * ) FROM user WHERE (age > ? AND is_delete = ?)
==> Parameters: 20(Integer), false(Boolean)
<==    Columns: COUNT( * )
<==        Row: 3
<==      Total: 1

6. BaseMapper#selectList，BaseMapper#selectMaps，BaseMapper#selectObjs

这些就不一一举例了

7. BaseMapper#selectPage，BaseMapper#selectMapsPage

对于分页，后面会单独讲