一、聚合查询
需求：聚合函数查询，完成count，max，min，avg，sum的使用
count：总记录数
max：最大值
min：最小值
avg：平均值
sum：求和
@SpringBootTest class Mybatisplus02DqlApplicationTests { @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的查询使用
@SpringBootTest class Mybatisplus02DqlApplicationTests { @Autowired private UserDao userDao; @Test void testGetAll(){ QueryWrapper<User> lqw = new QueryWrapper<User>(); lqw.select("count(*) as count,tel"); lqw.groupBy("tel"); List<Map<String, Object>> list = userDao.selectMaps(lqw); System.out.println(list); } }
groupBy分组，最终的sql语句为：
SELECT count(*) as count,tel FROM user GROUP BY tel
注意：
聚合与分组查询无法使用lambda表达式完成
MP只是对MyBatis增强，如果MP实现不了，我们可以直接在DAO接口中使用MyBatis的方式实现
三、等值查询
需求：根据用户名和密码查询用户信息
@SpringBootTest class Mybatisplus02DqlApplicationTests { @Autowired private UserDao userDao; @Test void testGetAll(){ LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<User>(); lqw.eq(User::getName, "Jerry").eq(User::getPassword, "jerry"); User loginUser = userDao.selectOne(lqw); System.out.println(loginUser); } }
eq()：相当于 = ，对应的sql语句为
SELECT id,name,password,age,tel FROM user WHERE (name = ? AND password = ?)
selectList：查询结果为多个或者单个
selectOne：查询结果为单个
