Sql构造器 (SqlWrapper)

Sql构造器-开始::: tip 说明:以下出现的第一个入参boolean condition表示该条件是否加入最后生成的sql中以下代码块内的多个方法均为从上往下补全个别boolean类型的入参,默认为trueboolean… escape参数控制生成的Sql是否转义, 可空参数所有的Sql不用强制as起别名, 因为BaseService中的方法在查询的时候自动把查询的列名转换成了小...

慕容-小灰灰

2126人浏览 · 2019-09-11 14:48:42

慕容-小灰灰 · 2019-09-11 14:48:42 发布

Sql构造器-开始

::: tip 说明:

以下出现的第一个入参boolean condition表示该条件是否加入最后生成的sql中
以下代码块内的多个方法均为从上往下补全个别boolean类型的入参,默认为true
boolean… escape参数控制生成的Sql是否转义, 可空参数
所有的Sql不用强制as起别名, 因为BaseService中的方法在查询的时候自动把查询的列名转换成了小驼峰命名并且返回
:::

::: 警告:
构建SqlWrapper必须要传递表名或者带@Table注解的实体，否则不会获取到表名, 以下四种方式都可以获取到表名

SqlWrapper.create(“burying_point”).build();
SqlWrapper.create(BuryingPoint.class).build();
SqlWrapper.create().build(“burying_point”);
SqlWrapper.create().build(BuryingPoint.class);
:::

SqlWrapper

::: tip 说明:
当前版本Sql构造器暂时只能用于单表Sql拼接, 能减少一部分开发工作量
:::

实际使用例子

String selectSql = SqlWrapper.create(BuryingPoint.class)
        //.select("id, content")
        .where(Convert.toInt(paramMap.get("id")) > 0, "id >", paramMap.get("id"))
        .where(StrUtil.isNotEmpty(Convert.toStr(paramMap.get("name"))), "name !=", paramMap.get("name"))
        .orWhere(StrUtil.isNotEmpty(Convert.toStr(paramMap.get("description"))),"description !=",  paramMap.get("description"))
        .like(StrUtil.isNotEmpty(Convert.toStr(paramMap.get("name"))), "name", paramMap.get("name"))
        .orLike(StrUtil.isNotEmpty(Convert.toStr(paramMap.get("type"))), "type", paramMap.get("type"))
        .orGroupStart()
            .like("name", paramMap.get("name"))
        .groupEnd()
        .notGroupStart()
            .where("name !=", paramMap.get("name"))
            .orWhere("description !=", paramMap.get("description"))
        .groupEnd()
        .orderBy("id", "desc")
        .orderBy("name", "asc")
        .groupBy("name")
        .build();

create (创建一个空的构造器)

public static <T> SqlWrapper create()
public static <T> SqlWrapper create(String tableNames)
public static <T> SqlWrapper create(Class<T> tClass)

使用方法
例1: SqlWrapper.create("burying_point");
例2: SqlWrapper.create(BuryingPoint.class);

select

select(String select, boolean... escape)
selectMax(String select, String... alias)
selectMin(String select, String... alias)
selectAvg(String select, String... alias)
selectSum(String select, String... alias)

传入想要查询的字段
例: SqlWrapper.create().select("id, name, burying_key")—>select id, name, burying_key ...
如果不调用select方法默认会生成 select * from xxxx

form

from(String from)

生成查询的from部分, 多个值以","分隔
例: SqlWrapper.create().form("burying_info, merchant_info")—>from burying_info, merchant_info

where

where(Boolean condition, String key, Object value, boolean... escape)
where(String key, Object value, boolean... escape)
where(String key, boolean... escape)
where(Map<String, Object> map, boolean... escape)

生成查询的WHERE部分, 使用“AND”分隔多个调用
例: SqlWrapper.create().where(StrUtil.isNotEmpty(paramName), "merchant_name = ", paramName)
第一个参数是条件，如果条件为true才进行and拼接条件 form xxx where xxx == xxx and merchant_name = {paramName}

orWhere

orWhere(Boolean condition, String key, Object value, boolean... escape)
orWhere(String key, Object value, boolean... escape)
orWhere(Map<String, Object> map, boolean... escape)

生成查询的WHERE部分, 使用“OR”分隔多个调用
例: SqlWrapper.create().orWhere(StrUtil.isNotEmpty(paramName), "merchant_name = ", paramName)
第一个参数是条件，如果条件为true才进行or拼接条件 form xxx where xxx == xxx or merchant_name = {paramName}

