Mybatis-Plus 条件构造器

目录

1. MyBatis-Plus 条件构造器

1.1 数据导入

1.2 构造器简介

1.3 构造器使用（1） 

1.3.1 带条件的查询

1.3.2 带条件的修改

1.3.3 带条件的删除

1.4 构造器使用（2）

1.4.1 allEq 

1.4.2 eq

1.4.3 ne 

1.4.4 gt 

1.4.5 ge 

1.4.6 lt 

1.4.7 le 

1.4.8 between 

1.4.9 notBetween

1.4.10 like

1.4.11 notLike

1.4.12 likeLeft

1.4.13 likeRight 

1.4.14 isNull

1.4.15 isNotNull

1.4.16 in 

1.4.17  notIn

1.4.18 inSql

1.4.19 notInSql

1.4.20 groupBy

1.4.21 orderByAsc

1.4.22 orderByDesc

1.4.23 orderBy

1.4.24 having

1.4.25 func

1.4.26 or 

1.4.27 and 

1.4.28 nested

1.4.29 apply

1.4.30 last

1.4.31 exists

1.4.32 notExists

---

前言

  Mybatis-Plus是基于sping-boot项目 如果还没有配置的先去看下面这篇博客 

  Mybatis-Plus入门 

1. MyBatis-Plus 条件构造器

  1.1 数据导入

## 使用库
USE mp;
## 清空表
TRUNCATE TABLE tbl_employee;	
## 导入数据
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Allan0','123@qq.com',0,21);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Allan1','123@qq.com',0,22);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Allan2','123@qq.com',0,23);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Allan3','123@qq.com',0,24);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Allan4','123@qq.com',0,25);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Allan5','123@qq.com',0,26);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Allan6','123@qq.com',0,27);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Allan7','123@qq.com',0,28);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Allan8','123@qq.com',0,29);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Allan9','123@qq.com',0,30);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Baby0','123@qq.com',1,21);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Baby1','123@qq.com',0,22);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Baby2','123@qq.com',1,23);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Baby3','123@qq.com',0,24);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Baby4','123@qq.com',1,25);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Baby5','123@qq.com',0,26);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Baby6','123@qq.com',1,27);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Baby7','123@qq.com',0,28);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Baby8','123@qq.com',1,29);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Baby9','123@qq.com',0,30);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Tom0','123@qq.com',1,21);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Tom1','123@qq.com',0,22);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Tom2','123@qq.com',1,23);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Tom3','123@qq.com',0,24);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Tom4','123@qq.com',1,25);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Tom5','123@qq.com',0,26);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Tom6','123@qq.com',1,27);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Tom7','123@qq.com',0,28);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Tom8','123@qq.com',1,29);
INSERT INTO tbl_employee(last_name,email,gender,age) VALUES('Tom9','123@qq.com',0,30);
## 查询数据
SELECT * FROM tbl_employee;

1.2 构造器简介

MyBatis-Plus 通过 EntityWrapper（简称 EW，MP 封装的一个查询条件构造器）或者 Condition（与 EW 类似） 来让用户自由的构建查询条件，简单便捷，没有额外的负担， 能够有效提高开发效率，它主要用于处理 sql 拼接，排序，实体参数查询等。 

注意：使用的是数据库字段，不是 Java 属性！ 

警告：MyBatis-Plus不支持以及不赞成在 RPC 调用中把 Wrapper 进行传输，Wrapper 很重，传输 Wrapper 可以类比为你的 controller 用 map 接收值(开发一时爽，维护火葬场)，正确的 RPC 调用姿势是写一个 DTO 进行传输，被调用方再根据 DTO 执行相应的操作。

1.3 构造器使用（1） 

1.3.1 带条件的查询

需求描述：查询所有姓名的包含B、且姓名为女（1）、且年龄大于24岁的员工信息 

@Test
void testSelectList1() {
    QueryWrapper queryWrapper = new QueryWrapper<>();
    queryWrapper
            .like("last_name","B")
            .eq("gender",1)
            .gt("age",24);
    List employees = employeeMapper.selectList(queryWrapper);
    employees.forEach(System.out::println);
}

 需求描述：查询所有员工信息

@Test
void testSelectList2() {
    List employees = employeeMapper.selectList(null);
    employees.forEach(System.out::println);
}

 需求描述：查询所有女生的数量（1）

