isNotNull
isNotNull(R column) isNotNull(boolean condition, R column)
- 字段 IS NOT NULL
- 例:
isNotNull("name")
—>name is not null
in
字段 IN (value.get(0), value.get(1), …)
in(R column, Collection<?> value) in(boolean condition, R column, Collection<?> value)
例
in("age",{1,2,3})
—>age in (1,2,3)
in(R column, Object... values) in(boolean condition, R column, Object... values)
字段 IN (v0, v1, …)
例
in("age", 1, 2, 3)
—>age in (1,2,3)
notIn
notIn(R column, Collection<?> value) notIn(boolean condition, R column, Collection<?> value)
- 字段 NOT IN (value.get(0), value.get(1), …)
- 例:
notIn("age",{1,2,3})
—>age not in (1,2,3)
notIn(R column, Object... values) notIn(boolean condition, R column, Object... values)
- 字段 NOT IN (v0, v1, …)
- 例:
notIn("age", 1, 2, 3)
—>age not in (1,2,3)
inSql
inSql(R column, String inValue) inSql(boolean condition, R column, String inValue)
字段 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
notInSql(R column, String inValue) notInSql(boolean condition, R column, String inValue)
字段 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
groupBy(R... columns) groupBy(boolean condition, R... columns)
- 分组:GROUP BY 字段, …
- 例:
groupBy("id", "name")
—>group by id,name
orderByAsc
排序:ORDER BY 字段, … ASC
orderByAsc(R... columns) orderByAsc(boolean condition, R... columns)
实例
orderByAsc("id", "name")
—>order by id ASC,name ASC
orderByDesc
orderByDesc(R... columns) orderByDesc(boolean condition, R... columns)
- 排序:ORDER BY 字段, … DESC
- 例:
orderByDesc("id", "name")
—>order by id DESC,name DESC
orderBy
orderBy(boolean condition, boolean isAsc, R... columns)
- 排序:ORDER BY 字段, …
- 例:
orderBy(true, true, "id", "name")
—>order by id ASC,name ASC
having
having(String sqlHaving, Object... params) having(boolean condition, String sqlHaving, Object... params)
- HAVING ( sql语句 )
- 例:
having("sum(age) > 10")
—>having sum(age) > 10
- 例:
having("sum(age) > {0}", 11)
—>having sum(age) > 11
func
func(Consumer<Children> consumer) func(boolean condition, Consumer<Children> consumer)
- unc 方法(主要方便在出现if…else下调用不同方法能不断链)
- 例:
func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})
or
or() or(boolean condition)
- 拼接 OR
::: tip 注意事项:
主动调用or
表示紧接着下一个方法不是用and
连接!(不调用or
则默认为使用and
连接)
::: - 例:
eq("id",1).or().eq("name","老王")
—>id = 1 or name = '老王'
or(Consumer<Param> consumer) or(boolean condition, Consumer<Param> consumer)
- OR 嵌套
- 例:
or(i -> i.eq("name", "李白").ne("status", "活着"))
—>or (name = '李白' and status <> '活着')
and
and(Consumer<Param> consumer) and(boolean condition, Consumer<Param> consumer)
- AND 嵌套
- 例:
and(i -> i.eq("name", "李白").ne("status", "活着"))
—>and (name = '李白' and status <> '活着')
nested
nested(Consumer<Param> consumer) nested(boolean condition, Consumer<Param> consumer)
- 正常嵌套 不带 AND 或者 OR
- 例:
nested(i -> i.eq("name", "李白").ne("status", "活着"))
—>(name = '李白' and status <> '活着')
apply-拼接SQL
apply(String applySql, Object... params) apply(boolean condition, String applySql, Object... params)
该方法可用于数据库函数
动态入参的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
last(String lastSql) last(boolean condition, String lastSql)
- 无视优化规则直接拼接到 sql 的最后
::: tip 注意事项:
只能调用一次,多次调用以最后一次为准
有sql注入的风险,请谨慎使用
::: - 例:
last("limit 1")
exists
exists(String existsSql) exists(boolean condition, String existsSql)
- 拼接 EXISTS ( sql语句 )
- 例:
exists("select id from table where age = 1")
—>exists (select id from table where age = 1)