使用js+jquery编写一简单的数据库查询界面（单jsp界面完成）

本文链接：https://blog.youkuaiyun.com/woaiweihai119/article/details/45096077

最近头儿让写一个类似于navicat可视化工具的查询界面，要求在单一jsp界面完成，由于本人水平有限，用了好几天才搞定，现把代码记录于此，以便日后查阅。

知识点：jqueryajax、jdbc数据库连接。

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ page language="java" import="java.sql.*"%>
<%!
//获取table方法
public String getInfo(String str){
   String sp="|";
   String str1="";
   try{
       Class.forName("com.mysql.jdbc.Driver").newInstance();
       String newurl = "jdbc:mysql://127.0.0.1:3306/"+str+"?user=root&password=admin&useUnicode=true&characterEncoding=UTF-8";
       Connection conn = DriverManager.getConnection(newurl);
       Statement stmt = conn
               .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                       ResultSet.CONCUR_UPDATABLE);
       String sql = "show tables";
       ResultSet newrs = stmt.executeQuery(sql);
       while(newrs.next()){
           str1+=newrs.getString(1)+sp;
       }

   }catch(Exception ex) {

   }
   return str1;
}
//获取sql查询的结果集方法
   public String getResult(String dbName,String tsql){
       String str1="";
       StringBuffer strbuf=new StringBuffer();
       try{
           String newurl = "jdbc:mysql://127.0.0.1:3306/"+dbName+"?user=root&password=admin&useUnicode=true&characterEncoding=UTF-8";
           Connection conn=DriverManager.getConnection(newurl);
           Statement stmt = conn
                   .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                           ResultSet.CONCUR_UPDATABLE);
           String sql = tsql;
           ResultSet newrs = stmt.executeQuery(sql);
          int columnCount=newrs.getMetaData().getColumnCount();
           //检索此 ResultSet 对象的列的编号、类型和属性。
           ResultSetMetaData rsmd=newrs.getMetaData();
           //获取当前列数
           int colCount=rsmd.getColumnCount();
           //获取表字段名
           strbuf.append("<tr>");
           for(int j=1;j<=colCount;j++){
               String name=rsmd.getColumnName(j);
               strbuf.append("<td>"+name+"</td>");
           }
           strbuf.append("</tr>");
           //获取数据
           while(newrs.next()){
               strbuf.append("<tr>");
               for(int i=1;i<=colCount;i++){
                    strbuf.append("<td>"+newrs.getString(i)+"</td>");

               }
               strbuf.append("</tr>");
           }
           str1=strbuf.toString();
           newrs.close();
       }catch(Exception exception){

       }
       return str1;
   }
%>
<%
//获取所有数据库名
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   String url = "jdbc:mysql://127.0.0.1:3306/test?user=root&password=admin&useUnicode=true&characterEncoding=UTF-8";
   Connection conn = DriverManager.getConnection(url);
   Statement stmt = conn
           .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                   ResultSet.CONCUR_UPDATABLE);
   String sql = "show databases";
   ResultSet rs = stmt.executeQuery(sql);
%>

<%
//返回某具体数据库下所有table
String act = request.getParameter("method");
String param = request.getParameter("param");
String mesg="";//tablename1,tablename2,....
if(act!=null&&act.equals("disInfo")){
mesg = getInfo(param) ;
   response.getWriter().write("\""+mesg+"\"");
   return;
}

//返回具体数据结果集
String dbName = request.getParameter("dbName");
String tsql = request.getParameter("tsql");
String res="";
if(dbName != null && dbName.equals("terplatform")){
   res=getResult(dbName, tsql);
   response.getWriter().write("\""+res+"\"");
   return;
}

