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

    如 :

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

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

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

    1. 第一步:
    2.  QueryWrapper qw = new QueryWrapper<>();
    3. qw.eq(StrUtil.isNotEmpty(dbType), TablePermission.DB_TYPE, dbType);
    4. qw.like(StrUtil.isNotEmpty(tableName), TablePermission.TABLE_NAME, tableName);
    5. qw.like(StrUtil.isNotEmpty(userName), TablePermission.USER_NAME, userName);
    6. qw.select("CONCAT(db_type,'-',table_name) as dbname");
    7. qw.orderByAsc(TablePermission.CREATE_TIME);
    8.  
    9. //第二步:
    10. Page pages = new Page<>(ePage, pageSize);
    11. Page lsit = baseMapper.lsitsss(pages,qw);
    12. //第三步mapper写法:
    13. @Select("SELECT dbname FROM (SELECT CONCAT(db_type,'-',table_name) as dbname FROM `mdn_table_permission` ${ew.customSqlSegment}) as q GROUP BY dbname")
    14. Page lsitsss(@Param("page")Page 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)

  • 相关阅读:
    python 学生编程--3 多彩同心圆
    QUIC协议
    环境温湿度在线监测如何实现？有何应用场景？
    Shell基础— 变量定义的规则和分类
    python用内省优化显示
    Hive3.1.2分区与排序（内置函数）
    wavesummit2024发布飞桨3.0版本
    外汇天眼：官网虚假宣传受到多重监管！FCA率先发出警告！
    openshift搭建Istio
    IS215UCVEM08B IS215UCVEH2AE VMIVME-7614-132 350-017614-132 D
  • 原文地址：https://blog.csdn.net/qq_38567039/article/details/126590316