2021-08-14 代码

任务：在线考试管理系统----用户信息管理模块

任务：在线考试管理系统----用户信息管理模块

子任务:
      用户信息注册
      用户信息查询
      用户信息删除
      用户信息更新


准备工作:

      1.创建用户信息表 Users.frm

      CREATE TABLE Users(

        userId int  primary key auto_increment, #用户编号
        userName varchar(50),    #用户名称
        password varchar(50),    #用户密码
        sex      char(1),        #用户性别 '男' 或则 '女'
        email    varchar(50)     #用户邮箱
      )

      auto_increment,自增序列    i++
      在插入时，如果不给定具体用户编号，此时根据auto_increment的值递增添加

      2.在src下 com.bjpowernode.entity.Users 实体类

      3.在src下 com.bjpowernode.util.JdbcUtil 工具类【复用】

      4.在web下WEB-INF下创建lib文件夹  存放mysql提供JDBC实现jar包

LoginServlet

package com.bjpowernose.controller;

import com.bjpowernose.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;

public class LoginServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String userName,password;
        UserDao dao = new UserDao();
        int result = 0;
        //1.调用请求对象对请求体使用utf-8字符集进行重新编辑
        request.setCharacterEncoding("utf-8");
        //2.调用请求对象读取请求体参数信息
        userName = request.getParameter("userName");
        password = request.getParameter("password");
        //3.调用Dao将查询验证信息推送到数据库服务器上
        result = dao.login(userName,password);
        //4.调用响应对象，根据验证结果将不同资源文件地址写入到响应头，交给浏览器
        if (result==1){
            response.sendRedirect("index.html");
        }else{
            response.sendRedirect("login_error.html");
        }
    }

}

UserAddServlet

package com.bjpowernose.controller;

import com.bjpowernose.dao.UserDao;
import com.bjpowernose.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,sex,email;
            UserDao dao = new UserDao();
            Users user =null;
            int result  = 0;
            PrintWriter out = null;
            //1.【调用请求对象】读取【请求头】参数信息，得到用户的信息
            userName = request.getParameter("userName");
            password = request.getParameter("password");
            sex = request.getParameter("sex");
            email= request.getParameter("email");

            //2.【调用UserDao】将用户信息填充到INSERT命令并借助JDBC规范发送到数据库服务器
            user = new Users(null,userName,password,sex,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指定编译器对响应体二进制内容编译
            //浏览器将编译后结果在窗口中展示给用户【结束】
}

UserDeleteServlet

package com.bjpowernose.controller;

import com.bjpowernose.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>");
        }

    }
}

UserFindServlet

package com.bjpowernose.controller;

import com.bjpowernose.dao.UserDao;
import com.bjpowernose.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.getSex()+"</td>");
            out.print("<td>"+users.getEmail()+"</td>");
            out.print("<td><a href='delete?userId="+users.getUserId()+"'>删除用户</a></td>");
            out.print("</tr>");
        }
        out.print("</table>");

    }
}

UserDao

package com.bjpowernose.dao;

import com.bjpowernose.entity.Users;
import com.bjpowernose.util.JdbcUtil;

import java.awt.*;
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,sex,email)" +
                "values(?,?,?,?)";
        PreparedStatement ps = util.createStatement(sql);
        int result = 0;
        try {
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getSex());
            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 {
            rs = ps.executeQuery();
            while (rs.next()) {
                Integer userId = rs.getInt("userId");
                String userName = rs.getString("userName");
                String password = rs.getString("password");
                String sex = rs.getString("sex");
                String email = rs.getString("email");
                Users users = new Users(userId, userName, password, sex, 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;
    }
}

Users

package com.bjpowernose.entity;

public class Users {

    private Integer userId;
    private String userName;
    private String password;
    private String sex;
    private String email;

    public Users() {
    }

    public Users(Integer userId, String userName, String password, String sex, String email) {
        this.userId = userId;
        this.userName = userName;
        this.password = password;
        this.sex = sex;
        this.email = 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 getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

JdbcUtil

package com.bjpowernose.util;


import java.sql.*;
// JdbcUtil obj = new JdbcUtil();  obj.getCon()
// JdbcUtil obj = new JdbcUtil();  obj.createStatement();
// JdbcUtil.getCon();
public class JdbcUtil {

     final String URL="jdbc:mysql://localhost:3306/bjpowernode";
     final String USERNAME="root";
     final String PASSWORD="123";
     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();
            }
        }

    }

     //select ps,con,rs
    public  void close(ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        close();
    }
}

web.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.bjpowernose.controller.UserAddServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>UserFindServlet</servlet-name>
        <servlet-class>com.bjpowernose.controller.UserFindServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>UserDeleteServlet</servlet-name>
        <servlet-class>com.bjpowernose.controller.UserDeleteServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>LoginServlet</servlet-name>
        <servlet-class>com.bjpowernose.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>
</web-app>

index.html

<html >
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>

<frameset rows="15%,85%">
    <frame name="top" src="top.html">
    <frameset cols="15%,85%">
        <frame name="left" src="left.html">
        <frame name="right" src="user_add.html">
    </frameset>
</frameset>
</html>

left.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <ul>
        <li>用户信息管理
            <ol>
                <li><a href="user_add.html" target="right">用户信息注册</a></li>
                <li><a href="user/find" target="right">用户信息查询</a></li>
            </ol>
        </li>
        <li>试题信息管理</li>
        <li>考试管理</li>
    </ul>
</body>
</html>

login.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
            <center>
                <form action="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" name="重置" /></td>
                        </tr>
                    </table>
                </form>

            </center>

</body>
</html>

login_error

<!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="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" name="重置" /></td>
            </tr>
        </table>
    </form>
</center>
</body>
</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: brown;font-size: 40px">在线考试管理系统</font>
        </center>
</body>
</html> 

user_add.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <center>
        <form action="user/add" method="get">
            <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>用户性别</td>
                    <td>
                        <input type="radio" name="sex" value="男"/>男
                        <input type="radio" name="sex" value="女"/>女
                    </td>
                </tr>
                <tr>
                    <td>用户邮箱</td>
                    <td><input type="text" name="email"/></td>
                </tr>
                <tr>
                    <td>
                        <input type="submit" value="用户注册"/>
                        <input type="reset" />
                    </td>
                </tr>
            </table>
        </form>
    </center>
</body>
</html>