一、实验目的:
掌握MVC模式的基本原理
掌握JSP+Servlet+JavaBean架构连接、查询、添加、修改、删除数据库的方法;
二、实验要求:
采用JSP+Servlet+JavaBean架构,设计通过下列JSP页面访问数据库(如表1-1),具体的界面和结构要求如下:
1.添加用户页面(AddUser.jsp),如图1-1所示;
2. 删除用户页面(DeleteUser.jsp), 如图1-2所示;
3.查找和修改用户页面(SearchAndModify.jsp), 如图1-3所示。
4. 基于MVC的程序体系结构,如图1-4所示。
三、实验步骤:
1.准备需要访问的数据库
下载并安装MySQL5.0
准备MySQL5.0的JDBC驱动mysql-connector-java-5.0.8-bin.jar。
数据库的设计如下表:
数据库采用MySQL5.0,数据库名:mydb,表名:userinfo(用户表)
表1-1 数据库的用户表
序号 | 列名 | 数据类型 | 长度 | 主键 | 允许空 | 默认值 | 说明 |
1 | username | varchar | 40 | 是 | 否 | 用户名 | |
2 | password | varchar | 40 | 是 | 口令 |
创建过程如下:(可选择使用MySQL的数据库管理和开发工具Navicat 和 MySQL-Front等)
Step1:进入MySQL程序的MySQL Command Line Client界面,如下图所示
Step2:分别执行下列命令://支持中文
- 创建数据库:
CREATE DATABASE ‘mydb’
CHARACTER SET 'utf8'
COLLATE 'utf8_general_ci';
USE mydb;
②创建表:
CREATE TABLE `userinfo` (
`username` varchar(20) NOT NULL PRIMARY KEY,
`password` varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
如果使用Myeclipse自带的数据库系统Derby,则操作过程如下:
在MyEclipse下面,启动Derby,工具按钮位于启动Tomcat之上。
然后将开发视图转换成:Database Explore或Java Persistence
在Connected to MyEclipse Derby上点右键,选择SQL Editor
2、代码开发过程如下:
Step1: 创建UserinfoVO:
UserinfoVO.java,内容如下:
package xpu.vo;
public class UserinfoVO {
private String username;//用户名
private String password;//口令
public UserinfoVO()
{
this.username="";
this.password="";
}
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;
}
}
Step2: 创建连接数据库类DBUtil
package xpu.util;
import java.sql.*;
public class DBUtil{
private static Connection conn=null;
public static Connection getConnection()
{
String driverClass="org.apache.derby.jdbc.ClientDriver";
String url=
"jdbc:derby://localhost:1527/myeclipse";
//与创建数据库的代码结合,保证访问数据库时不出现乱码
String username = "classiccars";
String password = "myeclipse";
try {
Class.forName(driverClass);// 加载数据库驱动
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
if (conn==null)
try{
conn = DriverManager.getConnection(url, username, password);//建立连接
} catch (SQLException e) {
e.printStackTrace();
System.out.print("数据库连接失败!");
}
return conn;
}
Step3: 创建数据库访问类—UserinfoDAO.java,内容如下:
package xpu.dao;
import java.sql.*;
import xpu.vo.UserinfoVO;
import xpu.util.DBUtil;
public class UserinfoDAO {
private static Connection conn=null;
private Statement stmt=null;
//构造函数中完成对数据库进行初始化
public UserinfoDAO() {
//创建连接
conn=DBUtil.getConnection();
try {
//创建Statement
stmt=conn.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
//将对象user添加到表中
public void add(UserinfoVO user) throws SQLException
{
String sql="INSERT INTO userinfo(username,password) VALUES('"
+user.getUsername()+"','"+user.getPassword()+"')";
stmt.executeUpdate(sql);
//此处可以使用预定义语句PreparedStatement
}
//根据用户名(关键字)从数据库删除相应的记录
public void delete(String username) throws SQLException
{
String sql="Delete From userinfo Where username='"+username+"'";
stmt.executeUpdate(sql);
}
//修改数据库中的user
public void update(UserinfoVO user) throws SQLException
{
String sql="UPDATE userinfo set password='"
+user.getPassword()+"' where username='"+user.getUsername()+"'";
stmt.executeUpdate(sql);
}
//根据用户名称(关键字)从数据库中查找记录,并将找到的记录写入对象user中返回
public UserinfoVO searchByUsername(String username) throws SQLException
{
UserinfoVO user=new UserinfoVO(); //ver1版本是User
String sql="select * from userinfo where username='"+username+"'";
ResultSet rs=stmt.executeQuery(sql);
while(rs.next())
{
String name=rs.getString("username");
if(name.equals(username))
{
//将从数据库查找得到的记录存入对象user中
user.setUsername(name);
user.setPassword(rs.getString("password"));
if(rs!=null)
rs.close();
return user;
}
}
return null;//查找不到返回null
}
//根据用户判断该记录是否存在
public boolean exists(String username)
{
boolean rtn=false;
try {
if(searchByUsername(username)!=null)
rtn=true;
} catch (SQLException e) {
e.printStackTrace();
}
return rtn;
}
//关闭与访问数据库有关连接
public void finalize()
{
try {
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
Step4: 创建JSP文件以及相应的Servlet
创建添加用户页面—AddUser.jsp,代码如下:
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>添加用户</title>
</head>
<body>
<form method="post" action="AddUserServlet">
<div align="center"><font face="宋体" size="6"><strong>
添加用户</strong></font><br/><hr/>
用户名:<input name="username" type="text"/>
<br/>
<br/>
密 码:
<input name="password" type="text"/>
<br />
<br />
<input type="submit" value="添加" />
<input type="reset" value="取消" />
</div>
</form>
</body>
</html>
相应的添加用户Servlet(AddUserServlet.java),代码如下:
package servlet.user;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import xpu.vo.UserinfoVO;
import xpu.dao.UserinfoDAO;
public class AddUserServlet extends HttpServlet {
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//返回上页
String goBack="<br><a href='javascript:window.history.go(-1);'>返回上页</a>";
response.setContentType("text/html;charset=gb2312");
PrintWriter out = response.getWriter();
String username=request.getParameter("username");
if(username==null||username.equals(""))
{
out.print("用户名不能为空!");
out.print(goBack);
}
String password=request.getParameter("password");
UserinfoVO user=new UserinfoVO();
user.setUsername(username);
user.setPassword(password);//注入属性
UserinfoDAO userDAO=new UserinfoDAO();
if(userDAO.exists(user.getUsername()))
{
out.print("用户名已存在!");
out.print(goBack);
return;
}
try {
userDAO.add(user);
} catch (SQLException e) {
e.printStackTrace();
out.print("添加失败:"+e.getLocalizedMessage());
out.print(goBack);
return;
}
out.print("添加成功!");
out.print(goBack);
out.close();
}
}
注意:servlet的访问路径,web.xml中url-pattern是否和jsp中的form表单中的action一致。
创建删除用户页面--DeleteUser.jsp,代码如下:
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>删除用户</title>
</head>
<body>
<form method="post" action="DeleteUserServlet">
<div align="center"><font face="宋体" size="6"><strong>
删除用户</strong></font><br/>
<hr/>
<p>
</p>
<p>输入删除的用户名:<input name="username" type="text"/><br/><br/>
<input type="submit" name="delete" value="删除用户" />
<br />
<br />
</p>
</div>
</form>
</body>
</html>
相应的删除用户Servlet (DeleteUserServlet.java)的代码,如下:
package servlet.user;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import xpu.dao.UserinfoDAO;
import xpu.vo.UserinfVO;
public class DeleteUserServlet extends HttpServlet {
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//返回上页
String goBack="<br><a href='javascript:window.history.go(-1);'>返回上页</a>";
response.setContentType("text/html;charset=gb2312");
PrintWriter out = response.getWriter();
String username=request.getParameter("username");
if(username==null||username.equals(""))
{
out.print("用户名不能为空!");
out.print(goBack);
}
UserinfoDAO userDAO=new UserinfoDAO();
if(!userDAO.exists(username))
{
out.print("用户名不存在!");
out.print(goBack);
return;
}
try {
userDAO.delete(username);
} catch (SQLException e) {
e.printStackTrace();
out.print("删除失败:"+e.getLocalizedMessage());
out.print(goBack);
return;
}
out.print("删除成功!");
out.print(goBack);
out.close();
}
}
创建查找和修改页面(SearchAndModify.jsp),代码如下:
<%@ page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<%@page import="xpu.vo.Userinfo,xpu.dao.UserinfoDAO"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>查询和修改用户</title>
</head>
<%
String username=request.getParameter("queryName");
UserinfoVO user=new UserinfoVO();
if(userame!=null){
if(username!=null&&username!="")
{
xpu.dao.UserinfoDAO userDAO=new xpu.dao.UserinfoDAO();
Userinfo temp=userDAO.searchByUsername(username);
if(temp==null)
{
out.print("用户名不存在!");
}
else
user=temp;//防止user为null,导致下文的user.getUsername()产生异常
}
}
%>
<body>
<div align="center">
<form name="query" method="post" action="">
<font face="宋体" size="6"><strong>查询和修改用户</strong></font><br/><hr/><br/>
输入查询的用户名:<input name="queryName" type="text"/>
<input type="submit" name="query" value="查询用户" /><br/><br/><hr/><br/>
</form>
<form name="modify" method="post" action="ModifyUserServlet">
用户名:
<input name="modfyName" type="text" value="<%=user.getUsername()%>" readonly="readonly" />
<br/><br/>
密 码:
<input name="password" type="text" value="<%=user.getPassword()%>"/>
<br /><br />
<input type="submit" name="modify" value="修改用户" />
</form>
<hr/>
<a href="DeleteUser.jsp">删除用户</a><br/>
<a href="AddUser.jsp">添加用户</a><br/>
</div>
</body>
</html>
修改用户的Servlet (ModifyUserServlet.java),代码如下:
package servlet.user;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import xpu.vo.UserinfoVO;
Import xpu.dao.UserinfoDAO;
public class ModifyUserServlet extends HttpServlet {
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//返回上页
String goBack="<br><a href='javascript:window.history.go(-1);'>返回上页</a>";
response.setContentType("text/html;charset=gb2312");
PrintWriter out = response.getWriter();
String name=request.getParameter("modify");
if(name!=null) //单击了‘修改用户’
{
String username=request.getParameter("modfyName");
String password=request.getParameter("password");
UserinfoVO user=new UserinfoVO();
user.setUsername(username);
user.setPassword(password);
UserinfoDAO userDAO=new UserinfoDAO();
try {
userDAO.update(user);
} catch (SQLException e) {
e.printStackTrace();
out.print("修改失败:"+e.getLocalizedMessage());
out.print(goBack);
return;
}
}
out.print("修改成功!");
out.print(goBack);
out.close();
}
}
注意上述的Servlet都需要在web.xml中部署,以AddUserServlet为例。
<servlet>
<servlet-name>addUserServlet</servlet-name>
<servlet-class>servlet.user.AddUserServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>AddUserServlet</servlet-name>
<url-pattern>/AddUserServlet<url-pattern>
</servlet-mapping>
Step5 启动服务器,发布应用,打开浏览器,在地址栏中输入地址进
正在上传…重新上传取消
正在上传…重新上传取消
图1-4基于MVC模式的程序架构