jingruixiang a900a8a71a snakerflow 1 год назад
..
src a900a8a71a snakerflow 1 год назад
README.md a900a8a71a snakerflow 1 год назад
pom.xml a900a8a71a snakerflow 1 год назад

README.md

通用查询服务

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

简单查询分析

主键查询

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 有配置样例