【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 string
switch 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 DELETE
result = "\"只能执行 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.ResponseWriter
httpResWriter(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 查询分析器的功能主要是开发和运维人员使用，应急处理数据问题。
本文完。
![NL2SQL基础系列(1)：业界顶尖排行榜、权威测评数据集及LLM大模型（Spider vs BIRD）全面对比优劣分析[Text2SQL、Text2DSL]](https://img-blog.csdnimg.cn/img_convert/97fd17e668d92df0cb1a53031a86fb34.jpeg)
