四：MyBatis-Plus 条件构造器

1. 条件构造器

在写SQL where条件的时候我们经常使用 =、!=、>、>=、 <、<=、in、like、between and、not、is null 等来构造条件，在MyBatis-Plus中也可以通过方法来构造这些where条件，在MyBatis-Plus中使用以下类来构造where条件：

• QueryWrapper（LambdaQueryWrapper）
• UpdateWrapper（LambdaUpdateWrapper）

QueryWrapper<User> queryWrapper new QueryWrapper<>();
QueryWrapper<User> queryWrapper new QueryWrapper<>(entity);
QueryWrapper<User> query = Wrappers.query();

// lambda风格
LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda();

// 构造update语句where条件
UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
UpdateWrapper<User> update = Wrappers.update();
LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();

2. select

用于查询指定字段，可以使用谓词来包含或者排除某些字段。

// 查询指定列
select(String... sqlSelect)
// 通过谓词来过滤列(排除或者包含)
select(Predicate<TableFieldInfo> predicate)
select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)

// SELECT id,age,username FROM tbl_user order by id asc, age desc
LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
        .select(User::getId, User::getAge, User::getUsername);


// ELECT id,username,name,...,update_time FROM tbl_user
LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
        .select(User.class, i -> !i.getColumn().equals("status") && !i.getColumn().equals("version"));

3. 实体参数

@Test
void testMyBatisPlus() {
    // 将实体作为条件构造器函数，实体中所有值不为null的都将作为where条件，使用and连接每个条件
    // SELECT * FROM tbl_user WHERE username=? AND age=?
    User user = new User();
    user.setUsername("sixbrother");
    user.setAge(30);
    user.setName(null);
    QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
    userMapper.selectList(queryWrapper);
}

将实体作为条件构造器函数这种应用场景一般是是前端传的查询条件全部都是等值条件才可以使用，像开发中经常要传一个日期范围（开始时间、结束时间）这种就不适合了，所以为了扩展性不太建议使用这种方式。也可以通过@TableField注解中的condition来改变默认的等值关系运算符，默认提供了等于、不等于、like 这三种，也可以自己直接定义字符串。虽然可以通过定义关系运算符字符串来改变默认的等值条件，但这样一定义所有使用实体作为条件的都会随着改变，还是有很大的局限性。个人感觉这个功能很局限。

public class User implements Serializable {
    /**
    * 姓名
    */
    @TableField(condition = SqlCondition.LIKE)
    private String name;
}

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE name LIKE CONCAT('%',?,'%') AND status=?
    User user = new User();
    user.setName("六哥");
    user.setStatus(0);

    QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
    userMapper.selectList(queryWrapper);
}

4. eq 等于 =

eq(R column, Object val)
eq(boolean condition, R column, Object val)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (username = ?)
    LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
    queryWrapper.eq(User::getUsername, "sixbrother");
}

5. allEq：全部eq(或个别isNull)

allEq就是全部eq(或个别isNull)，也就使用value不为null的所有字段都使用=关系运算符，value为null翻译成SQL为 is null, 所有条件默认都是使用and逻辑运算符来连接。

allEq比将实体作为条件更加灵活，可以配置是否将null作为条件，可以自己写判断条件来决定是否加入到SQL中。

allEq(Map<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> params, boolean null2IsNull)


allEq(BiPredicate<R, V> filter, Map<R, V> params)
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)

参数说明：

• params : key为数据库字段名，value为字段值。
• null2IsNull : 为true则在map的value为null时调用 isNull 方法,为false时则忽略value为null的, 默认为true。
• condition：表示是否将整个条件加入到SQL中，如果条件为true就加入，相当于MyBatis中的if标签。
• filter：过滤器，用于过滤每个条件，满足条件才会加入到SQL中。

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (name IS NULL AND username = ?)
    Map<String, Object> paramMap = new HashMap<>();
    paramMap.put("username", "sixbrother");
    paramMap.put("name", null);
    
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.allEq(paramMap);
}


@Test
void testMyBatisPlus() {
    // null2IsNull = false, value = null 的字段不加入到SQL语句中
    // SELECT * FROM tbl_user WHERE (username = ?)
    Map<String, Object> paramMap = new HashMap<>();
    paramMap.put("username", "sixbrother");
    paramMap.put("name", null);
    
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.allEq(paramMap, false);
}


