Mybatis Plus各种查询方法

1package com.xiao.permission_system;
2
3import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
4import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
5import com.baomidou.mybatisplus.extension.service.additional.query.impl.LambdaQueryChainWrapper;
6import com.mysql.cj.util.StringUtils;
7import com.xiao.permission_system.entity.UserInfo;
8import com.xiao.permission_system.mapper.UserInfoMapper;
9import org.junit.Test;
10import org.junit.runner.RunWith;
11import org.springframework.beans.factory.annotation.Autowired;
12import org.springframework.boot.test.context.SpringBootTest;
13import org.springframework.test.context.junit4.SpringRunner;
14
15import java.util.Arrays;
16import java.util.HashMap;
17import java.util.List;
18import java.util.Map;;
19
20@RunWith(SpringRunner.class)
21@SpringBootTest
22public class PermissionSystemApplicationTests {
23
24    @Test
25    public void contextLoads() {
26    }
27
28    @Autowired
29    private UserInfoMapper userInfoMapper;
30
31    /**
32     * 普通查询
33     */
34    @Test
35    public void selectById() {
36        UserInfo userInfo = userInfoMapper.selectById(123);
37        System.out.println(userInfo);
38    }
39
40    /**
41     * 批量查询
42     */
43    @Test
44    public void selectByIds() {
45        List<Long> ids = Arrays.asList(123L,124L,125L);
46        List<UserInfo> userInfo = userInfoMapper.selectBatchIds(ids);
47        System.out.println(userInfo);
48    }
49
50    /**
51     * 名字包含娟并且年龄小雨30
52     */
53    @Test
54    public void selectByWrapper() {
55        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
56        queryWrapper.like("username","娟").lt("age",30);
57        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
58        userInfoList.forEach(System.out::println);
59    }
60    /**
61     * 名字包含娟并且年龄大雨等于20且小于等于40并且email不为空
62     */
63    @Test
64    public void selectByWrapper2() {
65        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
66        queryWrapper.like("username","娟").between("age",20,30).isNotNull("email");
67        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
68        userInfoList.forEach(System.out::println);
69    }
70
71    /**
72     * 名字姓肖或者年量大雨等于20，按照年龄降序排列，年龄相同按照id生序排列
73     */
74    @Test
75    public void selectByWrapper3() {
76        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
77        queryWrapper.likeRight("username","肖")
78                .or().ge("age",20).orderByDesc("age").orderByAsc("id");
79        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
80        userInfoList.forEach(System.out::println);
81    }
82
83    /**
84     * 创建日期为2019年10月2日并且直属上级名字为王姓
85     */
86    @Test
87    public void selectByWrapper4() {
88        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
89        queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}","2019-10-07")
90                .inSql("parent_id","select id from user where username like '王%'");
91        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
92        userInfoList.forEach(System.out::println);
93    }
94
95    /**
96     * 名字为王姓并且（年龄小于40或邮箱不为空）
97     */
98    @Test
99    public void selectByWrapper5() {
100        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
101        queryWrapper.likeRight("username","王")
102                .and(wq->wq.lt("age",40))
103                .or().isNotNull("email");
104        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
105        userInfoList.forEach(System.out::println);
106    }
107    /**
108     * 名字为王姓并且（年龄小于40并且大与20或邮箱不为空）
109     */
110    @Test
111    public void selectByWrapper6() {
112        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
113        queryWrapper.likeRight("username","王")
114                .and(wq->wq.lt("age",40).gt("age",20))
115                .or().isNotNull("email");
116        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
117        userInfoList.forEach(System.out::println);
118    }
119    /**
120     * （年龄小于40并且大与20或邮箱不为空）名字为王姓并且
121     */
122    @Test
123    public void selectByWrapper7() {
124        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
125        queryWrapper.nested(wq->wq.lt("age",40))
126                .or().isNotNull("email")
127                .likeRight("username","王");
128        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
129        userInfoList.forEach(System.out::println);
130    }
131    /**
132     * 年龄23，30，40
133     */
134    @Test
135    public void selectByWrapper8() {
136        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
137        queryWrapper.in("age",Arrays.asList(20,30,40));
138        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
139        userInfoList.forEach(System.out::println);
140    }
141    /**
142     * 只返回满足条件的其中一条语句即可
143     */
144    @Test
145    public void selectByWrapper9() {
146        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
147        queryWrapper.in("age",Arrays.asList(20,30,40)).last("limit 1");
148        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
149        userInfoList.forEach(System.out::println);
150    }
151
152    /**
153     * 名字中包含雨并且年龄小于40(只取id,username)
154     */
155    @Test
156    public void selectByWrapper10() {
157        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
158        queryWrapper.select("id","username").like("username","雨").lt("age",40);
159        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
160        userInfoList.forEach(System.out::println);
161    }
162
163    /**
164     * 名字中包含雨并且年龄小于40(不取create_time,parent_id两个字段,即不列出全部字段)
165     */
166    @Test
167    public void selectByWrapper11() {
168        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
169        queryWrapper.like("username","雨").lt("age",40)
170                    .select(UserInfo.class,info->!info.getColumn().equals("create_time")&&
171                            !info.getColumn().equals("parent_id"));
172        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
173        userInfoList.forEach(System.out::println);
174    }
175
176    /**
177     * 姓名和邮箱不为空
178     */
179    public void testCondition() {
180        String username = "王";
181        String email = "";
182        condition(username,email);
183    }
184
185    private void condition(String username,String email){
186        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
187        queryWrapper.like(StringUtils.isNullOrEmpty(username),"name",username)
188                    .like(StringUtils.isNullOrEmpty(email),"email",email);
189        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
190        userInfoList.forEach(System.out::println);
191    }
192
193    /**
194     * 实体作为条件构造器方法的参数
195     */
196    @Test
197    public void selectByWrapperEntity() {
198        UserInfo whereUser = new UserInfo();
199        whereUser.setUsername("xiaojuan");
200        whereUser.setAge(22);
201        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>(whereUser);
202        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
203        userInfoList.forEach(System.out::println);
204    }
205    /**
206     * AllEq用法
207     */
208    @Test
209    public void selectByWrapperAllEq() {
210        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
211        Map<String, Object> params = new HashMap<String, Object>();
212        params.put("nuserame","xiaojuan");
213        params.put("age",null);
214        queryWrapper.allEq(params);
215        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
216        userInfoList.forEach(System.out::println);
217    }
218
219    /**
220     * AllEq用法(排除不是条件的字段)
221     */
222    @Test
223    public void selectByWrapperAllEq2() {
224        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
225        Map<String, Object> params = new HashMap<String, Object>();
226        params.put("nuserame","xiaojuan");
227        params.put("age",null);
228        queryWrapper.allEq((k,v)->!k.equals("name"),params);
229        List<UserInfo> userInfoList = userInfoMapper.selectList(queryWrapper);
230        userInfoList.forEach(System.out::println);
231    }
232
233    /**
234     * selectMaps
235     */
236    @Test
237    public void selectByWrapperMaps() {
238        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
239        queryWrapper.like("name","肖").lt("age",40);
240        List<Map<String,Object>> userInfoList = userInfoMapper.selectMaps(queryWrapper);
241        userInfoList.forEach(System.out::println);
242    }
243
244    /**
245     * 按照直属上级分组，查询每组的平均年龄，最大年龄，最小年龄。并且只取年龄总和小于500的组
246     */
247    @Test
248    public void selectByWrapperMaps2() {
249        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
250        queryWrapper.select("avg(age) avg_age","min(min) min_age","max(age) max_age")
251                .groupBy("parent_id").having("sum(age)<{0}",500);
252        List<Map<String,Object>> userInfoList = userInfoMapper.selectMaps(queryWrapper);
253        userInfoList.forEach(System.out::println);
254    }
255
256    /**
257     * selectObjs
258     */
259    @Test
260    public void selectByWrapperObjs() {
261        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
262        queryWrapper.select("id","name").like("name","肖").lt("age",40);
263        List<Object> userInfoList = userInfoMapper.selectObjs(queryWrapper);
264        userInfoList.forEach(System.out::println);
265    }
266
267    /**
268     * selectCount
269     */
270    @Test
271    public void selectByWrapperCount() {
272        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
273        queryWrapper.like("name","肖").lt("age",40);
274        Integer count = userInfoMapper.selectCount(queryWrapper);
275        System.out.println(count);
276    }
277
278    /**
279     * selectOne
280     */
281    @Test
282    public void selectByWrapperSelectOne() {
283        QueryWrapper<UserInfo> queryWrapper = new QueryWrapper<UserInfo>();
284        queryWrapper.like("name","肖").lt("age",40);
285        UserInfo user = userInfoMapper.selectOne(queryWrapper);
286        System.out.println(user);
287    }
288
289    /**
290     * 使用Lambda
291     */
292    @Test
293    public void selectLambda() {
294       // LambdaQueryWrapper<UserInfo> lambda = new QueryWrapper<UserInfo>().lambda();
295        LambdaQueryWrapper<UserInfo> lambda = new LambdaQueryWrapper<UserInfo>();
296        lambda.like(UserInfo::getUsername,"娟").lt(UserInfo::getAge,40);
297        List<UserInfo> userInfoList = userInfoMapper.selectList(lambda);
298        userInfoList.forEach(System.out::println);
299    }
300
301    /**
302     * 使用Lambda,名字为王姓（年龄小于40或邮箱不为空）
303     */
304    @Test
305    public void selectLambd2() {
306        LambdaQueryWrapper<UserInfo> lambda = new LambdaQueryWrapper<UserInfo>();
307        lambda.like(UserInfo::getUsername,"娟")
308                .and(lqw->lqw.lt(UserInfo::getAge,40).or().isNotNull(UserInfo::getEmail));
309        List<UserInfo> userInfoList = userInfoMapper.selectList(lambda);
310        userInfoList.forEach(System.out::println);
311    }
312
313    /**
314     * 使用Lambda链式
315     */
316    @Test
317    public void selectLambd3() {
318        List<UserInfo> userInfoList = new LambdaQueryChainWrapper<UserInfo>(userInfoMapper)
319                .like(UserInfo::getUsername,"娟").ge(UserInfo::getAge,20).list();
320        userInfoList.forEach(System.out::println);
321    }
322}
323