05.MyBatis-Plus条件构造器和常用接口

Leefs 2022-12-20 PM 981℃ 0条

[TOC]

### 一、Wrapper条件构造器

条件构造器wrapper就是用来封装CRUD方法参数条件的一个接口，其底层有很多的子类，最主要的就是最下面的四个子类：

- **queryWrapper**：可以用来删改查
- **updateWrapper**：可以在修改操作时不必创建实体类对象的操作
- **LambdaQueryWrapper**和**LambdaUpdateWrapper**：则是在字段参数的调用上进行了升级，其他都一样

因为增删改查中的增加记录不需要条件即可完成，所以增加方法无需条件构造器wrapper，其他的删改查则是有这个条件构造器参数的。

![05.MyBatis-Plus条件构造器和常用接口01.jpg](https://lilinchao.com/usr/uploads/2022/12/4641411.jpg)

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

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

***条件构造器官网地址**：https://baomidou.com/pages/10c804/#abstractwrapper*

### 二、QueryWrapper构造器

#### 2.1 组装查询条件

> 需求：查询用户名包含a，年龄在20-30岁，邮箱信息不是null的用户信息
>
> SQL：SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 
>          AND (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)

```java
@Test
public void test01(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.like("name", "a")
        .between("age", 20, 30)
        .isNotNull("email");
    List<User> list = userMapper.selectList(queryWrapper);
    list.forEach(System.out::println);
}
```

**运行结果**

```
==>  Preparing: SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
==> Parameters: %a%(String), 20(Integer), 30(Integer)
<==    Columns: id, name, age, email, is_deleted
<==        Row: 2, Jack, 20, test2@baomidou.com, 0
<==        Row: 4, Sandy, 21, test4@baomidou.com, 0
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3dd818e8]
User(id=2, name=Jack, age=20, email=test2@baomidou.com, isDeleted=0)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com, isDeleted=0)
```

#### 2.2 组装排序条件

> 需求：查询用户信息，按照年龄的降序排序，若年龄相同，则按照id升序
>
> SQL：SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 ORDER BY age DESC,id ASC

```java
@Test
public void test2() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.orderByDesc("age").orderByAsc("id");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
```

**运行结果**

```
==>  Preparing: SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 ORDER BY age DESC,id ASC
==> Parameters: 
<==    Columns: id, name, age, email, is_deleted
<==        Row: 3, Tom, 28, test3@baomidou.com, 0
<==        Row: 5, Billie, 24, test5@baomidou.com, 0
<==        Row: 4, Sandy, 21, test4@baomidou.com, 0
<==        Row: 2, Jack, 20, test2@baomidou.com, 0
<==        Row: 1, Jone, 18, test1@baomidou.com, 0
<==      Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7e2c64]
User(id=3, name=Tom, age=28, email=test3@baomidou.com, isDeleted=0)
User(id=5, name=Billie, age=24, email=test5@baomidou.com, isDeleted=0)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com, isDeleted=0)
User(id=2, name=Jack, age=20, email=test2@baomidou.com, isDeleted=0)
User(id=1, name=Jone, age=18, email=test1@baomidou.com, isDeleted=0)
```

#### 2.3 组装删除条件

> 需求：删除邮箱为null的用户信息
>
> SQL：UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NOT NULL)

```java
@Test
public void test3() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.isNull("email");
    userMapper.delete(queryWrapper);
}
```

**运行结果**

```
==>  Preparing: UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)
==> Parameters: 
<==    Updates: 1
```

#### 2.4 条件的优先级

> 需求：将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改
>
> SQL：UPDATE t_user SET age=? WHERE is_deleted=0 AND (age > ? AND name LIKE ? OR email IS NULL)

```java
@Test
public void test4() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.gt("age",20).like("name","a")
        .or().isNull("email");

User user = new User();
    user.setAge(18);
    userMapper.update(user,queryWrapper);
}
```

**运行结果**

```
==>  Preparing: UPDATE t_user SET age=? WHERE is_deleted=0 AND (age > ? AND name LIKE ? OR email IS NULL)
==> Parameters: 18(Integer), 20(Integer), %a%(String)
<==    Updates: 1
```

> 需求：将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
>
> SQL：UPDATE t_user SET age=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))

```java
@Test
public void test5() {
    ///将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //lambda表达式内的逻辑优先运算
    queryWrapper.like("name","a").and(i->i.gt("age",20).or().isNull("email"));

User user = new User();
    user.setAge(18);
    userMapper.update(user,queryWrapper);
}
```

