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 = ?)))