EmmmuaCode
wufan
2022-04-17
目录

条件构造器和常用接口

# 条件构造器和常用接口

# wapper介绍

01

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

# QueryWrapper

警告

方法参数中的String column，要写数据库中的字段名

# 组装查询条件

查询用户名包含a,年龄在20到30之间，邮箱信息不为null的用户信息

    @Test
    public void test01(){
        QueryWrapper<User> queryWrapper=new QueryWrapper<>();
        queryWrapper.like("user_name","a")
                .between("age",20,30)
                .isNotNull("email");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out :: println);
    }
}
1
2
3
4
5
6
7
8
9
10

# 结果

...
==>  Preparing: 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)//未删除状态的数据
==> Parameters: %a%(String), 20(Integer), 30(Integer)
<==    Columns: id, name, age, email, is_deleted
<==        Row: 6, Sandy, 21, test4@baomidou.com, 0
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7a7cc52c]
User(id=6, name=Sandy, age=21, email=test4@baomidou.com, isDeleted=0)
2022-04-17 21:23:31.912  INFO 24952 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 21:23:31.933  INFO 24952 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9
10
11
12

# 组装排序条件

查询用户信息，按照年龄的降序排序，若年龄相同，则按照Id升序排序

	@Test
    public void test02(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("age")
                .orderByAsc("uid");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out :: println);
    }
1
2
3
4
5
6
7
8

# 结果

...
==>  Preparing: 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
==> Parameters: 
<==    Columns: id, name, age, email, is_deleted
<==        Row: 7, Billie, 24, test5@baomidou.com, 0
<==        Row: 8, 张三, 23, zhangsan@atschool.com, 0
<==        Row: 9, 张三, 23, zhangsan@atschool.com, 0
<==        Row: 10, 张三, 23, zhangsan@atschool.com, 0
<==        Row: 6, Sandy, 21, test4@baomidou.com, 0
<==      Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@69228e85]
User(id=7, name=Billie, age=24, email=test5@baomidou.com, isDeleted=0)
User(id=8, name=张三, age=23, email=zhangsan@atschool.com, isDeleted=0)
User(id=9, name=张三, age=23, email=zhangsan@atschool.com, isDeleted=0)
User(id=10, name=张三, age=23, email=zhangsan@atschool.com, isDeleted=0)
User(id=6, name=Sandy, age=21, email=test4@baomidou.com, isDeleted=0)
2022-04-17 21:37:32.032  INFO 15980 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 21:37:32.067  INFO 15980 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 组装删除条件

删除邮箱地址为null的用户信息

    @Test
    public void test03(){
        //删除邮箱地址为null的用户信息
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.isNull("email");
        int result  = userMapper.delete(queryWrapper);
        System.out.println("受影响行数:"+result);

    }
1
2
3
4
5
6
7
8
9

# 结果

==>  Preparing: UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)逻辑删除
==> Parameters: 
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7139bd31]
受影响行数:1
2022-04-17 21:47:28.182  INFO 14044 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 21:47:28.414  INFO 14044 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9

# 修改-条件优先级

# 例一

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

	@Test
    public void test04(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.gt("age",20)
                .like("user_name","a")
                .or()
                .isNull("email");
        User user = new User();
        user.setName("小明");
        user.setEmail("test@atSchool.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("result:"+result);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13

# 结果

...
==>  Preparing: UPDATE t_user SET user_name=?, email=? WHERE is_deleted=0 AND (age > ? AND user_name LIKE ? OR email IS NULL)
==> Parameters: 小明(String), test@atSchool.com(String), 20(Integer), %a%(String)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@150ede8b]
result:1
2022-04-17 21:58:17.434  INFO 12304 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 21:58:17.447  INFO 12304 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9
10

# 例二

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

    @Test
    public void test05(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //lambda表达式内的逻辑优先运算
        queryWrapper.like("user_name","a")
                .and(i->i.gt("age",20)
                        .or()
                        .isNull("email"));
        User user = new User();
        user.setName("小红");
        user.setEmail("test@atSchool.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("result:"+result);

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 结果

...
==>  Preparing: UPDATE t_user SET user_name=?, email=? WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
==> Parameters: 小红(String), test@atSchool.com(String), %a%(String), 20(Integer)
<==    Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4e1ce44]
result:0
2022-04-17 22:15:10.654  INFO 4324 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 22:15:10.707  INFO 4324 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9
10

# 组装select子句

查询用户信息的username，age和email字段

   	@Test
    public void test06(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("user_name","age","email");
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        maps.forEach(System.out :: println);
    }
1
2
3
4
5
6
7

# 结果

...
==>  Preparing: SELECT user_name,age,email FROM t_user WHERE is_deleted=0
==> Parameters: 
<==    Columns: user_name, age, email
<==        Row: 小明, 21, test@atSchool.com
<==        Row: Billie, 24, test5@baomidou.com
<==        Row: 张三, 23, zhangsan@atschool.com
<==        Row: 张三, 23, zhangsan@atschool.com
<==        Row: 张三, 23, zhangsan@atschool.com
<==      Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@602ae7b6]
{user_name=小明, age=21, email=test@atSchool.com}
{user_name=Billie, age=24, email=test5@baomidou.com}
{user_name=张三, age=23, email=zhangsan@atschool.com}
{user_name=张三, age=23, email=zhangsan@atschool.com}
{user_name=张三, age=23, email=zhangsan@atschool.com}
2022-04-17 22:31:19.068  INFO 25564 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 22:31:19.251  INFO 25564 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 实现子查询

查询id小于等于4的用户信息

    @Test
    public void test07(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.inSql("uid","select uid from t_user where uid<=4");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out :: println);
    }
