JSP实现MySQL数据结构查询

程序员文章站 2022-06-06 12:36:03

...

流程简介:

                                 login                                use                    describe

                    登录<----------->选择数据库<--------->选择表<---------->显示表结构

                                logout                              back                      back

0.数据库处理的javabean:(我厚道吧,这都贴出来了)

MySQLmeans.java(位置:MySQL/mysqlmeans.java):

package MySQL;
import java.sql.*;
/**
*
* @author lucifer
*/

public class mysqlmeans {
     Connection con;
    String username = "";
    String passWord = "";
     String server = "";
     String dbname = "";
     public void setPRoperties(String serv,String db,String name,String pass){
          server = serv;
          dbname = db;
          username = name;
          password = pass;
     }

     public void setUserName(String username){
          this.username = username;
     }
     public String getUserName(){
          return username;
     }

     public void setPassword(String password){
          this.password = password;
     }
     public String getPassword(){
          return password;
     }

     public void setServer(String server){
          this.server = server;
     }
     public String getServer(){
          return server;
     }

     public void setDataBase(String daname){
          this.dbname = daname;
     }
     public String getDataBase(){
          return dbname;
     }

     public Connection getConnection(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection
                       ("jdbc:mysql://"+server+"/"+dbname+"?user="+username+"&password="+password, username, password);
            return con;
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public void exeUpdate(String sql){
        Connection upCon = getConnection();
        try{
            Statement stmt = upCon.createStatement();
            stmt.executeUpdate(sql);
            stmt.close();
            upCon.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public ResultSet getResult(String sql){
        ResultSet rs = null;
        try{
            Statement stmt = con.createStatement
                       (ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            rs = stmt.executeQuery(sql);
        }catch(Exception e){
            e.printStackTrace();
        }
        return rs;
    }

    public void closeConnection(){
        try{
            con.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void closeResultSet(ResultSet rs){
        try{
            rs.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public void closeStatement(Statement stmt){
        try{
            stmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

}

1.登录:

login.jsp:

<%--
    Document   : choose
    Created on : 2009-10-5, 19:07:36
    Author     : lucifer
--%>

<%@page contentType="text/html" pageEncoding="UTF-8" errorPage="login_error.jsp"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>登录</title>
    </head>
    <body bgcolor="#c0c0c0">
         <center>
              <font face="楷体" size="+3">
                   Sir_LuciFer<br>数据库查询系统
              </font>
              <br><br>
              <form method="post" action="chooseDataBase.jsp" name="form">
              <B>
              服务器：
              <input type="text" name="server" value="localhost"><br>
              用户名：
              <input type="text" name="username" value="root"><br>
              密码：
              <input type="password" name="password"><br>
              <input type="submit" value="Login!">
              </B>
         </form>
         </center>
    </body>
</html>
http://www.knowsky.com/

2.选择要使用的数据库:

chooseDataBase.jsp:

<%--
    Document   : choose
    Created on : 2009-10-5, 19:07:36
    Author     : lucifer
--%>

<%@page contentType="text/html" pageEncoding="UTF-8" import="java.sql.*"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<jsp:useBean id="mysql" class="MySQL.mysqlmeans"/>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>选择数据库</title>
        <script type="text/Javascript">
             function getDataBase(){
                  alert(document.getElementById("db").value)
             }
        </script>
    </head>
    <%
          String user = request.getParameter("username");
          String pass = request.getParameter("password");
          String serv = request.getParameter("server");
          mysql.setUserName(user);
          mysql.setPassword(pass);
          mysql.setServer(serv);
          String sql = "show databases";

          mysql.getConnection();
          ResultSet rs = mysql.getResult(sql);
    %>

    <body bgcolor="#c0c0c0">
         <center>
         <table border="1px">
              <tr>
                   <td>
                        数据库：
                   </td>
              </tr>
              <%
                    while(rs.next()){%>
                    <tr>
                    <td>
                         <%=rs.getString("Database")%>
                    </td>
                         </tr>
                    <%
                    }
                    mysql.closeResultSet(rs);
                    mysql.closeConnection();
                %>
         </table>
         <form method="post" action="chooseTable.jsp" name="form">
              <B>
              请选择数据库名称：
              </B>
              <input type="text" name="database" value="test"><br>
              <input type="hidden" name="username" value="<%=user%>">
              <input type="hidden" name="password" value="<%=pass%>">
              <input type="hidden" name="server" value="<%=serv%>">
              <input type="submit" value="USE!">
         </form>
         <form method="post" action="login.jsp">
                   <input type="submit" value="Logout!">
         </form>
         </center>
    </body>
</html>

3.选择表并看其结构:

chooseTable.jsp:

<%--
    Document   : chooseTable
    Created on : 2009-10-5, 19:19:37
    Author     : lucifer
--%>

<%@page contentType="text/html" pageEncoding="UTF-8" import="java.sql.*"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<jsp:useBean id="mysql" class="MySQL.mysqlmeans"/>
<%
          String user = request.getParameter("username");
          String pass = request.getParameter("password");
          String serv = request.getParameter("server");
          String dbname = request.getParameter("database");
          mysql.setUserName(user);
          mysql.setPassword(pass);
          mysql.setServer(serv);
          mysql.setDataBase(dbname);
          String sql = "show tables";

          mysql.getConnection();
          ResultSet rs = mysql.getResult(sql);

%>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>选择表</title>
    </head>
    <body bgcolor="#c0c0c0">
         <center>
         <table border="1px">
              <tr>
                   <td>
                        表：
                   </td>
              </tr>
              <%
                    while(rs.next()){%>
                    <tr>
                    <td>
                         <%=rs.getString("Tables_in_" + dbname)%>
                    </td>
                         </tr>
                    <%
                    }
                %>
         </table>
         <form method="post" action="describeTable.jsp" name="form2">
              <B>请选择一个表：
              <input type="text" name="table">
              <input type="hidden" name="username" value="<%=user%>">
              <input type="hidden" name="password" value="<%=pass%>">
              <input type="hidden" name="server" value="<%=serv%>">
              <input type="hidden" name="database" value="<%=dbname%>">
              <br>
              <input type="submit" value="DESCRIBE!">
              </B>
         </form>

         <form method="post" action="chooseDataBase.jsp" name="form1">
              <B>
              <input type="hidden" name="username" value="<%=user%>">
              <input type="hidden" name="password" value="<%=pass%>">
              <input type="hidden" name="server" value="<%=serv%>">
              <input type="submit" value="BACK!">
              </B>
         </form>
         </center>
    </body>
</html>

4.描述表:

describeTable.jsp:

<%--
    Document   : describeTable
    Created on : 2009-10-6, 19:49:26
    Author     : lucifer
--%>

<%@page contentType="text/html" pageEncoding="UTF-8" import="java.sql.*"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<jsp:useBean id="mysql" class="MySQL.mysqlmeans"/>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Describe table <%=request.getParameter("table")%></title>
    </head>
    <%
    String serv = request.getParameter("server");
    String user = request.getParameter("username");
    String pass = request.getParameter("password");
    String dbname = request.getParameter("database");
    String tname = request.getParameter("table");
    mysql.setServer(serv);
    mysql.setUserName(user);
    mysql.setPassword(pass);
    mysql.setDataBase(dbname);
    String sql = "describe " + tname;

    mysql.getConnection();
    ResultSet rs = mysql.getResult(sql);

    %>
    <body bgcolor="#c0c0c0">
         <center>
         <table border="1px">
              <tr>
                   <td>
                        Field
                   </td>
                   <td>
                        Type
                   </td>
                   <td>
                        Null
                   </td>
                   <td>
                        Key
                   </td>
                   <td>
                        Default
                   </td>
                   <td>
                        Extra
                   </td>
              </tr>
              <%
                    while(rs.next()){
                   %>
                         <tr>
                              <td>
                                   <%=rs.getString("Field")%>
                              </td>
                              <td>
                                   <%=rs.getString("Type")%>
                              </td>
                              <td>
                                   <%=rs.getString("Null")%>
                              </td>
                              <td>
                                   <%=rs.getString("Key")%>
                              </td>
                              <td>
                                   <%=rs.getString("Default")%>
                              </td>
                              <td>
                                   <%=rs.getString("Extra")%>
                              </td>
                         </tr>
             <%
                    }
              %>
         </table>
         <form method="post" action="chooseTable.jsp">
              <input type="hidden" name="username" value="<%=user%>">
              <input type="hidden" name="password" value="<%=pass%>">
              <input type="hidden" name="server" value="<%=serv%>">
              <input type="hidden" name="database" value="<%=dbname%>">
              <input type="submit" value="BACK!">
         </form>
         </center>
    </body>
</html>

JSP实现MySQL数据结构查询

MySQL 随机查询数据与随机更新数据实现代码_MySQL

[MySQL]实现树形的遍历（关于多级菜单栏以及多级上下部门的查询问题）_MySQL

解析MSSQL跨数据库查询的实现方法_MySQL

使MySQL查询区分大小写的实现方法_MySQL

string-mysql使用jsp中变量为查询条件

MySQL 多表查询实现分析

mysql数据库，在查询列名里子查询一列由两列组合去重的个数如何实现？

Android通过Tomcat用JSP做服务器实现用户登录（MySQL数据库）

mysql实现查询最接近的记录数据示例

MySQL 统计查询实现代码_MySQL