@Test
void testSelectList3() {
    QueryWrapper queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("gender", 1);
    Integer count = employeeMapper.selectCount(queryWrapper);
    System.out.println(count);
}

1.3.2 带条件的修改

需求信息：将年龄大于25岁的女生（1）的性别修改为男生（0） 

@Test
void testUpdate() {
    UpdateWrapper updateWrapper = new UpdateWrapper<>();
    updateWrapper
        .eq("gender", 1)
        .gt("age", 25)
        ;
    Employee employee = new Employee();
    employee.setGender(0);
    employeeMapper.update(employee, updateWrapper);
}

 1.3.3 带条件的删除

@Test
void testDelete() {
    QueryWrapper queryWrapper = new QueryWrapper<>();
    queryWrapper.like("last_name", "Tom");
    int result = employeeMapper.delete(queryWrapper);
    System.out.println(result);
}

1.4 构造器使用（2）

参数说明： 

• 以下出现的第一个入参boolean condition表示该条件是否加入最后生成的sql中
• 以下代码块内的多个方法均为从上往下补全个别boolean类型的入参，默认为true
• 以下出现的泛型Param均为Wrapper的子类实例(均具有AbstractWrapper的所有方法)
• 以下方法在入参中出现的为R泛型，在普通wrapper中是String，在LambdaWrapper中是函数(例：Entity::getId,Entity为实体类，getId为字段id的getMethod)
• 以下方法入参中的R column均表示数据库字段，当R具体类型为String时则为数据库字段名(字段名是数据库关键字的自己用转义符包裹)！而不是实体类数据字段名，另当R具体类型为SFunction时项目runtime不支持eclipse自家的编译器
• 以下举例均为使用普通wrapper，入参为Map和List的均以json形式表现
• 使用中如果入参的Map或者List为空，则不会加入最后生成的sql中

 AbstractWrapper：

说明：AbstractWrapper 是 QueryWrapper(LambdaQueryWrapper) 和 UpdateWrapper(LambdaUpdateWrapper) 的父类用于生成 sql 的 where 条件，entity 属性也用于生成 sql 的 where 条件，注意 entity 生成的 where 条件与使用各个 api 生成的 where 条件没有任何关联行为

1.4.1 allEq 

allEq(Map params)
allEq(Map params, boolean null2IsNull)
allEq(boolean condition, Map params, boolean null2IsNull)

