Mybatis-Plus 条件构造器

Mybatis-Plus是一个MyBatis的增强工具，在Mybatis-Plus的基础上只做增强不做改变，为简化开发、提高效率而生。

Mybatis-Plus的核心功能之一是条件构造器，通过条件构造器，我们在单表的增删改查的所有操作都不需要写SQL。

1、Wrapper

Mybatis Plus 提供的几种条件构造器，关系如下：

2、语法详情

方法名	说明	用法实例	等价SQL
allEq(Map<R,V> params)	全部等于	allEq({id:1,name:"老王"})	id = 1 and name = '老王'
eq(R column, Object val)	等于 =	eq("name", "老王")	name = '老王'
ne(R column, Object val)	不等于 <>	ne("name", "老王")	name <> '老王'
gt(R column, Object val)	大于 >	gt("age", 18)	age > 18
ge(R column, Object val)	大于等于 >=	ge("age", 18)	age >= 18
lt(R column, Object val)	小于 <	lt("age", 18)	age < 18
le(R column, Object val)	小于等于 <=	le("age", 18)	age <= 18
between(R column, Object val1, Object val2)	BETWEEN 值1 AND 值2	between("age", 18, 30)	age between 18 and 30
notBetween(R column, Object val1, Object val2)	NOT BETWEEN 值1 AND 值2	notBetween("age", 18, 30)	age not between 18 and 30
like(R column, Object val)	LIKE '%值%'	like("name", "王")	name like '%王%'
notLike(R column, Object val)	NOT LIKE '%值%'	notLike("name", "王")	name not like '%王%'
likeLeft(R column, Object val)	LIKE '%值'	likeLeft("name", "王")	name like '%王'
likeRight(R column, Object val)	LIKE '值%'	likeRight("name", "王")	name like '王%'
notLikeLeft(R column, Object val)	NOT LIKE '%值'	notLikeLeft("name", "王")	name not like '%王'
notLikeRight(R column, Object val)	NOT LIKE '值%'	notLikeRight("name", "王")	name not like '王%'
isNull(R column)	字段 IS NULL	isNull("name")	name is null
isNotNull(R column)	字段 IS NOT NULL	isNotNull("name")	name is not null
in(R column, Collection<?> value)	字段 IN (value.get(0), value.get(1), ...)	in("age",{1,2,3})	age in (1,2,3)
notIn(R column, Collection<?> value)	字段 NOT IN (value.get(0), value.get(1), ...)	notIn("age",{1,2,3})	age not in (1,2,3)
inSql(R column, String inValue)	字段 IN ( sql语句 )	inSql("id", "select id from table where id < 3")	id in (select id from table where id < 3)
notInSql(R column, String inValue)	字段 NOT IN ( sql语句 )	notInSql("id", "select id from table where id < 3")	id not in (select id from table where id < 3)
groupBy(R... columns)	分组：GROUP BY 字段, ...	groupBy("id", "name")	group by id,name
orderByAsc(R... columns)	排序：ORDER BY 字段, ... ASC	orderByAsc("id", "name")	order by id ASC,name ASC
orderByDesc(R... columns)	排序：ORDER BY 字段, ... DESC	orderByDesc("id", "name")	order by id DESC,name DESC
orderBy(boolean condition, boolean isAsc, R... columns)	排序：ORDER BY 字段, ...	orderBy(true, true, "id", "name")	order by id ASC,name ASC
having(String sqlHaving, Object... params)	HAVING ( sql语句 )	having("sum(age) > 10") having("sum(age) > {0}", 11)	having sum(age) > 10 having sum(age) > 11
func(Consumer<Children> consumer)	func 方法(主要方便在出现if...else下调用不同方法能不断链)	func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})
or()	拼接 OR	eq("id",1).or().eq("name","老王")	id = 1 or name = '老王'
or(Consumer<Param> consumer)	OR 嵌套	or(i -> i.eq("name", "李白").ne("status", "活着"))	or (name = '李白' and status <> '活着')
and(Consumer<Param> consumer)	AND 嵌套	and(i -> i.eq("name", "李白").ne("status", "活着"))	and (name = '李白' and status <> '活着')
nested(Consumer<Param> consumer)	正常嵌套 不带 AND 或者 OR	nested(i -> i.eq("name", "李白").ne("status", "活着"))	(name = '李白' and status <> '活着')
apply(String applySql, Object... params)	拼接 sql(该方法可用于数据库函数 动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!)	apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")	date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
last(String lastSql)	无视优化规则直接拼接到 sql 的最后(只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用)	last("limit 1")
exists(String existsSql)	拼接 EXISTS ( sql语句 )	exists("select id from table where age = 1")	exists (select id from table where age = 1)
notExists(String notExistsSql)	拼接 NOT EXISTS ( sql语句 )	notExists("select id from table where age = 1")	not exists (select id from table where age = 1)
select(String... sqlSelect)	设置查询字段	select("id", "name", "age")
set(String column, Object val)	SQL SET 字段	set("name", "老李头")
setSql(String sql)	设置 SET 部分 SQL	setSql("name = '老李头'")

3、具体使用

1.使用QueryWrapper和UpdateWrapper

 /**
     * 根据年龄查询
     * @param age
     * @return
     */
    @PostMapping("selectByAge")
    @ResponseBody
    public AjaxResult selectByAge(@RequestBody String age){
        //定义条件构造器
        QueryWrapper<A> qw = new QueryWrapper<>();
        //构造条件构造器
        qw.eq("age","18");
        //查询
        List<A> result = aService.list(qw);
        return AjaxResult.success(result);
    }

2.使用LambdaQueryWrapper和LambdaUpdateWrapper(推荐)

/**
     * 根据年龄查询
     * @param age
     * @return
     */
    @PostMapping("selectByAgeTwo")
    @ResponseBody
    public AjaxResult selectByAgeTwo(@RequestBody String age){
        LambdaQueryWrapper<A> lqw = new LambdaQueryWrapper<A>().eq(A::getAge,age);
        List<A> result = aService.list(lqw);
        return AjaxResult.success(result);
    }

上面两种方法，推荐使用第二种，因为使用QueryWrapper或者UpdateWrapper时，对于条件的某个列，我们是写的字符串配置，比如 QueryWrapper.eq(“age”,18);这里的age是数据库表的列名，很有可能我们会写错，但是通过lambda 的方式，LambdaQueryWrapper.eq(User::getAge,18)，这样就不会有写错的可能了。所以推荐大家使用Lambda 的方式。