条件构造器~wapper

想养一只萨摩耶～

已于 2023-08-25 09:27:58 修改

阅读量1k

点赞数

分类专栏： mybatis-plus 文章标签：数据库 sql java

于 2022-08-01 16:54:24 首次发布

本文链接：https://blog.csdn.net/qq_50652600/article/details/126100810

版权

mybatis-plus 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

2.4 queryWrapper实现修改功能

2.5 条件的优先级

2.6 组装select语句

2.7 组装子查询

2.8 使用updateWrapper实现修改功能

2.9 使用condition组装条件

2.10 LambdaQueryWrapper

2.11 LambdaUpdateWrapper

1 wapper介绍

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

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

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

LambdaQueryWrapper：用于Lambda语法使用的查询Wrapper

LambdaUpdateWrapper：用于Lambda更新封装Wrapper

2 条件查询

2.1 组装查询条件

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

sql拼接如下：

==>  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)
<==      Total: 0

2.2 组装排序条件

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

sql拼接如下：

==>  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: 6, ybc6, 26, null, 0
<==        Row: 5, ybc5, 25, null, 0
<==        Row: 3, ybc3, 23, null, 0
<==        Row: 2, ybc2, 22, null, 0
<==        Row: 4, ybc4, 22, null, 0
<==        Row: 1, ybc1, 21, null, 0
<==      Total: 6

可见是按照年龄的降序排列，当年龄相同的时候，则按照id的升序排列。

2.3 组装删除条件

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

sql拼接如下：

==>  Preparing: UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NULL)
==> Parameters: 
<==    Updates: 6
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5aaaa446]
result:6

注意：这其实只是一个逻辑删除。

2.4 queryWrapper实现修改功能

    @Test
    public void test04(){
        //将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改
        //UPDATE t_user SET user_name=?, email=? WHERE is_deleted=0 AND (age > ? AND user_name LIKE ? OR email IS NULL)
        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@atguigu.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("result:"+result);
    }

sql拼接如下：

==>  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@atguigu.com(String), 20(Integer), %a%(String)
<==    Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7dd611c8]
result:0

可见：update(@Param("et") T entity, @Param("ew") Wrapper<T> updateWrapper)

执行此条语法时，前面的需要被修改的值，后面是符合条件的数据。

2.5 条件的优先级

有些时候，我们虽然拼接了很多条件，但是合理的安排条件的执行顺序可以在很大程度上帮我们优化查询速度。

    @Test
    public void test05(){
        //将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
        //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);
    }

sql拼接如下：

==>  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@atguigu.com(String), %a%(String), 20(Integer)
<==    Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@35f639fa]
result:0

注意：在mybatisplus中，lambda中的条件优先执行。因为可以看到拼接的sql语句中age和email在一个小括号内。

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

拼接sql如下：

==>  Preparing: SELECT user_name,age,email FROM t_user WHERE is_deleted=0
==> Parameters: 
<==      Total: 0

2.7 组装子查询

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

拼接sql如下：

==>  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 <= 100))
==> Parameters: 
<==      Total: 0

2.8 使用updateWrapper实现修改功能

    @Test
    public void test08(){
        //将用户名中包含有a并且（年龄大于20或邮箱为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);
    }

拼接sql如下：

==>  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@atguigu.com(String), %a%(String), 20(Integer)
<==    Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3e47a03]
result：0

2.9 使用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);
    }

拼接sql如下：

==>  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

2.10 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;
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(username), User::getName, username)
                .ge(ageBegin != null, User::getAge, ageBegin)
                .le(ageEnd != null, User::getAge, ageEnd);
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }

拼接sql如下：

==>  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

2.11 LambdaUpdateWrapper

    @Test
    public void test12(){
        //将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改
        LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.like(User::getName, "a")
                .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);
    }

拼接sql如下：

==>  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@atguigu.com(String), %a%(String), 20(Integer)
<==    Updates: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3f702946]
result：0

想养一只萨摩耶～

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
条件构造器~wapper

Wrapper条件构造抽象类，最顶端父类用于查询条件封装，生成sql的where条件查询条件封装Update条件封装使用Lambda语法用于Lambda语法使用的查询Wrapper用于Lambda更新封装Wrapper。
复制链接

扫一扫