文章目录
一.查询投影
查询投影又称查询指定字段
@Testvoid testGetAll07(){ LambdaQueryWrapper<Users> lqw = new LambdaQueryWrapper<Users>(); lqw.select(Users::getId,Users::getName,Users::getAge); List<Users> userList = userDao.selectList(lqw); System.out.println(userList);}
比如下面查询的结果就隐藏了password、tel字段
二.聚合查询
使用通过MP使用聚合函数进行查询
@SpringBootTestclass Mybatisplus{ @Autowired private UserDao userDao; @Test void testGetAll(){ QueryWrapper<User> lqw = new QueryWrapper<User>(); //lqw.select("count(*) as count"); //SELECT count(*) as count FROM user //lqw.select("max(age) as maxAge"); //SELECT max(age) as maxAge FROM user //lqw.select("min(age) as minAge"); //SELECT min(age) as minAge FROM user //lqw.select("sum(age) as sumAge"); //SELECT sum(age) as sumAge FROM user lqw.select("avg(age) as avgAge"); //SELECT avg(age) as avgAge FROM user List<Map<String, Object>> userList = userDao.selectMaps(lqw); System.out.println(userList); }}
为了在做结果封装的时候能够更简单,我们将上面的聚合函数都起了个名称,方面后期来获取这些数据
三.分组查询
分组查询,完成 group by的查询使用
@SpringBootTestclass Mybatisplus{ @Autowired private UserDao userDao; @Test void testGetAll09(){ QueryWrapper<Users> lqw = new QueryWrapper<Users>(); lqw.select("count(*) as count,age"); lqw.groupBy("age"); List<Map<String, Object>> list = userDao.selectMaps(lqw); System.out.println(list); }}
groupBy为分组,最终的sql语句为:
SELECT count(*) as count,age FROM users GROUP BY age
注:
聚合与分组查询,无法使用lambda表达式来完成
MP只是对MyBatis的增强,如果MP实现不了,我们可以直接在DAO接口中使用MyBatis的方式实现
四.查询条件
4.1 等值查询
根据用户名和密码查询用户信息
@SpringBootTestclass Mybatisplus{ @Autowired private UserDao userDao; @Test void testGetAll13(){ LambdaQueryWrapper<Users> lqw = new LambdaQueryWrapper<Users>(); lqw.eq(Users::getName, "懒羊羊").eq(Users::getPassword, "123456"); Users users = userDao.selectOne(lqw); System.out.println(users); }}
eq(): 相当于 =
,对应的sql语句为
SELECT id,name,password,age,tel FROM user WHERE (name = ? AND password = ?)
selectList:查询结果为多个或者单个
selectOne:查询结果为单个
4.2 范围查询
对年龄进行范围查询,使用lt()、le()、gt()、ge()、between()进行范围查询
@SpringBootTestclass Mybatisplus{ @Autowired private UserDao userDao; @Test void testGetAll12(){ LambdaQueryWrapper<Users> lqw = new LambdaQueryWrapper<Users>(); lqw.between(Users::getAge, 10, 30); List<Users> userList = userDao.selectList(lqw); System.out.println(userList); }}
与之对应的SQL为:
SELECT id,name,password,age,tel FROM user WHERE (age BETWEEN ? AND ?)
4.3 模糊查询
查询表中name属性的值包含‘美’字的用户信息,使用like进行模糊查询
@SpringBootTestclass Mybatisplus { @Autowired private UserDao userDao; @Test void testGetAll10(){ LambdaQueryWrapper<Users> lqw = new LambdaQueryWrapper<Users>(); lqw.like(Users::getName, "美"); List<Users> userList = userDao.selectList(lqw); System.out.println(userList); }}
最后也是通过一个字查到了美羊羊
对应的SQL为:
SELECT id,name,password,age,tel FROM user WHERE (name LIKE ?)
- like():前后加百分号,如 %美%
- likeLeft():前面加百分号,如 %美
- likeRight():后面加百分号,如 美%
4.4 排序查询
查询所有数据,然后按照id降序
@SpringBootTestclass Mybatisplus{ @Autowired private UserDao userDao; @Test void testGetAll11(){ LambdaQueryWrapper<Users> lwq = new LambdaQueryWrapper<>(); lwq.orderBy(true,true, Users::getId); List<Users> users = userDao.selectList(lwq); System.out.println(users); }}
查询结果如下:
condition :条件,返回boolean,当condition为true,进行排序,如果为false,则不排序
isAsc:是否为升序,true为升序,false为降序
columns:需要操作的列
除了上面演示的这种实现方式,还有很多其他的排序方法可以被调用
- orderByAsc/Desc(单个column):按照指定字段进行升序/降序
- orderByAsc/Desc(多个column):按照多个字段进行升序/降序
- orderByAsc/Desc * condition:条件,true添加排序,false不添加排序 * 多个columns:按照多个字段进行排序
来源地址:https://blog.csdn.net/weixin_57535055/article/details/126926372