MyBatis-Plus 条件查询与进阶用法

目录

• 条件查询方式
• 多条件与null判定
• 查询投影与聚合分组
• 常用查询条件方法

---

条件查询方式

MyBatis-Plus 提供了多种条件查询构造器，极大简化了复杂SQL的编写。
Wrapper类，这个类就是用来构建查询条件的，如下图所示:

1. QueryWrapper

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

QueryWrapper<User> qw = new QueryWrapper<>();
qw.lt("age", 18); // 年龄小于18
List<User> users = userMapper.selectList(qw);

有个小问题就是在写条件的时候，容易出错，比如age写错，就会导致查询不成功

2. LambdaQueryWrapper

推荐使用Lambda表达式，字段安全且IDE可自动提示。

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<>();
lqw.lt(User::getAge, 18);
List<User> users = userMapper.selectList(lqw);

---

多条件与null判定

1. 多条件链式编程

LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<>();
lqw.lt(User::getAge, 30).gt(User::getAge, 10); // 10 < age < 30
List<User> users = userMapper.selectList(lqw);

2. or条件

lqw.lt(User::getAge, 10).or().gt(User::getAge, 30); // age<10 or age>30

3. null判定与动态条件

Integer minAge = 10;
Integer maxAge = 30;
LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<>();
lqw.lt(maxAge != null, User::getAge, maxAge);
lqw.gt(minAge != null, User::getAge, minAge);

---

查询投影与聚合分组

1. 查询部分字段

LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<>();
lqw.select(User::getId, User::getName, User::getAge);
List<User> users = userMapper.selectList(lqw);

2. 聚合查询

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;

QueryWrapper<User> qw = new QueryWrapper<>();
//qw.select("count(*) as count");
//SELECT count(*) as count FROM user

//qw.select("max(age) as maxAge");
//SELECT max(age) as maxAge FROM user

//qw.select("min(age) as minAge");
//SELECT min(age) as minAge FROM user

//qw.select("sum(age) as sumAge");
//SELECT sum(age) as sumAge FROM user

//qw.select("avg(age) as avgAge");
//SELECT avg(age) as avgAge FROM user
qw.select("count(*) as count");
List<Map<String, Object>> result = userMapper.selectMaps(qw);
System.out.println(result);

3. 分组查询

QueryWrapper<User> qw = new QueryWrapper<>();
qw.select("count(*) as count, tel");
qw.groupBy("tel");
List<Map<String, Object>> result = userMapper.selectMaps(qw);
System.out.println(result);

注意

• 聚合与分组查询，无法使用lambda表达式来完成
• MP只是对MyBatis的增强，如果MP实现不了，我们可以直接在DAO接口中使用MyBatis的方式实现

---

常用查询条件方法

方法	说明	示例
eq	等值	lqw.eq(User::getName, "Tom")
between	区间	lqw.between(User::getAge, 10, 30)
like	模糊	lqw.like(User::getName, "J")
orderByDesc	降序	lqw.orderByDesc(User::getId)
in	包含	lqw.in(User::getId, list)
isNull	字段为null	lqw.isNull(User::getTel)
isNotNull	字段不为null	lqw.isNotNull(User::getTel)

代码示例

LambdaQueryWrapper<User> lqw = new LambdaQueryWrapper<>();
lqw.eq(User::getName, "Jerry");
lqw.between(User::getAge, 10, 30);
lqw.like(User::getName, "J");
lqw.orderByDesc(User::getId);
lqw.in(User::getId, Arrays.asList(1L, 2L, 3L));
lqw.isNull(User::getTel);
lqw.isNotNull(User::getTel);
List<User> users = userMapper.selectList(lqw);

---

下一篇：MP 主键策略与逻辑删除