MyBatis-Plus学习笔记（Spring版）——（五）MyBatis-Plus条件构造器和常用接口

最新推荐文章于 2025-02-27 17:58:40 发布

kxttmx

最新推荐文章于 2025-02-27 17:58:40 发布

阅读量207

点赞数

分类专栏： MyBatisPlus（尚硅谷2022版）文章标签： mybatis 学习 spring mybatis-plus

本文链接：https://blog.youkuaiyun.com/kuaixiao0217/article/details/126003151

版权

MyBatisPlus（尚硅谷2022版）专栏收录该内容

8 篇文章

订阅专栏

本文介绍了MyBatis Plus中的条件构造器，如QueryWrapper和UpdateWrapper，展示了如何通过它们来组装查询、排序、删除、更新等SQL条件，并探讨了条件的优先级和子查询的使用。同时，给出了处理可选条件的两种策略，简化了代码编写。最后，演示了LambdaQueryWrapper和LambdaUpdateWrapper的Lambda表达式用法，增强了代码的可读性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

05、条件构造器和常用接口

5.1、wapper介绍

在这里插入图片描述

Wrapper ：条件构造抽象类，最顶端父类
- AbstractWrapper ：用于查询条件封装，生成sql 的 where 条件
  - QueryWrapper ：查询条件封装
  - UpdateWrapper ： Update 条件封装
  - AbstractLambdaWrapper ：使用Lambda 语法
    - LambdaQueryWrapper ：用于Lambda语法使用的查询Wrapper
    - LambdaUpdateWrapper ： Lambda 更新封装Wrapper

5.2、QueryWrapper

5.2.1、例1：组装查询条件

@Test
public void test01() {
    //查询用户名包括e,年龄在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","e")
            .between("age",20,30)
            .isNotNull("email");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

5.2.2、例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);
}

5.2.3、例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);
}

5.2.4、例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<>();
    queryWrapper.gt("age",20)
            .like("user_name","a")
            .or()
            .isNull("email");
    User user = new User();
    user.setName("田七");
    user.setEmail("tianqi@123.com");
    int result = userMapper.update(user, queryWrapper);
    System.out.println("result:" + result);
}

@Test
public void test05() {
    //将（年龄大于20或邮箱为null）并且用户名中包含有a的用户信息修改
    //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("ming@123.com");
    int result = userMapper.update(user, queryWrapper);
    System.out.println("result:" + result);
}

5.2.5、例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");
    List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
    maps.forEach(System.out::println);
}

5.2.6、例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);
}

5.3、UpdateWrapper

@Test
public void test08() {
    //将（年龄大于20或邮箱为null）并且用户名中包含有a的用户信息修改
    //UPDATE t_user SET user_name=?,email=? WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS 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","hong@123.com");
    int result = userMapper.update(null, updateWrapper);
    System.out.println("result:"+ result);
}

5.4、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);
}

5.5、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> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    lambdaQueryWrapper.like(StringUtils.isNotBlank(username), User::getName, username)
            .ge(ageBegin != null, User::getAge, ageBegin)
            .le(ageEnd != null, User::getAge, ageEnd);
    List<User> list = userMapper.selectList(lambdaQueryWrapper);
    list.forEach(System.out::println);
}

5.6、LambdaUpdateWrapper

@Test
public void test12() {
    //将（年龄大于20或邮箱为null）并且用户名中包含有a的用户信息修改
    //UPDATE t_user SET user_name=?,email=? WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS 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,"hong@123.com");
    int result = userMapper.update(null, updateWrapper);
    System.out.println("result:"+ result);
}