MyBatis-Plus - 条件构造器Wrapper

22 篇文章 0 订阅

MyBatis-Plus - 条件构造器Wrapper
1.概述
在我们实际操作数据库时会涉及到很多复杂的条件,所以mp为我们提供了一个强大的条件构造器Wrapper,使用她让我们更方便的构造条件

2. 继承关系

AbstractWrapper: 用于构造where条件

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

QueryWrapper: 额外提供select 方法 用于构造查询的哪些列

等价sql: select xxx,xxx,xxx from table 用于构造 xxx,xxx,xxx列

UpdateWrapper: 额外提供set方法,可以指定哪些字段进行更新

3.AbstractWrapper中的方法
3.0 须知
以下示例中比较简单的,显而易见一看就会的会一笔带过,不会写代码举例说明
以下出现第一个参数是 boolean类型的话,说明要判断第一个参数是否添加到生成的sql中,比如allEq(boolean condition, Map<R, V> params, boolean null2IsNull)
以下代码块内的多个方法均为从上往下补全个别boolean类型的入参,默认为true
以下方法入参中的R column均表示数据库字段,当R具体类型为String时则为数据库字段名(字段名是数据库关键字的自己用转义符包裹!)!而不是实体类数据字段名!!!
3.1 allEq
Eg1:

allEq(Map<R, V> params)

    QueryWrapper<SysUser> query =new QueryWrapper<>();
        Map<String, Object> map =new HashMap<>();
        map.put("email","123@qq.com");
        map.put("user_name","jinxs001");
        query.allEq(map);
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
4
5
6
等价sql:SELECT xxx,xxx,xxx FROM table WHERE (user_name = ‘jinxs001’ AND email = ‘123@qq.com’)
从sql中不难看出在条件构造器中的map k =字段名,v =字段值

Eg2:

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

    QueryWrapper<SysUser> query =new QueryWrapper<>();
        Map<String, Object> map =new HashMap<>();
        map.put("email","123@qq.com");
        map.put("user_name",null);
        query.allEq(map,true);
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
4
5
6
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (user_name IS NULL AND email = ‘123@qq.com’)
第二个boolean的参数若为true mp就会检查map中的value值 若value为null就会在最终的sql中增加 is null 条件,若第二个参数值为false 将忽略value中参数为null的

Eg3:

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

        int age =18;
        QueryWrapper<SysUser> query =new QueryWrapper<>();
        Map<String, Object> map =new HashMap<>();
        map.put("email","123@qq.com");
        map.put("user_name",null);
        query.allEq(age>20,map,true);
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
4
5
6
7
等价sql: 当第一个参数为false时等价于:SELECT xxx,xxx,xxx FROM sys_user
当第一个参数为true时等价于:SELECT xxx,xxx,xxx FROM sys_user WHERE (user_name IS NULL AND email = ‘123@qq.com’)
总结: 所以第一个参数的意义是,当第一个参数为false时不会将后面的条件构建到最终sql里面,为true时才会

Eg4:

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)

    QueryWrapper<SysUser> query =new QueryWrapper<>();
        Map<String, Object> map =new HashMap<>();
        map.put("email","123@qq.com");
        map.put("user_name",null);
        query.allEq((k,v) -> k.contains("a"),map);
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
        System.out.println(sysUsers.size());
1
2
3
4
5
6
7
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (user_name IS NULL AND email = ‘123@qq.com’)
说明: 参数filter代表一个过滤函数,其作用是在添加这个参数后,mp会在map里面筛选key,不符合条件的key会被过滤掉,不会添加到最终执行sql中
condition,null2IsNull 用法同上,不再赘述

3.2 eq(等于 =)
Eg:

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

     QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.eq("user_name","jinxs001");
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (user_name = ‘jinxs001’)
说明: R column 代表表字段 ,val 值 。

3.3 ne(不等于 <>)
Eg:

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

     QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.ne("user_name","jinxs001");
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (user_name <> ‘jinxs001’)

3.4 gt(大于 >)
Eg:

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

    QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.gt("age",5);
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (age > 5)

3.5 ge(大于等于 >=)
Eg:

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

 QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.ge("age",5);
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (age >= 5)

3.6 lt (小于 <)
Eg:

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

 QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.lt("age",5);
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT xxx,xxx,xxx sys_user WHERE (age < 5)

3.7 le(小于等于 <=)
le(R column, Object val)
le(boolean condition, R column, Object val)

QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.le("age",5);
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (age <= 5)

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

QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.between("age",5,6);
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (age BETWEEN 5 AND 6)

3.9 notBetween
notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (age not BETWEEN 5 AND 6)

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

   QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.like("user_name","jinxs");
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (user_name LIKE ‘%jinxs%’)

3.11 notLike
notLike(R column, Object val)
notLike(boolean condition, R column, Object val)
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (user_name NOT LIKE ‘%jinxs%’)

3.12 likeLeft
likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (user_name LIKE ‘%jinxs’)

3.13 likeRight
likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (user_name LIKE ‘jinxs%’)
总结: 做模糊查询时mp会为我们拼接上%% 所以传值的时候不需要我们传入%,只传入需要匹配的值即可

3.14 isNull
isNull(R column)
isNull(boolean condition, R column)
等价sql: isNull(“name”)—>name is null

