条件构造器

条件构造器是 MyBatis Plus 最强大的功能之一，它提供了优雅的方式来构建复杂的查询条件。本章将详细介绍各种条件构造器的使用方法。

Wrapper 家族

MyBatis Plus 提供了多种 Wrapper 类型：

Wrapper 类	说明
`QueryWrapper`	查询条件封装
`LambdaQueryWrapper`	使用 Lambda 表达式的查询条件封装
`UpdateWrapper`	更新条件封装
`LambdaUpdateWrapper`	使用 Lambda 表达式的更新条件封装
`AbstractWrapper`	所有 Wrapper 的父类，提供通用方法

比较操作

等于 eq

@Test
void testEq() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(User::getName, "张三");
    
    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testEqWithCondition() {
    String name = "张三";
    
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(name != null, User::getName, name);
    
    List<User> users = userMapper.selectList(wrapper);
}

不等于 ne

@Test
void testNe() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.ne(User::getAge, 25);
    
    List<User> users = userMapper.selectList(wrapper);
}

大于 gt、大于等于 ge

@Test
void testGtGe() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.gt(User::getAge, 20)
           .ge(User::getAge, 18);
    
    List<User> users = userMapper.selectList(wrapper);
}

小于 lt、小于等于 le

@Test
void testLtLe() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.lt(User::getAge, 30)
           .le(User::getAge, 60);
    
    List<User> users = userMapper.selectList(wrapper);
}

BETWEEN between

@Test
void testBetween() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.between(User::getAge, 20, 30);
    
    List<User> users = userMapper.selectList(wrapper);
}

NOT BETWEEN notBetween

@Test
void testNotBetween() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.notBetween(User::getAge, 20, 30);
    
    List<User> users = userMapper.selectList(wrapper);
}

模糊查询

LIKE like

@Test
void testLike() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.like(User::getName, "张");
    
    List<User> users = userMapper.selectList(wrapper);
}

NOT LIKE notLike

@Test
void testNotLike() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.notLike(User::getName, "张");
    
    List<User> users = userMapper.selectList(wrapper);
}

LIKE LEFT likeLeft

@Test
void testLikeLeft() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.likeLeft(User::getName, "三");
    
    List<User> users = userMapper.selectList(wrapper);
}

LIKE RIGHT likeRight

@Test
void testLikeRight() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.likeRight(User::getName, "张");
    
    List<User> users = userMapper.selectList(wrapper);
}

范围查询

IN in

@Test
void testIn() {
    List<Integer> ages = Arrays.asList(20, 25, 30);
    
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.in(User::getAge, ages);
    
    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testInArray() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.in(User::getAge, 20, 25, 30);
    
    List<User> users = userMapper.selectList(wrapper);
}

NOT IN notIn

@Test
void testNotIn() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.notIn(User::getAge, 20, 25, 30);
    
    List<User> users = userMapper.selectList(wrapper);
}

IS NULL isNull

@Test
void testIsNull() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.isNull(User::getEmail);
    
    List<User> users = userMapper.selectList(wrapper);
}

IS NOT NULL isNotNull

@Test
void testIsNotNull() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.isNotNull(User::getEmail);
    
    List<User> users = userMapper.selectList(wrapper);
}

分组与排序

GROUP BY groupBy

@Test
void testGroupBy() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.select("age", "COUNT(*) as count")
           .groupBy("age");
    
    List<Map<String, Object>> result = userMapper.selectMaps(wrapper);
    result.forEach(System.out::println);
}

ORDER BY orderByAsc / orderByDesc

@Test
void testOrderBy() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.orderByAsc(User::getAge)
           .orderByDesc(User::getCreateTime);
    
    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testOrderByWithCondition() {
    boolean isAsc = true;
    
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.orderBy(true, isAsc, User::getAge);
    
    List<User> users = userMapper.selectList(wrapper);
}

HAVING having

@Test
void testHaving() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.select("age", "COUNT(*) as count")
           .groupBy("age")
           .having("COUNT(*) > {0}", 1);
    
    List<Map<String, Object>> result = userMapper.selectMaps(wrapper);
}

逻辑连接

AND and