**运行结果**

```
==>  Preparing: UPDATE t_user SET age=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
==> Parameters: 18(Integer), %a%(String), 20(Integer)
<==    Updates: 1
```

#### 2.5 组装select子句

> 需求：查询用户信息的name和age字段
>
> SQL：SELECT name,age FROM t_user WHERE is_deleted=0

```java
@Test
public void test6() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

queryWrapper.select("name","age");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
```

#### 2.6 实现子查询

> 需求：查询id小于等于3的用户信息
>
> SQL：SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (id IN (select id from t_user where id <= 3))

```java
@Test
public void test7() {
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();

queryWrapper.inSql("id","select id from t_user where id <= 3");
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
```

**运行结果**

```
==>  Preparing: SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (id IN (select id from t_user where id <= 3))
==> Parameters: 
<==    Columns: id, name, age, email, is_deleted
<==        Row: 1, Jone, 18, test1@baomidou.com, 0
<==        Row: 2, Jack, 20, test2@baomidou.com, 0
<==        Row: 3, Tom, 28, test3@baomidou.com, 0
<==      Total: 3
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@481e91b6]
User(id=1, name=Jone, age=18, email=test1@baomidou.com, isDeleted=0)
User(id=2, name=Jack, age=20, email=test2@baomidou.com, isDeleted=0)
User(id=3, name=Tom, age=28, email=test3@baomidou.com, isDeleted=0)
```

### 三、UpdateWrapper

> 需求：将（年龄大于20或邮箱为null）并且用户名中包含有a的用户信息修改
>
> SQL：UPDATE t_user SET age=?, email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))

**方式一**

```java
@Test
public void test8() {
    // 组装set子句以及修改条件
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    updateWrapper.set("age",18).set("email","leefs@163.com")
        .like("name","a").and(i->i.gt("age",20).or().isNull("email"));
    userMapper.update(null,updateWrapper);
}
```

**运行结果**

```
==>  Preparing: UPDATE t_user SET age=?,email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
==> Parameters: 18(Integer), leefs@163.com(String), %a%(String), 20(Integer)
<==    Updates: 1
```

由SQL可见，使用updateWrapper和queryWrapper完成的修改功能一样，且调用的方法也一样(mapper.update)，二者的区别就是updateWrapper不用创建实体类对象，直接使用set方法就可以设置修改的字段值。

**方式二**

```java
@Test
public void test9() {
    // 组装set子句以及修改条件
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    updateWrapper
        .like("name","a").and(i->i.gt("age",20).or().isNull("email"));
    User user = new User();
    user.setAge(28);
    user.setEmail("leefs@163.com");
    userMapper.update(user,updateWrapper);
}
```

**运行结果**

```
==>  Preparing: UPDATE t_user SET age=?, email=? WHERE is_deleted=0 AND (name LIKE ? AND (age > ? OR email IS NULL))
==> Parameters: 28(Integer), leefs@163.com(String), %a%(String), 20(Integer)
<==    Updates: 1
```

### 四、condition

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

#### 思路一

```java
@Test
public void test10() {
    //定义查询条件，有可能为null（用户未输入或未选择）
    String username = null;
    Integer ageBegin = 10;
    Integer ageEnd = 24;
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace) 构成
    //这个是由mp提供的

if (StringUtils.isNotBlank(username)) {
        queryWrapper.like("name", username);
    }
    if (ageBegin != null) {
        queryWrapper.gt("age", ageBegin);
    }
    if (ageEnd != null) {
        queryWrapper.lt("age", ageEnd);
    }
    
    // SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (age > ? AND age < ?)
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
```

**运行结果**

```
==>  Preparing: SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (age > ? AND age < ?)
==> Parameters: 10(Integer), 24(Integer)
<==    Columns: id, name, age, email, is_deleted
<==        Row: 1, Jone, 18, test1@baomidou.com, 0
<==        Row: 2, Jack, 20, test2@baomidou.com, 0
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7c8f9c2e]
User(id=1, name=Jone, age=18, email=test1@baomidou.com, isDeleted=0)
User(id=2, name=Jack, age=20, email=test2@baomidou.com, isDeleted=0)
```

上面的代码使用了很多的if判断显得过于冗余，于是许多的方法都带有一个condition参数，当这个参数为true的时候才会拼接查询条件，下面的代码用来代替上面一堆的if判断。

