JSP实现MySQL数据结构查询

王朝学院·作者佚名  2009-10-09
窄屏简体版  字體: 小|中|大|超大  

流程简介:

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>

 
 
 
免责声明：本文为网络用户发布，其观点仅代表作者个人观点，与本站无关，本站仅提供信息存储服务。文中陈述内容未经本站证实，其真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航