Java：MybatisPlus--条件构造器

1、条件构造器类别

①wrapper：抽象类，条件类的顶层，提供了一些获取和判断相关的方法。

②AbstractWrapper：抽象类，Wrapper的子类，提供了所有的条件相关方法。

③AbstractLambdaWrapper：抽象类，AbstractWrapper的子类，确定字段参数为方法引用类型

④QueryWrapper：AbstractWrapper的子类，如果需要传递String类型的字段信息，创建该对象

⑤LambdaQueryWrapper：AbstractLambdaWrapper的子类，如果我们需要传递方法引用方式的字段信息，创建该对象。

 2、等值查询

测试：eq

@Test
void eq(){
    //1.创建QueryWrapper对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.设置条件，指定String字段名称和值
    lambdaQueryWrapper.eq(User::getName,"Jack");
    //3.使用条件完成查询
    User user = userMapper.selectOne(lambdaQueryWrapper);
    System.out.println(user);
}

测试：allEq

@Test
void allEq(){
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    lambdaQueryWrapper.eq(User::getName,"Jone");
    lambdaQueryWrapper.eq(User::getAge,18);

    User user = userMapper.selectOne(lambdaQueryWrapper);
    System.out.println(user);
}

测试：ne

@Test
void ne(){
    //1.创建QueryWrapper对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.设置条件，指定String字段名称和值
    String name = "Jone";
    lambdaQueryWrapper.ne(User::getName,name);
    //3.使用条件完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

3、范围查询

gt    lt

@Test
void gt(){
    //1.创建QueryWrapper对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.设置条件，指定字段名称和值
    Integer age = 18;
    lambdaQueryWrapper.gt(User::getAge,age);
    //3.使用条件完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

ge    le

@Test
void ge(){
    //1.创建QueryWrapper对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.设置条件，指定字段名称和值
    Integer age = 18;
    lambdaQueryWrapper.ge(User::getAge,age);
    //3.使用条件完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

between    notBetween

@Test
void between(){
    //1.创建 QueryWrapper 对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.设置条件，指定字段名称和值
    lambdaQueryWrapper.between(User::getAge,18,30);
    //3.使用条件完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

4、模糊查询

like、notLike、likeLeft、likeRight

@Test
void like(){
    //1.创建 QueryWrapper 对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.设置条件，指定字段名称和值
    lambdaQueryWrapper.like(User::getName,"J");
    //3.使用条件完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

5、判空查询

isNull   isNotNull

@Test
void isNull(){
    //1.创建 QueryWrapper 对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.设置条件，指定字段名称
    lambdaQueryWrapper.isNull(User::getName);
    //3.使用条件完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

6、包含查询

in、notIn、inSql、notInSql

@Test
void in(){
    //1.创建 QueryWrapper 对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.设置条件，指定字段名称和值
    ArrayList<Integer> arrayList = new ArrayList<>();
    Collections.addAll(arrayList,18,20,21);
    lambdaQueryWrapper.in(User::getAge,arrayList);
    //3.使用条件完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

7、分组查询

groupBy

@Test
void groupBy(){
    //1.创建 QueryWrapper 对象
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //2.设置条件，指定字段名称和值
    queryWrapper.groupBy("age");
    queryWrapper.select("age,count(*) as field_count");
    //3.使用条件完成查询
    List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
    System.out.println(maps);
}

8、聚合查询

having

@Test
void having(){
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //分组字段
    queryWrapper.groupBy("age");
    //查询字段
    queryWrapper.select("age,count(*) as field_count");
    //聚合条件筛选
    queryWrapper.having("field_count = 1");
    List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
    System.out.println(maps);
}

9、排序查询

orderBy、orderByAsc、orderByDesc

@Test
void orderBy(){
    //1.创建 QueryWrapper 对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.设置条件，指定降序排序字段
    lambdaQueryWrapper.orderBy(true,true,User::getId);
    lambdaQueryWrapper.orderBy(true,false,User::getAge);
    //3.使用条件完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

10、逻辑查询

and是默认的、or、nested

@Test
void nested(){
    //1.创建 QueryWrapper 对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.构建条件查询语句
    lambdaQueryWrapper.nested(i -> i.eq(User::getName, "Billie").ne(User::getAge,
    22));
    //3.完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

11、自定义查询

apply

@Test
void apply(){
    //1.创建 QueryWrapper 对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.构建条件查询语句
    lambdaQueryWrapper.apply("id = 1");
    //3.完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

12、last查询

@Test
void last(){
    //1.创建 QueryWrapper 对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.构建条件查询语句
    lambdaQueryWrapper.last("limit 0,2");
    //3.完成查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

13、exists查询

exists、notExists

@Test
void exists(){
    //1.创建 QueryWrapper 对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.构建查询条件
    lambdaQueryWrapper.exists("select id from powershop_user where age = 18");
    //3.查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}

14、字段查询

@Test
void select(){
    //1.创建 QueryWrapper 对象
    LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
    //2.构建查询条件
    lambdaQueryWrapper.select(User::getId,User::getName);
    //3.查询
    List<User> users = userMapper.selectList(lambdaQueryWrapper);
    System.out.println(users);
}