后台利用JFinal的Db+Record做连接数据库的工具,前端使用jQuery来动态修改网页。
前台页面如下:
<body>
<div class="am-margin">
<!-- 左边展示表名 -->
<div class="am-u-sm-2 am-u-md-2 am-scrollable-vertical" style="border:1px solid green;height:500px;">
<ul id="tables"></ul>
</div>
<!-- SQL窗口的tab -->
<div class="am-tabs am-u-sm-10 am-u-md-10" data-am-tabs>
<ul class="am-tabs-nav am-nav am-nav-tabs">
<li class="am-active"><a href="#tab1">SQL</a></li>
</ul>
<div class="am-tabs-bd">
<div class="am-tab-panel am-active" id="tab1">
<div class="am-btn-toolbar">
<div class="am-btn-group">
<button type="button" class="am-btn am-btn-primary am-radius am-margin-right-sm" οnclick="javascript:sqlexecute();">执行</button>
<button type="button" class="am-btn am-btn-primary am-radius am-margin-right-sm" οnclick="javascript:sqlformart();">格式化</button>
<button type="button" class="am-btn am-btn-primary am-radius am-margin-right-sm" οnclick="javascript:clearsqlstatement();">清空</button>
</div>
</div>
<textarea rows="4" cols="100" id="sql-statement" placeholder="请输入合法的SQL语句"></textarea>
<div id="tab" class="am-tabs am-margin-top-sm" data-am-tabs>
<ul class="am-tabs-nav am-nav am-nav-tabs">
<li class="am-active"><a href="#tab1">执行结果</a></li>
<li><a href="#tab2">结果集</a></li>
</ul>
<div class="am-tabs-bd">
<div class="am-tab-panel am-fade am-in am-active" id="tab1">
<div class="am-g am-form-group am-margin-top">
<div class="am-u-sm-12 am-u-md-12">
<div class="" id="info"></div>
</div>
</div>
</div>
<div class="am-tab-panel am-fade" id="tab2">
<div class="am-scrollable-horizontal am-scrollable-vertical">
<table id="result" class="am-table am-table-bordered am-table-striped am-text-nowrap am-hide">
<thead><tr></tr></thead>
<tbody></tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<script type="text/javascript" src="../template/assets/js/jquery.min.js"></script>
<script type="text/javascript" src="../template/assets/js/amazeui.min.js"></script>
<script type="text/javascript" src="../template/website/js/app.js"></script>
<script type="text/javascript">
//Html编码获取Html转义实体
function htmlEncode(value){
return $('<div/>').text(value).html();
}
//Html解码获取Html实体
function htmlDecode(value){
return $('<div/>').html(value).text();
}
function html2Escape(sHtml) { //Html转义
//console.info(sHtml);
if(null==sHtml){return "null";}
if(!isNaN(sHtml)){return sHtml+"";}
return sHtml.replace(/[<>]/g,function(c){
return {"<":"<",">":">"}[c];
});
}
function showStatement(data){
$("#sql-statement").val(data);
}
function sqlformart(){
var params={"sql-statement":$("#sql-statement").val()};
var reqUrl = "../h5/sql/sqlFormat";
sendYyhPostAjax(params,reqUrl,showStatement);
}
function clearsqlstatement(){
//console.info($("#sql-statement").val());
$("#sql-statement").val("");
}
function handleResult(data){
if(data.ResultCode!=1000){
$("#info").html(data.ResultCode+":"+data.Message);
return ;
}
$("#info").html("您执行的sql语句为:<span class='am-text-lg am-text-danger'>"+data.Data.sqlStatement+"</span><br/>"+data.Data.resultDesp);
var $result = $("#result");
var $headtr = $result.children("thead").children("tr");
var $body = $result.children("tbody");
$headtr.html("");
$body.html("");
$result.addClass("am-hide");
if(data.Data.result == 0){//select语句
$("#tab").tabs('open', 1);//切换到结果集
var datamap = data.Data.datamap;//[{},{}]
$result.removeClass("am-hide");
if(datamap.length>0){
var object = datamap[0];
var arr = new Array();
for(var key in object){//名称
arr.push(key);//为了保证顺序
$headtr.append("<th style='max-width:200px;' class='am-text-truncate'>"+key+"</th>");//头增加一列
}
for(var i=0;i<datamap.length;i++){ //循环所有列
var object = datamap[i];
var aline = "";
for(var j=0;j<arr.length;j++){
var content = html2Escape(object[arr[j]]);
aline+="<td style='max-width:200px;' class='am-text-truncate' title='"+content+"'>"+content+"</td>";
}
$body.append("<tr>"+aline+"</tr>");//体增加一列
}
}
}
}
function sqlexecute(){
var params={"sql-statement":$("#sql-statement").val()};//
//console.info(params);
$.ajax({
type: "post",
url: "../h5/sql/execute",
data: params,
dataType: "json",
success: function(data) {
//console.info(data);
handleResult(data);
}
});
}
var insertSelect = function(tableName){
$("#sql-statement").val("select * from "+tableName+" limit 0,100");
sqlexecute();
}
$(function(){
function showTables(data){
for(var i=0;i<data.length;i++){
var table = data[i];
for(var j in table){
//j=Tables_in_cqdzg,table[j]就是表名
//console.info(j+"===="+table[j]);
$("#tables").append("<li><a href='javascript:insertSelect(\""+table[j]+"\");'>"+table[j]+"</a></li>");
}
}
}
function initTables(){
var params = {};
var reqUrl = "../h5/sql/tables";
sendYyhPostAjax(params,reqUrl,showTables);
}
initTables();
});
</script>
</body>
@API("/sql")
public class SqlController extends BaseH5Controller{
public void sqlFormat(){
String sql = getPara("sql-statement").trim();
// System.out.println(sql);
if(StrKit.isBlank(sql)){
throw new RuntimeException("sql语句不对");
}
// sql可能是被转义的,所以反转义回来
sql = StringEscapeUtils.unescapeHtml(sql);
sql = SQLFormatter.format(sql);
ClientJsonObject cjo = new ClientJsonObject();
cjo.resultCode = ClientApiConstant.ResultCode.SUCCESS_CODE;
cjo.message = ClientApiConstant.Msg.SUCCESS;
cjo.obj = sql;
renderJson(JSON.toJSONString(cjo, true));
}
/**
*
* @Title: tables
* @Description:获取数据库中的表
*/
public void tables(){
List<Record> records = Db.find("show tables;");
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for(Record record : records){
list.add(record.getColumns());
}
ClientJsonObject cjo = new ClientJsonObject();
cjo.resultCode = ClientApiConstant.ResultCode.SUCCESS_CODE;
cjo.message = ClientApiConstant.Msg.SUCCESS;
cjo.obj = list;
renderJson(JSON.toJSONString(cjo, true));
}
/**
*
* @Title: execute
* @Description:执行SQL语句并返回结果
*/
public void execute(){
String sql = getPara("sql-statement").trim();
// System.out.println(sql);
if(StrKit.isBlank(sql)){
throw new RuntimeException("sql语句不对");
}
// sql可能是被转义的,所以反转义回来
sql = StringEscapeUtils.unescapeHtml(sql);
// System.out.println(sql);
ClientJsonObject cjo = new ClientJsonObject();
cjo.resultCode = ClientApiConstant.ResultCode.SUCCESS_CODE;
cjo.message = ClientApiConstant.Msg.SUCCESS;
if(sql.toUpperCase().indexOf("SELECT") == -1){// 说明是非select语句,insert,update,delete
String statement = sql.substring(0, sql.indexOf(" "));
int rows = Db.update(sql);
JSONObject object = new JSONObject();
object.put("result", 1);// 表示非select语句
object.put("resultDesp", statement + ",共影响 " + rows + " 条记录");// 信息
object.put("sqlStatement", sql);// 返回sql语句
cjo.obj = object;
renderJson(JSON.toJSONString(cjo, true));
return;
}
List<Record> records = Db.find(sql);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for(Record record : records){
list.add(record.getColumns());
}
JSONObject object = new JSONObject();
object.put("result", 0);// 表示select语句
object.put("resultDesp", "select语句,共 " + list.size() + " 条记录");// 信息
object.put("sqlStatement", sql);// 返回sql语句
object.put("datamap", list);// 把数据输出
cjo.obj = object;
renderJson(JSON.toJSONString(cjo, true));
}
}
效果: