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)