@Test
void testMyBatisPlus() {
    // condition = false 则整个条件都不加入到SQL中
    // SELECT * FROM tbl_user
    Map<String, Object> paramMap = new HashMap<>();
    paramMap.put("username", "sixbrother");
    paramMap.put("age", 30);
    paramMap.put("name", null);
    
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.allEq(false, paramMap, false);
}


@Test
void testMyBatisPlus() {
    // filter中只有username满足条件，age没有大于30，name为null了都不满足条件
    // SELECT * FROM tbl_user WHERE (username = ?)
    Map<String, Object> paramMap = new HashMap<>();
    paramMap.put("username", "sixbrother");
    paramMap.put("age", 30);
    paramMap.put("name", null);
    
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    queryWrapper.allEq(true
	    , (k, v) -> k.equals("username") || (v != null && Integer.parseInt(v.toString()) > 30) 
	    , paramMap
	    , true);
}

6. ne 不等于 <>

ne(R column, Object val)
ne(boolean condition, R column, Object val)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (username <> ?)
    LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.lambdaQuery();
    lambdaQueryWrapper.ne(User::getUsername, "xubaichuan");

    userMapper.selectList(lambdaQueryWrapper);
}

7. gt 大于 >

gt(R column, Object val)
gt(boolean condition, R column, Object val)

8. ge 大于等于 >=

ge(R column, Object val)
ge(boolean condition, R column, Object val)

9. lt 小于 <

lt(R column, Object val)
lt(boolean condition, R column, Object val)

10. le 小于等于 <=

le(R column, Object val)
le(boolean condition, R column, Object val)

11. between：BETWEEN 值1 AND 值2

between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)

12. notBetween NOT BETWEEN 值1 AND 值2

notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)

13. like：LIKE ‘%值%’

like(R column, Object val)
like(boolean condition, R column, Object val)

14. notLike：NOT LIKE ‘%值%’

notLike(R column, Object val)
notLike(boolean condition, R column, Object val)

15. likeLeft：LIKE ‘%值’

likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)

16. likeRight：LIKE ‘值%’

likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)

17. isNull：IS NULL

isNull(R column)
isNull(boolean condition, R column)

18. isNotNull：IS NOT NULL

isNotNull(R column)
isNotNull(boolean condition, R column)

19. in： IN (值1, 值2, …)

in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)

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

20. notIn：NOT IN (值1, 值2, …)

notIn(R column, Collection<?> value)
notIn(boolean condition, R column, Collection<?> value)

notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)

21. inSql：IN ( sql语句 )

// inValue是使用逗号分隔的字符串或者是一个子SQL语句
inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (age IN (30,31,32))
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
    	.inSql(User::getAge, "30,31,32");
    userMapper.selectList(queryWrapper);
}


@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (id IN (select id from tbl_user where id < 5))
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
    	.inSql(User::getId, "select id from tbl_user where id < 5");
    userMapper.selectList(queryWrapper);
}

22. notInSql：NOT IN ( sql语句 )

notInSql(R column, String inValue)
notInSql(boolean condition, R column, String inValue)

23. exists：EXISTS ( sql语句 )

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测 行 的存在。

exists(String existsSql)
exists(boolean condition, String existsSql)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (EXISTS (select id from tbl_user where id = 1))
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
            .exists("select id from tbl_user where id = 1");

    userMapper.selectList(queryWrapper);
}

24. notExists：NOT EXISTS ( sql语句 )

notExists(String notExistsSql)
notExists(boolean condition, String notExistsSql)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (NOT EXISTS (select id from tbl_user where id = 1))
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
            .notExists("select id from tbl_user where id = 1");

    userMapper.selectList(queryWrapper);
}

25. apply

apply用于构造带有函数的条件，如日期函数等。该方法可用于数据库函数 动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!

apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (date_format(create_time, '%Y-%m-%d') = ?)
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
          .apply("date_format(create_time, '%Y-%m-%d') = {0}", "2020-10-07");

    userMapper.selectList(queryWrapper);
}

26. func

func 方法(主要方便在出现if…else下调用不同方法能不断链)，相当于MyBatis中的when … otherwise

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (id <> ?)
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
           .func(i -> {
               if (false) {
                   i.eq(User::getId, 1);
               } else {
                   i.ne(User::getId, 1);
               }
           });

    userMapper.selectList(queryWrapper);
}

