mybatisPlus-条件查询
QueryWrapper
说明:
继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件
及 LambdaQueryWrapper, 可以通过 new QueryWrapper().lambda() 方法获取.
测试用表:
1. eq、ne
说明:
eq:等于,ne:不等于
测试:
@Test public void contextLoads(){ QueryWrapper<Employee> wrapper = new QueryWrapper<>(); //eq() 等于 wrapper.eq("last_name", "皮皮虾"); Employee one = employeeService.getOne(wrapper); System.out.println(one); }
@Test public void contextLoads(){ QueryWrapper<Employee> wrapper = new QueryWrapper<>(); //ne() 不等于 wrapper.ne("gender", 0); List<Employee> list = employeeService.list(wrapper); System.out.println(list); }
2. gt、ge、lt、le
说明:
gt:大于,ge:大于等于,lt:小于,le:小于等于
测试:
@Test public void contextLoads(){ QueryWrapper<Employee> gtWrapper = new QueryWrapper<>(); //gt() 大于 gtWrapper.gt("age", 27); List<Employee> gtList = employeeService.list(gtWrapper); System.out.println(gtList); //********************** QueryWrapper<Employee> geWrapper = new QueryWrapper<>(); //ge() 大于等于 geWrapper.ge("age", 26); List<Employee> geList = employeeService.list(geWrapper); System.out.println(geList); }
public void contextLoads(){ QueryWrapper<Employee> ltWrapper = new QueryWrapper<>(); //lt() 小于 ltWrapper.lt("age", 12); List<Employee> ltList = employeeService.list(ltWrapper); System.out.println(ltList); //********************** QueryWrapper<Employee> geWrapper = new QueryWrapper<>(); //le() 小于等于 leWrapper.le("age", 12); List<Employee> leList = employeeService.list(leWrapper); System.out.println(leList); }
3. between、notBetween
说明:
between:在值1和值2之间,notBetween:不在值1和值2之间
测试:
@Test public void contextLoads(){ QueryWrapper<Employee> betweenWrapper = new QueryWrapper<>(); //between() 区间的值 betweenWrapper.between("age", 10, 20); List<Employee> betweenList = employeeService.list(betweenWrapper); System.out.println(betweenList ); //********************** QueryWrapper<Employee> notBetweenWrapper = new QueryWrapper<>(); //notBetween() 不在区间的值 notBetweenWrapper.notBetween("age", 10, 21); List<Employee> notBetweenList = employeeService.list(notBetweenWrapper ); System.out.println(notBetweenList ); }
4. like、notLike、likeLeft、likeRight
说明:
like:’%值%’,notLike:’%值%’,likeLeft:’%值’,likeRight:'值%'
测试:
@Test public void contextLoads(){ QueryWrapper<Employee> likeWrapper = new QueryWrapper<>(); //like() 模糊查询匹配值 likeWrapper.like("last_name", "s"); List<Employee> likeList = employeeService.list(likeWrapper); System.out.println(likeList ); //********************** QueryWrapper<Employee> notLikeWrapper = new QueryWrapper<>(); //notLike() 模糊查询不匹配值 notLikeWrapper.notLike("last_name", "s"); List<Employee> notLikeList = employeeService.list(notLikeWrapper); System.out.println(notLikeList); }
@Test public void contextLoads(){ QueryWrapper<Employee> likeLeftWrapper = new QueryWrapper<>(); //likeLeft() 模糊查询匹配最后一位值 likeLeftWrapper.likeLeft("last_name", "s"); List<Employee> likeLeftList = employeeService.list(likeLeftWrapper); System.out.println(likeLeftList ); //********************** QueryWrapper<Employee> likeRightWrapper = new QueryWrapper<>(); //likeRight() 模糊查询匹配第一位值 likeRightWrapper.likeRight("last_name", "s"); List<Employee> likeRightList = employeeService.list(likeRightWrapper ); System.out.println(likeRightList ); }
5. isNull、isNotNull
说明:
isNull:字段 IS NULL,isNotNull:字段 IS NOT NULL
测试:
@Test public void contextLoads(){ QueryWrapper<Employee> isNullWrapper = new QueryWrapper<>(); //isNull() 为空 isNullWrapper.isNull("email"); List<Employee> isNullList = employeeService.list(isNullWrapper); System.out.println(isNullList ); //********************** QueryWrapper<Employee> isNotNullWrapper = new QueryWrapper<>(); //isNotNull() 不为空 isNotNullWrapper.isNotNull("last_name", "s"); List<Employee> isNotNullList = employeeService.list(isNotNullWrapper ); System.out.println(isNotNullList ); }
6. in、notIn
说明:
in:字段 IN (v0, v1, …),notIn:字段 NOT IN (value.get(0), value.get(1), …)
测试:
@Test public void contextLoads(){ QueryWrapper<Employee> inWrapper = new QueryWrapper<>(); //in() 符合多个条件的值 inWrapper.in("age", 8, 16, 26); List<Employee> inList = employeeService.list(inWrapper); System.out.println(inList ); //********************** QueryWrapper<Employee> notInWrapper= new QueryWrapper<>(); //notIn() 不符合多个条件的值 notInWrapper.notIn("age", 8, 16, 26); List<Employee> notInList = employeeService.list(notInWrapper); System.out.println(notInList ); }
7. or、and
说明:
or:或者 ,and:和
注意事项:
主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)
测试:
@Test public void contextLoads(){ QueryWrapper<Employee> orWrapper = new QueryWrapper<>(); //or() 查询age大于20 或者 gender等于1 的数据 orWrapper.gt("age", 20).or().eq("gender", 1); List<Employee> orList = employeeService.list(orWrapper); System.out.println(orList ); }
@Test public void contextLoads(){ QueryWrapper<Employee> andWrapper = new QueryWrapper<>(); //and() 查询last_name等于皮皮虾b 和 gender等于1 的数据 (不使用and() 默认就是and()) andWrapper.eq("last_name", "皮皮虾b").eq("gender", 1); List<Employee> andList = employeeService.list(andWrapper ); System.out.println(andList); }
8. orderByAsc、orderByDesc
说明:
orderByAsc:升序:ORDER BY 字段, … ASC,orderByDesc:降序:ORDER BY 字段, … DESC
测试:
@Test public void contextLoads(){ QueryWrapper<Employee> Wrapper = new QueryWrapper<>(); //orderByAsc() 升序排序 Wrapper.orderByAsc("id"); List<Employee> list = employeeService.list(Wrapper); for(Employee e : list) { System.out.println(e); } }
@Test public void contextLoads(){ QueryWrapper<Employee> Wrapper = new QueryWrapper<>(); //orderByDesc() 降序排序 Wrapper.orderByDesc("id"); List<Employee> list = employeeService.list(Wrapper); for(Employee e : list) { System.out.println(e); } }
9. inSql、notInSql (不常用)
说明:
inSql:字段 IN ( sql语句 ),notInSql:字段 NOT IN ( sql语句 )
测试:
@Test public void contextLoads(){ QueryWrapper<Employee> inSqlWrapper = new QueryWrapper<>(); //inSql() 符合sql语句的值 inSqlWrapper .inSql("select id from employee where id < 10"); List<Employee> inSqlList = employeeService.list(inSqlWrapper ); System.out.println(inSqlList ); //********************** QueryWrapper<Employee> notInSqlWrapper= new QueryWrapper<>(); //notInSql() 不符合sql语句的值 notInSqlWrapper.notInSql("select id from employee where id < 10"); List<Employee> notInSqlList = employeeService.list(notInSqlWrapper); System.out.println(notInSqlList ); }
10. exists、notExists (不常用)
说明:
exists:拼接 EXISTS ( sql语句 ),notExists:拼接 NOT EXISTS ( sql语句 )
测试:
@Test public void contextLoads(){ QueryWrapper<Employee> existsWrapper = new QueryWrapper<>(); //exists() 查询符合sql语句的值 existsWrapper.exists("select last_name,gender from employee where id = 1"); List<Employee> existsList = employeeService.list(existsWrapper); System.out.println(existsList ); //********************** QueryWrapper<Employee> notExistsWrapper = new QueryWrapper<>(); //notExists() 查询不符合sql语句的值 notExistsWrapper.notExists("select last_name,gender from employee where id = 1"); List<Employee> notExistsList = employeeService.list(notExistsWrapper); System.out.println(notExistsList ); }