Mybatis-Plus 之 Wrapper
前言
Wrapper 使用 lambda 形式可以通过方法引用的方式来使用实体字段名,避免直接写数据库表字段名时的错写名字。
一、new QueryWrapper()
一般的 QueryWrapper 写法:
List<User> userList = baseMapper.selectList(new QueryWrapper<User>().eq("user_id", userId));
1
需要手写数据库字段 user_id,容易出错,并且与数据库字段产生依赖。
lambda 的 QueryWrapper 写法:
List<User> userList = baseMapper.selectList(new QueryWrapper<User>().lambda().eq(User::getId, userId)));
1
直接调用类的 get 方法进行关联,有一定代码解耦效果。
二、Wrappers
List<User> userList = baseMapper.selectList(Wrappers.lambdaQuery(User.class).eq(User::getId, userId));
1
三、lambdaQueryWrapper
查一条数据:
User user = new LambdaQueryChainWrapper<>(baseMapper).eq(User::getId, userId).one();
1
查对象列表:
List<User> userList = new LambdaQueryChainWrapper<>(baseMapper).eq(User::getId, userId).list();
1
链式查询得到 list 对象列表,可读性没有 lambdaQuery 好。
四、Wrapper 拼接 sql 的方法
函数 说明 例子 生成的sql语句
eq 等于 = eq(“name”, “老王”) name = ‘老王’
ne 不等于 <> ne(“name”, ‘老王’ name <> ‘老王’
gt 大于 > gt(“age”, 18) age > 18
ge 大于等于 >= ge(“age”, 18) age >= 18
lt 小于 < lt(“age”, 18) age < 18
le 小于等于 <= le(“age”, 18) age <= 18
between BETWEEN v1 AND v2 between(“age”, 18, 24) age between 18 and 24
notBetween NOT BETWEEN v1 AND v2 notBetween(“age”,18,24) age not between 18 and 24
like LIKE ‘%值%’ like(“name”,“王”) name like '%王%’
notLike NOT LIKE ‘%值%’ notLike(“name”,“王”) name not like '%王%’
likeLeft LIKE ‘%值’ likeLeft(“name”,“王”) name like '%王
likeRight LIKE ‘值%’ likeRight (“name”,“王”) name like '王%’
isNull 字段 IS NULL isNull (“name”) name is null
isNotNull 字段 IS NOT NULL isNotNull (“name”) name is not null
in 字段 IN (v1, v2, v3) in(“age”,{1,2,3} ) age in (1,2,3)
notIn 字段 NOT IN (v1, v2, v3) notIn(“age”, 1, 2, 3) age not in (1, 2, 3)
inSql 字段 IN ( sql语句 ) inSql(“id”,“select id from table where id < 3”) id in (select id from table where id < 3)
notInSql 字段 NOT IN ( sql语句 ) notInSql(“id”, “select id from table where id < 3”) age not in (select id from table where id < 3)
groupBy 分组:GROUP BY 字段1, … groupBy(“id”, “name”) group by id, name
orderByAsc 升序排序:ORDER BY 字段1, … ASC orderByAsc(“id”, “name”) order by id ASC, name ASC
orderByDesc 降序排序:ORDER BY 字段1, … DESC orderByDesc(“id”, “name”) order by id DESC, name DESc
orderBy 排序:ORDER BY 字段1, … orderBy(true, true, “id”,“name”) orderBy(true,,true,“id”,“name”)
having HAVING ( sql 语句 ) having(“sum(age) > {0}”,11) having sum(age) > 11
or 拼接 OR 主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)
and 拼接 ADN 默认连接符
apply 拼接 sql 例: apply(“date_format(dateColumn, ‘%Y-%m-%d’) = {0}”,“2008-08-08”)—>date_format(dateColumn,‘%Y-%m-%d’) = ’2008-08-08’")
该方法可用于数据库函数动态入参的params对应前面sqlHaving内部的{index}部分.这样是不会有sql注入风险的,反之会有!
last 无视优化规则直接拼接到 sql 的最后,只能调用一次,多次调用以最后一次为准,有sql注入的风险,请谨慎使用。 例如 last(“limit 1”) (limit 1)
exists 拼接 EXISTS exists(“select id from table where age = 18”) exists(select id from table where age = 18)
notExists 拼接 NOT EXISTS notExists(“select id from table where age = 18”) not exists(“select id from table where age = 18”)
nested 正常嵌套不带 AND 或者 OR nested(i -> i.eq(“name”, “李白”).ne(“status”, “活着”)) (name = ‘李白’ and status <> ‘活着’)
Wrapper 附带 if 判断条件进行查询
List<SysDept> deptAllList = deptMapper.selectList(
Wrappers.<SysDept>lambdaQuery().like(StrUtil.isNotBlank(deptName), SysDept::getName, deptName));