• 全部 eq (或个别 isNull）

 个别参数说明：

params：key为数据库字段名，value为字段值
null2IsNull：为true则在map的value为null时调用 isNull方法为false时则忽略value为null的

•  例1: allEq({id:1,name:"老王",age:null})—>id = 1 and name = '老王' and age is null
•  例2: allEq({id:1,name:"老王",age:null}, false)—>id = 1 and name = '老王

allEq(BiPredicate filter, Map params)
allEq(BiPredicate filter, Map params, boolean null2IsNull)
allEq(boolean condition, BiPredicate filter, Map params, boolean null2IsNull) 

个别参数说明：

filter : 过滤函数，是否允许字段传入比对条件中
params 与 null2IsNull : 同上 

• 例1: allEq((k,v) -> k.indexOf("a") >= 0, {id:1,name:"老王",age:null})—>name = '老王' and age is null
• 例2: allEq((k,v) -> k.indexOf("a") >= 0, {id:1,name:"老王",age:null}, false)—>name = '老王'

 1.4.2 eq

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

• 等于 =
• 例: eq("name", "老王")—>name = '老王'

1.4.3 ne 

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

• 不等于 <>
• 例: ne("name", "老王")—>name <> '老王'

1.4.4 gt 

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

• 大于 >
• 例: gt("age", 18)—>age > 18

1.4.5 ge 

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

• 大于等于 >=
• 例: ge("age", 18)—>age >= 18

1.4.6 lt 

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

• 小于 <
• 例: lt("age", 18)—>age < 18

1.4.7 le 

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

• 小于等于 <=
• 例: le("age", 18)—>age <= 18

1.4.8 between 

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

• BETWEEN 值1 AND 值2
• 例: between("age", 18, 30)—>age between 18 and 30

1.4.9 notBetween

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

• NOT BETWEEN 值1 AND 值2
• 例: notBetween("age", 18, 30)—>age not between 18 and 30

1.4.10 like

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

• LIKE ‘%值%’
• 例: like("name", "王")—>name like '%王%'

1.4.11 notLike

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

• NOT LIKE ‘%值%’
• 例: notLike("name", "王")—>name not like '%王%'

1.4.12 likeLeft

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

• LIKE ‘%值’
• 例: likeLeft("name", "王")—>name like '%王'

1.4.13 likeRight 

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

• LIKE ‘值%’
• 例: likeRight("name", "王")—>name like '王%'

1.4.14 isNull

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

• 字段 IS NULL
• 例: isNull("name")—>name is null

1.4.15 isNotNull

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

• 字段 IS NOT NULL
• 例: isNotNull("name")—>name is not null

1.4.16 in 

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

• 字段 IN (value.get(0), value.get(1), …)
• 例: in("age",{1,2,3})—>age in (1,2,3)

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

• 字段 IN (v0, v1, …)
• 例: in("age", 1, 2, 3)—>age in (1,2,3)

1.4.17  notIn

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

• 字段 NOT IN (value.get(0), value.get(1), …)
• 例: notIn("age",{1,2,3})—>age not in (1,2,3)

1.4.18 inSql

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

• 字段 IN ( sql语句 )
• 例: inSql("age", "1,2,3,4,5,6")—>age in (1,2,3,4,5,6)
• 例: inSql("id", "select id from table where id < 3")—>id in (select id from table where id < 3)

1.4.19 notInSql

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

• 字段 NOT IN ( sql语句 )
• 例: notInSql("age", "1,2,3,4,5,6")—>age not in (1,2,3,4,5,6)
• 例: notInSql("id", "select id from table where id < 3")—>id not in (select id from table where id < 3)

1.4.20 groupBy

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

• 分组：GROUP BY 字段, …
• 例: groupBy("id", "name")—>group by id,name

1.4.21 orderByAsc

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

• 排序：ORDER BY 字段, … ASC
• 例: orderByAsc("id", "name")—>order by id ASC,name ASC

1.4.22 orderByDesc

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

• 排序：ORDER BY 字段, … DESC
• 例: orderByDesc("id", "name")—>order by id DESC,name DESC

1.4.23 orderBy

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

• 排序：ORDER BY 字段, …
• 例: orderBy(true, true, "id", "name")—>order by id ASC,name ASC

1.4.24 having

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

• HAVING ( sql语句 )
• 例: having("sum(age) > 10")—>having sum(age) > 10
• 例: having("sum(age) > {0}", 11)—>having sum(age) > 11

1.4.25 func

func(Consumer<Children> consumer)
func(boolean condition, Consumer<Children> consumer)

• func 方法(主要方便在出现if…else下调用不同方法能不断链)
• 例: func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})

1.4.26 or 

or()
or(boolean condition)

• 拼接 OR

注意事项:

主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)

• 例: eq("id",1).or().eq("name","老王")—>id = 1 or name = '老王'

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

• OR 嵌套
• 例: or(i -> i.eq("name", "李白").ne("status", "活着"))—>or (name = '李白' and status <> '活着')

1.4.27 and 

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

• AND 嵌套
• 例: and(i -> i.eq("name", "李白").ne("status", "活着"))—>and (name = '李白' and status <> '活着')

1.4.28 nested

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

• 正常嵌套 不带 AND 或者 OR
• 例: nested(i -> i.eq("name", "李白").ne("status", "活着"))—>(name = '李白' and status <> '活着')

1.4.29 apply

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

• 拼接 sql

注意事项:

该方法可用于数据库函数动态入参的params对应前面applySql内部的{index}部分，这样是不会有sql注入风险的，反之会有!

• 例: apply("id = 1")—>id = 1
• 例: apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")—>date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
• 例: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")—>date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")

1.4.30 last

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

• 无视优化规则直接拼接到 sql 的最后

注意事项:

只能调用一次，多次调用以最后一次为准，有sql注入的风险，请谨慎使用

• 例: last("limit 1")

1.4.31 exists

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

• 拼接 EXISTS ( sql语句 )
• 例: exists("select id from table where age = 1")—>exists (select id from table where age = 1)

1.4.32 notExists

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

• 拼接 NOT EXISTS ( sql语句 )
• 例: notExists("select id from table where age = 1")—>not exists (select id from table where age = 1)

  ---