MybatisPlus系列(三):条件构造器使用详解

上篇文章我们聊过，MyBatis Plus 封装了一套非常方便的增删改查操作。比如下面这段代码，一个是根据 ID 修改记录，另一个是根据用户名查询：

/**
 * 根据id修改
 * UPDATE user SET user_name=?, user_age=? WHERE (id = ?)
 */
@Test
public void testudpateById(){
    User user = new User();
    user.setUserAge("25");
    user.setUserName("test update");
    UpdateWrapper updateWrapper = new UpdateWrapper();
    updateWrapper.eq("id","3");
    int num = userMapper.update(user, updateWrapper);
    System.out.println("修改的记录数为："+num);
}
/**
 * 查询指定记录
 * SELECT id,user_name,user_age FROM user WHERE (user_name = ?)
 */
@Test
public void testSelectWrapper(){
    QueryWrapper wrapper = new QueryWrapper();
    wrapper.eq("user_name","IT可乐");
    List users = userMapper.selectList(wrapper);
    users.forEach(x-> System.out.println(x.getId()+"-"+x.getUserName()+"-"+x.getUserAge()));
}

你会发现，条件部分用到了 UpdateWrapper 和 QueryWrapper。那么，这个 Wrapper 到底是什么？其实，MyBatis Plus 的条件构造器，就是用来帮你拼出复杂 SQL 的利器。本篇我们就把它彻底讲清楚。

1、Wrapper

MyBatis Plus 提供了好几种条件构造器，它们的关系如下图所示：

日常开发中，我们主要用 QueryWrapper 和 UpdateWrapper 来构造条件。这两个和 LambdaQueryWrapper、LambdaUpdateWrapper 本质上是等价的，只不过后者用了 JDK 1.8 的 Lambda 语法，写起来更简洁、更安全。

2、语法详情总结

官方文档里其实已经列得很全了，这里把最常用的方法整理成一张速查表，方便你随时查阅：

3、语法详情演示

光看表不过瘾，下面直接把每个方法都跑一遍代码，你跟着敲一遍，基本就闭着眼也能写了。

