【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 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 查询分析器的功能主要是开发和运维人员使用,应急处理数据问题。


本文完。

  • 8
    点赞
  • 18
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值