MyBatis-Plus 入门教程之三

前言

版本说明

本文的教程内容是基于 MyBatis-Plus 3.5.2 版本讲解的,若你使用的是 2.x 或其他版本,可能会有部分知识点和案例代码不兼容。

条件构造器

提示

1、MyBatis-Plus 条件构造器的条件参数详细介绍可看 这里
2、本文所需的案例代码,可以直接从 GitHub 下载对应章节 mybatis-plus-lesson-05

Wrapper 介绍

  • Wrapper : 条件构造抽象类,最顶端父类
    • AbstractWrapper : 用于查询条件封装,生成 sql 的 where 条件
      • QueryWrapper : 查询条件封装
      • UpdateWrapper : Update 条件封装
      • AbstractLambdaWrapper : 使用 Lambda 语法
        • LambdaQueryWrapper : 用于 Lambda 语法使用的查询 Wrapper
        • LambdaUpdateWrapper : Lambda 更新封装 Wrapper

QueryWrapper

组装查询条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@SpringBootTest
public class QueryWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装查询条件
*/
@Test
public void selectList() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("last_name", 'i');
wrapper.isNotNull("email");
wrapper.between("age", 20, 25);
List<Employee> list = empMapper.selectList(wrapper);
list.forEach(System.out::println);
}

}
1
SELECT id,last_name,gender,email,age FROM t_employee WHERE (last_name LIKE ? AND email IS NOT NULL AND age BETWEEN ? AND ?)

组装排序条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@SpringBootTest
public class QueryWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装排序条件
*/
@Test
public void orderBy() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.orderByDesc("age");
wrapper.orderByAsc("last_name");
List<Employee> list = empMapper.selectList(wrapper);
list.forEach(System.out::println);
}

}
1
SELECT id,last_name,gender,email,age FROM t_employee ORDER BY age DESC,last_name ASC

组装删除条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@SpringBootTest
public class QueryWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装删除条件
*/
@Test
public void delete() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.isNull("email");
Integer deleteResult = empMapper.delete(wrapper);
System.out.println("deleteResult: " + deleteResult);
}

}
1
DELETE FROM t_employee WHERE (email IS NULL)

组装更新条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@SpringBootTest
public class QueryWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装更新条件
*/
@Test
public void update() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.ge("age", 26);
Employee employee = new Employee();
employee.setLastName("Jim");
Integer updateResult = empMapper.update(employee, wrapper);
System.out.println("updateResult: " + updateResult);
}

}
1
UPDATE t_employee SET last_name=? WHERE (age >= ?)

条件的优先级

提示

当存在多个条件时,Lambda 表达式中的条件优先执行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@SpringBootTest
public class QueryWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 条件的优先级
*/
@Test
public void conditionOrder() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
// Lambda 表达式中的条件优先执行,这里更改姓名中包含a并且(年龄大于26或邮箱为Null)的员工信息
wrapper.like("last_name", "a").and(i -> i.gt("age", 26).or().isNull("email"));
Employee employee = new Employee();
employee.setLastName("Albert");
Integer updateResult = empMapper.update(employee, wrapper);
System.out.println("updateResult: " + updateResult);
}

}
1
UPDATE t_employee SET last_name=? WHERE (last_name LIKE ? AND (age > ? OR email IS NULL))

组装 Select 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@SpringBootTest
public class QueryWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装 Select 语句
*/
@Test
public void selectColumns() {
// 查询指定的列
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.select("last_name", "gender");
List<Map<String, Object>> list = empMapper.selectMaps(wrapper);
list.forEach(System.out::println);
}

}
1
SELECT last_name,gender FROM t_employee

组装嵌套子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@SpringBootTest
public class QueryWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装嵌套子查询
*/
@Test
public void nestedSubSelect() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.inSql("id", "select id from t_employee where age <= 28");
List<Employee> list = empMapper.selectList(wrapper);
list.forEach(System.out::println);
}

}
1
SELECT id,last_name,gender,email,age FROM t_employee WHERE (id IN (select id from t_employee where age <= 28))

