MybatisPlus条件构造器

第四章 条件构造器

1. 说明

• MybatisPlus通过Wrapper（条件构造器）或者来让用户*的构建查询条件，简单便捷，没有额外的负担，能够有效提高开发效率
• 如果第一个参数为：boolean condition表示该条件是否加入最后生成的sql中
• 以下出现的泛型Param均为Wrapper的子类实例(均具有AbstractWrapper的所有方法)
• 方法参数中出现的R为泛型，在普通wrapper中是String
• 方法参数中的R column均表示数据库字段，当R为String时则为数据库字段名称(字段名是数据库关键字的自己用转义符包裹!)，而不是实体类的属性名称

2. AbstractWrapper

QueryWrapper和UpdateWrapper的父类，用于生成sql的where条件

2.1 allEq

全部eq(或个别isNull)

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

例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)

例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 = ‘张三’

个别参数说明:

• params : key为数据库字段名,value为字段值
• null2IsNull : 为true则在map的value为null时调用 isNull 方法,为false时则忽略value为null的
• filter : 过滤函数,是否允许字段传入比对条件中

2.2 eq

等于 =

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

例: eq(“name”, “张三”)—>name = ‘张三’

2.3 ne

不等于 <>

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

例: ne(“name”, “张三”)—>name <> ‘张三’

2.4 gt

大于 >

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

例: gt(“age”, 18)—>age > 18

2.5 ge

大于等于 >=

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

例: ge(“age”, 18)—>age >= 18

2.6 lt

小于 <

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

例: lt(“age”, 18)—>age < 18

2.7 le

小于等于 <=

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

例: le(“age”, 18)—>age <= 18

2.8 between

BETWEEN 值1 AND 值2

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

例: between(“age”, 18, 30)—>age between 18 and 30

2.9 notBetween

NOT BETWEEN 值1 AND 值2

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

例: notBetween(“age”, 18, 30)—>age not between 18 and 30

2.10 like

LIKE ‘%值%’

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

例: like(“name”, “王”)—>name like ‘%王%’

2.11 notLike

NOT LIKE ‘%值%’

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

例: notLike(“name”, “王”)—>name not like ‘%王%’

2.12 likeLeft

LIKE ‘%值’

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

例: likeLeft(“name”, “王”)—>name like ‘%王’

2.13 likeRight

LIKE ‘值%’

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

例: likeRight(“name”, “王”)—>name like ‘王%’

2.14 isNull

字段 IS NULL

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

例: isNull(“name”)—>name is null

2.15 isNotNull

字段 IS NOT NULL

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

例: isNotNull(“name”)—>name is not null

2.16 in

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

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

例: in(“age”,{1,2,3})—>age in (1,2,3)

字段 IN (v0, v1, …)

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

例: in(“age”, 1, 2, 3)—>age in (1,2,3)

2.17 notIn

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

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

例: notIn(“age”,{1,2,3})—>age not in (1,2,3)

字段 NOT IN (v0, v1, …)

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

例: notIn(“age”, 1, 2, 3)—>age not in (1,2,3)

2.18 inSql

字段 IN ( sql语句 )

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

例: 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)

2.19 notInSql

字段 NOT IN ( sql语句 )

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

例: 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”)—>age not in (select id from table where id < 3)

2.20 groupBy

分组：GROUP BY 字段, …

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

例: groupBy(“id”, “name”)—>group by id,name

2.21 orderByAsc

排序：ORDER BY 字段, … ASC

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

例: orderByAsc(“id”, “name”)—>order by id ASC,name ASC

2.22 orderByDesc

排序：ORDER BY 字段, … DESC

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

例: orderByDesc(“id”, “name”)—>order by id DESC,name DESC

2.23 orderBy

排序：ORDER BY 字段, …

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

例: orderBy(true, true, “id”, “name”)—>order by id ASC,name ASC

2.24 having

HAVING ( sql语句 )

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

例: having(“sum(age) > 10”)—>having sum(age) > 10
例: having(“sum(age) > {0}”, 11)—>having sum(age) > 11

2.25 or

拼接 OR

or()
or(boolean condition)

例: eq(“id”,1).or().eq(“name”,“老王”)—>id = 1 or name = ‘老王’

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

OR 嵌套

or(Function<Param, Param> func)
or(boolean condition, Function<Param, Param> func)

例: or(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))—>or (name = ‘李白’ and status <> ‘活着’)

2.26 and

AND 嵌套

and(Function<Param, Param> func)
and(boolean condition, Function<Param, Param> func)

例: and(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))—>and (name = ‘李白’ and status <> ‘活着’)

2.27 nested

正常嵌套 不带 AND 或者 OR

nested(Function<Param, Param> func)
nested(boolean condition, Function<Param, Param> func)

例: nested(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))—>(name = ‘李白’ and status <> ‘活着’)

2.28 apply

拼接 sql

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

例: 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’")

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

2.29 last

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

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

例: last(“limit 1”)

注意事项:只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用

2.30 exists

拼接 EXISTS ( sql语句 )

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

例: exists(“select id from table where age = 1”)—>exists (select id from table where age = 1)

2.31 notExists