1
2
3
4
5
6
7

# 结果

...
==>  Preparing: 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<=4))
==> Parameters: 
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3e15bb06]
2022-04-17 22:39:12.244  INFO 25816 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 22:39:12.331  INFO 25816 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9

# UpdateWrapper

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

    @Test
    public void test08(){
        UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
        userUpdateWrapper.like("user_name","a")
                .and(i->i.gt("age",20)
                .or()
                .isNull("email"));
        userUpdateWrapper.set("user_name","小黑")
                .set("email","abc@atSchool.com"); //不需要创建user对象
        int result = userMapper.update(null, userUpdateWrapper);
        System.out.println("result:"+result);
    }
1
2
3
4
5
6
7
8
9
10
11
12

# 结果

...
==>  Preparing: UPDATE t_user SET user_name=?,email=? WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
==> Parameters: 小黑(String), abc@atSchool.com(String), %a%(String), 20(Integer)
<==    Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2f61d591]
result:0
2022-04-17 22:50:23.079  INFO 24852 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 22:50:23.125  INFO 24852 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9
10

# condition

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

# 思路一

	@Test
    public void test09(){
        //定义查询条件，有可能为null（用户未输入或未选择）
        String username="";
        Integer ageBegin=20;
        Integer ageEnd=30;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        if(StringUtils.isNotBlank(username)){
            //isNotBlank 判断某个字符串是否不为空字符串，不为null,不为空白符
            queryWrapper.like("user_name",username);
        }
        if(ageBegin!=null){
            queryWrapper.ge("age",ageBegin);
        }
        if(ageEnd!=null){
            queryWrapper.le("age",ageEnd);
        }
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out :: println);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 结果

...
==>  Preparing: SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (age >= ? AND age <= ?)
==> Parameters: 20(Integer), 30(Integer)
<==    Columns: id, name, age, email, is_deleted
<==        Row: 6, 小明, 21, test@atSchool.com, 0
<==        Row: 7, Billie, 24, test5@baomidou.com, 0
<==        Row: 8, 张三, 23, zhangsan@atschool.com, 0
<==        Row: 9, 张三, 23, zhangsan@atschool.com, 0
<==        Row: 10, 张三, 23, zhangsan@atschool.com, 0
<==      Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2f61d591]
User(id=6, name=小明, age=21, email=test@atSchool.com, isDeleted=0)
User(id=7, name=Billie, age=24, email=test5@baomidou.com, isDeleted=0)
User(id=8, name=张三, age=23, email=zhangsan@atschool.com, isDeleted=0)
User(id=9, name=张三, age=23, email=zhangsan@atschool.com, isDeleted=0)
User(id=10, name=张三, age=23, email=zhangsan@atschool.com, isDeleted=0)
2022-04-17 23:01:59.138  INFO 23772 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 23:01:59.163  INFO 23772 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 思路二

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

    @Test
    public void test10(){
        String username="a";
        Integer ageBegin=null;
        Integer ageEnd=30;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(username),"user_name",username)
                .ge(ageBegin!=null,"age",ageBegin)
                .le(ageEnd!=null,"age",ageEnd);
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out :: println);

    }
1
2
3
4
5
6
7
8
9
10
11
12
13

# 结果

...
==>  Preparing: 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 <= ?)
==> Parameters: %a%(String), 30(Integer)
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@524a2ffb]
2022-04-17 23:13:16.331  INFO 26596 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 23:13:16.370  INFO 26596 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9

# LambdaQueryWrapper

    @Test
    public void test11(){
        //定义查询条件，有可能为null（用户未输入）
        String username="a";
        Integer ageBegin=null;
        Integer ageEnd=30;
        LambdaQueryWrapper<User> userLambdaQueryWrapper = new LambdaQueryWrapper<>();
        //避免使用字符串表示字段，防止运行时错误
        userLambdaQueryWrapper.like(StringUtils.isNotBlank(username),
                User :: getName,username)
                .ge(ageBegin!=null,User::getAge,ageBegin)
                .le(ageEnd!=null,User::getAge,ageEnd);
        List<User> list = userMapper.selectList(userLambdaQueryWrapper);
        list.forEach(System.out :: println);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 结果

...
==>  Preparing: 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 <= ?)
==> Parameters: %a%(String), 30(Integer)
<==      Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@75361cf6]
2022-04-17 23:21:28.345  INFO 5168 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 23:21:28.371  INFO 5168 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9