Condition 组装条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@SpringBootTest
public class QueryWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* Condition 组装条件
*/
@Test
public void condition() {
Integer minAge = 25;
Integer maxAge = null;
String lastName = "j";
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
// 当 Condition 条件成立时,才会拼接 SQL 语句
wrapper.like(StringUtils.isNotBlank(lastName), "last_name", lastName);
wrapper.ge(minAge != null, "age", minAge);
wrapper.le(maxAge != null, "age", maxAge);
List<Employee> list = empMapper.selectList(wrapper);
list.forEach(System.out::println);
}

}
1
SELECT id,last_name,gender,email,age FROM t_employee WHERE (last_name LIKE ? AND age >= ?)

UpdateWrapper

组装更新条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@SpringBootTest
public class UpdateWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装更新条件
*/
@Test
public void update() {
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
// 更新的条件
wrapper.like("last_name", "a");
wrapper.gt("age", 27);
// 更新的字段
wrapper.set("gender", "1");
wrapper.set("email", null);
Integer updateResult = empMapper.update(null, wrapper);
System.out.println("updateResult: " + updateResult);
}

}
1
UPDATE t_employee SET gender=?,email=? WHERE (last_name LIKE ? AND age > ?)

组装更新 SQL 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
@SpringBootTest
public class UpdateWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装更新 SQL 语句
*/
@Test
public void updateSql() {
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
// 更新的条件
wrapper.like("last_name", "a");
wrapper.gt("age", 27);
// 更新的 SQL 语句
wrapper.setSql("gender = '1' and email = null");
Integer updateResult = empMapper.update(null, wrapper);
System.out.println("updateResult: " + updateResult);
}

}
1
UPDATE t_employee SET gender = '1' and email = null WHERE (last_name LIKE ? AND age > ?)

LambdaQueryWrapper

值得一提的是,使用 LambdaQueryWrapper 的好处是可以防止表字段的名称被拼接错。

组装查询条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@SpringBootTest
public class LambdaQueryWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装查询条件
*/
@Test
public void selectList() {
LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>();
wrapper.like(Employee::getLastName, 'j')
.le(Employee::getAge, 28)
.eq(Employee::getGender, "1");
List<Employee> list = empMapper.selectList(wrapper);
list.forEach(System.out::println);
}

}
1
SELECT id,last_name,gender,email,age FROM t_employee WHERE (last_name LIKE ? AND age <= ? AND gender = ?)

Condition 组装条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@SpringBootTest
public class LambdaQueryWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* Condition 组装条件
*/
@Test
public void condition() {
Integer minAge = 25;
Integer maxAge = null;
String lastName = "j";
LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>();
// 当 Condition 条件成立时,才会拼接 SQL 语句
wrapper.like(StringUtils.isNotBlank(lastName), Employee::getLastName, lastName)
.ge(minAge != null, Employee::getAge, minAge)
.le(maxAge != null, Employee::getAge, maxAge);
List<Employee> list = empMapper.selectList(wrapper);
list.forEach(System.out::println);
}

}
1
SELECT id,last_name,gender,email,age FROM t_employee WHERE (last_name LIKE ? AND age >= ?)

LambdaUpdateWrapper

值得一提的是,使用 LambdaUpdateWrapper 的好处是可以防止表字段的名称被拼接错。

组装更新条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@SpringBootTest
public class LambdaUpdateWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装更新条件
*/
@Test
public void update() {
LambdaUpdateWrapper<Employee> wrapper = new LambdaUpdateWrapper<>();
wrapper.like(Employee::getLastName, "a")
.gt(Employee::getAge, 27)
.set(Employee::getGender, "1")
.set(Employee::getEmail, null);
Integer updateResult = empMapper.update(null, wrapper);
System.out.println("updateResult: " + updateResult);
}

}
1
UPDATE t_employee SET gender=?,email=? WHERE (last_name LIKE ? AND age > ?)

组装更新 SQL 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
@SpringBootTest
public class LambdaUpdateWrapperTest {

@Autowired
private EmployeeMapper empMapper;

/**
* 组装更新 SQL 语句
*/
@Test
public void updateSql() {
LambdaUpdateWrapper<Employee> wrapper = new LambdaUpdateWrapper<>();
wrapper.like(Employee::getLastName, "a")
.gt(Employee::getAge, 27)
.setSql("gender = '1' and email = null");
Integer updateResult = empMapper.update(null, wrapper);
System.out.println("updateResult: " + updateResult);
}

}
1
UPDATE t_employee SET gender = '1' and email = null WHERE (last_name LIKE ? AND age > ?)