@Test
void testAnd() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(User::getName, "张三")
           .and(w -> w.gt(User::getAge, 20).lt(User::getAge, 30));
    
    List<User> users = userMapper.selectList(wrapper);
}

生成的 SQL：

WHERE name = '张三' AND (age > 20 AND age < 30)

OR or

@Test
void testOr() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(User::getName, "张三")
           .or()
           .eq(User::getName, "李四");
    
    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testOrNested() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(User::getName, "张三")
           .or(w -> w.gt(User::getAge, 30).isNotNull(User::getEmail));
    
    List<User> users = userMapper.selectList(wrapper);
}

生成的 SQL：

WHERE name = '张三' OR (age > 30 AND email IS NOT NULL)

NESTED nested

@Test
void testNested() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.nested(w -> w.eq(User::getName, "张三").gt(User::getAge, 20));
    
    List<User> users = userMapper.selectList(wrapper);
}

生成的 SQL：

WHERE (name = '张三' AND age > 20)

其他条件

APPLY apply

用于拼接 SQL 片段：

@Test
void testApply() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.apply("date_format(create_time, '%Y-%m') = {0}", "2024-01");
    
    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testApplyWithDate() {
    String startDate = "2024-01-01";
    String endDate = "2024-12-31";
    
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.apply("create_time BETWEEN {0} AND {1}", startDate, endDate);
    
    List<User> users = userMapper.selectList(wrapper);
}

LAST last

在 SQL 末尾追加内容：

@Test
void testLast() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.last("LIMIT 10");
    
    List<User> users = userMapper.selectList(wrapper);
}

EXISTS exists

@Test
void testExists() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.exists("SELECT 1 FROM t_order WHERE t_order.user_id = t_user.id");
    
    List<User> users = userMapper.selectList(wrapper);
}

NOT EXISTS notExists

@Test
void testNotExists() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.notExists("SELECT 1 FROM t_order WHERE t_order.user_id = t_user.id");
    
    List<User> users = userMapper.selectList(wrapper);
}

UpdateWrapper

UpdateWrapper 用于构建更新条件。

基本更新

@Test
void testUpdateWrapper() {
    LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
    wrapper.eq(User::getName, "张三")
           .set(User::getAge, 26)
           .set(User::getEmail, "[email protected]");
    
    int result = userMapper.update(null, wrapper);
}

更新为 NULL

@Test
void testUpdateToNull() {
    LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
    wrapper.eq(User::getId, 1L)
           .set(User::getEmail, null);
    
    int result = userMapper.update(null, wrapper);
}

setSql 自定义 SQL

@Test
void testSetSql() {
    LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
    wrapper.eq(User::getId, 1L)
           .setSql("age = age + 1");
    
    int result = userMapper.update(null, wrapper);
}

条件判断

条件参数

所有方法都支持条件判断：

@Test
void testCondition() {
    String name = null;
    Integer minAge = 20;
    Integer maxAge = null;
    
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(name != null, User::getName, name)
           .ge(minAge != null, User::getAge, minAge)
           .le(maxAge != null, User::getAge, maxAge);
    
    List<User> users = userMapper.selectList(wrapper);
}

动态条件构建

public List<User> searchUsers(String name, Integer minAge, Integer maxAge, String email) {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    
    wrapper.like(StringUtils.isNotBlank(name), User::getName, name)
           .ge(minAge != null, User::getAge, minAge)
           .le(maxAge != null, User::getAge, maxAge)
           .like(StringUtils.isNotBlank(email), User::getEmail, email);
    
    return userMapper.selectList(wrapper);
}

查询字段选择

select 指定字段

@Test
void testSelectFields() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.select(User::getId, User::getName, User::getAge)
           .gt(User::getAge, 20);
    
    List<User> users = userMapper.selectList(wrapper);
}

排除字段

@Test
void testExcludeFields() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.select(User.class, info -> !info.getColumn().equals("password"));
    
    List<User> users = userMapper.selectList(wrapper);
}

实战示例

多条件组合查询

