展开
拓展 关闭
订阅号推广码
GitHub
视频
公告栏 关闭

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";
}
posted @   DogLeftover  阅读(8399)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
· 面试官:你是如何进行SQL调优的?
点击右上角即可分享
微信分享提示

目录导航