MybatisPlus条件查询方法全解 |
您所在的位置：网站首页 › sql语句条件查询为变量 › MybatisPlus条件查询方法全解 |
MybatisPlus条件查询方法全解
|
1、是什么？
MybatisPlus通过条件构造器可以组装复杂的查询条件，写一些复杂的SQL语句，从而简化我们的开发提升我们的开发效率 # 可以简单的理解为就是我们写SQL语句时where后面的条件 where xxx... 2、怎么玩？ (1) 获取条件构造器的几种方式 /** * 获取普通条件构造器的几种方式 */ @Test public void testGetWrapper() { Wrapper wrapper = new QueryWrapper(); Wrapper query = Wrappers.query(); } /** * 获取Lambda条件构造器的几种方式 */ @Test public void testGetLambdaWrapper() { Wrapper wrapper = new LambdaQueryWrapper(); Wrapper query = Wrappers.lambdaQuery(); } (2) eq 等于 =例如：我想查询姓名为张三的员工信息 mysql select * from employee where name = '张三';
*/ @Test public void testEq() { Wrapper wrapper = Wrappers .lambdaQuery() .eq(Employee::getName, "张三"); List 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 wrapper = Wrappers.lambdaQuery() .eq(StringUtils.isNotBlank(keyword), Employee::getName, keyword); List employees = employeeService.list(wrapper); log.info("employees:{}", employees); } (3) ne 不等于 例如：我想查询姓名不为张三的员工信息 mysql select * from employee where name '张三';
例如：我想查询id大于3的员工信息 mysql select * from employee where id > 3;
例如：我想查询id大于等于3的员工信息 mysql select * from employee where id > 3;
例如：如果条件存在查询id为1的员工信息，否则查询id不等于2的员工信息; 当然这个是可以多重判断的 MP /** * 内嵌逻辑：可以在满足某些条件|不满足某些条件时，添加查询条件 * func(Consumer consumer) * 类似于MySQL中的If语句 * IF search_condition THEN * statement_list * ELSE * statement_list * END IF; */ @Test public void testFunc() { String keyword = "ly"; Wrapper wrapper = Wrappers.lambdaQuery().func(w -> { if (StringUtils.isNotBlank(keyword)) { w.eq(Employee::getId, 1); } else { w.ne(Employee::getId, 2); } }); List 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 wrapper = Wrappers.lambdaQuery() .eq(Employee::getName, "张三") .or() .eq(Employee::getId, 2); List 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 wrapper = Wrappers.lambdaQuery() .eq(Employee::getName, "张三") .and(e -> { e.eq(Employee::getId,2); }); List employees = employeeService.list(wrapper); log.info("employees:{}", employees); } (28) nested 正常嵌套 不带 AND 或者 OR MP /** * 正常嵌套 不带 AND 或者 OR * nested(Consumer consumer) SELECT * FROM employee WHERE ((id = ? OR id = ?)) */ @Test public void testNested() { Wrapper wrapper = Wrappers.lambdaQuery() .nested(w -> { w.eq(Employee::getId, 1) .or() .eq(Employee::getId, 2); }); List 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 wrapper = Wrappers.lambdaQuery() .apply("id = {0}", 2); List 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 wrapper = Wrappers.lambdaQuery() .last("limit 0,1"); List 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 wrapper = Wrappers.lambdaQuery() .exists("select id from employee where id = {0}", 10); List 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 wrapper = Wrappers.lambdaQuery() .notExists("select id from employee where id = {0} or id = {1}", 10, 1); List employees = employeeService.list(wrapper); log.info("employees:{}", employees); } |
【本文地址】
今日新闻 |
推荐新闻 |