MyBatisPlus:条件构造器和常用接口

Wapper介绍:

QueryWapper

1.组装查询条件

//查询用户名包含a，年龄在20到30之间，并且邮箱不为null的用户信息

    @Test
    public void test01(){
        //查询用户名包含a，年龄在20到30之间，并且邮箱不为null的用户信息
        //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (username LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","a")
                .between("age",20,30).isNotNull("email");
        List<User> list = usermapperP.selectList(queryWrapper);
        list.forEach(System.out::println);

    }

 2.组装排序查询

    @Test
    public void test02(){
        //按年龄降序查询用户，如果年龄相同则按id升序排列
        //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 ORDER BY age DESC,id ASC
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("age").orderByAsc("id");
        List<User> list = usermapperP.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

 3.组装删除条件

    @Test
    public void test03(){
        //删除email为空的用户
        //UPDATE t_user SET is_delete=1 WHERE is_delete=0 AND (email IS NULL)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.isNull("email");
        usermapperP.delete(queryWrapper);
    }

 4.条件的优先级

    @Test
    public void test04(){
        //将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改
        //UPDATE t_user SET age=?, email=? WHERE (username LIKE ? AND age > ? OR email IS NULL)
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.gt("age",20).like("name","a").or().isNull("email");
        User user = new User();
        user.setAge(18);
        user.setEmail("666@tinstu.com");
        usermapperP.update(user,queryWrapper);
    }

    @Test
    public void test05(){
        //将用户名中包含有a并且（年龄大于20或者邮箱为null）的用户进行修改
        //lambda 中的条件优先执行
        //UPDATE t_user SET age=?, email=? WHERE is_delete=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","a").and(i->i.gt("age",20).or().isNull("email"));
        User user = new User();
        user.setAge(18);
        user.setEmail("666@tinstu.com");
        usermapperP.update(user,queryWrapper);
    }

 5.组装select语句

    @Test
    public void test06() {
         //查询用户信息的username和age字段
         //SELECT username,age FROM t_user
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("name", "age");
        //selectMaps()返回Map集合列表，通常配合select()使用，避免User对象中没有被查询到的列值为null
        List<Map<String, Object>> maps = usermapperP.selectMaps(queryWrapper);
        maps.forEach(System.out::println);
    }

 6.实现子查询

    @Test
    public void test07() {
        //子查询 查询id小于等于3的用户信息    （在b表中查询a表中的id<3的用户信息）
        //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (id IN (select id from t_user where id <= 3))
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.inSql("id", "select id from t_user where id <= 3");
         //selectObjs的使用场景：只返回一列
        List<Object> objects = usermapperP.selectObjs(queryWrapper);
        objects.forEach(System.out::println);
    }

UpdateWapper

    @Test
    public void test08() {
         //将（年龄大于20或邮箱为null）并且用户名中包含有a的用户信息修改
         //组装set子句以及修改条件
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
         //lambda表达式内的逻辑优先运算
        updateWrapper
                .set("age", 18)
                .set("email", "user@atguigu.com")
                .like("name", "a")
                .and(i -> i.gt("age", 20).or().isNull("email"));
         //这里必须要创建User对象，否则无法应用自动填充。如果没有自动填充，可以设置为null
         //UPDATE t_user SET username=?, age=?,email=? WHERE (username LIKE ? AND (age > ? OR email IS NULL))
         User user = new User();
         user.setName("张三");
         int result = usermapperP.update(user, updateWrapper);
    }

condition   模拟开发中组装条件的情况

思路一:

在真正开发的过程中，组装条件是常见的功能，而这些条件数据来源于用户输入，是可选的，因此我们在组装这些条件时，必须先判断用户是否选择了这些条件，若选择则需要组装该条件，若没有选择则一定不能组装，以免影响SQL执行的结果

 @Test
    public void test09(){
        //定义查询条件，可能为空，用户未输入或未选择
        String name = "";
        Integer ageBegin = 10;
        Integer ageEnd = 24;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace)构成
        if(StringUtils.isNotBlank(name)){
            queryWrapper.like("username","a");
        }
        if(ageBegin != null){
            queryWrapper.ge("age", ageBegin);
        }
        if(ageEnd != null){
            queryWrapper.le("age", ageEnd);
        }
        //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (age >=? AND age <= ?)
        List<User> users = usermapperP.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

 思路二

上面的代码比较复杂，使用带condition参数的重载方法构建查询条件，简化代码的编写

LambdaQueryWrapper

    @Test
    public void test11() {
        //定义查询条件，有可能为null（用户未输入）
        String username = "a";
        Integer ageBegin = 10;
        Integer ageEnd = 24;
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        //避免使用字符串表示字段，防止运行时错误
        queryWrapper
                .like(StringUtils.isNotBlank(username), User::getName, username)
                .ge(ageBegin != null, User::getAge, ageBegin)
                .le(ageEnd != null, User::getAge, ageEnd);
        List<User> users = usermapperP.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

LambdaUpdateWrapper

    @Test
    public void test12() {
        //组装set子句
        LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper
                .set(User::getAge, 18)
                .set(User::getEmail, "user@atguigu.com")
                .like(User::getName, "a")
                .and(i -> i.lt(User::getAge, 24).or().isNull(User::getEmail)); //lambda表达式内的逻辑优先运算
        User user = new User();
        int result = usermapperP.update(user, updateWrapper);
        System.out.println("受影响的行数：" + result);
    }