仅仅只是人手不足，想偷懒，简单地设计一下通用查询。这里先简单的描述一下场景：
- 当你作为甲方有n个业务系统，n个业务库，然后所有厂商开发人员没办法或者已经没有人手进行二次开发的时候；
- 现在某一些新进来厂商开发的系统需要读业务库的数据
- 直接给数据库访问权限不是很现实，所以需要写接口
- 但是没人可以写了，怎么办？
- 然后这个可能就有一丢丢用了
- 通过定义一些获取数据的规范，让开发人员可以方便的读取数据
- 还能当网关使，后续可以加上鉴权/限流/监控各种
简单查询分析
主键查询
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 = ?
说明：
{dbName} 为配置的多数据源key，约等于数据库名
{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 ?,?
说明：
{dbName} 为配置的多数据源key，约等于数据库名
{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 ?,?
说明：
{dbName} 为配置的多数据源key，约等于数据库名
{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 |
关于多数据源
需要进行多数据源配置，这里略。
截图
代码实现
- 操作符枚举类-代码片段
public enum OperateTypeEnum {
EQ("等于","=?"),
NE("不等于","<>?"),
GT("大于",">"),
GE("大于等于",">=?"),
LT("小于","<?"),
LE("小于等于","<=?"),
BT("区间范围","between ? and ?"),
NBT("非区间范围","not between ? and ?"),
LIKE("模糊","like ?"),
LLIKE("左模糊","like ?"),
RLIKE("右模糊","like ?"),
IN("包含","in"),
NIN("不包含","not in(?)"),
OR("或", "or (?)")
;
OperateTypeEnum(String name,String value) {
this.name = name;
this.value = value;
}
private String name;
private String value;
}
- 构建条件方法-代码片段
/**
* 构建动态条件
*
* @param sb
* @param map
* @return
*/
private List<Object> buildCondition(StringBuilder sb, Map<String, Object> map) {
int i = 0;
List<Object> params = new ArrayList<>();
Set<Map.Entry<String, Object>> set = map.entrySet();
Iterator<Map.Entry<String, Object>> iterator = set.iterator();
while (iterator.hasNext()) {
// 只处理key值满足m_EQ_xxx 类的参数
Map.Entry<String, Object> entity = iterator.next();
String key = entity.getKey();
Object value = entity.getValue();
String[] arr = key.split("_");
if (!key.startsWith("m_") || arr.length < 3 || value == null) {
// 只处理m_前辍的参数
continue;
}
OperateTypeEnum ops[] = OperateTypeEnum.values();
// 默认等值
OperateTypeEnum operateType = OperateTypeEnum.EQ;
for (OperateTypeEnum item : ops) {
// 匹配查询操作
if (item.name().equals(arr[1].toUpperCase())) {
operateType = item;
break;
}
}
String column = String.join("_", Arrays.asList(arr).subList(2, arr.length));
switch (operateType) {
case EQ: case NE: case GT: case GE: case LT: case LE:
if (i == 0) {
sb.append(" where ").append(column).append(" ").append(operateType.getValue()).append(" ");
} else {
sb.append(" and ").append(column).append(" ").append(operateType.getValue()).append(" ");
}
params.add(value);
i++;
break;
case BT: case NBT:
if (value instanceof Collection<?>) {
Collection<?> collection = (Collection<?>) value;
if (collection.size() == 2) {
if (i == 0) {
sb.append(" where ").append(column).append(" ").append(operateType.getValue()).append(" ");
} else {
sb.append(" and ").append(column).append(" ").append(operateType.getValue()).append(" ");
}
params.addAll(collection);
i++;
}
}
break;
case LIKE:
if (i == 0) {
sb.append(" where ").append(column).append(" like ?");
} else {
sb.append(" and ").append(column).append(" like ?");
}
params.add("%"+value+"%");
i++;
break;
case LLIKE:
if (i == 0) {
sb.append(" where ").append(column).append(" like ?");
} else {
sb.append(" and ").append(column).append(" like ?");
}
params.add("%"+value);;
i++;
break;
case RLIKE:
if (i == 0) {
sb.append(" where ").append(column).append(" like ?");
} else {
sb.append(" and ").append(column).append(" like ?");
}
params.add(value+"%");
i++;
break;
case IN: case NIN:
if (value instanceof Collection<?>) {
Collection<?> collection = (Collection<?>) value;
if (!collection.isEmpty()) {
if (i == 0) {
sb.append(" where ").append(column).append(" in(");
} else {
sb.append(" and ").append(column).append(" in(");
}
for (int j = 0, len = collection.size(); j < len; j++) {
if (j < len - 1) {
sb.append("?,");
} else {
sb.append("?)");
}
}
params.addAll(collection);
}
}
break;
}
}
return params;
}
源码
小结
本文只是实现了简单的单表查询,基本上可以满足大多数查询需求，后续优化方向：
- 看是否有必要支持多表-其实简单撸一下视图，当单表使也ok。
- 目前只有AND查询，看是否加入OR等查询条件
- 多数据源配置
- 管理后台
- 数据源管理
- 数据权限管理