#### 思路二

> 上面的实现方案没有问题，但是代码比较复杂，我们可以使用带condition参数的重载方法构建查询条件，简化代码的编写。

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

**运行结果**

```
==>  Preparing: SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (age > ? AND age < ?)
==> Parameters: 10(Integer), 24(Integer)
<==    Columns: id, name, age, email, is_deleted
<==        Row: 1, Jone, 18, test1@baomidou.com, 0
<==        Row: 2, Jack, 20, test2@baomidou.com, 0
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7e2c64]
User(id=1, name=Jone, age=18, email=test1@baomidou.com, isDeleted=0)
User(id=2, name=Jack, age=20, email=test2@baomidou.com, isDeleted=0)
```

### 五、lambdaXxxWrapper构造器

> **lambdaXxxWrapper与xxxWrapper的区别就是**
>
> 可以使用Lambda的方式直接调用对象的getter方法来指定字段，而不用对照**数据库**中的字段名，这样就特意避免参数对应不上**数据库**字段的问题。
>
> 除了在调用字段时的写法不一样之外，其他的写法上二者的方式都一样。

#### 5.1 lambdaQueryWrapper构造器

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

**运行结果**

```
==>  Preparing: SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (age > ? AND age < ?)
==> Parameters: 10(Integer), 24(Integer)
<==    Columns: id, name, age, email, is_deleted
<==        Row: 1, Jone, 18, test1@baomidou.com, 0
<==        Row: 2, Jack, 20, test2@baomidou.com, 0
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3a543f31]
User(id=1, name=Jone, age=18, email=test1@baomidou.com, isDeleted=0)
User(id=2, name=Jack, age=20, email=test2@baomidou.com, isDeleted=0)
```

#### 5.2 lambdaUpdateWrapper构造器

> 需求：更新年龄大于30的用户信息
>
> SQL：UPDATE t_user SET age=?,email=? WHERE is_deleted=0 AND (age > ?)

```java
@Test
public void test13() {
    LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
    updateWrapper.set(User::getAge,20).set(User::getEmail,"leefs@163.com")
        .gt(User::getAge,30);
    userMapper.update(null,updateWrapper);
}
```

**运行结果**

```
==>  Preparing: UPDATE t_user SET age=?,email=? WHERE is_deleted=0 AND (age > ?)
==> Parameters: 20(Integer), leefs@163.com(String), 30(Integer)
<==    Updates: 1
```

### 六、语法详情总结

