MybatisPlus:wapper组装查询条件

后端 MybatisPlus
发布时间 :
字数:2k 阅读 :
  1. wapper介绍
  2. QueryWrapper
    1. 组装查询条件
    2. 组装排序条件
    3. 组装删除条件
    4. 条件的优先级
    5. 组装select子句
    6. 实现子查询
  3. UpdateWrapper
  4. condition
    1. 使用if判断
    2. condition
  5. LambdaQueryWrapper
  6. LambdaUpdateWrapper

wapper介绍

Wrapper ： 条件构造抽象类，最顶端父类

  • AbstractWrapper ： 用于查询条件封装，生成 sql 的 where 条件
    • QueryWrapper ： 查询条件封装
    • UpdateWrapper ： Update 条件封装
    • AbstractLambdaWrapper ： 使用Lambda 语法
      • LambdaQueryWrapper ：用于Lambda语法使用的查询Wrapper
      • LambdaUpdateWrapper ： Lambda 更新封装Wrapper

Wrapper /ˈræpə(r)/ noun（食品等的）包装材料，包装纸，包装塑料

QueryWrapper

组装查询条件

@Test
public void test01(){
    //查询用户名包含a，年龄在20到30之间，并且邮箱不为null的用户信息
    //Preparing: SELECT uid AS id,name AS username,age,email,is_deleted FROM t_user 
    // WHERE (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
    //Parameters: %a%(String), 20(Integer), 30(Integer)
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    // 这里的Column 对应的是 数据库里面的字段
    queryWrapper.like("name", "a")// name LIKE ? 
        .between("age", 20, 30) // BETWEEN ? AND ?
        .isNotNull("email"); // email IS NOT NULL
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

组装排序条件

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

组装删除条件

@Test
public void test03(){
    //删除email为空的用户
    //DELETE FROM t_user WHERE (email IS NULL)
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.isNull("email"); //email IS NULL
    //条件构造器也可以构建删除语句的条件
    int result = userMapper.delete(queryWrapper);
    System.out.println("受影响的行数：" + result);
}

条件的优先级

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

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

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

@Test
public void test04sub() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
    //UPDATE t_user SET age=?, email=? WHERE (name LIKE ? AND (age > ? OR email IS NULL))
    queryWrapper.like("name", "a") // name LIKE ?
        //lambda表达式内的逻辑优先运算:AND (age > ? OR email IS NULL)
        .and(i -> i.gt("age", 20).or().isNull("email"));
    /**
         * 泛型 Param 是具体需要运行函数的类(也是 wrapper 的子类)
         *     default Children and(Consumer<Param> consumer) {
         *         return and(true, consumer);
         *     }
         *
         * */
    User user = new User();
    user.setAge(18);
    user.setEmail("user@atguigu.com");
    int result = userMapper.update(user, queryWrapper);
    System.out.println("受影响的行数：" + result);
}

组装select子句

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

实现子查询

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

UpdateWrapper

不通过实体类

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

通过实体类

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

condition

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

使用if判断

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

condition

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

上面使用的都是带有condition参数的方法

@Override
public Children like(boolean condition, R column, Object val) {
    return likeValue(condition, LIKE, column, val, SqlLike.DEFAULT);
}
@Override
public Children ge(boolean condition, R column, Object val) {
    return addCondition(condition, column, GE, val);
}
@Override
public Children like(boolean condition, R column, Object val) {
    return likeValue(condition, LIKE, column, val, SqlLike.DEFAULT);
}

LambdaQueryWrapper

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

LambdaUpdateWrapper

@Test
public void test10() {
    //组装set子句
    LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
    updateWrapper
        .set(User::getAge, 18)
        .set(User::getEmail, "zzzz")
        .like(User::getUsername, "a")
        //lambda表达式内的逻辑优先运算
        .and(i -> i.lt(User::getAge, 24).or().isNull(User::getEmail));
    User user = new User();
    // UPDATE t_user SET age=?,email=? WHERE (name LIKE ? AND (age < ? OR email IS NULL))
    int result = userMapper.update(user, updateWrapper);
    System.out.println("受影响的行数：" + result);
}
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 1909773034@qq.com

赏

©2017 XieShaoLin

Built with Hexo and 3-hexo theme

×

喜欢就点赞,疼爱就打赏