MybatisPlus条件查询方法全解

1、是什么？

MybatisPlus通过条件构造器可以组装复杂的查询条件，写一些复杂的SQL语句，从而简化我们的开发提升我们的开发效率

# 可以简单的理解为就是我们写SQL语句时where后面的条件where xxx...

2、怎么玩？

(1) 获取条件构造器的几种方式

/** * 获取普通条件构造器的几种方式 */@Testpublic void testGetWrapper() {	Wrapper<Employee> wrapper = new QueryWrapper<Employee>();	Wrapper<Employee> query = Wrappers.<Employee>query();}/** * 获取Lambda条件构造器的几种方式 */@Testpublic void testGetLambdaWrapper() {	Wrapper<Employee> wrapper = new LambdaQueryWrapper<>();	Wrapper<Employee> query = Wrappers.<Employee>lambdaQuery();}

(2) eq 等于 =

例如：我想查询姓名为张三的员工信息

• mysql

select * from employee where name = '张三';

• MP

/** * 等于条件，可以存在多个，多个条件之间默认使用and连接 * eq(R column, Object val) : select * from employee where name = '张三'; * <p> */@Testpublic void testEq() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().eq(Employee::getName, "张三");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在eq基础上，满足condition为true的情况，才会拼接条件 * eq(boolean condition, R column, Object val); */@Testpublic void testEqCondition() {// String keyword = "张三";String keyword = "";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().eq(StringUtils.isNotBlank(keyword), Employee::getName, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(3) ne 不等于 <>

例如：我想查询姓名不为张三的员工信息

• mysql

select * from employee where name <> '张三';

• MP

 /** * 不等于条件，可以存在多个，多个条件之间默认使用and连接 * ne(R column, Object val) */@Testpublic void testNe() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().ne(Employee::getName, "张三");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在ne基础上，满足condition为true的情况，才会拼接条件 * ne(boolean condition, R column, Object val); */@Testpublic void testNeCondition() {String keyword = "张三";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().ne(StringUtils.isNotBlank(keyword), Employee::getName, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(4) gt 大于 >

例如：我想查询id大于3的员工信息

• mysql

select * from employee where id > 3;

• MP

/** * 大于条件，可以存在多个，多个条件之间默认使用and连接 * gt(R column, Object val) */@Testpublic void testGt() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().gt(Employee::getId, 3);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在gt基础上，满足condition为true的情况，才会拼接条件 * gt(boolean condition, R column, Object val) */@Testpublic void testGtCondition() {Integer keyword = 3;Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().gt(keyword < 5, Employee::getId, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(5) ge 大于等于 >=

例如：我想查询id大于等于3的员工信息

• mysql

select * from employee where id > 3;

• MP

/** * 大于等于条件，可以存在多个，多个条件之间默认使用and连接 * ge(R column, Object val) */@Testpublic void testGe() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().ge(Employee::getId, 3);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在ge基础上，满足condition为true的情况，才会拼接条件 * ge(boolean condition, R column, Object val) */@Testpublic void testGeCondition() {Integer keyword = 3;Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().ge(keyword < 5, Employee::getId, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(6) lt 小于 <

例如：我想查询id小于3员工信息

• mysql

select * from employee where id < 3;

• MP

 /** * 小于条件，可以存在多个，多个条件之间默认使用and连接 * lt(R column, Object val) */@Testpublic void testLt() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().lt(Employee::getId, 3);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在lt基础上，满足condition为true的情况，才会拼接条件 * lt(boolean condition, R column, Object val) */@Testpublic void testLtCondition() {Integer keyword = 3;Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().lt(keyword < 5, Employee::getId, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(7) le 小于等于 <=

例如：我想查询id小于等于3的员工信息

• mysql

select * from employee where id <= 3;

• MP

/** * 小于等于条件，可以存在多个，多个条件之间默认使用and连接 * le(R column, Object val) */@Testpublic void testLe() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().le(Employee::getId, 3);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在le基础上，满足condition为true的情况，才会拼接条件 * le(boolean condition, R column, Object val) */@Testpublic void testLeCondition() {Integer keyword = 3;Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().le(keyword < 5, Employee::getId, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(8) between 范围条件 BETWEEN xxx and xxx

例如：我想查询id在2-4的员工信息

• mysql

select * from employee WHERE id BETWEEN 2 and 4;

• MP

/** * 范围条件，可以存在多个，多个条件之间默认使用and连接(但是没有意义)；可以使用or连接 * between(R column, Object val1, Object val2) */@Testpublic void testBetween() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().between(Employee::getId, 2, 4).or().between(Employee::getId, 2, 4);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在between基础上，满足condition为true的情况，才会拼接条件 * between(boolean condition, R column, Object val1, Object val2) */@Testpublic void testBetweenCondition() {Integer keyword1 = 2;Integer keyword2 = 4;Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().between(keyword1 < 3 || keyword2 > 5, Employee::getId, keyword1, keyword2);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(9) notBetween 范围条件 NOT BETWEEN xxx and xxx

例如：我想查询id不在2-4的员工信息

• mysql

select * from employee WHERE id NOT BETWEEN 2 and 4;

• MP

/** * 不在范围条件中，可以存在多个，多个条件之间默认使用and连接(但是没有意义)；可以使用or连接 * notBetween(R column, Object val1, Object val2) */@Testpublic void testNotBetween() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notBetween(Employee::getId, 2, 4);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在notBetween基础上，满足condition为true的情况，才会拼接条件 * notBetween(boolean condition, R column, Object val1, Object val2) */@Testpublic void testNotBetweenCondition() {Integer keyword1 = 2;Integer keyword2 = 4;Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notBetween(keyword1 < 3 || keyword2 > 5, Employee::getId, keyword1, keyword2);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(10) like 全模糊查询条件 like %xxx%

例如：我想查询姓名包含张的员工信息

• mysql

select * from employee where name like '%张%';

• MP

/** * 全模糊查询，可以存在多个，多个条件之间默认使用and连接 * like(R column, Object val) */@Testpublic void testLike() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().like(Employee::getName, "张");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在like基础上，满足condition为true的情况，才会拼接条件 * like(boolean condition, R column, Object val) */@Testpublic void testLikeCondition() {String keyword = "张";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().like(StringUtils.isNotBlank(keyword), Employee::getName, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(11) like 不在全模糊查询条件中 not like %xxx%

例如：我想查询姓名不包含张的员工信息

• mysql

select * from employee where name not like '%张%';

• MP

/** * 不在全模糊查询条件中，可以存在多个，多个条件之间默认使用and连接 * notLike(R column, Object val) */@Testpublic void testNotLike() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notLike(Employee::getName, "张");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在notLike基础上，满足condition为true的情况，才会拼接条件 * notLike(boolean condition, R column, Object val) */@Testpublic void testNotLikeCondition() {String keyword = "张";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notLike(StringUtils.isNotBlank(keyword), Employee::getName, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(12) likeLife 左模糊查询条件 like %xxx

例如：我想查询姓名以张结尾的员工信息

• mysql

select * from employee where name like '%张';

• MP

/** * 左模糊查询，可以存在多个，多个条件之间默认使用and连接 * likeLife(R column, Object val) */@Testpublic void testLikeLife() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().likeLeft(Employee::getName, "张");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在likeLeft基础上，满足condition为true的情况，才会拼接条件 * likeLeft(boolean condition, R column, Object val) */@Testpublic void testLikeLifeCondition() {String keyword = "张";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().likeLeft(StringUtils.isNotBlank(keyword), Employee::getName, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(13) notLikeLife 左模糊查询条件 not like %xxx

例如：我想查询姓名不以张结尾的员工信息

• mysql

select * from employee where name not like '%张';

• MP

/** * 不在左模糊查询条件中，可以存在多个，多个条件之间默认使用and连接 * notLikeLeft(R column, Object val) */@Testpublic void testNotLikeLife() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notLikeLeft(Employee::getName, "张");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在notLikeLeft基础上，满足condition为true的情况，才会拼接条件 * notLikeLeft(boolean condition, R column, Object val) */@Testpublic void testNotLikeLifeCondition() {String keyword = "张";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notLikeLeft(StringUtils.isNotBlank(keyword), Employee::getName, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(14) likeRight 右模糊查询条件 like xxx%

例如：我想查询姓名以张开头的员工信息

• mysql

select * from employee where name like '张%';

• MP

/** * 右模糊查询，可以存在多个，多个条件之间默认使用and连接 * likeRight(R column, Object val) */@Testpublic void testLikeRight() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().likeRight(Employee::getName, "张");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在likeRight基础上，满足condition为true的情况，才会拼接条件 * likeRight(boolean condition, R column, Object val) */@Testpublic void testLikeRightCondition() {String keyword = "张";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().likeRight(StringUtils.isNotBlank(keyword), Employee::getName, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(15) notLikeRight 不在右模糊查询条件中 not like xxx%

例如：我想查询姓名不以张开头的员工信息

• mysql

select * from employee where name not like '张%';

• MP

/** * 不在右模糊查询条件中，可以存在多个，多个条件之间默认使用and连接 * notLikeRight(R column, Object val) */@Testpublic void testNotLikeRight() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notLikeRight(Employee::getName, "张");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在notLikeRight基础上，满足condition为true的情况，才会拼接条件 * notLikeRight(boolean condition, R column, Object val) */@Testpublic void testNotLikeRightCondition() {String keyword = "张";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notLikeRight(StringUtils.isNotBlank(keyword), Employee::getName, keyword);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(16) isNull field为nul查询条件 is null

例如：我想查询姓名为null的员工信息

• mysql

select * from employee where name is null;

• MP

/** * 查询field为null，可以存在多个，多个条件之间默认使用and连接 * isNull(R column, Object val) */@Testpublic void testIsNull() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().isNull(Employee::getName);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在isNull基础上，满足condition为true的情况，才会拼接条件 * isNull(boolean condition, R column) */@Testpublic void testIsNullCondition() {String keyword = "张";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().isNull(StringUtils.isNotBlank(keyword), Employee::getName);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(17) isNotNull field不为nul查询条件 is not null

例如：我想查询姓名不为null的员工信息

• mysql

select * from employee where name is not null;

• MP

/** * 查询field不为null，可以存在多个，多个条件之间默认使用and连接 * isNotNull(R column, Object val) : select * from employee where name is not null; */@Testpublic void testIsNotNull() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().isNotNull(Employee::getName);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在IsNotNull基础上，满足condition为true的情况，才会拼接条件 * IsNotNull(boolean condition, R column) */@Testpublic void testIsNotNullCondition() {String keyword = "刘";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().isNotNull(StringUtils.isNotBlank(keyword), Employee::getName);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(18) inSql in操作 in (xxx,xxx,xxx)

例如：我想查询id在1,3,5中的员工信息

• mysql

select * from employee where id in(1,3,5);

• MP

/** * in操作，可以存在多个，多个条件之间默认使用and连接 * inSql(R column, String inValue) */@Testpublic void testInSql() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().inSql(Employee::getId, "1,3,5");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在inSql基础上，满足condition为true的情况，才会拼接条件 * inSql(boolean condition, R column, String inValue) */@Testpublic void testInSqlCondition() {// String str = Arrays.asList(1,3,5).toString();// String ids = str.substring(1, str.length() - 1);String ids = StringUtils.joinWith(",",1,3,5);Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().inSql(StringUtils.isNotBlank(ids), Employee::getId, ids);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(19) notInSql not in操作 not in (xxx,xxx,xxx)

例如：我想查询id不在1,3,5中的员工信息

• mysql

select * from employee where id not in(1,3,5);

• MP

/** * not in操作，可以存在多个，多个条件之间默认使用and连接 * notInSql(R column, String inValue) */@Testpublic void testNotInSql() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notInSql(Employee::getId, "1,3,5");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在notInSql基础上，满足condition为true的情况，才会拼接条件 * notInSql(boolean condition, R column, String inValue) */@Testpublic void testNotInSqlCondition() {String ids = StringUtils.joinWith(",", 1,3,5);Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notInSql(StringUtils.isNotBlank(ids), Employee::getId, ids);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(20) groupBy 分组操作 groupBy xxx

例如：我想按照性别分组统计员工个数

• mysql

select COUNT(*) AS emp_count from employee GROUP BY `sex`;

• MP

/** * 分组操作，可以存在多个，多个条件之间默认使用and连接 * groupBy(R column) */@Testpublic void testGroupBy() {Wrapper<Employee> wrapper = Wrappers.<Employee>query().select("count(id) as emp_count").groupBy("sex");List<Map<String, Object>> listMaps = employeeService.listMaps(wrapper);log.info("listMaps:{}", listMaps);}/** * 在groupBy基础上，满足condition为true的情况，才会拼接条件 * groupBy(boolean condition, R column) */@Testpublic void testGroupByCondition() {String keyword = "ly";Wrapper<Employee> wrapper = Wrappers.<Employee>query().select("count(id) as emp_count").groupBy(StringUtils.isNotBlank(keyword), "sex");List<Map<String, Object>> listMaps = employeeService.listMaps(wrapper);log.info("listMaps:{}", listMaps);}

注意我用的是普通的条件构造器

(21) orderByAsc 排序操作: 升序 order by xxx asc

例如：我想按照员工编号升序查询员工信息

• mysql

select * from employee order by id asc;

• MP

/** * 排序操作: 升序 * orderByAsc(R column) */@Testpublic void testOrderByAsc() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().orderByAsc(Employee::getId);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在orderByAsc基础上，满足condition为true的情况，才会拼接条件 * orderByAsc(boolean condition, R column) */@Testpublic void testOrderByAscCondition() {String keyword = "ly";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().orderByAsc(StringUtils.isNotBlank(keyword), Employee::getId);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(22) orderByDesc 排序操作: 降序 order by xxx desc

例如：我想按照员工编号降序查询员工信息

• mysql

select * from employee order by id desc;

• MP

/** * 排序操作: 降序 * orderByDesc(R column)  */@Testpublic void testOrderByDesc() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().orderByDesc(Employee::getId);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}/** * 在orderByDesc基础上，满足condition为true的情况，才会拼接条件 * orderByDesc(boolean condition, R column) */@Testpublic void testOrderByDescCondition() {String keyword = "ly";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().orderByDesc(StringUtils.isNotBlank(keyword), Employee::getId);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(23) orderBy 排序操作: 自定义升、降序 order by xxx asc,xxx desc

例如：我想按照姓名升序，员工编号降序查询员工信息

• mysql

select * from employee order by name asc , id desc;

• MP

 /** * 排序操作: 自定义升、降序 * orderBy(boolean condition, boolean isAsc, R column)  : select * from employee ORDER BY id DESC ; * 参数解释: * param1: 满足condition为true的情况，才会拼接条件 * param2: 是否升序 true:升序 false:降序 * param3: 排序字段 * <p> * 排序字段是可以为多个的 */@Testpublic void testOrder() {String keyword = "ly";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().orderBy(StringUtils.isNotBlank(keyword), true, Employee::getId)				.orderBy(StringUtils.isNotBlank(keyword), false, Employee::getName);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(24) having 分组后操作 having xxx ...

例如：我想查询姓名为张三的员工信息

• mysql

select sex , COUNT(*) as emp_count from employee GROUP BY sex HAVING emp_count >=3;

• MP

/** * 分组后操作 * orderBy(boolean condition, boolean isAsc, R column) */@Testpublic void testHaving() {String keyword = "ly";Wrapper<Employee> wrapper = Wrappers.<Employee>query().select("sex,count(*) as emp_count").groupBy("sex").having("emp_count >= 3");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(25) func 内嵌逻辑 类似于在mysql中做条件判断

例如：如果条件存在查询id为1的员工信息，否则查询id不等于2的员工信息;
当然这个是可以多重判断的

• MP

/** * 内嵌逻辑：可以在满足某些条件|不满足某些条件时，添加查询条件 * func(Consumer<Children> consumer) * 类似于MySQL中的If语句 * IF search_condition THEN * statement_list * ELSE * statement_list * END IF; */@Testpublic void testFunc() {String keyword = "ly";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().func(w -> {if (StringUtils.isNotBlank(keyword)) {w.eq(Employee::getId, 1);} else {w.ne(Employee::getId, 2);}});List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(26) or 或操作

• MP

/** * 或操作 * or  SELECT * FROM employee WHERE (name = ? OR id = ?) */@Testpublic void testOr() {String keyword = "ly";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().eq(Employee::getName, "张三").or().eq(Employee::getId, 2);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(27) and 与操作

• MP

/** * 与操作 * and  SELECT * FROM employee WHERE (name = ? AND id = ?) */@Testpublic void testAnd() {String keyword = "ly";Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().eq(Employee::getName, "张三").and(e -> {e.eq(Employee::getId,2);});List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(28) nested 正常嵌套 不带 AND 或者 OR

• MP

/** * 正常嵌套 不带 AND 或者 OR * nested(Consumer<Param> consumer)  SELECT * FROM employee WHERE ((id = ? OR id = ?)) */@Testpublic void testNested() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().nested(w -> {w.eq(Employee::getId, 1).or().eq(Employee::getId, 2);});List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(29) apply 自定义查询条件

• MP

/** * 自定义查询条件 * apply(String applySql, Object... values)  SELECT * FROM employee WHERE (id = ?) */@Testpublic void testApply() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().apply("id = {0}", 2);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(30) last 无视优化规则直接拼接到 sql 的最后

• MP

/** * 无视优化规则直接拼接到 sql 的最后 * last(String lastSql)  SELECT * FROM employee limit 0 , 1 */@Testpublic void testLast() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().last("limit 0,1");List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(31) exists mysql中的exists 语句,当子查询中的存在查询结果时,我们的主查询结果才会显示

• MP

/** * exists 语句,当子查询中的存在查询结果时,我们的主查询结果才会显示 * exists(String existsSql, Object... values)  SELECT id,name,password,sex,birthday,address,email FROM employee WHERE (EXISTS (select id from employee where id = 10)) */@Testpublic void testExists() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().exists("select id from employee where id = {0}", 10);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}

(32) notExists mysql中的notExists 语句,当子查询中的不存在查询结果时,我们的主查询结果才会显示

• MP

/** * notExists 语句,当子查询中的不存在查询结果时,我们的主查询结果才会显示 * notExists(String existsSql, Object... values)  SELECT id,name,password,sex,birthday,address,email FROM employee WHERE (EXISTS (select id from employee where id = 10)) */@Testpublic void testNotExists() {Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery().notExists("select id from employee where id = {0} or id = {1}", 10, 1);List<Employee> employees = employeeService.list(wrapper);log.info("employees:{}", employees);}