拼接 NOT EXISTS ( sql语句 )

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

例: notExists(“select id from table where age = 1”)—>not exists (select id from table where age = 1)

3. QueryWrapper

继承自AbstractWrapper，自身的内部属性entity也用于生成where条件

3.1 select

设置查询字段

select(String... sqlSelect)
select(Predicate<TableFieldInfo> predicate)
select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)

例: select(“id”, “name”, “age”)
例: select(i -> i.getProperty().startsWith(“test”))

4. UpdateWrapper

继承自AbstractWrapper，自身的内部属性entity也用于生成where条件

4.1 set

SQL SET 字段

set(String column, Object val)
set(boolean condition, String column, Object val)

例: set(“name”, “老李头”)
例: set(“name”, “”)—>数据库字段值变为空字符串
例: set(“name”, null)—>数据库字段值变为null

4.2 setSql

设置 SET 部分 SQL

setSql(String sql)

例: set("name = ‘老李头’)

5. 使用Wrapper自定义SQL

5.1 注解方式

在Mapper接口中自定义方法，参数为Wrapper类型，并添加注解

@Select("select * from mysql_data ${ew.customSqlSegment}")
List<MysqlData> getAll(@Param(Constants.WRAPPER) Wrapper wrapper);

5.2 XML形式

在Mapper.xml映射文件中，添加sql语句

<select id="getAll" resultType="MysqlData">
    SELECT * FROM mysql_data ${ew.customSqlSegment}
</select>

6. 构造器应用

6.1 比较运算

QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("age", 20);
List<Person> list = personMapper.selectList(queryWrapper);

//生成的sql语句
SELECT id,last_name,age,gender,email FROM tmp_person WHERE age > ?

6.2 between

QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.between("age", 21, 28);
List<Person> list = personMapper.selectList(queryWrapper);

//生成的sql语句
SELECT id,last_name,age,gender,email FROM tmp_person WHERE age BETWEEN ? AND ?

6.3 like

QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.like("last_name", "张");
List<Person> list = personMapper.selectList(queryWrapper);

//生成的sql语句
SELECT id,last_name,age,gender,email FROM tmp_person WHERE last_name LIKE ?
Parameters: %张%(String)

6.4 in

QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.in("id", 1,2,3);
List<Person> list = personMapper.selectList(queryWrapper);

//生成的sql语句
SELECT id,last_name,age,gender,email FROM tmp_person WHERE last_name LIKE ?
Parameters: %张%(String)

6.5 分组

//接口中添加自定义方法
@Select("select gender,count(gender) num from tmp_person ${ew.customSqlSegment}")
List<Map<String, Object>> selectGroupByGender(@Param(Constants.WRAPPER) Wrapper queryWrapper);

//测试
QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.groupBy("gender");
List<Map<String, Object>> list = personMapper.selectGroupByGender(queryWrapper);

//生成的sql语句
select gender,count(gender) num from tmp_person GROUP BY gender

6.6 排序

QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.orderBy(true, true , "age","id");
List<Person> list = personMapper.selectList(queryWrapper);

//生成的sql语句
SELECT id,last_name,age,gender,email FROM tmp_person ORDER BY age ASC , id ASC

6.7 or

主动调用or表示紧接着调用下一个方法是用or连接

QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.lt("age", 20).or().like("last_name", "j");
List<Person> list = personMapper.selectList(queryWrapper);

//生成的sql语句
SELECT id,last_name,age,gender,email FROM tmp_person WHERE age < ? OR last_name LIKE ?

利用lambda表达式实现or的嵌套

QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 20).or(i->i.eq("gender",1).ne("last_name", "tom"));
List<Person> list = personMapper.selectList(queryWrapper);

//生成的sql语句
SELECT id,last_name,age,gender,email FROM tmp_person WHERE age >= ? OR ( gender = ? AND last_name <> ? )

6.8 and

默认为使用and连接，表示紧接着调用下一个方法是用and连接

QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("age", 20).eq("gender", 1);
List<Person> list = personMapper.selectList(queryWrapper);

//生成的sql语句
SELECT id,last_name,age,gender,email FROM tmp_person WHERE age > ? AND gender = ?

利用lambda表达式实现and的嵌套

QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age", 20).and(i->i.eq("gender",1).or().ne("last_name", "tom"));
List<Person> list = personMapper.selectList(queryWrapper);

//生成的sql语句
SELECT id,last_name,age,gender,email FROM tmp_person WHERE age >= ? AND ( gender = ? OR last_name <> ? )

6.9 select设置查询字段

QueryWrapper<Person> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id","last_name","age");
List<Person> list = personMapper.selectList(queryWrapper);

//生成的sql语句
SELECT id,last_name,age FROM tmp_person

6.10 LambdaQueryWrapper

//获取支持lambda表达式的条件构造器
LambdaQueryWrapper<Person> lambdaQueryWrapper = new QueryWrapper<Person>().lambda();
lambdaQueryWrapper.eq(Person::getLastName, "tom");
List<Person> list = personMapper.selectList(lambdaQueryWrapper);

//生成的sql语句
SELECT id,last_name,age,gender,email FROM tmp_person WHERE last_name = ?