Sql构造器 (SqlWrapper)

最新推荐文章于 2024-05-07 17:21:07 发布

转载最新推荐文章于 2024-05-07 17:21:07 发布 · 2.3k 阅读

2 ·

CC 4.0 BY-SA版权

原文链接：https://ace.rthinkingsoft.cn/guide/wrapper.html

文章标签：

#SqlWrapper #Java

本文详细介绍了一种SQL构造器的使用方法，包括如何构建SQL查询语句，如何使用各种方法如where、orWhere、like等来形成复杂的查询条件，以及如何进行分组、排序和聚合操作。通过实际例子展示了构造器的强大功能。

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语句
:::