[【JSON2WEB】 13 基于REST2SQL 和 Amis 的 SQL 查询分析器

【JSON2WEB】01 WEB管理信息系统架构设计

【JSON2WEB】02 JSON2WEB初步UI设计

【JSON2WEB】03 go的模板包html/template的使用

【JSON2WEB】04 amis低代码前端框架介绍

【JSON2WEB】05 前端开发三件套 HTML CSS JavaScript 速成

【JSON2WEB】06 JSON2WEB前端框架搭建

【JSON2WEB】07 Amis可视化设计器CRUD增删改查

【JSON2WEB】08 Amis的事件和校验

【JSON2WEB】09 Amis-editor的代码移植到json2web

【JSON2WEB】10 基于 Amis 做个登录页面login.html

【JSON2WEB】11 基于 Amis 角色功能权限设置页面

【JSON2WEB】 12基于Amis-admin的动态导航菜单树

关于数据的问题没有一句SQL不能解决的，如果有就两句。
我设计开发的所有信息系统 内嵌 SQL 查询分析器是标配，可执行CRUD的操作，也就是SQL的Insert、Select、Update、Delete操作。号称SQL的四大基本操作。

SQL才是做完美的存在。BS前端就不吐槽了，知识点太多，比上下五千年的历史事件还多，比地球上的乡村还多。

1 数据库端设计

针对Oracle数据设计，创建2个视图。

1.1 用户表视图

create or replace view user_tabs as
select t.table_name, tab_cnt(t.table_name) as row_count ,c.comments
from user_all_tables t --用户表视图
left join user_tab_comments c on c.table_name = t.table_name
order by t.table_name;

1.2 用户表和视图的视图

create or replace view user_table_view_v as
select table_name as s_name,comments, 'TABLE' as s_type from user_tabs
union all -- 用户表和视图的视图
select view_name as s_name,'' as comments, 'VIEW' as s_type from user_views
;

2 REST2SQL的微调

只修改一下 REST2SQL - dothing.go 文件的 doSQL()函数即可。

2.1 doSQL()的代码

// 根据请求参数执行不同的SQL操作 //
func doSQL(w http.ResponseWriter, req map[string]interface{}) {//w.Write([]byte("\ndoSQL()\n"))//w.Write([]byte("\"data\":"))//资源名sql语句resSQL := req["ResName"].(string)fmt.Println("SQL://", resSQL)sqlToUpper := strings.ToUpper(resSQL)sql6 := sqlToUpper[:6]var result stringswitch sql6 {case "SELECT":result = Icrud.SelectData(resSQL)case "INSERT":result = "[" + Icrud.InsertData(resSQL) + "]"case "UPDATE":result = "[" + Icrud.UpdateData(resSQL) + "]"case "DELETE":result = "[" + Icrud.DeleteData(resSQL) + "]"default:// 过滤sql ，只能执行 SELECT INSERT UPDATE DELETEresult = "\"只能执行 SELECT INSERT UPDATE DELETE\""}logger.Alog(true, fmt.Sprint("SQL:", resSQL))// w.Write([]byte(result))// w.Write([]byte("}"))var dataset []map[string]interface{}// json串反序列化err := json.Unmarshal([]byte(result), &dataset)if err != nil {fmt.Println("Error:", err)return}// 返回数据rows := make(map[string]interface{})rows["rows"] = dataset//data := make(map[string]interface{})rw := returnMap()rw["data"] = rows// 输出到 http.ResponseWriterhttpResWriter(w, rw)
}

重点就是所有的查询返回结果统一化。

2.2 SELECT 的返回结果

http://127.0.0.1:5217/sql/select * from s_role

{"data": {"rows": [{"P_ID": "R010","S_NAME": "开发测试role","S_NOTE": null,"TIME_STAMP": null},{"P_ID": "R100","S_NAME": "测试10","S_NOTE": "1000","TIME_STAMP": "2024-04-09T17:08:42.794Z"},{"P_ID": "R020","S_NAME": "系统管理","S_NOTE": null,"TIME_STAMP": null},{"P_ID": "R030","S_NAME": "静态数据","S_NOTE": null,"TIME_STAMP": null},{"P_ID": "R040","S_NAME": "动态数据","S_NOTE": null,"TIME_STAMP": "2024-04-09T17:21:18.603Z"},{"P_ID": "R050","S_NAME": "管输排产","S_NOTE": null,"TIME_STAMP": null},{"P_ID": "S520","S_NAME": "BS静态数据","S_NOTE": null,"TIME_STAMP": "2024-04-08T15:39:58.489Z"},{"P_ID": "S510","S_NAME": "BS系统管理","S_NOTE": null,"TIME_STAMP": "2024-04-10T15:33:15.739Z"}]},"msg": "","status": 0
}

2.3 INSERT 的返回结果

http://127.0.0.1:5217/sql/insert into s_role (p_id,s_name) values (‘RRR’,‘测试数据随后就删除’)

{"data": {"rows": [{"Insert rowsAffected": 1}]},"msg": "","status": 0
}

2.4 UPDATE 的返回结果

http://127.0.0.1:5217/sql/update s_role set s_name = ‘SSSS’ where p_id = ‘RRR’

{"data": {"rows": [{"Update rowsAffected": 1}]},"msg": "","status": 0
}

2.5 DELETE 的返回结果

http://127.0.0.1:5217/sql/delete s_role where p_id = ‘RRR’

{"data": {"rows": [{"Delete rowsAffected": 1}]},"msg": "","status": 0
}

3 基于Amis-Editor的 SQL 查询分析器页面

3.1 页面布局

左边栏列出用户表和视图，右上未 SQL 输入编辑区域，右下为 SQL 执行结果JSON输出区域。

3.2 左边栏

放一个 crud2 设置及代码如下：

{"type": "crud2","id": "u:2350a7316069","mode": "table2","dsType": "api","syncLocation": true,"primaryField": "s_name","loadType": "","api": {"url": "http://127.0.0.1:5217/rest/user_table_view_v","method": "get"},"filter": {"type": "form","title": "表或视图查询","mode": "inline","columnCount": 1,"clearValueOnHidden": true,"behavior": ["SimpleQuery"],"body": [{"name": "S_NAME","label": "名称","type": "input-text","size": "full","required": false,"behavior": "SimpleQuery","id": "u:120b4ef2515e"},{"name": "COMMENTS","label": "备注","type": "input-text","size": "full","required": false,"behavior": "SimpleQuery","id": "u:201a4c336663"}],"actions": [{"type": "reset","label": "重置","id": "u:1c5e9794de0a"},{"type": "submit","label": "查询","level": "primary","id": "u:4bd7edd74974"}],"id": "u:714d678e7896","feat": "Insert"},"headerToolbar": [{"type": "flex","direction": "row","justify": "flex-start","alignItems": "stretch","items": [{"type": "container","align": "left","behavior": ["Insert","BulkEdit","BulkDelete"],"body": [{"type": "tpl","tpl": "鼠标双击行可以生成 SELECT 查询语句","inline": true,"wrapperComponent": "","id": "u:81c3176e9478"}],"wrapperBody": false,"id": "u:e754ddc5a2a5"},{"type": "container","align": "right","behavior": ["FuzzyQuery"],"body": [],"wrapperBody": false,"id": "u:81f562752dea"}],"id": "u:21f94630ae3e","isFixedHeight": false,"isFixedWidth": false}],"footerToolbar": [{"type": "flex","direction": "row","justify": "flex-start","alignItems": "stretch","style": {"position": "static"},"items": [{"type": "container","align": "left","body": [],"wrapperBody": false,"style": {"flexGrow": 1,"flex": "1 1 auto","position": "static","display": "flex","flexBasis": "auto","flexDirection": "row","flexWrap": "nowrap","alignItems": "stretch","justifyContent": "flex-start"},"id": "u:ad6a4c49cf11"},{"type": "container","align": "right","body": [{"type": "pagination","behavior": "Pagination","layout": ["total","perPage","pager"],"perPage": 50,"perPageAvailable": [10,20,50,100,500],"align": "right","id": "u:0fac899d9be2"}],"wrapperBody": false,"style": {"flexGrow": 1,"flex": "1 1 auto","position": "static","display": "flex","flexBasis": "auto","flexDirection": "row","flexWrap": "nowrap","alignItems": "stretch","justifyContent": "flex-end"},"id": "u:1307d8ea5a6b"}],"id": "u:c01dcade7257"}],"columns": [{"type": "tpl","title": "名称","name": "S_NAME","id": "u:e8cb1c25ee76","placeholder": "-"},{"type": "tpl","title": "备注","name": "COMMENTS","id": "u:d09e124643fc","placeholder": "-"},{"type": "tpl","title": "类型","name": "S_TYPE","id": "u:d25820de66dd","placeholder": "-"}],"editorSetting": {"mock": {"enable": true,"maxDisplayRows": 5}},"loadDataOnce": true,"onEvent": {"rowDbClick": {"weight": 0,"actions": [{"componentId": "u:e92cb0a1b8d7","ignoreError": false,"actionType": "setValue","args": {"value": "${\"select * from \" + event.data.item.S_NAME}"}}]}},"matchFunc": ""
}

这里重点是加了一个行双击事件生成Select查询语句并赋值给 SQL 编辑框。

"onEvent": {"rowDbClick": {"weight": 0,"actions": [{"componentId": "u:e92cb0a1b8d7","ignoreError": false,"actionType": "setValue","args": {"value": "${\"select * from \" + event.data.item.S_NAME}"}}]}}

3.3 右上区域

先放一个面板panel，再在面板的内容去放一多行文本框即可，相关设置代码如下：

{"type": "panel","title": "SQL编辑器","body": [{"type": "textarea","label": "","name": "sql","id": "u:e92cb0a1b8d7","minRows": 6,"maxRows": 10,"value": "select sysdate from dual","validateOnChange": false,"clearable": true}],"id": "u:9d0e47e56bfb","affixFooter": true,"actions": [{"type": "button","label": "执行","onEvent": {"click": {"actions": [{"componentId": "","ignoreError": false,"actionType": "ajax","args": {},"outputVar": "responseResult","options": {},"api": {"url": "http://127.0.0.1:5217/sql/${sql}","method": "get","requestAdaptor": "","adaptor": "","messages": {}}},{"componentId": "u:505178345a09","ignoreError": false,"actionType": "setValue","args": {"value": "${event.data.responseResult.responseData|json}"}}]}},"id": "u:6ded52fac842","size": "lg","block": false,"level": "warning","themeCss": {"className": {"font:default": {"fontSize": "var(--fonts-size-7)","text-align": "center"},"padding-and-margin:default": {}}}},{"type": "button","label": "复制SQL","onEvent": {"click": {"actions": [{"ignoreError": false,"actionType": "copy","args": {"copyFormat": "text/plain","content": "${sql}"}}]}},"id": "u:eee1cae708cb","themeCss": {"className": {"padding-and-margin:default": {}}},"block": false,"size": "md","level": "info"},{"type": "button","label": "复制结果JSON","onEvent": {"click": {"actions": [{"ignoreError": false,"actionType": "copy","args": {"copyFormat": "text/plain","content": "${result}"}}]}},"id": "u:a2396ccdb96b","level": "success"}],"subFormMode": "","actionsClassName": "m-xs","className": "Panel--info","headerClassName": "","bodyClassName": "m-xs","footerClassName": "","subFormHorizontal": {"leftFixed": "normal"}
}

这里重点是执行按钮上的2个点击事件，第一个是数据请求，这里要调用REST2SQL 的API,参数就是SQL 多行文本框输入的全部内容，第二个事件是把返回结果赋值给执行结果JSON的多行问文本框：

"type": "button","label": "执行","onEvent": {"click": {"actions": [{"componentId": "","ignoreError": false,"actionType": "ajax","args": {},"outputVar": "responseResult","options": {},"api": {"url": "http://127.0.0.1:5217/sql/${sql}","method": "get","requestAdaptor": "","adaptor": "","messages": {}}},{"componentId": "u:505178345a09","ignoreError": false,"actionType": "setValue","args": {"value": "${event.data.responseResult.responseData|json}"}}]}}

另 ： 【复制SQL】按钮的事件就是把 sql 多行文本框的内容复制到剪贴板；

"onEvent": {"click": {"actions": [{"ignoreError": false,"actionType": "copy","args": {"copyFormat": "text/plain","content": "${sql}"}}]}}

【复制结果JSON】按钮的事件就是把 result 多行文本框的内容复制到剪贴板；

"onEvent": {"click": {"actions": [{"ignoreError": false,"actionType": "copy","args": {"copyFormat": "text/plain","content": "${result}"}}]}}

3.4 右下区域

先放一个面板panel，面板的内容区域放一个多行文本框：

{"type": "panel","title": "执行结果JSON","body": [{"type": "textarea","label": "","name": "result","id": "u:505178345a09","minRows": 17,"maxRows": 30}],"id": "u:dac15e24efb3","affixFooter": false,"className": "Panel--success","bodyClassName": "m-xs"
}

4 实操演练

4.1 SELECT

4.2 INSERT

4.3 UPDATE

4.4 DELETE

SQL 查询分析器的功能主要是开发和运维人员使用，应急处理数据问题。

本文完。