【MyBatis-Plus】条件构造器wepper和常用接口

举报
Laura_张 发表于 2022/08/27 00:17:18 2022/08/27
【摘要】 文章目录 wrapper介绍QueryWrapper例1 组装查询条件例2 组装排序条件例3 组装删除条件例4 组装修改条件例5 组装select子句例6 实现子查询 UpdateWrapp...


wrapper介绍

在这里插入图片描述

在这里插入图片描述

QueryWrapper

例1 组装查询条件

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

例2 组装排序条件

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

例3 组装删除条件

    @Test
    public void test03(){
        //删除邮箱地址为null的用户信息
        //UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)
       
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.isNull("email");
        int result = userMapper.delete(queryWrapper);
        System.out.println("result:"+result);
    }

例4 组装修改条件

   @Test
    public void test04(){
        //将(年龄大于20并且用户名中包含有a)或邮箱为null的用户信息修改
        //UPDATE t_user SET user_name=?, email=? WHERE is_deleted=0 AND (age > ? AND user_name LIKE ? OR email IS NULL)
       
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //gt是大于的意思
        queryWrapper.gt("age", 20)
                .like("user_name", "a")
                .or()
                .isNull("email");
        User user = new User();
        user.setName("小明");
        user.setEmail("test@163.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("result:"+result);
    }

注意:优先级改变了,导致操作的数据也改变了。

    @Test
    public void test05(){
        //将用户名中包含有a并且(年龄大于20或邮箱为null)的用户信息修改
        //lambda中的条件优先执行
        //UPDATE t_user SET user_name=?, email=? WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
       
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("user_name", "a")
                .and(i->i.gt("age",20).or().isNull("email"));
        User user = new User();
        user.setName("小红");
        user.setEmail("test@163.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("result:"+result);
    }

例5 组装select子句

    @Test
    public void test06(){
        //查询用户的用户名、年龄、邮箱信息
        //SELECT user_name,age,email FROM t_user WHERE is_deleted=0
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("user_name", "age", "email");
        //selectMaps()返回Map集合列表,通常配合select()使用,避免User对象中没有被查询到的列值 为null
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        maps.forEach(System.out::println);
    }

例6 实现子查询

    @Test
    public void test07(){
        //查询id小于等于100的用户信息
        //SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (uid IN (select uid from t_user where uid <= 100))
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.inSql("uid", "select uid from t_user where uid <= 100");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

UpdateWrapper

   @Test
    public void test08(){
        //将用户名中包含有a并且(年龄大于20或邮箱为null)的用户信息修改
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper
            .like("user_name", "a")
             .and(i -> i.gt("age", 20).or().isNull("email"));
        updateWrapper
           	 .set("user_name", "小黑")
           	 .set("email","abc@163.com");
        int result = userMapper.update(null, updateWrapper);
        System.out.println("result:"+result);
    }

condition

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

思路一

@Test
    public void test09(){
        //SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (user_name LIKE ? AND age <= ?)
        String username = "a";
        Integer ageBegin = null;
        Integer ageEnd = 30;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        if(StringUtils.isNotBlank(username)){
            //isNotBlank判断某个字符串是否不为空字符串、不为null、不为空白符
            queryWrapper.like("user_name", username);
        }
        if(ageBegin != null){
            queryWrapper.ge("age", ageBegin);
        }
        if(ageEnd != null){
            queryWrapper.le("age", ageEnd);
        }
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

思路二

上面的实现方案没有问题,但是代码比较复杂,我们可以使用带condition参数的重载方法构建查询条件,简化代码的编写

 @Test
    public void test10(){
        String username = "a";
        Integer ageBegin = null;
        Integer ageEnd = 30;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(username), "user_name", username)
                .ge(ageBegin != null, "age", ageBegin)
                .le(ageEnd != null, "age", ageEnd);
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

LambdaQueryWrapper

 @Test
    public void test11(){
        //SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (user_name LIKE ? AND age <= ?)
        String username = "a";
        Integer ageBegin = null;
        Integer ageEnd = 30;
        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> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

LambdaUpdateWrapper

@Test
    public void test12(){
        //将用户名中包含有a并且(年龄大于20或邮箱为null)的用户信息修改
        LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.like(User::getName, "a")
                .and(i -> i.gt(User::getAge, 20).or().isNull(User::getEmail));
        updateWrapper.set(User::getName, "小黑").set(User::getEmail,"abc@163.com");
        int result = userMapper.update(null, updateWrapper);
        System.out.println("result:"+result);
    }

文章来源: blog.csdn.net,作者:张艳伟_Laura,版权归原作者所有,如需转载,请联系作者。

原文链接:blog.csdn.net/Laura__zhang/article/details/124619791

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。