| 方法名                                                | 说明                                                      | 用法实例                                                  | 等价SQL                                         |
| ----------------------------------------------------- | --------------------------------------------------------- | --------------------------------------------------------- | ----------------------------------------------- |
| `allEq(Map<R, V> params)`                             | 全部等于                                                  | map.put(“id”,“3”);map.put(“user_name”,“Leefs”);allEq(map) | user_name = “Leefs” AND id = 3                  |
| **eq(R column, Object val)**                          | **等于 =**                                                | **eq(“id”,“3”)**                                          | **id = 3**                                      |
| **ne(R column, Object val)**                          | **不等于 <>**                                             | **ne(“id”, “3”)**                                         | **id <> 3**                                     |
| **gt(R column, Object val)**                          | **大于 >**                                                | **gt(“user_age”,“18”)**                                   | **user_age > 18**                               |
| **ge(R column, Object val)**                          | **大于等于 >=**                                           | **ge(“user_age”,“18”)**                                   | **user_age >= 18**                              |
| **lt(R column, Object val)**                          | **小于 <**                                                | **lt(“user_age”,“18”)**                                   | **user_age < 18**                               |
| **le(R column, Object val)**                          | **小于等于 <=**                                           | **le(“user_age”,“18”)**                                   | **user_age <= 18**                              |
| **between(R column, Object val1, Object val2)**       | **BETWEEN 值1 AND 值2**                                   | **between(“user_age”,“18”,“25”)**                         | **user_age BETWEEN 18 AND 25**                  |
| notBetween(R column, Object val1, Object val2)        | NOT BETWEEN 值1 AND 值2                                   | notBetween(“user_age”,“18”,“25”)                          | user_age NOT BETWEEN 18 AND 25                  |
| **like(R column, Object val)**                        | **LIKE ‘%值%’**                                           | **like(“user_name”,“Leefs”)**                             | **like ‘%Leefs%’**                              |
| **notLike(R column, Object val)**                     | **NOT LIKE ‘%值%’**                                       | **notLike(“user_name”,“Leefs”)**                          | **not like ‘%Leefs%’**                          |
| **likeLeft(R column, Object val)**                    | **LIKE ‘%值’**                                            | **likeLeft(“user_name”,“Leefs”)**                         | **like ‘%Leefs’**                               |
| **likeRight(R column, Object val)**                   | **LIKE ‘值%’**                                            | **likeRight(“user_name”,“Leefs”)**                        | **like ‘Leefs%’**                               |
| **isNull(R column)**                                  | **字段 IS NULL**                                          | **isNull(“user_name”)**                                   | **user_name IS NULL**                           |
| isNotNull(R column)                                   | 字段 IS NOT NULL                                          | isNotNull(“user_name”)                                    | user_name IS NOT NULL                           |
| in(R column, Collection<?> value)                     | 字段 IN (value.get(0), value.get(1), …)                   | in(“user_age”,{1,2,3})                                    | user_age IN (?,?,?)                             |
| notIn(R column, Collection<?> value)                  | 字段 NOT IN (value.get(0), value.get(1), …)               | notIn(“user_age”,{1,2,3})                                 | user_age NOT IN (?,?,?)                         |
| inSql(R column, String inValue)                       | 字段 IN ( sql语句 )                                       | inSql(“id”,“select id from user”)                         | id IN (select id from user)                     |
| notInSql(R column, String inValue)                    | 字段 NOT IN ( sql语句 )                                   | notInSql(“id”,“select id from user where id > 2”)         | id NOT IN (select id from user where id > 2)    |
| **groupBy(R… columns)**                               | **分组：GROUP BY 字段, …**                                | **groupBy(“id”,“user_age”)**                              | **GROUP BY id,user_age**                        |
| **orderByAsc(R… columns)**                            | **排序【升序】：ORDER BY 字段, … ASC**                    | **orderByAsc(“id”,“user_age”)**                           | **ORDER BY id ASC,user_age ASC**                |
| **orderByDesc(R… columns)**                           | **排序【降序】：ORDER BY 字段, … DESC**                   | **orderByDesc(“id”,“user_age”)**                          | **ORDER BY id DESC,user_age DESC**              |
| orderBy(boolean condition, boolean isAsc, R… columns) | ORDER BY 字段, …                                          | orderBy(true,true,“id”,“user_age”)                        | ORDER BY id ASC,user_age ASC                    |
| having(String sqlHaving, Object… params)              | HAVING ( sql语句 )                                        | having(“sum(user_age)>{0}”,“25”)                          | HAVING sum(user_age)>25                         |
| **or()**                                              | **拼接 OR**                                               | **eq(“id”,1).or().eq(“user_age”,25)**                     | **id = 1 OR user_age = 25**                     |
| **and(Consumer consumer)**                            | **AND 嵌套**                                              | **and(i->i.eq(“id”,1).ne(“user_age”,18))**                | **id = 1 AND user_age <> 25**                   |
| nested(Consumerconsumer)                              | 正常嵌套 不带 AND 或者 OR                                 | nested(i->i.eq(“id”,1).ne(“user_age”,18))                 | id = 1 AND user_age <> 25                       |
| apply(String applySql, Object… params)                | 拼接 sql(不会有SQL注入风险)                               | apply(“user_age>{0}”,“25 or 1=1”)                         | user_age >‘25 or 1=1’                           |
| last(String lastSql)                                  | 拼接到 sql 的最后,多次调用以最后一次为准(有sql注入的风险) | last(“limit 1”)                                           | limit 1                                         |
| exists(String existsSql)                              | 拼接 EXISTS ( sql语句 )                                   | exists(“select id from user where user_age = 1”)          | EXISTS (select id from user where user_age = 1) |

*附参考文章链接地址*

*《尚硅谷MyBatisPlus教程》*

标签: MyBatis, MyBatis-Plus

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://lilinchao.com/archives/2696.html

上一篇 04.MyBatis-Plus常用注解

下一篇 06.MyBatis-Plus插件介绍

05.MyBatis-Plus条件构造器和常用接口

评论啦~

栏目分类

标签云

友情链接申请

05.MyBatis-Plus条件构造器和常用接口

 评论啦~

 栏目分类

标签云

友情链接申请

评论啦~

栏目分类

标签云

友情链接申请