whereIn

whereIn(Boolean condition, String key, List<Object> values, boolean... escape)
whereIn(String key, List<Object> values, boolean... escape)

生成一个Where查询条件 IN(‘item’, ‘item’), 在适当的情况下加上“AND”
例: SqlWrapper.create().whereIn(paramsList.size() > 0, "merchant_no", paramsList)
生成的sql为：AND merchant_no IN('item1', 'item2')

orWhereIn

orWhereIn(Boolean condition, String key, List<Object> values, boolean... escape)
orWhereIn(String key, List<Object> values, boolean... escape)

生成一个Where查询条件 IN(‘item’, ‘item’), 在适当的情况下加上“AND”
例: SqlWrapper.create().orWhereIn(paramsList.size() > 0, "merchant_no", paramsList)
生成的sql为：OR merchant_no IN('item1', 'item2')

whereNotIn

whereNotIn(Boolean condition, String key, List<Object> values, boolean... escape)
whereNotIn(String key, List<Object> values, boolean... escape)

生成一个Where查询：field NOT IN(‘item’, ‘item’), 在适当的情况下加上“AND”
例: SqlWrapper.create().whereNotIn(paramsList.size() > 0, "merchant_no", paramsList)
生成的sql为：AND merchant_no NOT IN('item1', 'item2')

orWhereNotIn

orWhereNotIn(Boolean condition, String key, List<Object> values, boolean... escape)
orWhereNotIn(String key, List<Object> values, boolean... escape)

生成一个Where查询：field NOT IN(‘item’, ‘item’), 在适当的情况下加上“OR”
例: SqlWrapper.create().orWhereNotIn(paramsList.size() > 0, "merchant_no", paramsList)
生成的sql为：OR merchant_no NOT IN('item1', 'item2')

like

like(Boolean condition, String field, Object match, boolean... escape)
like(Boolean condition, String field, Object match, String side,  boolean... escape)
like(String field, Object match, String side, boolean... escape)
like(String field, Object match, boolean... escape)
like(Map<String, Object> fields, String side, boolean... escape)
like(Map<String, Object> fields, boolean... escape)

生成查询的%LIKE%部分, 使用“AND”分隔多个调用, side可选值有：both, none, before, after, 默认为both
例: SqlWrapper.create().like(StrUtil.isNotEmpty(paramName), "merchant_name", paramName)
生成的sql为：AND merchant_name like '%{paramName}%'

notLike

notLike(String field, Object match, String side, boolean... escape)
notLike(Boolean condition, String field, Object match, boolean... escape)
notLike(Boolean condition, String field, Object match, String side, boolean... escape)
notLike(String field, Object match, boolean... escape)
notLike(Map<String, Object> fields, String side, boolean... escape)
notLike(Map<String, Object> fields, boolean... escape)

生成查询的 NOT %LIKE% 部分, 使用“AND”分隔多个调用, side可选值有：both, none, before, after, 默认为both
例: SqlWrapper.create().notLike(StrUtil.isNotEmpty(paramName), "merchant_name", paramName)
生成的sql为：AND merchant_name NOT LIKE '%{paramName}%'

orLike

orLike(Boolean condition, String field, Object match, boolean... escape)
orLike(Boolean condition, String field, Object match, String side, boolean... escape)
orLike(String field, Object match, String side, boolean... escape)
orLike(String field, Object match, boolean... escape)
orLike(Map<String, Object> fields, String side, boolean... escape)
orLike(Map<String, Object> fields, boolean... escape)

生成查询的 %LIKE% 部分, 使用“OR”分隔多个调用, side可选值有：both, none, before, after, 默认为both
例: SqlWrapper.create().orLike(StrUtil.isNotEmpty(paramName), "merchant_name", paramName)
生成的sql为：OR merchant_name LIKE '%{paramName}%'

orNotLike

orNotLike(Boolean condition, String field, Object match, boolean... escape)
orNotLike(Boolean condition, String field, Object match, String side, boolean... escape)
orNotLike(String field, Object match, String side, boolean... escape)
orNotLike(String field, Object match, boolean... escape)
orNotLike(Map<String, Object> fields, String side, boolean... escape)
orNotLike(Map<String, Object> fields, boolean... escape)

