JSP实现MySQL数据结构查询

最新推荐文章于 2024-07-14 01:31:01 发布

anuo20

最新推荐文章于 2024-07-14 01:31:01 发布

阅读量185

点赞数

分类专栏： java开发 jsp 文章标签：数据结构 mysql jsp

本文链接：https://blog.csdn.net/anuo20/article/details/84460801

版权

java开发同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

jsp

2 篇文章 0 订阅

订阅专栏

流程简介:

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>