<%@ page language="java" import="java.util.*" pageEncoding="UTF-8" %>
|
<%@ taglib uri="http://shiro.apache.org/tags" prefix="shiro" %>
|
<%
|
String path = request.getContextPath();
|
String basePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
|
+ path + "/";
|
%>
|
<!DOCTYPE HTML>
|
<html>
|
<head>
|
<base href="<%=basePath%>">
|
<meta name="viewport" content="width=device-width, initial-scale=1.0">
|
<meta http-equiv="X-UA-Compatible" content="ie=edge">
|
<title>运维SQL工作台</title>
|
<!--[if !IE]><!-->
|
<link href="static/plugins/font-awesome/css/font-awesome.css" rel="stylesheet" type="text/css">
|
<!--<![endif]-->
|
<!--[if gt IE 8]>
|
<link href="static/plugins/font-awesome/css/font-awesome.css" rel="stylesheet" type="text/css">
|
<![endif]-->
|
<link href="static/plugins/jquery-easyui/themes/bootstrap/easyui.css" rel="stylesheet" type="text/css">
|
<link rel="stylesheet" href="static/styles/initialize.css" type="text/css">
|
<link rel="stylesheet" href="static/styles/common.css" type="text/css">
|
<link rel="stylesheet" href="static/plugins/layer/skin/default/layer.css" type="text/css">
|
<script type="text/javascript" src="static/plugins/jquery/jquery.min.js"></script>
|
<script type="text/javascript" src="static/plugins/jquery-easyui/jquery.easyui.min.js"></script>
|
<script type="text/javascript" src="static/plugins/jquery-easyui/locale/easyui-lang-zh_CN.js"></script>
|
<script type="text/javascript" src="static/plugins/layer/layer.js"></script>
|
<script type="text/javascript" src="static/plugins/spinjs/spin.js"></script>
|
<script type="text/javascript" src="static/plugins/spinjs/jquery.spin.js"></script>
|
<script type="text/javascript" src="static/plugins/jquery.blockUI.js"></script>
|
<script type="text/javascript" src="static/wcp/scripts/wcp.js"></script>
|
<script type="text/javascript" src="static/wcp/scripts/libs/wcp.layer.js"></script>
|
<script type="text/javascript" src="static/wcp/scripts/libs/wcp.blockUI.js"></script>
|
<script type="text/javascript" src="static/wcp/scripts/libs/wcp.spin.js"></script>
|
<script type="text/javascript" src="static/wcp/scripts/libs/wcp.jquery.js"></script>
|
<script src="static/plugins/ace/src-noconflict/ace.js" type="text/javascript"></script>
|
<style>
|
html, body { height: 100%; margin: 0; font-size: 13px; }
|
#mainLayout { border: 0; }
|
.ops-side-pad { padding: 8px; }
|
#sqlText {
|
width: 100%; height: 130px; box-sizing: border-box; border: 1px solid #ddd; border-radius: 2px;
|
}
|
.ops-toolbar { padding: 8px 0; }
|
|
/* 细一点、圆角、悬停变深的滚动条,覆盖页面里所有可滚动区域(左侧表列表、结果表格横向滚动条等) */
|
#mainLayout *::-webkit-scrollbar { width: 8px; height: 8px; }
|
#mainLayout *::-webkit-scrollbar-track { background: transparent; }
|
#mainLayout *::-webkit-scrollbar-thumb { background: #c3c9d1; border-radius: 4px; }
|
#mainLayout *::-webkit-scrollbar-thumb:hover { background: #9aa3ae; }
|
|
/* 查询结果表头加粗 */
|
#resultGridWrap .datagrid-header .datagrid-cell { font-weight: bold; }
|
.ops-result-meta { color: #888; margin-left: 12px; }
|
.ops-center-wrap { height: 100%; box-sizing: border-box; padding: 8px; display: flex; flex-direction: column; }
|
#resultGridWrap { flex: 1; overflow: hidden; }
|
</style>
|
</head>
|
<body>
|
<div id="mainLayout" class="easyui-layout" style="width:100%;height:100%;">
|
<div data-options="region:'west',split:true" style="width:260px;" class="pt-tree">
|
<h3 class="pt-tree-title"><i class="fa fa-database"></i> 表</h3>
|
<ul id="tableTree"></ul>
|
</div>
|
<div data-options="region:'center',border:false" style="overflow:hidden;">
|
<div class="ops-center-wrap">
|
<pre id="sqlText">select 1</pre>
|
<div class="ops-toolbar">
|
<button class="pt-btn pt-btn-primary" onclick="opsConsole.execute()">
|
<i class="fa fa-play"></i>执行(最多返回1000行)
|
</button>
|
<span class="ops-result-meta">有选中内容时只执行选中部分,否则执行全部</span>
|
<span id="resultMeta" class="ops-result-meta"></span>
|
</div>
|
<div id="resultGridWrap">
|
<table id="dg_result" style="width:100%;height:100%;"></table>
|
</div>
|
</div>
|
</div>
|
</div>
|
|
<script type="text/javascript">
|
var sqlEditor;
|
|
var opsConsole = {
|
init: function () {
|
sqlEditor = ace.edit('sqlText');
|
sqlEditor.setTheme('ace/theme/sqlserver');
|
sqlEditor.session.setMode('ace/mode/mysql');
|
sqlEditor.setFontSize(13);
|
sqlEditor.setShowPrintMargin(false);
|
|
$('#tableTree').tree({
|
data: [],
|
//字段/索引/约束这些子节点没有 isTable 标记,点击时不当成表名去预览/查询
|
onClick: function (node) {
|
if (!node.attributes || !node.attributes.isTable) {
|
return;
|
}
|
//双击时浏览器/easyui 会先触发一次单击,这里给单击加个短延迟,双击一来就取消掉,避免单击的预览请求和双击的查询请求同时打出去
|
clearTimeout(opsConsole.previewClickTimer);
|
opsConsole.previewClickTimer = setTimeout(function () {
|
opsConsole.previewTable(node.text);
|
}, 260);
|
},
|
onDblClick: function (node) {
|
if (!node.attributes || !node.attributes.isTable) {
|
return;
|
}
|
clearTimeout(opsConsole.previewClickTimer);
|
opsConsole.queryTable(node.text);
|
},
|
//展开表节点第一次懒加载字段/索引/约束时(还没有子节点)盖个遮罩层,加载完/失败都要摘掉;
|
//已经加载过的表节点再次展开不会重新请求,也就不会重复触发这两个事件
|
onBeforeExpand: function (node) {
|
if (node.attributes && node.attributes.isTable && !$('#tableTree').tree('getChildren', node.target).length) {
|
wcp.ui.setBusy('#tableTree');
|
}
|
},
|
onLoadSuccess: function () {
|
wcp.ui.clearBusy('#tableTree');
|
},
|
onLoadError: function () {
|
wcp.ui.clearBusy('#tableTree');
|
}
|
});
|
opsConsole.loadTables();
|
},
|
|
loadTables: function () {
|
wcp.ui.setBusy("#tableTree", wcp.ajax({
|
url: 'ops/console/listTables',
|
type: 'GET',
|
success: function (tables) {
|
var data = (tables || []).map(function (t) {
|
return {id: t, text: t, iconCls: 'fa fa-table', state: 'closed', attributes: {isTable: true}};
|
});
|
$('#tableTree').tree('loadData', data);
|
//表列表加载完之后再挂url,展开某个表节点(没有children)时才会以id=表名去请求字段/索引/约束;
|
//初始化时就挂url的话,easyui会先发一次id为空的根节点请求,把上面loadData塞进去的表列表覆盖掉
|
$('#tableTree').tree('options').url = 'ops/console/nodeChildren';
|
}
|
}));
|
},
|
|
previewTable: function (tableName) {
|
wcp.ajax({
|
url: 'ops/console/previewTable',
|
type: 'GET',
|
data: {tableName: tableName},
|
success: function (rows) {
|
opsConsole.renderRows(rows || []);
|
$('#resultMeta').text('表 ' + tableName + ',返回 ' + (rows ? rows.length : 0) + ' 行(最多显示1000行)');
|
}
|
});
|
},
|
|
//双击表名:在编辑器里生成一条查询该表的语句并直接执行
|
queryTable: function (tableName) {
|
sqlEditor.setValue('select * from ' + tableName, 1);
|
opsConsole.execute();
|
},
|
|
execute: function () {
|
//有选中内容时只执行选中部分,方便编辑器里写多条语句、每次只跑其中一条;没有选中内容时才执行整个编辑器的内容
|
var sql = sqlEditor.getSelectedText();
|
if (!sql || !$.trim(sql)) {
|
sql = sqlEditor.getValue();
|
}
|
if (!sql || !$.trim(sql)) {
|
wcp.notify.error('请输入SQL');
|
return;
|
}
|
|
//UPDATE/DELETE 先预估影响行数,弹窗确认后才真正执行,避免误操作
|
if (/^\s*(update|delete)\b/i.test(sql)) {
|
wcp.ui.setBusy("body", wcp.ajax({
|
url: 'ops/console/previewAffectedRows',
|
type: 'POST',
|
contentType: 'application/x-www-form-urlencoded',
|
data: {sql: sql},
|
success: function (count) {
|
var msg = count >= 0
|
? ('此操作预计影响 <b>' + count + '</b> 行,确定要执行吗?')
|
: '未能自动预估影响行数(语句结构较复杂),仍要执行吗?';
|
layer.confirm(msg, {icon: 3, title: '执行确认'}, function (index) {
|
layer.close(index);
|
opsConsole.doExecute(sql);
|
});
|
}
|
}));
|
} else {
|
opsConsole.doExecute(sql);
|
}
|
},
|
|
doExecute: function (sql) {
|
wcp.ui.setBusy("body", wcp.ajax({
|
url: 'ops/console/execute',
|
type: 'POST',
|
contentType: 'application/x-www-form-urlencoded',
|
data: {sql: sql},
|
success: function (result) {
|
opsConsole.renderRows(result.rows || []);
|
|
var metaText;
|
if (result.statementKind === 'UPDATE_COUNT') {
|
metaText = '影响 ' + result.rowCount + ' 行,耗时 ' + result.durationMs + 'ms';
|
} else if (result.statementKind === 'EXECUTE') {
|
metaText = '执行成功,耗时 ' + result.durationMs + 'ms';
|
} else {
|
metaText = '返回 ' + result.rowCount + ' 行,耗时 ' + result.durationMs + 'ms';
|
}
|
$('#resultMeta').text(metaText);
|
}
|
}));
|
},
|
|
//把单元格内容按纯文本转义,避免字段里存的 HTML/模板片段被当成真实标签渲染出来
|
escapeHtml: function (value) {
|
if (value === null || value === undefined) {
|
return '';
|
}
|
return String(value)
|
.replace(/&/g, '&')
|
.replace(/</g, '<')
|
.replace(/>/g, '>')
|
.replace(/"/g, '"')
|
.replace(/'/g, ''');
|
},
|
|
renderRows: function (rows) {
|
var columns = rows.length ? Object.keys(rows[0]).map(function (c) {
|
return {
|
field: c, title: c, width: 150, sortable: true,
|
formatter: function (value) {
|
return opsConsole.escapeHtml(value);
|
}
|
};
|
}) : [{field: 'placeholder', title: '', width: 100}];
|
|
// 当前bundle的easyui没有loadColumns方法,每次结果列不同,直接重建表格
|
$('#resultGridWrap').html('<table id="dg_result" style="width:100%;height:100%;"></table>');
|
$('#dg_result').datagrid({
|
striped: true,
|
nowrap: true,
|
rownumbers: true,
|
singleSelect: true,
|
emptyMsg: '暂无数据',
|
columns: [columns]
|
});
|
$('#dg_result').datagrid('loadData', {total: rows.length, rows: rows});
|
}
|
};
|
|
$(function () {
|
opsConsole.init();
|
});
|
</script>
|
</body>
|
</html>
|