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();}@Testpublic void testGetLambdaWrapper() {Wrapper<Employee> wrapper = new LambdaQueryWrapper<>();Wrapper<Employee> query = Wrappers.<Employee>lambdaQuery();}

(2) eq 等于 =

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

• mysql

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

• MP

    @Test    public void testEq() {        Wrapper<Employee> wrapper = Wrappers                .<Employee>lambdaQuery()                .eq(Employee::getName, "张三");        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

     @Test    public void testNe() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .ne(Employee::getName, "张三");        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testGt() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .gt(Employee::getId, 3);        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testGe() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .ge(Employee::getId, 3);        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

     @Test    public void testLt() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .lt(Employee::getId, 3);        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testLe() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .le(Employee::getId, 3);        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public 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);    }        @Test    public 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

    @Test    public void testNotBetween() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .notBetween(Employee::getId, 2, 4);        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testLike() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .like(Employee::getName, "张");        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testNotLike() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .notLike(Employee::getName, "张");        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testLikeLife() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .likeLeft(Employee::getName, "张");        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testNotLikeLife() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .notLikeLeft(Employee::getName, "张");        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testLikeRight() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .likeRight(Employee::getName, "张");        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testNotLikeRight() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .notLikeRight(Employee::getName, "张");        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testIsNull() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .isNull(Employee::getName);        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testIsNotNull() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .isNotNull(Employee::getName);        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testInSql() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .inSql(Employee::getId, "1,3,5");        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testNotInSql() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .notInSql(Employee::getId, "1,3,5");        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public 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);    }        @Test    public 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

    @Test    public void testOrderByAsc() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .orderByAsc(Employee::getId);        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

    @Test    public void testOrderByDesc() {        Wrapper<Employee> wrapper = Wrappers.<Employee>lambdaQuery()                .orderByDesc(Employee::getId);        List<Employee> employees = employeeService.list(wrapper);        log.info("employees:{}", employees);    }        @Test    public 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

     @Test    public 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

    @Test    public 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

    @Test    public 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

    @Test    public 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

    @Test    public 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

    @Test    public 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

    @Test    public 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

    @Test    public 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

    @Test    public 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

    @Test    public 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);    }

来源地址：https://blog.csdn.net/weixin_45529338/article/details/130448579