一、完成准备工作
任务:在线考试系统
子任务:
- 用户信息注册
- 用户信息查询
- 用户信息删除
准备工作:
1.创建用户信息表 User。frm
CREATE TABLE Users(
userId int primary key auto_increment, #用户编号 auto_increment表示自增序列
userName varchar(50), #用户名称
password varchar(50), #用户密码
gender char(1), #用户性别 ‘男’ ‘女’
email varchar(50) #用户邮箱
)
2. 在src中,创建com.lht.entity.Users实体类
3. 在src中,创建com.lht.util.JdbcUtil 工具类
4.在web下WEB-INF下创建lib文件夹,存放mysql提供JDBC实现jar包
com.lht.entity.Users实体类
package com.lht.entity;
public class Users {
private Integer userId;
private String userName;
private String password;
private String gender;
private String email;
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Users() {
}
public Users(Integer userId, String userName, String password, String gender, String email) {
this.userId = userId;
this.userName = userName;
this.password = password;
this.gender = gender;
this.email = email;
}
}
com.lht.util.JdbcUtil 工具类
package com.lht.util;
import java.sql.*;
public class JdbcUtil {
final String URL = "jdbc:mysql://localhost:3306/test";
final String USERNAME = "root";
final String PASSWORD = "root";
PreparedStatement ps = null;
Connection con = null;
//将jar包中driver实现类加载到JVM中
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e){
e.printStackTrace();
}
}
//封装链接通道创建细节
public Connection getCon(){
try {
con = DriverManager.getConnection(URL,USERNAME,PASSWORD);
} catch (SQLException e){
e.printStackTrace();
}
return con;
}
//封装交通工具创建细节
public PreparedStatement createStatement(String sql){
try {
ps = getCon().prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return ps;
}
//ps与con销毁细节 insert,update,delete
public void close(){
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void close(ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
整个Dao层文件UserDao
package com.lht.dao;
import com.lht.entity.Users;
import com.lht.util.JdbcUtil;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class UserDao {
private JdbcUtil util = new JdbcUtil();
//用户注册
public int add(Users user){
String sql = "insert into users(userName,password,gender,email)" + "value(?,?,?,?)";
PreparedStatement ps = util.createStatement(sql);
int result = 0;
try {
ps.setString(1,user.getUserName());
ps.setString(2,user.getPassword());
ps.setString(3,user.getGender());
ps.setString(4,user.getEmail());
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.close();
}
return result;
}
//查询所有用户信息
public List findAll(){
String sql = "select * from users";
PreparedStatement ps = util.createStatement(sql);
ResultSet rs = null;
List userList = new ArrayList();
try {
ResultSet resultSet = rs = ps.executeQuery();
while(rs.next()){
Integer userId = rs.getInt("userId");
String userName = rs.getString("userName");
String password = rs.getString("password");
String gender = rs.getString("gender");
String email = rs.getString("email");
Users users = new Users(userId,userName,password,gender,email);
userList.add(users);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.close(rs);
}
return userList;
}
//根据用户编号删除用户信息
public int delete(String userId){
String sql = "delete from users where userId=?";
PreparedStatement ps = util.createStatement(sql);
int result = 0;
try {
ps.setInt(1,Integer.valueOf(userId));
result = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.close();
}
return result;
}
//登陆验证
public int login(String userName,String password){
String sql = "select count(*) from users where userName=? and password=?";
PreparedStatement ps = util.createStatement(sql);
ResultSet rs = null;
int result = 0;
try {
ps.setString(1,userName);
ps.setString(2,password);
rs = ps.executeQuery();
while(rs.next()){
result = rs.getInt("count(*)");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
util.close(rs);
}
return result;
}
}
整个项目的XML文件
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<servlet>
<servlet-name>UserAddServlet</servlet-name>
<servlet-class>com.lht.controller.UserAddServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>UserFindServlet</servlet-name>
<servlet-class>com.lht.controller.UserFindServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>UserDeleteServlet</servlet-name>
<servlet-class>com.lht.controller.UserDeleteServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>LoginServlet</servlet-name>
<servlet-class>com.lht.controller.LoginServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>LoginServlet</servlet-name>
<url-pattern>/login</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>UserDeleteServlet</servlet-name>
<url-pattern>/user/delete</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>UserFindServlet</servlet-name>
<url-pattern>/user/find</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>UserAddServlet</servlet-name>
<url-pattern>/user/add</url-pattern>
</servlet-mapping>
<!--自定义默认欢迎资源文件规则-->
<welcome-file-list>
<welcome-file>login.html</welcome-file>
<welcome-file>user/find</welcome-file><!--servlet作为默认欢迎资源文件,开头斜线必须抹掉-->
</welcome-file-list>
</web-app>
二、用户信息注册
用户注册功能实现UserAddServlet文件
package com.lht.controller;
import com.lht.dao.UserDao;
import com.lht.entity.Users;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
public class UserAddServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String userName,password,gender,email;
UserDao dao = new UserDao();
Users user = null;
int result = 0;
PrintWriter out = null;
//1.【调用请求对象】读取【请求头】参数信息,得到用户的信息
userName = request.getParameter("userName");
password = request.getParameter("password");
gender = request.getParameter("gender");
email = request.getParameter("email");
//2.【调用UserDAO】将用户信息填充到INSERT命令并借助JDBC规范发送到数据库服务器
user = new Users(null,userName,password,gender,email);
result = dao.add(user);
//3.【调用响应对象】将【处理结果】以二进制形式写入到响应体
response.setContentType("text/html;charset=utf-8");
out = response.getWriter();
if(result == 1){
out.print("<font style='color:red;font-size:40'>用户信息注册成功</font>");
}else{
out.print("<font style='color:red;font-size:40'>用户信息注册失败</font>");
}
}
//Tomcat负责销毁【请求对象】和【响应对象】
//Tomcat负责将Http响应协议包推送到发起请求的浏览器上
//浏览器根据响应头content-type指定编译器对响应体二进制内容编辑
//浏览器将编辑结果在窗口中展示给用户
}
三、用户信息查询
用户查询功能实现UserFindServlet文件
package com.lht.controller;
import com.lht.dao.UserDao;
import com.lht.entity.Users;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
public class UserFindServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
UserDao dao = new UserDao();
PrintWriter out;
//1. 【调用DAO】将查询命令推送到数据库服务器上,得到所有用户信息【list】
List<Users> userList = dao.findAll();
//2. 【调用响应对象】将用户信息结合<table>标签命令以二进制形式写入到响应体
response.setContentType("text/html;charset=utf-8");
out = response.getWriter();
out.print("<table border='2' align='center'>");
out.print("<tr>");
out.print("<td>用户编号</td>");
out.print("<td>用户姓名</td>");
out.print("<td>用户密码</td>");
out.print("<td>用户性别</td>");
out.print("<td>用户邮箱</td>");
out.print("<td>操作</td>");
out.print("</tr>");
for(Users users:userList){
out.print("<tr>");
out.print("<td>"+users.getUserId()+"</td>");
out.print("<td>"+users.getUserName()+"</td>");
out.print("<td>******</td>");
out.print("<td>"+users.getGender()+"</td>");
out.print("<td>"+users.getEmail()+"</td>");
out.print("<td><a href='/myWeb/user/delete?userId="+users.getUserId()+"'>删除用户</a></td>");
out.print("</tr>");
}
out.print("</table>");
}
}
四、用户信息删除
用户删除功能的实现UserDeleteServlet文件
package com.lht.controller;
import com.lht.dao.UserDao;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
public class UserDeleteServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String userId;
UserDao dao = new UserDao();
int result = 0;
PrintWriter out = null;
//1. 【调用请求参数】读取【请求头】参数(用户编号)
userId = request.getParameter("userId");
//2. 【调用DAO】将用户编号填充到delete命令并发送到数据库服务器
result = dao.delete(userId);
//3. 【调用响应对象】将处理结果以二进制写入到响应体,交给浏览器
response.setContentType("text/html;charset=utf-8");
out = response.getWriter();
if(result == 1){
out.print("<font style='color:red;font-size:40'>用户信息删除成功</font>");
}else{
out.print("<font style='color:red;font-size:40'>用户信息删除失败</font>");
}
}
}
五、主界面
整个网站的结构分布index.html
<html>
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<frameset rows="15%,85%">
<frame name="top" src="/myWeb/top.html"/>
<frameset cols="15%,85%">
<frame name="left" src="/myWeb/left.html">
<frame name="right"/>
</frameset>
</frameset>
</html>
上半部分展示top.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body style="background-color: green">
<center>
<font style="color: red;font-size: 40px">在线考试管理系统</font>
</center>
</body>
</html>
左半部分展示left.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<ul>
<li>用户管理系统
<ol>
<li><a href="/myWeb/user_Add.html" target="right">用户信息注册</a></li>
<li><a href="/myWeb/user/find" target="right">用户信息查询</a></li>
</ol>
</li>
<li>试题信息管理</li>
<li>考试管理</li>
</ul>
</body>
</html>
用户添加页面文件user_Add.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<center>
<form action="/myWeb/user/add" method="get">
<table border="2">
<tbody><tr>
<td>用户姓名</td>
<td><input type="text" name="userName"></td>
</tr>
<tr>
<td>用户密码</td>
<td><input type="password" name="password"></td>
</tr>
<tr>
<td>用户性别</td>
<td>
<input type="radio" name="gender" value="男">男
<input type="radio" name="gender" value="女">女
</td>
</tr>
<tr>
<td>用户邮箱</td>
<td><input type="text" name="email"></td>
</tr>
<tr>
<td><input type="submit" value="用户注册"></td>
<td><input type="reset"></td>
</tr>
</tbody></table>
</form>
</center>
</body>
</html>
六、登录界面
登录主界面文件login.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<center>
<form action="/myWeb/login" method="post">
<table border="2">
<tr>
<td>登录名</td>
<td><input type="text" name="userName"></td>
</tr>
<tr>
<td>密码</td>
<td><input type="password" name="password"></td>
</tr>
<tr>
<td><input type="submit" value="登录"></td>
<td><input type="reset"></td>
</tr>
</table>
</form>
</center>
</body>
</html>
登录失败页面文件Login_error.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<center>
<font style="color: red;font-size: 30px">登录信息不存在,请重新登录</font>
<form action="/myWeb/login" method="post">
<table border="2">
<tr>
<td>登录名</td>
<td><input type="text" name="userName"></td>
</tr>
<tr>
<td>密码</td>
<td><input type="password" name="password"></td>
</tr>
<tr>
<td><input type="submit" value="登录"></td>
<td><input type="reset"></td>
</tr>
</table>
</form>
</center>
</body>
</html>
完成之后
项目结构部署
结果展示