MyBatis-Plus 入门教程之三

发表于 更新于 阅读次数： 评论数： 本文字数： 1.4k 阅读时长 ≈ 1 分钟

大纲

前言

版本说明

本文的教程内容是基于 MyBatis-Plus 3.5.2 版本编写的，若你使用的是 2.x 或其他版本，可能会有部分知识点、案例代码不兼容，一切以 MyBatis-Plus 官方文档为准。

条件构造器

提示

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 = ?)

条件的优先级

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

	@Autowired
	private EmployeeMapper empMapper;
	
	/**
	 * 条件的优先级
	 */
	@Test
	public void conditionOrder() {
		LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>();
		wrapper.eq(Employee::getLastName, "jim");
		wrapper.and(i -> i.gt(Employee::getAge, 26).or().isNull(Employee::getEmail));
		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 = ? AND (age > ? OR email IS NULL))

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);
	}

}