Mybatis-Plus 条件构造器

## 使用库
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<Employee> queryWrapper = new QueryWrapper<>();
    queryWrapper
            .like("last_name","B")
            .eq("gender",1)
            .gt("age",24);
    List<Employee> employees = employeeMapper.selectList(queryWrapper);
    employees.forEach(System.out::println);
}

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

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

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

@Test
void testSelectList3() {
    QueryWrapper<Employee> 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<Employee> 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<Employee> 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<R, V> params)
allEq(Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, Map<R, V> 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<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)

个别参数说明：

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)

JoneClassMate

关注

3
点赞
踩
16

收藏

觉得还不错? 一键收藏
0
评论
Mybatis-Plus 条件构造器

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

扫一扫