%>
<html>
<head>
<script type="text/javascript" src="jquery-1.8.3.min.js"></script>
<link rel="stylesheet" type="text/css" href="databaseUI.css" />
<style type="text/css">
.buttonb {
    display: inline-block;
    zoom: 1; /* zoom and *display = ie7 hack for display:inline-block */
    *display: inline;
    vertical-align: baseline;
    margin: 0 2px;
    outline: none;
    cursor: pointer;
    text-decoration: none;
    font: 14px/100% Arial, Helvetica, sans-serif;
    padding: .5em 2em .55em;
    text-shadow: 0 1px 1px rgba(0,0,0,.3);
    -webkit-border-radius: .5em;
    -moz-border-radius: .5em;
    border-radius: .5em;
    -webkit-box-shadow: 0 1px 2px rgba(0,0,0,.2);
    -moz-box-shadow: 0 1px 2px rgba(0,0,0,.2);
    box-shadow: 0 1px 2px rgba(0,0,0,.2);
}
</style>

<script type="text/javascript">
       function selectPrint(value){

           var url="jdbc:mysql://127.0.0.1:3306/"+value+"?user=root&password=admin&useUnicode=true&characterEncoding=UTF-8";
           //window.location.href="databaseUIF.jsp?url="+url;
       }

   </script>
</head>

<body>

   <div id="root">
       <div id="head">
           <div id="select">
               <input type="button" id="select1" value="查询" οnclick="doClick()"/>
           </div>
       </div>
       <div id="leftup">
           <div id="textareaa">
               <textarea id="textsql" ></textarea>
           </div>
           <div id="lefttab" style="overflow:auto">
               <table id="leftId" class="gridtable">

               </table>
           </div>
       </div>
       <div id="right">
           <div id="righttest">
                   <SELECT id="select"  >
                           <option id="sj" selected="selected">--数据库列表--</option>
                   <%
                       while(rs.next()){
                   %>
                           <OPTION οnclick="addToDivInfo('<%=rs.getString(1) %>')"   ><%=rs.getString(1) %></OPTION>
                   <%
                       }
                   %>

               </SELECT>
                    <input type="hidden" id="totals" name="totals" ></input>
                   <%
                       rs.close();
                       stmt.close();
                       conn.close();
                   %>
           </div>
           <div id="righttab" style="overflow: auto;">

           </div>
       </div>

   </div>
</body>
</html>
<script language="javaScript">
    var act = "disInfo" ;
   var aid = "table";
   function addToDivInfo(param){
       act = "disInfo" ;
        aid = param;
       getInfo(act,param);
   }

    function getInfo(act,param){
        $.ajax({
           url:"databaseUI.jsp",
           type:"post",
           dataType:"text",
           data:{"method":act,"param":param},
           success:function(m){
               var dataObj = eval(m);
              var data=dataObj.split("|");
              var insertT="";
              for(var i=0;i<data.length;i++){
                  insertT+="<tr><td οnclick=\"clicktab('"+data[i]+"')\" >"+data[i] +"</td></tr>";
              }
              //$("#tableId").empty().append(insertT);
              document.getElementById("righttab").innerHTML = "<table class=\"gridtable\">"+insertT+"</table>";

           }
        });
    }
    function clicktab(tdId){
       var textareaobj=document.getElementById("textsql");
       textareaobj.value="select * from "+tdId;
    }

    function doClick(){
       var textsql=document.getElementById("textsql").value; //JS获取值方式。

       var getM=textsql.trim().substring(0,6);
       //getM.toLowerCase();//忽略大小写的方法，但无效
       if(textsql == '' ){
           alert("请选择具体的数据库，并输入查询语句！");
           return;
       }else if(getM !='select' ){
           alert("只允许进行查询！！");
           return;
       }
       var flag=0;
       flag += textsql.indexOf("--") + 1;
        if(flag != 0){
           alert("语句中含有非法字符，请检查！");
           return;
        }

       var dbName="terplatform";

        $.ajax({
           url:"databaseUI.jsp",
           type:"post",
           dataType:"text",
           data:{"dbName":dbName,"tsql":textsql},
           success:function(m){
               var dataob=eval(m);
               $("#leftId").empty().append(dataob);
           }
        });

   }
</script>