# LambdaUpdateWrapper

    @Test
    public void test12(){
        //组装set子句
        LambdaUpdateWrapper<User> userLambdaUpdateWrapper = new LambdaUpdateWrapper<>();
        userLambdaUpdateWrapper.like(User :: getName,"a")
                .and(i->i.gt(User::getAge,20)
                        .or().isNull(User::getEmail));
        userLambdaUpdateWrapper.set(User::getName,"小黑")
                .set(User ::getEmail,"abc@atSchool.com");//lambda表达式内的逻辑优先运算
        int result = userMapper.update(null, userLambdaUpdateWrapper);
        System.out.println("result:"+result);
    }
1
2
3
4
5
6
7
8
9
10
11
12

# 结果

...
==>  Preparing: UPDATE t_user SET user_name=?,email=? WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
==> Parameters: 小黑(String), abc@atSchool.com(String), %a%(String), 20(Integer)
<==    Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6d303498]
result:0
2022-04-17 23:35:34.607  INFO 22852 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-04-17 23:35:34.710  INFO 22852 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

Process finished with exit code 0
1
2
3
4
5
6
7
8
9
10

# Wrappers工具类

Wrappers工具类为我们提供了一种简洁、灵活的方式来构建查询和更新条件。用于创建各种类型的Wrapper对象。Wrapper对象是用于构建查询条件或更新条件的容器，它提供了一系列的方法来设置条件、排序、分页等操作。

# Wrappers工具类的使用方法

# 查询条件的构建

使用Wrappers工具类构建查询条件非常简单。可以通过静态方法Wrappers.lambdaQuery创建LambdaQueryWrapper对象，然后使用各种条件方法进行条件的设置。例如，.eq(User::getName, "John")表示设置name字段等于"John"的条件。

除了eq方法，还有许多其他常用的条件方法，如ne、like、in等，可以根据具体的业务需求选择合适的方法进行条件的设置。

# 更新条件的构建

使用Wrappers工具类构建更新条件也非常简单。可以通过静态方法Wrappers.lambdaUpdate创建LambdaUpdateWrapper对象，然后使用各种条件方法进行条件的设置。与查询条件的构建类似，可以使用诸如eq、ne、like、in等方法来设置条件。

在更新条件设置完成后，可以通过set方法设置要更新的字段和值，然后调用baseMapper.update方法将更新条件应用到数据库中，实现数据的更新操作。

# 示例代码

下面是一个简单的示例代码，展示了如何使用Wrappers工具类构建查询和更新条件：

// 查询条件的构建
LambdaQueryWrapper<User> queryWrapper = Wrappers.lambdaQuery(User.class)
        .eq(User::getAge, 25)
        .like(User::getName, "John");

// 更新条件的构建
LambdaUpdateWrapper<User> updateWrapper = Wrappers.lambdaUpdate(User.class)
        .eq(User::getAge, 18)
        .set(User::getName, "Adult");

// 应用查询条件
List<User> userList = userMapper.selectList(queryWrapper);

// 应用更新条件
userMapper.update(null, updateWrapper);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

在上述示例中，我们使用Wrappers.lambdaQuery方法创建了一个LambdaQueryWrapper对象queryWrapper，并使用eq和like方法设置了查询条件。然后，我们使用Wrappers.lambdaUpdate方法创建了一个LambdaUpdateWrapper对象updateWrapper，并使用eq和set方法设置了更新条件。

最后，我们通过调用selectList方法和update方法，将查询条件和更新条件应用到数据库中，实现了查询和更新操作。

# 总结

Wrappers工具类是MyBatis-Plus框架中一个非常有用的工具，它提供了一种简洁、灵活的方式来构建查询和更新条件。通过类型安全的Lambda表达式和链式调用的方式，我们可以轻松地构建复杂的查询和更新逻辑。同时，IDE的支持也使得使用Wrappers工具类更加便捷。希望本文能够帮助你更好地理解和应用Wrappers工具类，提升开发效率。

#MyBatis-Plus
上次更新: 2024/04/18, 13:08:42
MyBatis-Plus 常用注解
MyBatis-Plus 插件

← MyBatis-Plus 插件→

最近更新
01
微信支付功能的实现与流程
11-21
02
购物车与结算区域的深入优化与功能完善
11-21
03
购物车与结算区域的功能实现与优化
11-21
更多文章>
Theme by Vdoing | Copyright © 2023-2024 EmmmuaCode | 黔ICP备2022009864号-2