Mybatis-Plus复杂语句多级嵌套分组带分页查询_mybatis plus mapper 多级查询

作者：weixin_40725706 | 2024-07-23 14:40:05

踩

mybatis plus mapper 多级查询

如 :


SELECT dbname FROM (
 
SELECT CONCAT(db_type,'-',table_name) as dbname FROM `mdn_table_permission` WHERE (db_type = 'MYSQL') ORDER BY create_time ASC
 
) as q GROUP BY dbname LIMIT 1

这样的语句是用条件构造器很难实现的，故用注解形式：

通过 QueryWrapper来拼接需要查询的参数: ${ew.customSqlSegment}进行注入


第一步:
 QueryWrapper<TablePermission> qw = new QueryWrapper<>();
qw.eq(StrUtil.isNotEmpty(dbType), TablePermission.DB_TYPE, dbType);
qw.like(StrUtil.isNotEmpty(tableName), TablePermission.TABLE_NAME, tableName);
qw.like(StrUtil.isNotEmpty(userName), TablePermission.USER_NAME, userName);
qw.select("CONCAT(db_type,'-',table_name) as dbname");
qw.orderByAsc(TablePermission.CREATE_TIME);
 
//第二步:
Page<TablePermission> pages = new Page<>(ePage, pageSize);
Page<String> lsit = baseMapper.lsitsss(pages,qw);
//第三步mapper写法:
@Select("SELECT dbname FROM (SELECT CONCAT(db_type,'-',table_name) as dbname FROM `mdn_table_permission` ${ew.customSqlSegment}) as q GROUP BY dbname")
Page<String> lsitsss(@Param("page")Page<TablePermission> page,@Param(Constants.WRAPPER) Wrapper wrapper);

官网网址

条件构造器 | MyBatis-Plus

函数名	说明	示例
allEq	全部eq(或个别isNull)	例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 = '老王'
eq	等于 =	例eq("name", "老王")--->name = '老王'
ne	不等于 <>	例: ne("name", "老王")--->name <> '老王'
ge	大于 >	例: gt("age", 18)--->age > 18
ge	大于等于 >=	例: ge("age", 18)--->age >= 18
lt	小于 <	例: lt("age", 18)--->age < 18
lt	小于 <	例: lt("age", 18)--->age < 18
between	BETWEEN 值1 AND 值2	例: between("age", 18, 30)--->age between 18 and 30
notBetween	NOT BETWEEN 值1 AND 值2	例: notBetween("age", 18, 30)--->age not between 18 and 30
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 (value.get(0), value.get(1), ...) 字段 IN (v0, v1, ...)	例: in("age",{1,2,3})--->age in (1,2,3) 例: in("age", 1, 2, 3)--->age in (1,2,3)
notIn	字段 NOT IN (value.get(0), value.get(1), ...) 字段 NOT IN (v0, v1, ...)	例: notIn("age",{1,2,3})--->age not in (1,2,3) 例: notIn("age", 1, 2, 3)--->age not in (1,2,3)
inSql	字段 IN ( sql语句 )	例: 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语句 )	例: 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") --->id not in (select id from table where id < 3)
groupBy	分组：GROUP BY 字段, ...	例: groupBy("id", "name")--->group by id,name
orderByAsc	排序：ORDER BY 字段, ... ASC	例: orderByAsc("id", "name")--->order by id ASC,name ASC
orderByDesc	排序：ORDER BY 字段, ... DESC	例: orderByDesc("id", "name")--->order by id DESC,name DESC
orderBy	排序：ORDER BY 字段, ...	例: orderBy(true, true, "id", "name")--->order by id ASC,name ASC
having	HAVING ( sql语句 )	例: having("sum(age) > 10")--->having sum(age) > 10 例: having("sum(age) > {0}", 11)--->having sum(age) > 11
func	func 方法(主要方便在出现if...else下调用不同方法能不断链)	例: func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})
or	拼接 OR。主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)	例: eq("id",1).or().eq("name","老王") --->id = 1 or name = '老王' 例: or(i -> i.eq("name", "李白").ne("status", "活着")) --->or (name = '李白' and status <> '活着')
and	AND 嵌套	例: and(i -> i.eq("name", "李白").ne("status", "活着")) --->and (name = '李白' and status <> '活着')
nested	正常嵌套不带 AND 或者 OR	例: nested(i -> i.eq("name", "张三").gt("age", 25)) --->(name = '张三' and age > 25) 例： .eq("name", "李四") .nested(i -> i.gt("age", 25).or().lt("age", 12)) .eq("email", "abc@qq.com") --->name = '李四" AND (age > 25 OR age < 12) AND email = "abc@qq.com"
apply	拼接 sql。该方法可用于数据库函数动态入参的params对应前面applySql内部的{index}部分。这样是不会有sql注入风险的，反之会有!	例: 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'")
last	无视优化规则直接拼接到 sql 的最后。只能调用一次,多次调用以最后一次为准。有sql注入的风险，请谨慎使用	例: last("limit 1")
exists	拼接 EXISTS ( sql语句 )	例: exists("select id from table where age = 1") --->exists (select id from table where age = 1)
notExists	拼接 NOT EXISTS ( sql语句 )	例: notExists("select id from table where age = 1") --->not exists (select id from table where age = 1)

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/870354