package com.ys.mybatisplusstudy;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ys.mybatisplusstudy.entry.User;
import com.ys.mybatisplusstudy.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@SpringBootTest
public class WrapperTest {
    @Autowired
    private UserMapper userMapper;
    /**
     * 新增一条记录
     */
    @Test
    public void testInsert(){
        User user = new User();
        user.setId(4L);
        user.setUserName("test insert");
        user.setUserAge("1");
        int insert = userMapper.insert(user);
        System.out.println("影响记录数："+insert);
    }
    /**
     * allEq 全部等于
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name = ? AND id = ?)
     */
    @Test
    public void testAllEq(){
        QueryWrapper queryWrapper = new QueryWrapper();
        Map map = new HashMap<>();
        map.put("id","3");
        map.put("user_name","IT可乐");
        queryWrapper.allEq(map);
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * eq 等于
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (id = ?)
     */
    @Test
    public void testEq(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("id","3");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * ne 不等于
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (id <> ?)
     */
    @Test
    public void testNe(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.ne("id","3");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * gt 大于
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age > ?)
     */
    @Test
    public void testGt(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.gt("user_age","18");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * ge 大于等于
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age >= ?)
     */
    @Test
    public void testGe(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.ge("user_age","18");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * lt 小于
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age < ?)
     */
    @Test
    public void testLt(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.lt("user_age","18");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * le 小于等于
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age <= ?)
     */
    @Test
    public void testLe(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.le("user_age","18");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * between 值1和值2之间,两边临界值都包含
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age BETWEEN ? AND ?)
     */
    @Test
    public void testBetween(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.between("user_age","18","25");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * notBetween 不在值1和值2之间，两边临界值都包含
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age NOT BETWEEN ? AND ?)
     */
    @Test
    public void testNoBetween(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.notBetween("user_age","18","25");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * like 模糊查询，会在参数左右两边加上 %
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name LIKE ?)
     */
    @Test
    public void testLike(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.like("user_name","可乐");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * notLike NOT LIKE ‘%parameter%'
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name NOT LIKE ?)
     */
    @Test
    public void testNotLike(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.notLike("user_name","可乐");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * likeLeft LIKE ‘%parameter'
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name LIKE '%parameter')
     */
    @Test
    public void testLikeLeft(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.likeLeft("user_name","可乐");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * likeRight LIKE ‘parameter%'
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name LIKE 'parameter%')
     */
    @Test
    public void testLikeRight(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.likeRight("user_name","可乐");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * isNull 判断字段为null
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name IS NULL)
     */
    @Test
    public void testIsNull(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.isNull("user_name");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * isNotNull 判断字段不为null
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_name IS NOT NULL)
     */
    @Test
    public void testIsNotNull(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.isNotNull("user_name");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * in 范围定值查询
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age IN (?,?,?))
     */
    @Test
    public void testIn(){
        QueryWrapper queryWrapper = new QueryWrapper();
        List queryList = new ArrayList<>();
        queryList.add(18);
        queryList.add(1);
        queryList.add(25);
        queryWrapper.in("user_age",queryList);
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * notIn
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age IN (?,?,?))
     */
    @Test
    public void testNotIn(){
        QueryWrapper queryWrapper = new QueryWrapper();
        List queryList = new ArrayList<>();
        queryList.add(18);
        queryList.add(1);
        queryList.add(25);
        queryWrapper.notIn("user_age",queryList);
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * inSql
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (id IN (select id from user))
     */
    @Test
    public void testInSql(){
        QueryWrapper queryWrapper = new QueryWrapper();
        //查询所有数据
        queryWrapper.inSql("id","select id from user");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * notInSql
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (id NOT IN (select id from user where id > 2))
     */
    @Test
    public void testNotInSql(){
        QueryWrapper queryWrapper = new QueryWrapper();
        //查询所有数据
        queryWrapper.notInSql("id","select id from user where id > 2");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * groupBy 分组
     * 下面SQL有个问题，在MySQL8.0版本中，是可以执行下面SQL语句的，select user_name并没有出现在group by 语句中
     * 实例SQL：SELECT id,user_name,user_age FROM user GROUP BY id,user_age
     */
    @Test
    public void testGroupBy(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.groupBy("id","user_age");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * orderByAsc 升序
     * 实例SQL：SELECT id,user_name,user_age FROM user ORDER BY id ASC,user_age ASC
     */
    @Test
    public void testOrderByAsc(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.orderByAsc("id","user_age");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * orderByDesc 降序
     * 实例SQL：SELECT id,user_name,user_age FROM user ORDER BY id DESC,user_age DESC
     */
    @Test
    public void testOrderByDesc(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.orderByDesc("id","user_age");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * orderBy 指定顺序排序
     * 实例SQL：SELECT id,user_name,user_age FROM user ORDER BY id ASC,user_age ASC
     */
    @Test
    public void testOrderBy(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.orderBy(true,true,"id","user_age");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * having
     * 实例SQL：SELECT id,user_name,user_age FROM user GROUP BY id,user_age HAVING sum(user_age)>?
     */
    @Test
    public void testHaving(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.groupBy("id","user_age");
        queryWrapper.having("sum(user_age)>{0}","25");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * having
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (id = ? OR user_age = ?)
     */
    @Test
    public void testOr(){
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("id",1);
        queryWrapper.or();
        queryWrapper.eq("user_age",25);
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * and
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE ((id = ? AND user_age <> ?))
     */
    @Test
    public void testAnd(){
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.and(i->i.eq("id",1).ne("user_age",18));
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * nested
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE ((id = ? AND user_age <> ?))
     */
    @Test
    public void testNested(){
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.nested(i->i.eq("id",1).ne("user_age",18));
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * apply
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (user_age>?)
     */
    @Test
    public void testApplyd(){
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.apply("user_age>{0}","25 or 1=1");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * last
     * 实例SQL：SELECT id,user_name,user_age FROM user limit 1
     */
    @Test
    public void testLast(){
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.last("limit 1 ");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * exists
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (EXISTS (select id from user where user_age = 1))
     */
    @Test
    public void testExists(){
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.exists("select id from user where user_age = 1");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
    /**
     * notExists
     * 实例SQL：SELECT id,user_name,user_age FROM user WHERE (EXISTS (select id from user where user_age = 1))
     */
    @Test
    public void testNotExists(){
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.notExists("select id from user where user_age = 1");
        List list = userMapper.selectList(queryWrapper);
        System.out.println(list);
    }
}

4、LambdaQueryWrapper和LambdaUpdateWrapper（推荐）

上面讲的 QueryWrapper 和 UpdateWrapper 写条件时，字段名是字符串，比如 eq("id", 1)。问题来了：字符串拼写错了怎么办？编译期不会报错，运行时才炸。所以，更推荐用 Lambda 版本——LambdaQueryWrapper 和 LambdaUpdateWrapper。

获取方式有两种：

//两种方式        
LambdaQueryWrapper queryLambda = new QueryWrapper().lambda();
LambdaQueryWrapper lambdaQueryWrapper = new LambdaQueryWrapper<>();
//两种方式
LambdaUpdateWrapper updateLambda = new UpdateWrapper().lambda();
LambdaUpdateWrapper lambdaUpdateWrapper = new LambdaUpdateWrapper();

注意，使用 Lambda 语法时，要带上泛型，比如 LambdaQueryWrapper，这样 User::getId 才能生效。

来看两个实际例子：

/**
 * LambdaQueryWrapper
 * SQL实例：SELECT id,user_name,user_age FROM user WHERE (id = ? AND user_age <> ?)
 */
@Test
public void testLambdaQueryWrapper(){
    LambdaQueryWrapper queryLambda = new LambdaQueryWrapper<>();
    queryLambda.eq(User::getId,"1").ne(User::getUserAge,25);
    List users = userMapper.selectList(queryLambda);
    System.out.println(users);
}
/**
 * LambdaQueryWrapper
 * SQL实例：UPDATE user SET user_name=? WHERE (user_name = ?)
 */
@Test
public void testLambdaUpdateWrapper(){
    User user = new User();
    user.setUserName("LambdaUpdateWrapper");
    LambdaUpdateWrapper userLambdaUpdateWrapper = new LambdaUpdateWrapper<>();
    userLambdaUpdateWrapper.eq(User::getUserName,"IT可乐");
    userMapper.update(user,userLambdaUpdateWrapper);
}

5、总结

MyBatis Plus 的四种条件构造器，到这里就全部梳理完了。建议跟着上面的代码敲一遍，手熟了自然就记住了。

最后多说一句，Lambda 语法写起来确实爽，不光简洁，还能避免硬编码字符串带来的拼写错误。用 User::getId 代替 "id"，编译器就能帮你检查，再也不用担心手抖写错字段名了。所以，平时开发尽量用 Lambda 版本。

讲到这里，MyBatis Plus 的常规用法基本就告一段落了。不过好戏还在后头，后续会继续分享一些高阶玩法，让开发体验更丝滑，敬请期待。