MyBatisPlus：条件构造器

1：wapper介绍

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

2：Querywrapper

1：查询条件

 //wrapper的泛型为实体类
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        //数据库字段名 column
        queryWrapper.like("name", "%a%")
                .between("age", 20,30)
                .isNotNull("email");

生成的SQL语句： SELECT id,name,age,email,is_deleted AS Deleted FROM t_users WHERE is_deleted=0 AND (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
使用此Wrapper： List<User> users = userMapper.selectList(queryWrapper);

2：排序条件

 QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.orderByDesc("age")
                .orderByAsc("id");

生成的SQL语句：SELECT id,name,age,email,is_deleted AS Deleted FROM t_users WHERE is_deleted=0 ORDER BY age DESC,id ASC
使用此Wrapper： List<User> users = userMapper.selectList(queryWrapper);

3：删除条件

 QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.between("age", 10, 25)
                .like("name","%a%");

生成的SQL：UPDATE t_users SET is_deleted=1 WHERE is_deleted=0 AND (age BETWEEN ? AND ? AND name LIKE ?)
使用此Wrapper： userMapper.delete(queryWrapper);

4：修改条件

 QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.like("name", "%a")
                .gt("age", 16)//年纪大于16
                .or()//或
                .isNotNull("email");

生成的SQL：UPDATE t_users SET name=? WHERE is_deleted=0 AND (name LIKE ? AND age > ? OR email IS NOT NULL)
使用此Wrapper：

			 //设置修改的字段，修改为小明
        User user=new User(null,"小明",null,null);
        //传入两个参数
        userMapper.update(user,queryWrapper);`

5：优先级查询

//i 代表前面的queryWrapper的lambda里面的条件的优先级高
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.like("name", "a")
                .and(i->i.gt("age", 20).or().isNull("email"));

生成的SQL：UPDATE t_users SET is_deleted=1 WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
使用此Wrapper： int delete = userMapper.delete(queryWrapper);

6：指定字段查询

QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        //字段列表
        queryWrapper.select("name","age");

生成的SQL：SELECT name,age FROM t_users WHERE is_deleted=0

7：子查询

QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        //子查询字段  子查询sql语句
        queryWrapper.inSql("id", "select id from t_users where id <=100");

生成的SQL：SELECT id,name,age,email,is_deleted AS Deleted FROM t_users WHERE is_deleted=0 AND (id IN (select id from t_users where id <=100))
使用此Warpper： List<User> users = userMapper.selectList(queryWrapper);

3：UpdateWrapper

    UpdateWrapper<User> updateWrapper=new UpdateWrapper<>();
    //用户名包含a并(年纪大于20或邮箱为空)的用户信息修改
    updateWrapper.like("name", "a")
            .and(i->i.gt("age", 20).or().isNull("email"));
     //设置修改的字段
    updateWrapper.set("name", "小黑").set("email", "1111");
    userMapper.update(null, updateWrapper);

生成的SQL：UPDATE t_users SET name=?,email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))

4：组装条件

4.1：原生组装

    //组装的条件
    String name="a";
    Integer ageBegin=null;
    Integer ageEnd=30;
    
    QueryWrapper<User> queryWrapper=new QueryWrapper<>();
    
    queryWrapper.select("name");
    
    if(StringUtils.isNotBlank(name)){
        //isNotBlank：判断字符串是否不为空，不为null，不为空白符
       queryWrapper.like("name", name);
    }
    
    if (ageBegin!=null){
        queryWrapper.ge("age", ageBegin);
    }
    
    if(ageEnd!=null){
        queryWrapper.le("age", ageEnd);
    }
}

生成的SQL：SELECT name FROM t_users WHERE is_deleted=0 AND (name LIKE ? AND age <= ?)

4.2：使用condition组装条件

    String name="a";
    Integer ageBegin=null;
    Integer ageEnd=30;
    
    QueryWrapper<User> queryWrapper=new QueryWrapper<>();
    
    queryWrapper.select("name");
    
    //是否存在，字段，值
    queryWrapper.like(StringUtils.isNotBlank(name), "name", name);
    
    queryWrapper.ge(ageBegin!=null,"age", ageBegin);
    
    queryWrapper.le(ageEnd!=null,"age",ageEnd);
}

生成的SQL： SELECT name FROM t_users WHERE is_deleted=0 AND (name LIKE ? AND age <= ?)

5：LambdaQuery/Update

组装条件：

 String name="a";
    Integer ageBegin=null;
    Integer ageEnd=30;
    
    LambdaQueryWrapper<User> queryWrapper=new LambdaQueryWrapper<>();
    
	//查询字段为name字段 对应实体类为User，调用getName方法确认查询的字段
    queryWrapper.select(User::getName);
    //字段为name
    queryWrapper.like(StringUtils.isNotBlank(name),User::getName,name);
    //字段为age
    queryWrapper.ge(ageBegin!=null,User::getAge,ageBegin);
    //字段为age
    queryWrapper.le(ageEnd!=null,User::getAge,ageEnd);
  
    List<User> users = userMapper.selectList(queryWrapper);
}

生成的SQL： SELECT name FROM t_users WHERE is_deleted=0 AND (name LIKE ? AND age <= ?)

优先级：

 LambdaUpdateWrapper<User> updateWrapper=new LambdaUpdateWrapper<>();
    //模糊查询字段为name  且优先级高， 后面不为空的为email age<=21
    updateWrapper.like(User::getName,"a")
            .and(i->i.isNull(User::getEmail).le(User::getAge, 21));
    //更新的字段为name 更新为小黑  年纪更新为100
    updateWrapper.set(User::getName,"小黑").set(User::getAge,100);
    userMapper.update(null,updateWrapper);
}

生成的SQL： UPDATE t_users SET name=?,age=? WHERE is_deleted=0 AND (name LIKE ? AND (email IS NULL AND age <= ?))