526互联

Mybatis-Plus条件构造器

发布时间 2023-05-07 22:19:47作者: 学无止境，贵在当下

Wrapper 介绍

image-20230507210613126

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

QueryWrapper

组装查询条件

@Test
public void test1() {
    //查询用户名包含a，年龄在20到30之间，并且邮箱不为null的用户信息
    //SELECT id,name,age,email FROM t_user WHERE (name 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 list = this.userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

组装排序条件

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

组装删除条件

@Test
public void test3() {
    //删除email为空的用户
    //DELETE FROM t_user WHERE (email IS NULL)
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.isNull("email");
    int result = this.userMapper.delete(queryWrapper);
    System.out.println("result:" + result);
}

条件的优先级

将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改:

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

将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改:

lambda表达式内的逻辑会优先运算

@Test
public void test5() {
    //将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
    //UPDATE t_user SET age=?, email=? WHERE (name LIKE ? AND (age > ? OR email IS NULL))
    //lambda表达式内的逻辑优先运算
    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("user@atguigu.com");
    int result = this.userMapper.update(user, queryWrapper);
    System.out.println("result:" + result);
}

组装select子句

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

实现子查询

@Test
public void test7() {
    //查询id小于等于3的用户信息
    //SELECT id,name,age,email 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");
    List<User> users = this.userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

使用condition组装条件

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

@Test
public void test8() {
    //定义查询条件，有可能为null（用户未输入或未选择）
    //SELECT id,name,age,email FROM t_user WHERE (age >= ? AND age <= ?)
    //        String username = null;
    //        Integer ageBegin = 10;
    //        Integer ageEnd = 24;

    //SELECT id,name,age,email FROM t_user WHERE (name LIKE ? AND age <= ?)
    String username = "a";
    Integer ageBegin = null;
    Integer ageEnd = 24;
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like(StringUtils.isNotBlank(username), "name", username)
        .ge(ageBegin != null, "age", ageBegin)
        .le(ageEnd != null, "age", ageEnd);
    List<User> users = this.userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

UpdateWrapper

@Test
public void test9() {
    //将（年龄大于20或邮箱为null）并且用户名中包含有a的用户信息修改
    //方式一：
    //UPDATE t_user SET name=?,age=? WHERE (name LIKE ? AND (age > ? OR email IS NULL))
    //        UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
    //        userUpdateWrapper.set("name", "张三").set("age", 18)
    //                .like("name", "a")
    //                .and(i -> i.gt("age", 20).or().isNull("email"));
    //        this.userMapper.update(null, userUpdateWrapper);

    //方式二：
    //UPDATE t_user SET name=?, age=? WHERE (name LIKE ? AND (age > ? OR email IS NULL))
    UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
    userUpdateWrapper.like("name", "a")
        .and(i -> i.gt("age", 20).or().isNull("email"));
    User user = new User();
    user.setName("张三");
    user.setAge(18);
    this.userMapper.update(user, userUpdateWrapper);
}

LambdaQueryWrapper

@Test
public void test10() {
    //定义查询条件，有可能为null（用户未输入或未选择）
    //SELECT id,name,age,email FROM t_user WHERE (name LIKE ? AND age <= ?)
    String username = "a";
    Integer ageBegin = null;
    Integer ageEnd = 24;
    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> users = this.userMapper.selectList(lambdaQueryWrapper);
    users.forEach(System.out::println);
}

LambdaUpdateWrapper

@Test
public void test11() {
    //将（年龄大于20或邮箱为null）并且用户名中包含有a的用户信息修改
    //UPDATE t_user SET name=?,age=? WHERE (name LIKE ? AND (age > ? OR email IS NULL))
    LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
    lambdaUpdateWrapper.set(User::getName, "张三")
        .set(User::getAge, 18)
        .like(User::getName, "a")
        .and(i -> i.gt(User::getAge, 20).or().isNull(User::getEmail));
    this.userMapper.update(null, lambdaUpdateWrapper);
}