ActiveRecord 模式

Active Record(活动记录)是一种领域模型模式,简称 AR 模式;特点是一个模型类对应关系型数据库中的一个表,而模型类的一个实例对应表中的一行记录。ActiveRecord 一直广受动态语言(PHP 、 Ruby 等)的喜爱,而 Java 作为准静态语言,对于 ActiveRecord 往往只能感叹其优雅,所以 MyBatis-Plus 也在 ActiveRecord 道路上进行了一定的探索。

使用 ActiveRecord 模式

使用 ActiveRecord 模式时,仅仅需要让实体类继承 Model 类,并实现指定主键的 pkVal() 方法即可。值得一提的是,如果主键属性的名称本来就是 id,则可以忽略实现 pkVal() 方法。

  • 实体类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
public class Employee extends Model<Employee> {

private Long id;
private String lastName;
private String gender;
private String email;
private Integer age;

...

@Override
public Serializable pkVal() {
return this.id;
}

}

特别注意

必须存在对应的原始 Mapper 接口,并继承 BaseMapper 接口(如下所示)的前提下才能使用 ActiveRecord 模式!

  • Mapper 接口
1
2
3
public interface EmployeeMapper extends BaseMapper<Employee> {

}

ActiveRecord 模式操作

由于 Model 类提供了通用的 CRUD 方法,因此实体类继承 Model 类之后,可以直接执行 CRUD 操作。本节所需的案例代码,可以直接从 GitHub 下载对应章节 mybatis-plus-lesson-06

查询数据操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
@SpringBootTest
public class ActiveRecordTest {

/**
* 根据ID查询数据
*/
@Test
public void selectById() {
Employee employee = new Employee();
employee.setId(1L);
Employee result = employee.selectById();
System.out.println(result);
}

/**
* 查询所有数据
*/
@Test
public void selectAll() {
Employee employee = new Employee();
List<Employee> list = employee.selectAll();
list.forEach(System.out::println);
}

/**
* 根据条件查询数据
*/
@Test
public void selectByWrapper() {
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like("last_name", "j");
wrapper.eq("gender", "1");

Employee employee = new Employee();
List<Employee> list = employee.selectList(wrapper);
list.forEach(System.out::println);
}

/**
* 查询总记录数
*/
@Test
public void selectCount() {
Employee employee = new Employee();
Long count = employee.selectCount(null);
System.out.println("count: " + count);
}

}

插入数据操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@SpringBootTest
public class ActiveRecordTest {

/**
* 插入数据
*/
@Test
public void insert() {
Employee employee = new Employee();
employee.setAge(24);
employee.setGender("1");
employee.setLastName("David");
employee.setEmail("david@gmail.com");
boolean insertResult = employee.insert();
System.out.println("insertResult: " + insertResult);
}

}

更新数据操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
@SpringBootTest
public class ActiveRecordTest {

/**
* 根据ID更改数据
*/
@Test
public void updateById() {
Employee employee = new Employee();
employee.setId(1L);
employee.setAge(24);
employee.setGender("1");
boolean updateResult = employee.updateById();
System.out.println("updateResult: " + updateResult);
}

/**
* 根据条件更改数据
*/
@Test
public void updateByWrapper() {
QueryWrapper<Employee> wraper = new QueryWrapper<>();
wraper.isNull("email");
wraper.eq("gender", "1");

Employee employee = new Employee();
employee.setAge(24);
boolean updateResult = employee.update(wraper);
System.out.println("updateResult: " + updateResult);
}

}

删除数据操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
@SpringBootTest
public class ActiveRecordTest {

/**
* 根据ID删除数据
*/
@Test
public void deleteById() {
Employee employee = new Employee();
employee.setId(11L);
boolean deleteResult = employee.deleteById();
System.out.println("deleteResult: " + deleteResult);
}

/**
* 根据条件删除数据
*/
@Test
public void deleteByWrapper() {
QueryWrapper<Employee> wraper = new QueryWrapper<>();
wraper.isNull("email");
wraper.eq("gender", "1");

Employee employee = new Employee();
boolean deleteResult = employee.delete(wraper);
System.out.println("deleteResult: " + deleteResult);
}

}