Mybatisplus条件构造器

一、Wrapper介绍

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

• AbstractWrapper ： 用于查询条件封装，生成 sql 的 where 条件

• • QueryWrapper ： 查询条件封装
  • UpdateWrapper ： Update 条件封装
  • AbstractLambdaWrapper ： 使用Lambda 语法

• • • LambdaQueryWrapper ：用于Lambda语法使用的查询Wrapper
    • LambdaUpdateWrapper ： Lambda 更新封装Wrapper

条件构造器是一个用于构建SQL查询条件的工具，它可以帮助你通过链式调用来拼装查询条件，进一步简化了查询条件的编写。使用条件构造器，你可以灵活地构建各种条件，包括等于、不等于、大于、小于、模糊查询等。

二、QueryWrapper

a>例1：组装查询条件

/** 查询用户包含a,年龄在20到30之间，邮箱信息不为null的用户信息 **/
@Test
public void test01() {
    /*
        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> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

b>例2：组装排序条件

/** 组装排序条件 查询用户信息，按照年龄的降序排序，若年龄相同，则按照id升序排序 **/
@Test
public void test02() {
    /*
        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> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

c>例3：组装删除条件

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

d>例4：条件的优先级

 /** 组装修改条件  将(年龄大于20并且用户名中包含有a)或邮箱为null的用户信息修改 **/
@Test
public void test04() {
    /*
        UPDATE t_user SET user_name=?, email=?
        WHERE is_deleted=0 AND (age > ? AND user_name LIKE ? OR email IS NOT NULL)
     */
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.gt("age", 20)
            .like("user_name", "a")
            .or()
            .isNotNull("email");
    User user = new User();
    user.setName("小明");
    user.setEmail("test@atguigu.com");
    int result = userMapper.update(user, queryWrapper);
    System.out.println("result = " + result);
}
 
/** 条件优先级  将用户名中包含a并且(年龄大于20或邮箱为null)的用户信息修改 **/
@Test
public void test05() {
    // 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@atguigu.com");
    int result = userMapper.update(user, queryWrapper);
    System.out.println("result = " + result);
}

e>例5：组装select子句（查询部分字段）

/** 组装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>> users = userMapper.selectMaps(queryWrapper);
    users.forEach(System.out::println);
}

f>例6：实现子查询

/** 组装子查询 查询id小于100的用户信息**/
@Test
public void test07() {
    /*
        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

/** 使用UpdateWrapper实现修改功能 将用户名中包含a并且(年龄大于20或邮箱为null)的用户信息修改 **/
@Test
public void test08() {
    /*
        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", "abc@atguigu.com");
    int result = userMapper.update(null, updateWrapper);
    System.out.println("result = " + result);
}

四、使用condition组装条件

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

思路一：

使用if判断语句

/** 模拟开发中组装条件的情况  **/
@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.gt("age", ageBegin);
    }
    if (ageEnd != null) {
        queryWrapper.le("age", ageEnd);
    }
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

思路二：

使用带condition参数的重载方法构建查询条件，简化代码的编写

以like举例，condition用于做判断

/** 使用condition组装条件 **/
@Test
public void test10() {
    /*
        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<>();
    queryWrapper.like(StringUtils.isNotBlank(username), "user_name", username)
            .gt(ageBegin != null, "age", ageBegin)
            .le(ageEnd != null, "age", ageEnd);
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

五、LambdaQueryWrapper

功能等同于QueryWrapper，提供了Lambda表达式的语法可以避免填错列名。

/** 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;
    //组装set子句
    LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
    //避免使用字符串表示字段，防止运行时错误
    queryWrapper.like(StringUtils.isNotBlank(username), User::getName, username)
            .gt(ageBegin != null, User::getAge, ageBegin)
            .le(ageEnd != null, User::getAge, ageEnd);
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}

六、LambdaUpdateWrapper

/** LambdaUpdateWrapper **/
@Test
public void test12() {
    /*
        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")
            //lambda表达式内的逻辑优先运算
            .and(i -> i.gt(User::getAge, 20).or().isNull(User::getEmail)); 
    updateWrapper.set(User::getName, "小黑").set(User::getEmail, "abc@atguigu.com");
    int result = userMapper.update(null, updateWrapper);
    System.out.println("result = " + result);
}