Mybatis plus入门（九）：运算符

between

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

    @RequestMapping("/test4")
    @ResponseBody
    public String test4(){
        QueryWrapper<User> qw = new QueryWrapper<>();
        qw.select("id","name").between("age",20,25)
                .orderByAsc("role_id","id");
        List<User> plainUsers = userMapper.selectList(qw);
        // 方式2
        LambdaQueryWrapper<User> lwq = new LambdaQueryWrapper<>();
        lwq.select(User::getId,User::getName).between(User::getAge,20,25)
                .orderByAsc(User::getRoleId,User::getId);
        List<User> lambdaUsers = userMapper.selectList(lwq);
        return "success";
    }
 
# 控制台
SELECT id,name FROM user WHERE (age BETWEEN ? AND ?) ORDER BY role_id ASC,id ASC
SELECT id,name FROM user WHERE (age BETWEEN ? AND ?) ORDER BY role_id ASC,id ASC

notBetween

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

    @GetMapping("/test15")
    @ResponseBody
    public String test15() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.notBetween("age", 20, 50);
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }
 
# 控制台
SELECT id,name,age,bir FROM user WHERE (age NOT BETWEEN ? AND ?)

isNull

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

    @GetMapping("/test11")
    @ResponseBody
    public String test11() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // 查询name为空的
        queryWrapper.isNull("name");
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }
 
# 控制台
SELECT id,name,age,bir FROM user WHERE (name IS NULL)

isNotNull

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

    @GetMapping("/test12")
    @ResponseBody
    public String test12() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // 查询name不为空的
        queryWrapper.isNotNull("name");
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }
 
# 控制台
SELECT id,name,age,bir FROM user WHERE (name IS NOT NULL)

ne

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

    @GetMapping("/test13")
    @ResponseBody
    public String test13() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        // 不等于
        queryWrapper.ne("name", "大周");
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }
 
# 控制台
SELECT id,name,age,bir FROM user WHERE (name <> ?)

多条件

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

    @GetMapping("/test16")
    @ResponseBody
    public String test16() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("id", 5);
        map.put("name", "wesson5");
        map.put("age", 29);
        queryWrapper.allEq(map);
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }
 
# 控制台
SELECT id,name,age,bir FROM user WHERE (name = ? AND id = ? AND age = ?)

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

    @GetMapping("/test17")
    @ResponseBody
    public String test17() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("age", 29)
                .eq("name", "wesson5")
                .eq("id", 5);
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }
 
# 控制台
Preparing: SELECT id,name,age,bir FROM user WHERE (age = ? AND name = ? AND id = ?)

and、or

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

    @GetMapping("/test18")
    @ResponseBody
    public String test18() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("age", 29)
                .or()
                .eq("name", "wesson5")
                .or()
                .eq("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 (age = ? OR name = ? OR id = ?)

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

    @GetMapping("/test19")
    @ResponseBody
    public String test19() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("age", 29)
                .or(
                        i -> i.eq("name", "wesson5")
                                .or()
                                .eq("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 (age = ? OR (name = ? OR id = ?))

in、not in

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

    @GetMapping("/test20")
    @ResponseBody
    public String test20() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.notIn("id", 1, 2, 3);
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }
 
# 控制台
SELECT id,name,age,bir FROM user WHERE (id NOT IN (?,?,?))

拼接

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

    @GetMapping("/test22")
    @ResponseBody
    public String test22() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.last("limit 1");
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }
 
# 控制台
SELECT id,name,age,bir FROM user limit 1

查询指定的列

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

    @GetMapping("/test23")
    @ResponseBody
    public String test23() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("id", "name", "age");
        List<User> users = userDao.selectList(queryWrapper);
        users.forEach(user-> System.out.println(user));
        return "success";
    }
 
# 控制台
SELECT id,name,age FROM user