一个类似阿里云RDS的网页数据库管理系统

最新推荐文章于 2023-10-05 14:06:35 发布

恒奇恒毅

最新推荐文章于 2023-10-05 14:06:35 发布

阅读量1.3k

点赞数 2

分类专栏：数据库

本文链接：https://blog.csdn.net/xxssyyyyssxx/article/details/54348590

版权

数据库专栏收录该内容

43 篇文章 3 订阅

订阅专栏

后台利用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));
    }
}

效果：