MybatisPlus条件查询方法全解

1、是什么？

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

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

2、怎么玩？

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

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

/**
     * 获取Lambda条件构造器的几种方式
     */
@Test
public 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>
     */
    @Test
    public 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);
     */
    @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

 /**
     * 不等于条件，可以存在多个，多个条件之间默认使用and连接
     * ne(R column, Object val)
     */
    @Test
    public 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);
     */
    @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

/**
     * 大于条件，可以存在多个，多个条件之间默认使用and连接
     * gt(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

/**
     * 大于等于条件，可以存在多个，多个条件之间默认使用and连接
     * ge(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

 /**
     * 小于条件，可以存在多个，多个条件之间默认使用and连接
     * lt(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

/**
     * 小于等于条件，可以存在多个，多个条件之间默认使用and连接
     * le(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

/**
     * 范围条件，可以存在多个，多个条件之间默认使用and连接(但是没有意义)；可以使用or连接
     * between(R column, Object val1, Object val2)
     */
    @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);
    }

    /**
     * 在between基础上，满足condition为true的情况，才会拼接条件
     * between(boolean condition, R column, Object val1, Object val2)
     */
    @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

/**
     * 不在范围条件中，可以存在多个，多个条件之间默认使用and连接(但是没有意义)；可以使用or连接
     * notBetween(R column, Object val1, Object val2)
     */
    @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);
    }

    /**
     * 在notBetween基础上，满足condition为true的情况，才会拼接条件
     * notBetween(boolean condition, R column, Object val1, Object val2)
     */
    @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

/**
     * 全模糊查询，可以存在多个，多个条件之间默认使用and连接
     * like(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

/**
     * 不在全模糊查询条件中，可以存在多个，多个条件之间默认使用and连接
     * notLike(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

/**
     * 左模糊查询，可以存在多个，多个条件之间默认使用and连接
     * likeLife(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

/**
     * 不在左模糊查询条件中，可以存在多个，多个条件之间默认使用and连接
     * notLikeLeft(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

/**
     * 右模糊查询，可以存在多个，多个条件之间默认使用and连接
     * likeRight(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

/**
     * 不在右模糊查询条件中，可以存在多个，多个条件之间默认使用and连接
     * notLikeRight(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

/**
     * 查询field为null，可以存在多个，多个条件之间默认使用and连接
     * isNull(R column, Object val)
     */
    @Test
    public 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)
     */
    @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

/**
     * 查询field不为null，可以存在多个，多个条件之间默认使用and连接
     * isNotNull(R column, Object val) : select * from employee where name is not null;
     */
    @Test
    public 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)
     */
    @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

/**
     * in操作，可以存在多个，多个条件之间默认使用and连接
     * inSql(R column, String inValue)
     */
    @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);
    }

    /**
     * 在inSql基础上，满足condition为true的情况，才会拼接条件
     * inSql(boolean condition, R column, String inValue)
     */
    @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

/**
     * not in操作，可以存在多个，多个条件之间默认使用and连接
     * notInSql(R column, String inValue)
     */
    @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);
    }

    /**
     * 在notInSql基础上，满足condition为true的情况，才会拼接条件
     * notInSql(boolean condition, R column, String inValue)
     */
    @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

/**
     * 分组操作，可以存在多个，多个条件之间默认使用and连接
     * groupBy(R column)
     */
    @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);
    }

    /**
     * 在groupBy基础上，满足condition为true的情况，才会拼接条件
     * groupBy(boolean condition, R column)
     */
    @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

/**
     * 排序操作: 升序
     * orderByAsc(R column)
     */
    @Test
    public 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)
     */
    @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

/**
     * 排序操作: 降序
     * orderByDesc(R column) 
     */
    @Test
    public 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)
     */
    @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

 /**
     * 排序操作: 自定义升、降序
     * orderBy(boolean condition, boolean isAsc, R column)  : select * from employee ORDER BY id DESC ;
     * 参数解释:
     * param1: 满足condition为true的情况，才会拼接条件
     * param2: 是否升序 true:升序 false:降序
     * param3: 排序字段
     * <p>
     * 排序字段是可以为多个的
     */
    @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

/**
     * 分组后操作
     * orderBy(boolean condition, boolean isAsc, R column)
     */
    @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

/**
     * 内嵌逻辑：可以在满足某些条件|不满足某些条件时，添加查询条件
     * func(Consumer<Children> consumer)
     * 类似于MySQL中的If语句
     * IF search_condition THEN
     * statement_list
     * ELSE
     * statement_list
     * END IF;
     */
    @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

/**
     * 或操作
     * or  SELECT * FROM employee WHERE (name = ? OR id = ?)
     */
    @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

/**
     * 与操作
     * and  SELECT * FROM employee WHERE (name = ? AND id = ?)
     */
    @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

/**
     * 正常嵌套 不带 AND 或者 OR
     * nested(Consumer<Param> consumer)  SELECT * FROM employee WHERE ((id = ? OR id = ?))
     */
    @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

/**
     * 自定义查询条件
     * apply(String applySql, Object... values)  SELECT * FROM employee WHERE (id = ?)
     */
    @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

/**
     * 无视优化规则直接拼接到 sql 的最后
     * last(String lastSql)  SELECT * FROM employee limit 0 , 1
     */
    @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

/**
     * 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))
     */
    @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

/**
     * 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))
     */
    @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);
    }