mldong/mldong-data-service

通用查询服务

仅仅只是人手不足，想偷懒，简单地设计一下通用查询。

简单查询分析

主键查询

select * from sys_menu where id = ?

等于

select * from sys_menu where parent_id = ?

不等于

select * from sys_menu where parent_id <> ?

大于

select * from sys_menu where sort > ?

大于等于

select * from sys_menu where sort >= ?

小于

select * from sys_menu where sort < ?

小于等于

select * from sys_menu where sort <= ?

区间范围

select * from sys_menu where id between ? and ?

非区间范围

select * from sys_menu where id not between ? and ?

模糊(全/左/右)

select * from sys_menu where name like ?

值集合

select * from sys_menu where id in (?,?,?)

非值集合

select * from sys_menu where id not in (?,?,?)

组合查询(and)

select * from sys_menu where parent_id = ? and id between ? and ?

入参设计

主键查询

请求地址1：/ds/{tableName}

请求地址2：/ds/{dbName}/{tableName}

入参：

{
    "pk_id":1
}

最终生成sql==>

select * from {tableName} where id = ?

说明：

{tableName}为要查询的表

pk_为参数前辍，之后的为数据库表主键列名

curl样例：

curl -X POST -H  "Accept:*/*" -H  "Request-Origion:Knife4j" -H  "Content-Type:application/json" -d "{\"pk_id\":1}" "http://localhost:8888/ds/sys_menu"

返回结果：

{
  "code": 0,
  "msg": "操作成功",
  "data": {
    "id": 1,
    "parent_id": 0,
    "name": "系统设置",
    "sort": 10,
    "route_name": "sys",
    "icon": "sys",
    "is_show": 2,
    "create_time": "2020-06-25 21:05:01",
    "update_time": "2020-09-08 15:31:18",
    "is_deleted": 1
  }
}

常规查询

请求地址1：/ds/{tableName}?pageNum=1&pageSize=10

请求地址2：/ds/{dbName}/{tableName}?pageNum=1&pageSize=10

入参：

{
	"m_EQ_parent_id": 0
}

最终生成sql==>

select * from {tableName} where parent_id = ? limit ?,?

说明：

{tableName}为要查询的表

m_为参数前辍

EQ/BT为操作符，对应关系见下表，之后的为数据库表列名

pageNum为当前页，默认1

pageSize为每页大小，默认10

curl样例：

curl -X POST -H  "Accept:*/*" -H  "Request-Origion:Knife4j" -H  "Content-Type:application/json" -d "{\"m_EQ_parent_id\":0}" "http://localhost:8888/ds/sys_menu?pageNum=1&pageSize=10"

返回结果：

{
  "code": 0,
  "msg": "操作成功",
  "data": {
    "pageNum": 1,
    "pageSize": 10,
    "recordCount": 5,
    "totalPage": 1,
    "rows": [
      {
        "id": 1,
        "parent_id": 0,
        "name": "系统设置",
        "sort": 10,
        "route_name": "sys",
        "icon": "sys",
        "is_show": 2,
        "create_time": "2020-06-25 21:05:01",
        "update_time": "2020-09-08 15:31:18",
        "is_deleted": 1
      },
      {
        "id": 6,
        "parent_id": 0,
        "name": "内容管理",
        "sort": 11,
        "route_name": "cms",
        "icon": "cms",
        "is_show": 2,
        "create_time": "2020-06-25 21:09:05",
        "update_time": "2020-06-29 09:31:58",
        "is_deleted": 1
      },
      {
        "id": 10,
        "parent_id": 0,
        "name": "订单管理",
        "sort": 12,
        "route_name": "oms",
        "icon": "oms",
        "is_show": 2,
        "create_time": "2020-06-25 21:11:29",
        "update_time": "2020-06-29 09:31:58",
        "is_deleted": 1
      },
      {
        "id": 13,
        "parent_id": 0,
        "name": "商品管理",
        "sort": 13,
        "route_name": "pms",
        "icon": "pms",
        "is_show": 2,
        "create_time": "2020-06-25 21:14:02",
        "update_time": "2020-07-10 10:31:46",
        "is_deleted": 1
      },
      {
        "id": 17,
        "parent_id": 0,
        "name": "vhhg",
        "sort": 10,
        "route_name": "fgh",
        "is_show": 2,
        "create_time": "2020-07-04 09:18:45",
        "update_time": "2020-09-04 22:25:26",
        "is_deleted": 2
      }
    ]
  }
}

组合查询

请求地址1：/ds/{tableName}?pageNum=1&pageSize=10

请求地址2：/ds/{dbName}/{tableName}?pageNum=1&pageSize=10

入参：

{
	"m_EQ_parent_id": 0,
	"m_BT_create_time": ["2020-06-25","2020-06-25 21:11:29"]
}

最终生成sql==>

select * from {tableName} where parent_id = ? and create_time between ? and ? limit ?,?

说明：

{tableName}为要查询的表

m_为参数前辍

EQ/BT为操作符，对应关系见下表，之后的为数据库表主键列名

pageNum为当前页，默认1

pageSize为每页大小，默认10

curl样例：

curl -X POST -H  "Accept:*/*" -H  "Request-Origion:Knife4j" -H  "Content-Type:application/json" -d "{\"m_EQ_parent_id\":0,\"m_BT_create_time\":[\"2020-06-25\",\"2020-06-25 21:11:29\"]}" "http://localhost:8888/ds/sys_menu?pageNum=1&pageSize=10"

返回结果：

{
  "code": 0,
  "msg": "操作成功",
  "data": {
    "pageNum": 1,
    "pageSize": 10,
    "recordCount": 3,
    "totalPage": 1,
    "rows": [
      {
        "id": 1,
        "parent_id": 0,
        "name": "系统设置",
        "sort": 10,
        "route_name": "sys",
        "icon": "sys",
        "is_show": 2,
        "create_time": "2020-06-25 21:05:01",
        "update_time": "2020-09-08 15:31:18",
        "is_deleted": 1
      },
      {
        "id": 6,
        "parent_id": 0,
        "name": "内容管理",
        "sort": 11,
        "route_name": "cms",
        "icon": "cms",
        "is_show": 2,
        "create_time": "2020-06-25 21:09:05",
        "update_time": "2020-06-29 09:31:58",
        "is_deleted": 1
      },
      {
        "id": 10,
        "parent_id": 0,
        "name": "订单管理",
        "sort": 12,
        "route_name": "oms",
        "icon": "oms",
        "is_show": 2,
        "create_time": "2020-06-25 21:11:29",
        "update_time": "2020-06-29 09:31:58",
        "is_deleted": 1
      }
    ]
  }
}

操作符说明

操作名	说明	传参类型
EQ	等于=	string
NE	不等于<>	string
GT	大于>	string
GE	大于等于>=	string
LT	小于<	string
LE	小于等于<=	string
BT	between ? and ?	array
NBT	not between ? and ?	array
LIKE	like '%值%'	string
NLIKE	not like '%值%'	string
LLIKE	like '%abc'	string
RLIKE	like 'abc%'	string
IN	in(?,?)	array
NIN	not in(?,?)	array

关于多数据源

src/main/java/com/mldong/config/DsConfig.java 有配置样例