5 Mybatis-Plus条件构造器

在MP中，Wrapper接口的实现类关系如下：

在MP查询中，还可以使用lambda方式查询，降低数据库列名写错的风险。

5.1 基本比较操作

eq

等于 =

ne

不等于 <>

gt

大于 >

ge

大于等于 >=

lt

小于 <

le

小于等于 <=

between

BETWEEN 值1 AND 值2

notBetween

NOT BETWEEN 值1 AND 值2

in

字段 IN (value.get(0), value.get(1), ...)

notIn

字段 NOT IN (v0, v1, ...)

测试用例：

@Test
public void testEq() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();

    //SELECT id,user_name,password,name,age,email FROM tb_user 
    //WHERE password = ? AND age >=? AND name IN (?,?,?)
    wrapper.eq("password", "123456")
        .ge("age", 20)
        .in("name", "李四", "王五", "赵六");

    List<User> users = this.userMapper.selectList(wrapper);
    for (User user : users) {
        System.out.println(user);
    }
}

Lambda方式构造条件：

LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();

//SELECT id,user_name,password,name,age,email FROM tb_user 
//WHERE password = ? AND age >= ? AND name IN (?,?,?)
wrapper.eq(User::getPassword, "123456")
    .ge(User::getAge, 20)
    .in(User::getName, "李四", "王五", "赵六");

List<User> users = this.userMapper.selectList(wrapper); 
for (User  user : users) {
    System.out.println(user);
}

通常在开发中要根据表达式进行判断，表达式为true则拼接条件，如下：

eq(boolean condition, R column, Object val) 
in(boolean   condition,   R   column,   Object...   values)
...

一个例子：

//比如根据name来判断，如果name不为空则拼接条件String name = null; 
wrapper.eq(User::getPassword, "123456")
    .ge(User::getAge, 20)
    .in(name!=null,User::getName, "李四", "王五", "赵六");

5.2 模糊查询

测试用例:

@Test
public void testWrapper() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //SELECT id,user_name,password,name,age,email FROM tb_user WHERE name LIKE ?
    //Parameters: % 五 %(String)
    wrapper.like("name",   "五");
    List<User> users = this.userMapper.selectList(wrapper);
    for (User user : users)   {
        System.out.println(user);
    }
}

Lambda方式构造条件：

LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>(); 
wrapper.like(User::getName,   "五");

5.3 逻辑查询

测试用例：

@Test
public void testWrapper2() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //SELECT id,name,age FROM tb_user WHERE name = ? OR age = ?
    wrapper.eq("name", "李四")
        .or()
        .eq("age", 24)
        .select("id", "name", "age");
    List<User> users = this.userMapper.selectList(wrapper);
    for (User user : users) {
        System.out.println(user);
    }
}

Lambda方式构造条件：

LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getName, "李四")
.or()
.eq(User::getAge, 24);

5.4 select

在MP查询中，默认查询所有的字段，如果有需要也可以通过select方法进行指定字段。

@Test
public void testWrapper() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //SELECT id,name,age FROM tb_user WHERE name = ? OR age = ?
    wrapper.eq("name", "李四")
        .or()
        .eq("age", 24)
        .select("id", "name", "age");
    List<User> users = this.userMapper.selectList(wrapper);
    for (User user : users) {
        System.out.println(user);
    }
}

Lambda方式构造条件：

LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(User::getName, "李四")
    .or()
    .eq(User::getAge, 24)
    .select(User::getId, User::getName, User::getAge);

5.5 排序

orderBy(boolean condition, boolean isAsc, R... columns)
- 自定义排序规则
- 参数1：true有效，false无效 ，参数2：是否升序，参数3..设置多个字段
- 例: `orderBy(true, true, "id", "name")`--->`order by id ASC,name ASC`
- 也可以多个orderBy拼装，如下：
orderBy(true, true, "id").orderBy(true, true, "name") 效果同上
@Test
public void testWrapper() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();
    //SELECT id,user_name,password,name,age,email FROM tb_user ORDER BY age DESC
    wrapper.orderByDesc("age");
    List<User> users = this.userMapper.selectList(wrapper);
    for (User user : users) {
        System.out.println(user);
    }
}

Lambda方式构造条件：

LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
wrapper.orderByDesc(User::getAge);