条件构造器

最新推荐文章于 2024-07-26 21:26:51 发布

原创最新推荐文章于 2024-07-26 21:26:51 发布 · 266 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #sql #开发语言

mybatis-plus(new) 专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了如何在MybatisPlus中使用Wrapper和LambdaQueryWrapper/LambdaUpdateWrapper进行查询、排序、删除、修改及复杂条件组装。涵盖了SQL构建、Lambda表达式应用和不同场景下的操作演示。

在这里插入图片描述

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

组装查询条件

@SpringBootTest
public class MybatisplusWrapperTest {

    @Autowired
    UserMapper userMapper;

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

组装排序条件

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

组装删除条件

    @Test
    public void test03() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.isNull("email");
        int i = userMapper.delete(queryWrapper);
        System.out.println(i);
    }

实现修改功能

    @Test
    public void test04() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.gt("age",20)
                .like("name","a")
                .or()
                .isNull("email");
        User user = new User();
        user.setAge(30);
        int i = userMapper.update(user, queryWrapper);
        System.out.println(i);
    }

条件优先级

    @Test
    public void test05() {
        //将用户名中包含a并且（年龄大于20或邮箱为null）的用户信息修改
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name","a")
                .and(i->i.gt("age",20).or().isNull("email"));
        User user = new User();
        user.setAge(30);
        int i = userMapper.update(user, queryWrapper);
        System.out.println(i);
    }

组装select子句

    @Test
    public void test06() {
        //将用户名中包含a并且（年龄大于20或邮箱为null）的用户信息修改
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("name","age");
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        maps.forEach(System.out::println);
    }

组装子查询

    @Test
    public void test07() {
        //id 小于等于 100 的数据
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.inSql("id","select id from user where id <= 100");
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        maps.forEach(System.out::println);
    }

使用UpdateWrapper实现修改功能

    @Test
    public void test08() {
        //
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.like("name","a")
                .and(i->i.gt("age",20).or().isNull("email"));
        updateWrapper.set("name","fk");
        int i = userMapper.update(null, updateWrapper);
        System.out.println(i);
    }

模拟开发中组装条件的情况

    @Test
    public void test09() {

        String username = "a";
        Integer ageBegin = 20;
        Integer ageEnd = 30;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        if (StringUtils.isNotBlank(username)) {
            queryWrapper.like("name",username);
        }
        if (ageBegin != null) {
            queryWrapper.ge("age",ageBegin);
        }
        if (ageEnd != null) {
            queryWrapper.ge("age",ageEnd);
        }
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

使用condition组装条件

    @Test
    public void test10() {
        String username = "a";
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(username),"name",username);
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

LambdaQueryWrapper

    @Test
    public void test11() {
        String username = "a";
        Integer ageBegin = 20;
        Integer ageEnd = 30;
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.like(StringUtils.isNotBlank(username),User::getName,username)
                .ge(ageBegin!=null,User::getAge,ageBegin)
                .le(ageEnd!=null,User::getAge,ageEnd);
        List<User> users = userMapper.selectList(lambdaQueryWrapper);
        users.forEach(System.out::println);
    }

LambdaUpdateWrapper

    @Test
    public void test12() {
        //
        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,"fk");
        int i = userMapper.update(null, updateWrapper);
        System.out.println(i);
    }