发布于 

MybatisPlus条件语句

说明

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

AbstractWrapper

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

allEq

全部eq(或个别isNull)

1
2
3
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 = ‘张三’

1
2
3
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 : 过滤函数,是否允许字段传入比对条件中

eq

等于 =

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

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

ne

不等于 <>

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

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

gt

大于 >

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

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

ge

大于等于 >=

1
2
3
ge(R column, Object val)
ge(boolean condition, R column, Object val)

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

lt

小于 <

1
2
3
lt(R column, Object val)
lt(boolean condition, R column, Object val)

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

le

小于等于 <=

1
2
3
le(R column, Object val)
le(boolean condition, R column, Object val)

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

between

BETWEEN 值1 AND 值2

1
2
3
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

notBetween

NOT BETWEEN 值1 AND 值2

1
2
3
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

like

LIKE ‘%值%’

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

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

notLike

NOT LIKE ‘%值%’

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

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

likeLeft

LIKE ‘%值’

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

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

likeRight

LIKE ‘值%’

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

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

isNull

字段 IS NULL

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

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

isNotNull

字段 IS NOT NULL

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

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

in

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

1
2
in(R column, Collection<?> value)
in(bo```olean condition, R column, Collection<?> value)

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

字段 IN (v0, v1, …)

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

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

notIn

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

1
2
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, …)

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

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

inSql

字段 IN ( sql语句 )

1
2
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)

notInSql

字段 NOT IN ( sql语句 )

1
2
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)

groupBy

分组：GROUP BY 字段, …

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

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

orderByAsc

排序：ORDER BY 字段, … ASC

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

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

orderByDesc

排序：ORDER BY 字段, … DESC

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

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

orderBy

排序：ORDER BY 字段, …

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

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

having

HAVING ( sql语句 )

1
2
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

or

拼接 OR

1
2
or()
or(boolean condition)

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

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

OR 嵌套

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

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

and

AND 嵌套

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

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

nested

正常嵌套 不带 AND 或者 OR

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

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

apply

拼接 sql

1
2
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注入风险的,反之会有!

last

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

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

例: last(“limit 1”)

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

exists

拼接 EXISTS ( sql语句 )

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

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

notExists

拼接 NOT EXISTS ( sql语句 )

1
2
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)

QueryWrapper

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

select

设置查询字段

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

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

UpdateWrapper

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

set

SQL SET 字段

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

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

setSql

设置 SET 部分 SQL

1
setSql(String sql)

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

使用Wrapper自定义SQL

注解方式

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

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

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

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

构造器应用

比较运算
1
2
3
4
5
6
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 > ?
between
1
2
3
4
5
6
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 ?
like
1
2
3
4
5
6
7
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)
in
1
2
3
4
5
6
7
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)
分组
1
2
3
4
5
6
7
8
9
10
11
//接口中添加自定义方法
@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
排序
1
2
3
4
5
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
or

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

1
2
3
4
5
6
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的嵌套

1
2
3
4
5
6
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 <> ? )
and

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

1
2
3
4
5
6
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的嵌套
1
2
3
4
5
6
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 <> ? )
select设置查询字段
1
2
3
4
5
6
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
LambdaQueryWrapper
1
2
3
4
5
6
7
//获取支持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 = ?
分享文章
快来参与讨论吧