MVC模式使用JDBC查询数据库中表的记录
程序员文章站
2024-03-08 16:25:16
...
一、实验内容
使用MVC设计一个查询数据库表中记录的JSP程序。编写一个JSP页面
inputDatabase.jsp,用户可以输人数据库名、表名后提交给一个servlet,servlet将查询结果放人一个Javabean模型中,然后转发到inputDatabase.jsp，inputDatabase.jsp页面再显示Javabean中的数据。
数据库设计表：建表如下：
二、参考代码
Web.xml
<servlet>
<servlet-name>shiyan1Servlet</servlet-name>
<servlet-class>myservlet.control.Shiyan1_Servlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>shiyan1Servlet</servlet-name>
<url-pattern>/shiyan1Servlet</url-pattern>
</servlet-mapping>
Shiyan1_Bean.java
package mybean.data;
public class Shiyan1_Bean{
String [] columnName;
//存放列名
String [] [] tableRecord=null;
//存放查询到的记录
public Shiyan1_Bean() {
tableRecord = new String[1][1];
columnName = new String[1];
}
public void setTableRecord(String [] [] s) {
tableRecord=s;
}
public String [] [] getTableRecord() {
return tableRecord;
}
public void setColumnName(String[]s){
columnName = s;
}
public String [] getColumnName () {
return columnName;
}
}
inputDatabase.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<jsp:useBean id = "recordBean" class = "mybean.data.Shiyan1_Bean" scope ="session" />
<!DOCTYPE html>
<HTML><body bgcolor= yellow><font size= 2>
<form action ="shiyan1Servlet" method= post>
<b>数据库:<input type = "text" name = "dataBase" size= 22 value = world></b>
<br>表名: <input type= "text" name = "tableName" size= 23 value = product>
<br>用户名(默认root): <input type= "text" name= "user" size= 10 value= root>
<br>用户密码(默认空): <input type= "text" name = "password" size= 10>
<br><input type= "submit" name= "b" value= "提交">
</form>
<table border=1>
<%
String[][] table = recordBean.getTableRecord();
if(table== null) {
out.print("没有记录");
return;
}
String []columnName = recordBean.getColumnName();
if(columnName != null) {
out.print("<tr>");
for(int i= 0;i < columnName.length;i++){
out.print("<th>" + columnName[i]+ "</th>");
out.print("</tr>");
}
out.println("全部记录数" + table.length);
//全部记录数
}
for(int i=0;i<table.length;i++) {
out.print("<tr>");
for(int j= 0;j<columnName.length;j++) {
out.print("<td>" + table[i][j] + "</td>");
}
out.print("</tr>");
}
%>
</table>
</font></body></HTML>
Shiyan1_Servlet.java
package myservlet.control;
import mybean.data.Shiyan1_Bean;
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class Shiyan1_Servlet extends HttpServlet{
/**
*
*/
private static final long serialVersionUID = 1L;
public void init(ServletConfig config) throws ServletException{
super.init(config);
try { Class.forName("com.mysql.jdbc.Driver");
}
catch(Exception e){}
}
public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException,IOException{
request.setCharacterEncoding( "gb2312");
String dataBase=request.getParameter("dataBase");
String tableName= request.getParameter("tableName");
String user = request.getParameter("user");
String password= request.getParameter("password");
boolean boo = (dataBase ==null || dataBase.length()== 0);
boo=boo || (tableName == null || tableName.length()==0);
boo = boo || (user== null || user.length() == 0);
if(boo){
fail(request,response,"查询失败 ");
}
HttpSession session = request.getSession(true);
Connection con = null;
Shiyan1_Bean recordBean= null;
try{
recordBean= (Shiyan1_Bean)session.getAttribute("recordBean");
if(recordBean == null){
recordBean= new Shiyan1_Bean(); //创建Javabean对象
session.setAttribute("recordBean",recordBean);
}
}
catch(Exception exp){
recordBean = new Shiyan1_Bean();
session.setAttribute("recordBean",recordBean);
}
String uri="jdbc:mysql://127.0.0.1/"+dataBase;
try {
con = DriverManager.getConnection(uri,user,password);
Statement sql = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = sql.executeQuery("select * from "+tableName);
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
//得到结果集的列数
String []columnName =new String[columnCount];
for(int i= 0;i<columnName.length;i++) {
columnName[i]=metaData.getColumnName(i+1);//得到列名，
}
recordBean.setColumnName(columnName);
//更新Javabean数据模型
rs.last();
int rowNumber = rs.getRow();
//得到记录数
String[][] tableRecord = recordBean.getTableRecord();
tableRecord = new String[rowNumber][columnCount];
rs.beforeFirst();
int i=0;
while(rs.next()){
for(int k= 0;k <columnCount;k++ )
tableRecord[i][k] = rs.getString(k+1);
i++;
}
recordBean.setTableRecord(tableRecord);
//更新Javabean数据模型
con.close();
response.sendRedirect("inputDatabase.jsp");
//重定向
}
catch(SQLException e) {
System.out.println(e);
}
}
public void doGet(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException{
doPost(request, response);
}
public void fail(HttpServletRequest request, HttpServletResponse response,
String backNews) {
response.setContentType("text/html;charset = GB2312");
try {
PrintWriter out = response.getWriter();
out.println("<html><body>");
out.println("<h2 >" + backNews+ "</h2>") ;
out.println("返回");
out.println("<a href = inputDatabase.jsp>输人正确信息</a>");
out.println("</body></html>");
}
catch( IOException exp){}
}
}
三、实验结果
输入密码提交后：
上一篇： 详解Java设计模式编程中的中介者模式
下一篇： 二刷java第一天 ——」 日更