public IPage<User> searchUsers(UserQuery query, int pageNum, int pageSize) {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    
    wrapper.like(StringUtils.isNotBlank(query.getName()), User::getName, query.getName())
           .eq(query.getAge() != null, User::getAge, query.getAge())
           .between(query.getMinAge() != null && query.getMaxAge() != null,
                   User::getAge, query.getMinAge(), query.getMaxAge())
           .like(StringUtils.isNotBlank(query.getEmail()), User::getEmail, query.getEmail())
           .in(query.getStatusList() != null && !query.getStatusList().isEmpty(),
               User::getStatus, query.getStatusList())
           .orderByDesc(User::getCreateTime);
    
    Page<User> page = new Page<>(pageNum, pageSize);
    return userMapper.selectPage(page, wrapper);
}

复杂更新

public void updateUserStatus(Long userId, Integer newStatus, String operator) {
    LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();
    wrapper.eq(User::getId, userId)
           .set(User::getStatus, newStatus)
           .set(User::getUpdateTime, LocalDateTime.now())
           .set(User::getUpdateBy, operator)
           .setSql("version = version + 1");
    
    userMapper.update(null, wrapper);
}

统计查询

public Map<String, Object> getUserStatistics() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    wrapper.select("COUNT(*) as total",
                   "AVG(age) as avgAge",
                   "MAX(age) as maxAge",
                   "MIN(age) as minAge",
                   "status");
    wrapper.groupBy("status");
    
    List<Map<String, Object>> stats = userMapper.selectMaps(wrapper);
    
    Map<String, Object> result = new HashMap<>();
    result.put("statistics", stats);
    result.put("total", userMapper.selectCount(null));
    
    return result;
}

小结

本章我们学习了：

Wrapper 类型：QueryWrapper、LambdaQueryWrapper、UpdateWrapper、LambdaUpdateWrapper
比较操作：eq、ne、gt、ge、lt、le、between
模糊查询：like、likeLeft、likeRight、notLike
范围查询：in、notIn、isNull、isNotNull
分组排序：groupBy、orderByAsc、orderByDesc、having
逻辑连接：and、or、nested
高级用法：apply、last、exists、条件判断

参考资源

MyBatis Plus 条件构造器文档

Wrapper 家族​

推荐使用 Lambda​

比较操作​

等于 eq​

不等于 ne​

大于 gt、大于等于 ge​

小于 lt、小于等于 le​

BETWEEN between​

NOT BETWEEN notBetween​

模糊查询​

LIKE like​

NOT LIKE notLike​

LIKE LEFT likeLeft​

LIKE RIGHT likeRight​

范围查询​

IN in​

NOT IN notIn​

IS NULL isNull​

IS NOT NULL isNotNull​

分组与排序​

GROUP BY groupBy​

ORDER BY orderByAsc / orderByDesc​

HAVING having​

逻辑连接​

AND and​

OR or​

NESTED nested​

其他条件​

APPLY apply​

LAST last​

EXISTS exists​

NOT EXISTS notExists​

UpdateWrapper​

基本更新​

更新为 NULL​

setSql 自定义 SQL​

条件判断​

条件参数​

动态条件构建​

查询字段选择​

select 指定字段​

排除字段​

实战示例​

多条件组合查询​

复杂更新​

统计查询​

小结​

参考资源​

Wrapper 家族

推荐使用 Lambda

比较操作

等于 eq

不等于 ne

大于 gt、大于等于 ge

小于 lt、小于等于 le

BETWEEN between

NOT BETWEEN notBetween

模糊查询

LIKE like

NOT LIKE notLike

LIKE LEFT likeLeft

LIKE RIGHT likeRight

范围查询

IN in

NOT IN notIn

IS NULL isNull

IS NOT NULL isNotNull

分组与排序

GROUP BY groupBy

ORDER BY orderByAsc / orderByDesc

HAVING having

逻辑连接

AND and

OR or

NESTED nested

其他条件

APPLY apply

LAST last

EXISTS exists

NOT EXISTS notExists

UpdateWrapper

基本更新

更新为 NULL

setSql 自定义 SQL

条件判断

条件参数

动态条件构建

查询字段选择

select 指定字段

排除字段

实战示例

多条件组合查询

复杂更新

统计查询

小结

参考资源