生成查询的 NOT %LIKE% 部分, 使用“OR”分隔多个调用, side可选值有：both, none, before, after, 默认为both
例: SqlWrapper.create().orNotLike(StrUtil.isNotEmpty(paramName), "merchant_name", paramName)
生成的sql为：OR merchant_name NOT LIKE '%{paramName}%'

groupStart

groupStart()

启动查询组, 对该组进行AND (操作, 要配合groupEnd()使用
例: SqlWrapper.create().groupStart().orNotLike(StrUtil.isNotEmpty(paramName), "merchant_name", paramName).groupEnd()
生成的sql为：AND (merchant_name NOT LIKE '%{paramName}% AND xxxx')

orGroupStart

orGroupStart()

启动查询组, 对该组进行OR (操作, 要配合groupEnd()使用
例: SqlWrapper.create().orGroupStart().orNotLike(StrUtil.isNotEmpty(paramName), "merchant_name", paramName).groupEnd()
生成的sql为：OR (merchant_name NOT LIKE '%{paramName}%')

notGroupStart

notGroupStart()

启动查询组, 对该组进行AND NOT (操作, 要配合groupEnd()使用
例: SqlWrapper.create().notGroupStart().orNotLike(StrUtil.isNotEmpty(paramName), "merchant_name", paramName).groupEnd()
生成的sql为：AND NOT (merchant_name NOT LIKE '%{paramName}%')

orNotGroupStart

orNotGroupStart()

启动查询组, 对该组进行OR NOT (操作, 要配合groupEnd()使用
例: SqlWrapper.create().orNotGroupStart().orNotLike(StrUtil.isNotEmpty(paramName), "merchant_name", paramName).groupEnd()
生成的sql为：OR NOT (merchant_name NOT LIKE '%{paramName}%')

groupEnd

groupEnd()

结束查询组
例: SqlWrapper.create().orNotGroupStart().orNotLike(StrUtil.isNotEmpty(paramName), "merchant_name", paramName).groupEnd()

groupBy

groupBy(Boolean condition, String by, boolean... escape)
groupBy(Boolean condition, String[] bys, boolean... escape)
groupBy(String by, boolean... escape)
groupBy(String[] bys, boolean... escape)

设置 GROUP BY, 排序字段，多个值以“,”分隔，如：name desc,age asc
例: SqlWrapper.create().groupBy("name desc,age asc")

having

having(String key, Object value, boolean... escape)
having(String key, boolean... escape)
having(Map<String, Object> map, boolean... escape)
having(Boolean condition, String key, Object value, boolean... escape)
having(Boolean condition, String key, boolean... escape)
having(Boolean condition, Map<String, Object> map, boolean... escape)

设置 HAVING, 用“AND”分隔多个调用
例: SqlWrapper.create().having("name", paramValue)

orHaving

orHaving(String key, Object value, boolean... escape)
orHaving(String key, boolean... escape)
orHaving(Map<String, Object> map, boolean... escape)
orHaving(Boolean condition, String key, Object value, boolean... escape)
orHaving(Boolean condition, String key, boolean... escape)
orHaving(Boolean condition, Map<String, Object> map, boolean... escape)

设置 HAVING, 用“OR”分隔多个调用
例: SqlWrapper.create().orHaving("name", paramValue)

orderBy

orderBy(Boolean condition, String orderby, String direction, boolean... escape)
orderBy(String orderby, String direction, boolean... escape)
orderBy(int seed, String direction, boolean... escape)
orderBy(String orderby, boolean... escape)

设置 ORDER BY, 排序方向，如：ASC(升序), DESC(降序) or RANDOM(随机)
例: SqlWrapper.create().orderBy("name", "asc")

build

build()
build(Class<T> className)
build(String table)

编译Select查询拼接的sql语句
例: SqlWrapper.create("tableName").orderBy("name", "asc").build()
Sql: select * from tableName order by name asc

::: tip
注：Sql构造器只有最终调用了build方法才可以生成Sql语句
:::

华为云开发者联盟

为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。

更多推荐

通过HPA+CronHPA组合应对业务复杂弹性伸缩场景

华为云开发者联盟

FT-FMEA融合混沌演练，零售运营系统韧性架构在线验证实践

华为云开发者联盟

如何使用Python和Plotly绘制3D图形

华为云开发者联盟

所有评论(0)

查看更多评论

慕容-小灰灰

@fuwei54321

已为社区贡献1条内容