Mybatis plus入门（七）：排序、分组、求和、最大值

目录

排序

• 调用BaseMapper接口中的selectList方法，传入参数Wrappers
• 调用BaseMapper接口中的selectList方法，传入参数Wrappers，写法2

分组

• 使用BaseMapper接口中的selectMaps方法，传入参数QueryWrapper
• 使用BaseMapper接口中的selectMaps方法，传入参数LambdaQueryWrapper

求和

• 使用BaseMapper接口的selectList方法，传入参数QueryWrapper

最大值

• 使用BaseMapper接口的selectOne方法，传入参数QueryWrapper

排序

• 调用BaseMapper接口中的selectList方法，传入参数Wrappers

    @RequestMapping(value = "/test4")
    @ResponseBody
    public String test4(){
        // 查询所有，根据age升序
        List<User> users = mapper.selectList(Wrappers.<User>query().orderByAsc("age"));
        System.out.println(users);
        assertThat(users).isNotEmpty();
        //多字段排序
        List<User> users2 = mapper.selectList(Wrappers.<User>query().orderByAsc("age", "name"));
        System.out.println(users2);
        assertThat(users2).isNotEmpty();
        //先按age升序排列，age相同再按name降序排列
        List<User> users3 = mapper.selectList(Wrappers.<User>query().orderByAsc("age").orderByDesc("name"));
        System.out.println(users3);
        assertThat(users3).isNotEmpty();
        return "success";
    }

# 控制台打印结果
SELECT id,name,age,email FROM user ORDER BY age ASC
[
    User(id=1,
    name=Jone,
    age=18,
    email=ab@c.c,
    ignoreColumn=ignoreColumn,
    count=null),
    User(id=2,
    name=Jack,
    age=20,
    email=test2@baomidou.com,
    ignoreColumn=ignoreColumn,
    count=null),
    User(id=5,
    name=Billie,
    age=24,
    email=test5@baomidou.com,
    ignoreColumn=ignoreColumn,
    count=null),
    User(id=3,
    name=Tom,
    age=28,
    email=test3@baomidou.com,
    ignoreColumn=ignoreColumn,
    count=null)
]

SELECT id,name,age,email FROM user ORDER BY age ASC,name ASC
[
    User(id=1,
    name=Jone,
    age=18,
    email=ab@c.c,
    ignoreColumn=ignoreColumn,
    count=null),
    User(id=2,
    name=Jack,
    age=20,
    email=test2@baomidou.com,
    ignoreColumn=ignoreColumn,
    count=null),
    User(id=5,
    name=Billie,
    age=24,
    email=test5@baomidou.com,
    ignoreColumn=ignoreColumn,
    count=null),
    User(id=3,
    name=Tom,
    age=28,
    email=test3@baomidou.com,
    ignoreColumn=ignoreColumn,
    count=null)
]
SELECT id,name,age,email FROM user ORDER BY age ASC,name DESC
[
    User(id=1,
    name=Jone,
    age=18,
    email=ab@c.c,
    ignoreColumn=ignoreColumn,
    count=null),
    User(id=2,
    name=Jack,
    age=20,
    email=test2@baomidou.com,
    ignoreColumn=ignoreColumn,
    count=null),
    User(id=5,
    name=Billie,
    age=24,
    email=test5@baomidou.com,
    ignoreColumn=ignoreColumn,
    count=null),
    User(id=3,
    name=Tom,
    age=28,
    email=test3@baomidou.com,
    ignoreColumn=ignoreColumn,
    count=null)
]

• 调用BaseMapper接口中的selectList方法，传入参数Wrappers，写法2

    @RequestMapping(value = "/test7")
    @ResponseBody
    public String test7(){
        // 查询所有，根据age升序排序
        List<User> users = mapper.selectList(Wrappers.<User>lambdaQuery().orderByAsc(User::getAge));
        assertThat(users).isNotEmpty();
        System.out.println("test7: " + users);
        //多字段排序
        List<User> users2 = mapper.selectList(Wrappers.<User>lambdaQuery().orderByAsc(User::getAge, User::getName));
        assertThat(users2).isNotEmpty();
        //先按age升序排列，age相同再按name降序排列
        List<User> users3 = mapper.selectList(Wrappers.<User>lambdaQuery().orderByAsc(User::getAge).orderByDesc(User::getName));
        assertThat(users3).isNotEmpty();
        return "success";
    }

分组

• 使用BaseMapper接口中的selectMaps方法，传入参数QueryWrapper
• 使用BaseMapper接口中的selectMaps方法，传入参数LambdaQueryWrapper

    @RequestMapping(value = "/test9")
    @ResponseBody
    public String test9(){
        // 根据age分组
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select("age, count(*)")
                .groupBy("age");
        List<Map<String, Object>> maplist = mapper.selectMaps(wrapper);
        for (Map<String, Object> mp : maplist) {
            System.out.println(mp);
        }
        // 写法2
        LambdaQueryWrapper<User> lambdaQueryWrapper = new QueryWrapper<User>().lambda()
                .select(User::getAge)
                .groupBy(User::getAge)
                .orderByAsc(User::getAge);
        for (User user : mapper.selectList(lambdaQueryWrapper)) {
            System.out.println(user);
        }
        return "success";
    }

# 控制台
SELECT age, count(*) FROM user GROUP BY age
{count(*)=1, age=18}
{count(*)=1, age=20}
{count(*)=1, age=28}
{count(*)=1, age=24}

SELECT age FROM user GROUP BY age ORDER BY age ASC
User(id=null, name=null, age=18, email=null, ignoreColumn=ignoreColumn, count=null)
User(id=null, name=null, age=20, email=null, ignoreColumn=ignoreColumn, count=null)
User(id=null, name=null, age=24, email=null, ignoreColumn=ignoreColumn, count=null)
User(id=null, name=null, age=28, email=null, ignoreColumn=ignoreColumn, count=null)

求和

• 统计不同年龄的数量
• 使用BaseMapper接口的selectList方法，传入参数QueryWrapper

    @RequestMapping(value = "/test10")
    @ResponseBody
    public String test10(){
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select("age, count(age) as count")
                .groupBy("age");
        List<User> list = mapper.selectList(wrapper);
        list.forEach(System.out::println);
        list.forEach(x -> {
            System.out.println(x.getId());
        });
        return "success";
    }

# 控制台
SELECT age, count(age) as count FROM user GROUP BY age
User(id=null, name=null, age=18, email=null, ignoreColumn=ignoreColumn, count=1)
User(id=null, name=null, age=20, email=null, ignoreColumn=ignoreColumn, count=1)
User(id=null, name=null, age=28, email=null, ignoreColumn=ignoreColumn, count=1)
User(id=null, name=null, age=24, email=null, ignoreColumn=ignoreColumn, count=1)

最大值

• 使用BaseMapper接口的selectOne方法，传入参数QueryWrapper

    @RequestMapping(value = "/test8")
    @ResponseBody
    public String test8(){
        // 创建wapper，查询最大的id
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.select("max(id) as id");
        User user = mapper.selectOne(wrapper);
        System.out.println("maxId=" + user.getId());
        return "success";
    }