3.15 isNotNull
isNotNull(R column)
isNotNull(boolean condition, R column)
等价sql: isNotNull(“name”)—>name is not null

3.16 in
isNotNull(R column)
isNotNull(boolean condition, R column)
in(R column, Object… values)
in(boolean condition, R column, Object… values)


图中可以看出,传入的 in后面的数据可以是集合也可以是数组 所以,

         QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.in("age",1,2,3);
        //===================或者是==============
        //List<Integer> list =new ArrayList<>();
        //list.add(4);
        //list.add(5);
        //list.add(6);
        //query.in("age", list);
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
4
5
6
7
8
9
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (age IN (1,2,3))

3.17 notIn
notIn(R column, Collection<?> value) notIn(boolean condition, R column, Collection<?> value)
notIn(R column, Object… values)
notIn(boolean condition, R column, Object… values)
说明: 相当于 not in 用法同上

3.18 inSql
in(sql语句)

inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)
等价sql: inSql(“id”, “select id from table where id < 3”)—>id in (select id from table where id < 3)
in后面的参数是String类型 可以是子查询的sql语句 也可以是一串字符串值,就像是这样
inSql(“age”, “1,2,3,4,5,6”)—>age in (1,2,3,4,5,6)

3.19 notInSql
notInSql(R column, String inValue) notInSql(boolean condition, R
column, String inValue)
说明: 使用方法同上,相当于 not in

3.20 groupBy
groupBy(R… columns)
groupBy(boolean condition, R… columns)
说明: 分组字段相当于 GROUP BY 字段
等价sql: groupBy(“id”, “name”)—>group by id,name

3.21 orderByAsc
orderByAsc(R… columns)
orderByAsc(boolean condition, R… columns)
说明: 相当于 ORDER BY 字段, … ASC 升序排序
等价sql: orderByDesc(“id”, “name”)—>order by id DESC,name DESC

3.22 orderByDesc
orderByDesc(R… columns)
orderByDesc(boolean condition, R… columns)
说明: 相当于ORDER BY 字段, … 降序排序
等价sql: orderBy(true, true, “id”, “name”)—>order by id ASC,name ASC

3.23 having
having(String sqlHaving, Object… params)
having(boolean condition, String sqlHaving, Object… params)
说明: HAVING ( sql语句 )
having(String sqlHaving, Object… params)
having(boolean condition, String sqlHaving, Object… params)

3.24 func
func(Consumer consumer)
func(boolean condition, Consumer consumer)

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

3.25 or
or()
or(boolean condition)
注意事项:
主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)
例: eq(“id”,1).or().eq(“name”,“老王”)—>id = 1 or name = ‘老王’

3.26 and
and(Consumer consumer)
and(boolean condition, Consumer consumer)
说明: 嵌套语法
例: and(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))—>and (name = ‘李白’ and status <> ‘活着’)

3.27 nested
apply(String applySql, Object… params)
apply(boolean condition, String applySql, Object… params)
说明: 正常嵌套 不带 AND 或者 OR
例: nested(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))—>(name = ‘李白’ and status <> ‘活着’)

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

 QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2020-08-08");
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT xxx,xxx,xxx FROM sys_user WHERE (date_format(create_time,‘%Y-%m-%d’) = ‘2020-08-08’)

3.29 last
last(String lastSql)
last(boolean condition, String lastSql)
说明: 直接将代码块拼接到最后。只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用

 QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.last("limit 1");
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT xxx,xxx,xxx FROM sys_user limit 1

3.30 exists
exists(String existsSql)
exists(boolean condition, String existsSql)
说明: 拼接exists
例: exists(“select id from table where age = 1”)—>exists (select id from table where age = 1)

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

4. QueryWrapper
说明:
继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件
及 LambdaQueryWrapper, 可以通过 new QueryWrapper().lambda() 方法获取

4.1 select(设置查询字段)
说明: 这里的使用有两种形式,第一种是直接设置要查询的字段,第二种是过滤查询的字段(主键不会被过滤),入参不包含 字节码文件的的调用前需要wrapper内的entity属性有值! 这两类方法重复调用以最后一次为准
Eg1:

select(String… sqlSelect)

 QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.select("user_id","user_name","age");
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT user_id,user_name,age FROM sys_user
说明: select 用来构建查询的列

Eg2:

select(Class entityClass, Predicate predicate)

先看写源代码,代码注释中列举了几种写法


QueryWrapper<SysUser> query =new QueryWrapper<>();
        query.select(SysUser.class,tableFieldInfo -> "user_name".equals(tableFieldInfo.getColumn()));
        List<SysUser> sysUsers = sysUserMapper.selectList(query);
1
2
3
等价sql: SELECT user_id,user_name FROM sys_user

5.UpdateWrapper
说明:
继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件
及 LambdaUpdateWrapper, 可以通过 new UpdateWrapper().lambda() 方法获取!

5.1set
set(String column, Object val)
set(boolean condition, String column,Object val)
例: set(“name”, “老李头”)
例: set(“name”, “”)—>数据库字段值变为空字符串
例: set(“name”, null)—>数据库字段值变为null

5.2 setSql
说明:
setSql(String sql)
setSql(“name = ‘老李头’”)
————————————————
版权声明:本文为CSDN博主「J.XS」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_43807429/article/details/125267700

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值