流程简介:
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>
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>