27. and

and用于将多个条件括起来。

and(Consumer<Param> consumer)
and(boolean condition, Consumer<Param> consumer)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (username = ? AND (id >= ? AND age < ?))
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
            .eq(User::getUsername, "sixbrother")
            .and(i -> i.ge(User::getId, 2).lt(User::getAge, 30));

    userMapper.selectList(queryWrapper);
}

28. or

主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)
注意：在SQL中or的优先级是低于and的优先级，所以在有and和or的时候注意使用小括号来表示一个整体条件。

or()
or(boolean condition)

// 用于将多个条件括起来
or(Consumer<Param> consumer)
or(boolean condition, Consumer<Param> consumer)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (username = ? OR age > ?)
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
            .eq(User::getUsername, "sixbrother")
            .or().gt(User::getAge, 30);

    userMapper.selectList(queryWrapper);
}


@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE (username = ? OR (age >= ? AND id < ?))
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
            .eq(User::getUsername, "sixbrother")
            .or(i -> i.ge(User::getAge, 30).lt(User::getId, 5));

    userMapper.selectList(queryWrapper);
}

29. nested

正常嵌套 不带 AND 或者 OR，一般直接作为where的第一个条件，此时不需要and或者or来连接条件。一般用于将第一个条件作为一个整体条件用小括号括住。

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user WHERE ((name LIKE ? OR gender = ?))
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
            .nested(i -> i.like(User::getName, "六哥").or().eq(User::getGender, 1));

    userMapper.selectList(queryWrapper);
}

30. last

无视优化规则直接拼接到 sql 的最后，只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用，如果参数值是前台传过来的谨慎使用。

last(String lastSql)
last(boolean condition, String lastSql)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user limit 1
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
           .last("limit 1");

    userMapper.selectList(queryWrapper);
}

31. orderByAsc：ORDER BY 字段, … ASC

所有排序的字段都采用升续排序。

orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user ORDER BY id ASC,age ASC
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
           .orderByAsc(User::getId, User::getAge);

    userMapper.selectList(queryWrapper);
}

32. orderByDesc：ORDER BY 字段, … DESC

orderByDesc(R... columns)
orderByDesc(boolean condition, R... columns)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user ORDER BY id DESC,age DESC
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
           .orderByDesc(User::getId, User::getAge);

    userMapper.selectList(queryWrapper);
}

// 同时使用orderByAsc和orderByDesc
@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user order by id asc, age desc
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
           .orderByAsc(User::getId).orderByDesc(User::getAge);

    userMapper.selectList(queryWrapper);
}

33. orderBy

orderBy可以动态的统一设置升续或者降续。

orderBy(boolean condition, boolean isAsc, R... columns)

@Test
void testMyBatisPlus() {
    // SELECT * FROM tbl_user ORDER BY id ASC,age ASC
    LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery()
           .orderBy(true, true, User::getId, User::getAge);

    userMapper.selectList(queryWrapper);
}

34. groupBy

groupBy(R... columns)
groupBy(boolean condition, R... columns)

@Test
void testMyBatisPlus() {
    // SELECT gender,count(*) as count FROM tbl_user GROUP BY gender
    QueryWrapper<User> queryWrapper = Wrappers.<User>query()
            .select("gender", "count(*) as count").groupBy("gender");

    userMapper.selectList(queryWrapper);
}

35. having

having(String sqlHaving, Object... params)
having(boolean condition, String sqlHaving, Object... params)

@Test
void testMyBatisPlus() {
    // SELECT gender,count(*) as count FROM tbl_user GROUP BY gender HAVING count > 3
    QueryWrapper<User> queryWrapper = Wrappers.<User>query()
            .select("gender", "count(*) as count")
            .groupBy("gender")
            .having("count > 3");

    userMapper.selectObjs(queryWrapper);
}

36. 实体参数与条件构造器互不影响

@Test
void testMyBatisPlus() {
    // 注意：实体中的条件和Wrapper中的条件相互独立，互不影响，所以才会出现两个username条件
    // SELECT * FROM tbl_user WHERE username=? AND age=? AND (username = ?)
    User user = new User();
    user.setUsername("sixbrother");
    user.setAge(30);
    user.setName(null);
    QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
    queryWrapper.eq("username", "gongshu");
    userMapper.selectList(queryWrapper);
}