MyBatisPlus之条件查询（常规查询、范围查询、模糊查询、null值处理等）

MyBatisPlus之条件查询

MyBatisPlus之条件查询

1、设置查询条件

1.1 常规格式

@TestpublicvoidtestInuire(){QueryWrapper<User> qw =newQueryWrapper<User>();//查询年龄大于等于15岁，小于30岁的用户
        qw.lt("age",30);
        qw.ge("age",15);List<User> userList = userDao.selectList(qw);System.out.println(userList);}

1.2 链式编程格式

@TestpublicvoidtestInuire(){QueryWrapper<User> qw =newQueryWrapper<User>();//查询年龄大于等于15岁，小于30岁的用户
        qw.lt("age",30).ge("age",15);List<User> userList = userDao.selectList(qw);System.out.println(userList);}

1.3 lambda格式1

@TestpublicvoidtestLambda1(){QueryWrapper<User> qw =newQueryWrapper<User>();//查询年龄大于等于15岁，小于30岁的用户
        qw.lambda().lt(User::getAge,30).ge(User::getAge,15);List<User> userList = userDao.selectList(qw);System.out.println(userList);}

1.4 lambda格式2

@TestpublicvoidtestLambda2(){LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();//查询年龄大于等于15岁，小于30岁的用户
        lqw.lt(User::getAge,30).ge(User::getAge,15);List<User> userList = userDao.selectList(lqw);System.out.println(userList);}

2、组合查询条件

2.1 并且

@TestpublicvoidtestLambda2(){LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();//查询年龄大于等于18岁，小于30岁的用户
        lqw.lt(User::getAge,30).gt(User::getAge,18);List<User> userList = userDao.selectList(lqw);System.out.println(userList);}

2.2 或者

//多条件查询@TestpublicvoidtestLambda3(){//小于18岁或者大于30岁LambdaQueryWrapper<User> lqw2 =newLambdaQueryWrapper<User>();
        lqw2.lt(User::getAge,18).or().gt(User::getAge,30);List<User> userList2 = userDao.selectList(lqw2);System.out.println(userList2);}

3、条件查询null值判定

3.1 封装User模型的查询条件

观察User类里哪些适合设置上下限

packagecom.example.maybatisplus_01_quickstart.domain.query;importcom.example.maybatisplus_01_quickstart.domain.User;importlombok.Data;@DatapublicclassUserQueryextendsUser{privateInteger age2;}

3.2 测试类

packagecom.example.maybatisplus_01_quickstart;importcom.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;importcom.example.maybatisplus_01_quickstart.dao.UserDao;importcom.example.maybatisplus_01_quickstart.domain.User;importcom.example.maybatisplus_01_quickstart.domain.query.UserQuery;importorg.junit.jupiter.api.Test;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.boot.test.context.SpringBootTest;importjava.util.List;@SpringBootTestpublicclassInquireNotNullTest{@AutowiredprivateUserDao userDao;@TestpublicvoidinqueryNotNull(){//模拟页面传递过来的查询数据UserQuery uq =newUserQuery();
        uq.setAge(15);
        uq.setAge2(30);LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();
        lqw.lt(null!= uq.getAge2(),User::getAge, uq.getAge2());
        lqw.gt(null!= uq.getAge(),User::getAge, uq.getAge());List<User> userList = userDao.selectList(lqw);System.out.println(userList);}}

3.3 null值处理

3.3.1 条件参数控制

LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();
lqw.lt(null!= uq.getAge2(),User::getAge, uq.getAge2());
lqw.gt(null!= uq.getAge(),User::getAge, uq.getAge());List<User> userList = userDao.selectList(lqw);System.out.println(userList);

3.3.2 条件参数控制（链式编程）

LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();
lqw.lt(null!= uq.getAge2(),User::getAge, uq.getAge2()).gt(null!= uq.getAge(),User::getAge, uq.getAge());List<User> userList = userDao.selectList(lqw);System.out.println(userList);

4、查询投影

4.1 查询结果中包含模型类中 部分属性

@TestpublicvoidtestQueryProjection1(){LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();//这种方式只适合lambda表达式
        lqw.select(User::getId,User::getName,User::getAge);List<User> userList = userDao.selectList(lqw);System.out.println(userList);}

4.2 查询结果包含模型类中 未定义的属性

@TestpublicvoidtestQueryProjection3(){QueryWrapper<User> lqw =newQueryWrapper<User>();
        lqw.select("count(*) as count, tel");
        lqw.groupBy("tel");List<Map<String,Object>> userList = userDao.selectMaps(lqw);System.out.println(userList);}

4.3 原生mysql查询

如果上述方法都无法实现，只能自己手写mysql代码。

4.4 完整测试样例

packagecom.example.maybatisplus_01_quickstart;importcom.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;importcom.baomidou.mybatisplus.core.conditions.query.QueryWrapper;importcom.example.maybatisplus_01_quickstart.dao.UserDao;importcom.example.maybatisplus_01_quickstart.domain.User;importorg.junit.jupiter.api.Test;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.boot.test.context.SpringBootTest;importjava.util.List;importjava.util.Map;@SpringBootTestpublicclassQueryProjectionTest{@AutowiredprivateUserDao userDao;//查询投影@TestpublicvoidtestQueryProjection1(){LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();
        lqw.select(User::getId,User::getName,User::getAge);List<User> userList = userDao.selectList(lqw);System.out.println(userList);}@TestpublicvoidtestQueryProjection2(){QueryWrapper<User> lqw =newQueryWrapper<User>();
        lqw.select("id","name","age");List<User> userList = userDao.selectList(lqw);System.out.println(userList);}@TestpublicvoidtestQueryProjection3(){QueryWrapper<User> lqw =newQueryWrapper<User>();
        lqw.select("count(*) as count, tel");
        lqw.groupBy("tel");List<Map<String,Object>> userList = userDao.selectMaps(lqw);System.out.println(userList);}}

5、查询条件设置（范围匹配、模糊匹配等）

示例

packagecom.example.maybatisplus_01_quickstart;importcom.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;importcom.example.maybatisplus_01_quickstart.dao.UserDao;importcom.example.maybatisplus_01_quickstart.domain.User;importorg.junit.jupiter.api.Test;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.boot.test.context.SpringBootTest;importjava.util.List;@SpringBootTestpublicclassConditionalQueryMoreTest{@AutowiredprivateUserDao userDao;//模拟登录操作查询@TestpublicvoidtestConditionalQueryMore(){//条件查询LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();//模拟登录操作
        lqw.eq(User::getName,"西瓜").eq(User::getPassword,"ewfvfv");User loginUser = userDao.selectOne(lqw);System.out.println(loginUser);}//范围查询@TestpublicvoidtestfindRangeQuery(){LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();//范围查询 lt(不带等号) le(带等号) gt(不带等号) le(带等号) eq between//between中前小后大
        lqw.between(User::getAge,10,22);List<User> userList = userDao.selectList(lqw);System.out.println(userList);}//模糊查询@TestpublicvoidtestfuzzyQuery(){LambdaQueryWrapper<User> lqw =newLambdaQueryWrapper<User>();//模糊匹配//likeRight表示搜索是 西%//likeLeft表示搜索是 %西
        lqw.likeRight(User::getName,"西");List<User> userList = userDao.selectList(lqw);System.out.println(userList);}}