一、引言

那么那么那么今天来说下MP中强大的条件查询功能。

本章是基于上个案例来讲的：MyBaits-Plus 快速入门案例

二、具体操作

首先来说说基本的查询吧，根据主键或者简单的查询条件进行查询。

1. /**
2. * 通过单个ID主键进行查询
3. */
4. @Test
5. public void selectById() {
6. User user = userMapper.selectById(1094592041087729666L);
7. System.out.println(user);
8. }
9.  
10. /**
11. * 通过多个ID主键查询
12. */
13. @Test
14. public void selectByList() {
15. List<Long> longs = Arrays.asList(1094592041087729666L, 1094590409767661570L);
16. List<User> users = userMapper.selectBatchIds(longs);
17. users.forEach(System.out::println);
18. }
19.  
20. /**
21. * 通过Map参数进行查询
22. */
23. @Test
24. public void selectByMap() {
25. Map<String, Object> params = new HashMap<>();
26. params.put("name", "张雨琪");
27. List<User> users = userMapper.selectByMap(params);
28. users.forEach(System.out::println);
29. }

MyBatis-Plus还提供了Wrapper条件构造器，具体使用看如下代码：

1. /**
2. * 名字包含雨并且年龄小于40
3. * <p>
4. * WHERE name LIKE ‘%雨%‘ AND age < 40
5. */
6. @Test
7. public void selectByWrapperOne() {
8. QueryWrapper<User> wrapper = new QueryWrapper();
9. wrapper.like("name", "雨").lt("age", 40);
10. List<User> users = userMapper.selectList(wrapper);
11. users.forEach(System.out::println);
12. }
13.  
14. /**
15. * 名字包含雨
16. * 年龄大于20小于40
17. * 邮箱不能为空
18. * <p>
19. * WHERE name LIKE ‘%雨%‘ AND age BETWEEN 20 AND 40 AND email IS NOT NULL
20. */
21. @Test
22. public void selectByWrapperTwo() {
23. QueryWrapper<User> wrapper = Wrappers.query();
24. wrapper.like("name", "雨").between("age", 20, 40).isNotNull("email");
25. List<User> users = userMapper.selectList(wrapper);
26. users.forEach(System.out::println);
27. }
28.  
29. /**
30. * 名字为王性
31. * 或者年龄大于等于25
32. * 按照年龄降序排序，年龄相同按照id升序排序
33. * <p>
34. * WHERE name LIKE ‘王%‘ OR age >= 25 ORDER BY age DESC , id ASC
35. */
36. @Test
37. public void selectByWrapperThree() {
38. QueryWrapper<User> wrapper = Wrappers.query();
39. wrapper.likeRight("name", "王").or()
40. .ge("age", 25).orderByDesc("age").orderByAsc("id");
41. List<User> users = userMapper.selectList(wrapper);
42. users.forEach(System.out::println);
43. }
44.  
45. /**
46. * 查询创建时间为2019年2月14
47. * 并且上级领导姓王
48. * <p>
49. * WHERE date_format(create_time,‘%Y-%m-%d‘) = ‘2019-02-14‘ AND manager_id IN (select id from user where name like ‘王%‘)
50. */
51. @Test
52. public void selectByWrapperFour() {
53. QueryWrapper<User> wrapper = Wrappers.query();
54. wrapper.apply("date_format(create_time,‘%Y-%m-%d‘) = {0}", "2019-02-14")
55. .inSql("manager_id", "select id from user where name like ‘王%‘");
56. List<User> users = userMapper.selectList(wrapper);
57. users.forEach(System.out::println);
58. }
59.  
60. /**
61. * 查询王姓
62. * 并且年龄小于40或者邮箱不为空
63. * <p>
64. * WHERE name LIKE ‘王%‘ AND ( age < 40 OR email IS NOT NULL )
65. */
66. @Test
67. public void selectByWrapperFive() {
68. QueryWrapper<User> wrapper = Wrappers.query();
69. wrapper.likeRight("name", "王").and(qw -> qw.lt("age", 40).or().isNotNull("email"));
70. List<User> users = userMapper.selectList(wrapper);
71. users.forEach(System.out::println);
72. }
73.  
74. /**
75. * 查询王姓
76. * 并且年龄大于20 、年龄小于40、邮箱不能为空
77. * <p>
78. * WHERE name LIKE ? OR ( age BETWEEN ? AND ? AND email IS NOT NULL )
79. */
80. @Test
81. public void selectByWrapperSix() {
82. QueryWrapper<User> wrapper = Wrappers.query();
83. wrapper.likeRight("name", "王").or(
84. qw -> qw.between("age", 20, 40).isNotNull("email")
85. );
86. List<User> users = userMapper.selectList(wrapper);
87. users.forEach(System.out::println);
88. }
89.  
90. /**
91. * (年龄小于40或者邮箱不为空) 并且名字姓王
92. * WHERE ( age < 40 OR email IS NOT NULL ) AND name LIKE ‘王%‘
93. */
94. @Test
95. public void selectByWrapperSeven() {
96. QueryWrapper<User> wrapper = Wrappers.query();
97. wrapper.nested(qw -> qw.lt("age", 40).or().isNotNull("email"))
98. .likeRight("name", "王");
99. List<User> users = userMapper.selectList(wrapper);
100. users.forEach(System.out::println);
101. }
102.  
103. /**
104. * 查询年龄为30、31、32
105. * WHERE age IN (?,?,?)
106. */
107. @Test
108. public void selectByWrapperEight() {
109. QueryWrapper<User> wrapper = Wrappers.query();
110. wrapper.in("age", Arrays.asList(30, 31, 32));
111. List<User> users = userMapper.selectList(wrapper);
112. users.forEach(System.out::println);
113. }
114.  
115. /**
116. * 查询一条数据
117. * limit 1
118. */
119. @Test
120. public void selectByWrapperNine() {
121. QueryWrapper<User> wrapper = Wrappers.query();
122. wrapper.in("age", Arrays.asList(30, 31, 32)).last("limit 1");
123. List<User> users = userMapper.selectList(wrapper);
124. users.forEach(System.out::println);
125. }