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

Mybatis plus入门(八):子查询、嵌套查询

目录

子查询

  • 使用BaseMapper接口中的selectList方法,传入参数QueryWrapper
  • 使用BaseMapper接口中的selectList方法,传入参数QueryWrapper,写法2
  • 使用BaseMapper接口中的selectList方法,传入参数QueryWrapper

嵌套查询

  • 使用BaseMapper接口的selectList方法,传入参数QueryWrapper
  • 使用BaseMapper接口的selectList方法,传入参数QueryWrapper,写法2
  • 使用BaseMapper接口的selectList方法,传入参数QueryWrapper

子查询

  • 使用BaseMapper接口中的selectList方法,传入参数QueryWrapper
@RequestMapping("/test5")
@ResponseBody
public String test5() {
// 查询role_id为2的对象
List<User> plainUsers2 = userMapper.selectList(new QueryWrapper<User>()
.inSql("role_id", "select id from role where id = 2"));
// 使用lambda表达式
List<User> lambdaUsers2 = userMapper.selectList(new QueryWrapper<User>().lambda()
.inSql(User::getRoleId, "select id from role where id = 2"));
return "success";
}
# 控制台
SELECT id,name,age,email,role_id FROM user WHERE (role_id IN (select id from role where id = 2))
SELECT id,name,age,email,role_id FROM user WHERE (role_id IN (select id from role where id = 2))
  • 使用BaseMapper接口中的selectList方法,传入参数QueryWrapper,写法2
@RequestMapping("/test5")
@ResponseBody
public String test5() {
List<User> plainUsers2 = userMapper.selectList(new LambdaQueryWrapper<User>()
.inSql(User::getRoleId, "select id from role where id = 2"));
List<User> lambdaUsers2 = userMapper.selectList(new QueryWrapper<User>().lambda()
.inSql(User::getRoleId, "select id from role where id = 2"));
return "success";
}
# 控制台
SELECT id,name,age,email,role_id FROM user WHERE (role_id IN (select id from role where id = 2))
SELECT id,name,age,email,role_id FROM user WHERE (role_id IN (select id from role where id = 2))
  • 使用BaseMapper接口中的selectList方法,传入参数QueryWrapper
@GetMapping("/test21")
@ResponseBody
public String test21() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.in("id", "select id from user where id < 5");
List<User> users = userDao.selectList(queryWrapper);
users.forEach(user-> System.out.println(user));
return "success";
}
# 控制台
SELECT id,name,age,bir FROM user WHERE (id IN (?))
select id from user where id < 5(String)

嵌套查询

  • 使用BaseMapper接口的selectList方法,传入参数QueryWrapper
@RequestMapping("/test5")
@ResponseBody
public String test5() {
// 查询role_id为2或role_id为3,同时age大于等于20的数据
List<User> plainUsers3 = userMapper.selectList(new QueryWrapper<User>()
.nested(i -> i.eq("role_id", 2L).or().eq("role_id", 3L))
.and(i -> i.ge("age", 20)));
// 使用lambda表达式
List<User> lambdaUsers3 = userMapper.selectList(new QueryWrapper<User>().lambda()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
return "success";
}
# 控制台打印
SELECT id,name,age,email,role_id FROM user WHERE ((role_id = ? OR role_id = ?) AND (age >= ?))
SELECT id,name,age,email,role_id FROM user WHERE ((role_id = ? OR role_id = ?) AND (age >= ?))
  • 使用BaseMapper接口的selectList方法,传入参数QueryWrapper,写法2
@RequestMapping("/test5")
@ResponseBody
public String test5() {
List<User> plainUsers3 = userMapper.selectList(new LambdaQueryWrapper<User>()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
List<User> lambdaUsers3 = userMapper.selectList(new QueryWrapper<User>().lambda()
.nested(i -> i.eq(User::getRoleId, 2L).or().eq(User::getRoleId, 3L))
.and(i -> i.ge(User::getAge, 20)));
return "success";
}
# 控制台打印
SELECT id,name,age,email,role_id FROM user WHERE ((role_id = ? OR role_id = ?) AND (age >= ?))
SELECT id,name,age,email,role_id FROM user WHERE ((role_id = ? OR role_id = ?) AND (age >= ?))
  • 使用BaseMapper接口的selectList方法,传入参数QueryWrapper
@RequestMapping("/test3")
@ResponseBody
public String test3(){
QueryWrapper<User> w = new QueryWrapper<>();
w.and(i -> i.eq("1", 1))
.nested(i ->
i.and(j -> j.eq("name", "a").eq("age", 2))
.or(j -> j.eq("name", "b").eq("age", 2)));
userMapper.selectList(w);
return "success";
}
# 控制台
SELECT id,name,age,email,role_id FROM user WHERE ((1 = ?) AND ((name = ? AND age = ?) OR (name = ? AND age = ?)))
posted @   DogLeftover  阅读(6775)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
· 面试官:你是如何进行SQL调优的?
点击右上角即可分享
微信分享提示

目录导航