MyBatisPlus-聚合查询、分组查询及等值查询

目录

一、聚合查询

二、分组查询

三、等值查询

---

一、聚合查询

需求：聚合函数查询，完成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：查询结果为单个