6.5

web作业4：

实验项目名称：实验四  Web综合应用程序设计

 

一、实验目的

通过使用Java MVC模式设计简单的数据库管理系统，巩固使用JDBC技术访问数据库的方法，学习使用Java语言对服务器端进行编程，深入理解MVC网站设计模式的基本概念和框架结构。

二、实验内容和基本要求

从以下列举的四个管理系统中，任选其一，基于MVC设计模式，完成信息的查看、增加、删除和修改等功能。

1) 学生管理系统：存储的信息包括学生学号、姓名、性别、生日等。

2) 商品管理系统：存储的信息包括商品ID、商品名称、商品数量、生产厂家等。

3) 客户管理系统：存储的信息包括客户ID、客户姓名、客户地址、手机号码等。

4) 车辆管理系统：存储的信息包括汽车ID、品牌、颜色、车主姓名等。

对于客户端增加和修改信息页面，要使用Javascript进行必要的数据的非空验证。自行添加一些CSS，使得页面和字体更加美观。将网页代码、程序源代码和浏览器截图写入实验报告。

DBUtil:
package student.bean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DBUtil {
    private String driverStr = "com.mysql.jdbc.Driver";
    private String connStr = "jdbc:mysql://127.0.0.1:3306/student?useSSL=false&useUnicode=true&characterEncoding=utf-8";
    private String dbusername = "root";
    private String dbpassword = "qaz9583";
    private Connection conn = null;
    private Statement stmt = null;

    public DBUtil() {
        try {
            Class.forName(driverStr);
            conn = DriverManager.getConnection(connStr, dbusername, dbpassword);
            stmt = conn.createStatement();
        } catch (Exception ex) {
            System.out.println("数据库连接失败！");
        }
    }


    public int executeUpdate(String s) {
        int result = 0;
        try {
            result = stmt.executeUpdate(s);
        } catch (Exception ex) {
            System.out.println("更新出现异常！");
        }
        return result;
    }
    

    public ResultSet executeQuery(String s) {
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(s);
        } catch (Exception ex) {
            System.out.println("查询出现异常！");
        }
        return rs;
    }


    public void close() {
        try {
            stmt.close();
            conn.close();
        } catch (Exception e) {
        }
    }

}
Student.bean：
studentInfo:
package student.bean;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import student.bean.studentInfo;
import student.bean.DBUtil;

public class studentInfo {
    private String id;
    private String name;
    private String sex;
    private String birth;
    
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public String getBirth() {
        return birth;
    }
    public void setBirth(String birth) {
        this.birth = birth;
    }
    
    public static ArrayList<studentInfo> getstudentList() {
        ArrayList<studentInfo> list = new ArrayList<studentInfo>();
        String sql = "select * from studentinfo";
        DBUtil jdbc = new DBUtil();
        ResultSet rs = jdbc.executeQuery(sql);
        try {
            while (rs.next()) {
                studentInfo bi = new studentInfo();
                bi.setId(rs.getString("id"));
                bi.setName(rs.getString("name"));
                bi.setSex(rs.getString("sex"));
                bi.setBirth(rs.getString("birth"));
                list.add(bi);
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.close();
        return list;
    }


    public static studentInfo getstudentById(String id) {
        String sql = "select * from student where id=" + id;
        DBUtil jdbc = new DBUtil();
        ResultSet rs = jdbc.executeQuery(sql);
        studentInfo bi = new studentInfo();
        try {
            if (rs.next()) {
                bi.setId(rs.getString("id"));
                bi.setName(rs.getString("name"));
                bi.setSex(rs.getString("sex"));
                bi.setBirth(rs.getString("birth"));
            }
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        jdbc.close();
        return bi;
    }

    public static int updatestudent(studentInfo bi) {
        int result = 0;
        String sql = "update studentinfo set name='" + bi.getName() + "',id'" + bi.getId() + "',sex="
                + bi.getSex() + " where birth=" + bi.getBirth();
        DBUtil jdbc = new DBUtil();
        result = jdbc.executeUpdate(sql);
        jdbc.close();
        return result;
    }


    public static int deletestudent(String id) {
        int result = 0;
        String sql = "delete from student where id=" + id;
        DBUtil jdbc = new DBUtil();
        result = jdbc.executeUpdate(sql);
        jdbc.close();
        return result;
    }

    public static int addstudent(studentInfo bi) {
        int result = 0;
        String sql = "insert into student values(null,'" + bi.getName() + "','" + bi.getId() + "',"
                + bi.getId() + ")";
        DBUtil jdbc = new DBUtil();
        result = jdbc.executeUpdate(sql);
        jdbc.close();
        return result;
    }

}
AjaxController:
package servlets;

import java.io.IOException;
import java.util.*;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.*;

import student.bean.studentInfo;

/**
 * Servlet implementation class AjaxController
 */
@WebServlet("/AjaxController")
public class AjaxController extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public AjaxController() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        response.getWriter().append("Served at: ").append(request.getContextPath());
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub

        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");

        String actionUrl = request.getServletPath(); // 获取客户端的访问URL地址信息
        
        if (actionUrl.equals("/list.action")) { // 查询所有图书
            ArrayList<studentInfo> list = studentInfo.getstudentList(); // 调用studentInfo的getstduentList方法完成
            // 使用JSONArray对象将结果构建为json对象并输出到客户端
            JSONArray jsonArray = new JSONArray();
            for (int i = 0; i < list.size(); i++) {
                studentInfo stduent = list.get(i);
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("id", stduent.getId());
                map.put("name", stduent.getName());
                map.put("sex", stduent.getSex());
                map.put("birth", stduent.getBirth());
                JSONObject stduentObj = new JSONObject(map);
                jsonArray.put(stduentObj);
            }
            // 向客户端返回json结果
            response.getWriter().print(jsonArray.toString());

        } else if (actionUrl.equals("/add.action")) { // 增加图书操作
            studentInfo bi = new studentInfo();
            bi.setName(request.getParameter("name"));
            bi.setSex(request.getParameter("sex"));
            bi.setBirth(request.getParameter("birth"));
        
            int r = studentInfo.addstudent(bi); // 调用studentInfo的addstduent方法完成
            // 向客户端返回结果
            response.getWriter().print(r);

        } else if (actionUrl.equals("/edit.action")) { // 编辑图书操作
            String id = request.getParameter("id");
            studentInfo bi = studentInfo.getstudentById(id); // 调用studentInfo的getstduentById方法完成
            // 将该对象构建为json数据
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("id", bi.getId());
            map.put("name", bi.getName());
            map.put("sex", bi.getSex());
            map.put("birth", bi.getBirth());
            JSONObject studentObj = new JSONObject(map);
            // 向客户端返回结果
            response.getWriter().print(studentObj.toString());

        } else if (actionUrl.equals("/update.action")) { // 更新图书操作
            studentInfo bi = new studentInfo();
            bi.setId(request.getParameter("id"));
            bi.setName(request.getParameter("name"));
            bi.setSex(request.getParameter("sex"));
            bi.setBirth(request.getParameter("birth"));
            int r = studentInfo.updatestudent(bi);// 调用studentInfo的updatestduent方法完成
            response.getWriter().print(r); // 向客户端返回结果

        } else if (actionUrl.equals("/delete.action")) { // 删除图书操作
            String id = request.getParameter("id");
            int r = studentInfo.deletestudent(id); // 调用studentInfo的deletestduent方法完成
            response.getWriter().print(r); // 向客户端返回结果
        }
    }

}

studentController:
package servlets;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import student.bean.studentInfo;

/**
 * Servlet implementation class studentController
 */
@WebServlet("/studentController")
public class studentController extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public studentController() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        response.getWriter().append("Served at: ").append(request.getContextPath());
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        request.setCharacterEncoding("utf-8");

        String actionUrl = request.getServletPath(); // 获取客户请求的Servlet地址

        if (actionUrl.equals("/index.do")) { // 查询所有图书
            ArrayList<studentInfo> list = studentInfo.getstudentList(); // 调用studentInfo的getBookList方法查询所有图书，赋值给list
            request.setAttribute("list", list); // 在request增加属性list，其结果为list对象
            request.getRequestDispatcher("/index.jsp").forward(request, response);// 重定向至index.jsp进行显示

        } else if (actionUrl.equals("/addview.do")) { // 新增图书显示页面
            request.getRequestDispatcher("add.html").forward(request, response); 
        } else if (actionUrl.equals("/add.do")) { // 新增图书
            studentInfo bi = new studentInfo();
            bi.setName(request.getParameter("Name"));
            bi.setSex(request.getParameter("Sex"));
            bi.setBirth(request.getParameter("Birth"));
            int r = studentInfo.addstudent(bi); // 调用studentInfor的addstudent方法完成
            if (r == 1)
                request.getRequestDispatcher("success.html").forward(request, response); // 成功的话重定向至success.html
            else
                request.getRequestDispatcher("failure.html").forward(request, response); // 失败的话重定向至failure.html

        } else if (actionUrl.equals("/edit.do")) { // 客户端要对指定id的图书进行修改
            String id = request.getParameter("id");
            studentInfo bi = studentInfo.getstudentById(id); // 调用studentInfo的getstudentById方法获取图书信息，赋值给bi对象
            request.setAttribute("bi", bi); // 将bi对象增加到request的属性中
            request.getRequestDispatcher("/edit.jsp").forward(request, response);// 重定向至edit.jsp进行显示

        } else if (actionUrl.equals("/update.do")) { // 用户输入要修改的图书的信息之后需要保存到数据库
            studentInfo bi = new studentInfo();
            bi.setId(request.getParameter("id"));
            bi.setName(request.getParameter("name"));
            bi.setSex(request.getParameter("sex"));
            bi.setBirth(request.getParameter("birth"));
            int r = studentInfo.updatestudent(bi);// 调用studentInfo的updatestudent方法实现
            if (r == 1)
                request.getRequestDispatcher("/success.html").forward(request, response);// 成功的话重定向至success.html
            else
                request.getRequestDispatcher("/failure.html").forward(request, response);// 失败的话重定向至failure.html

        } else if (actionUrl.equals("/delete.do")) { // 用户需要删除指定id的图书
            String id = request.getParameter("id");
            int r = studentInfo.deletestudent(id); // 调用studentInfo的deletestudent方法实现
            if (r == 1)
                request.getRequestDispatcher("/success.html").forward(request, response);// 成功的话重定向至success.html
            else
                request.getRequestDispatcher("/failure.html").forward(request, response);// 失败的话重定向至failure.html
        }
    
    }

}

add.html:
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加论文</title>
<style type="text/css">

table
{
    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
    font-size: 16px;
    align:center;
    margin:0 auto;
    width: 500px;
    text-align: left;
    border-collapse: collapse;
}
th
{
    font-size: 13px;
    font-weight: normal;
    padding: 8px;
    background: #b9c9fe;
    border-top: 4px solid #aabcfe;
    border-bottom: 1px solid #fff;
    color: #039;
}
td
{
    padding: 8px;
    background: #e8edff; 
    border-bottom: 1px solid #fff;
    color: #669;
    border-top: 1px solid transparent;
}

input,submit{
    background: #d0dafd;
    color: #339;
    border: 0;
}
</style>
<script>
</script>
</head>
<body>
    <div class="leftMenu">
  <table >
 <form action="add.do" method="post">
    <tr>
    <td colspan="2" align="center">添加学生信息</td>
  </tr>
   <tr>
   <td>id：</td>
   <td><input type="text" name="id" id="id"></td>
   </tr>
   <tr>
   <td>name：</td>
   <td><input type="text" name="name" id="name"></td>
   </tr>
   <tr>
   <td>sex：</td>
   <td><input type="text" name="sex" id="sex"></td>
   </tr>
   <tr>
   <td>birrh：</td>
   <td><input type="text" name="birth" id="birth"></td>
   </tr>

    <tr> 
    <tr align="center">
    <th colspan="2">
    <input type="submit" value="提交">
    </th>
    </tr>
    </form>
</table>
</div>
</body>
</html>
edit.jsp:
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改学生信息</title>
<style type="text/css">

table
{
    font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
    font-size: 16px;
    align:center;
    margin:0 auto;
    width: 500px;
    text-align: left;
    border-collapse: collapse;
}
th
{
    font-size: 13px;
    font-weight: normal;
    padding: 8px;
    background: #b9c9fe;
    border-top: 4px solid #aabcfe;
    border-bottom: 1px solid #fff;
    color: #039;
}
td
{
    padding: 8px;
    background: #e8edff; 
    border-bottom: 1px solid #fff;
    color: #669;
    border-top: 1px solid transparent;
}

input,submit{
    background: #d0dafd;
    color: #339;
    border: 0;
}

</style>
<script>
</script>
</head>
<body>
<%
         Object message = request.getAttribute("message");
         if(message!=null && !"".equals(message)){
     
    %>
         <script type="text/javascript">
              alert("<%=request.getAttribute("message")%>");
         </script>
    <%} %>
    <div class="leftMenu">
  <table>
  <form action="update.do" method="post"  onsubmit="return check()">
  
    <tr>
    <td colspan="2" align="center">修改学生信息</td>
  </tr>
  <td>需要修改的学生id为：</td>
   <td><input type="text" name="id0" id="id"></td>
   </tr>
   <td>修改后:</td>
   </tr>
   <tr>
   <td>id：</td>
   <td><input type="text" name="id" id="id"></td>
   </tr>
   <tr>
   <td>name：</td>
   <td><input type="text" name="name" id="name"></td>
   </tr>

   <tr>
   <td>sex：</td>
   <td><input type="text" name="sex" id="sex"></td>
   </tr>
   <tr>
   <td>birth：</td>
   <td><input type="text" name="birth" id="birth"></td>
   </tr>
    <tr align="center">
    <th colspan="2">
    <input type="submit" value="提交">
    </th>
    </tr>
    </form>
</table>
</div>
</body>
</html>
success.html:
<!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">
<link rel="stylesheet" type="text/css" href="css/style.css">
<title>操作成功提示</title>
</head>
<body>
    <p style="color: #CC1111">操作成功!</p>
    <a href="index.do">转到主页</a>
</body>
</html>
Index.html:
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生管理系统</title>
<style type="text/css">

         .title{
            width: 100%;
            height:60px;

        }
        li{
            list-style: none;
            padding: 10px;
            margin:0;
            float:left;
            

        }
        .ltitle{
            width: 100%;
            height: 50px;
            background: #b9c9fe;
            color: #669;
            font-size: 20px;
            text-align:center;
            letter-spacing:3px;
        }
        iframe{
            width:100%;
            height:500px;
            border: medium none;
            border-radius:10px;
            
        }
        a{
            color:#669;
            text-decoration:none;
            
        }
        a:visited{
            color:#669;
        }
        a:hover{
            font-size:20px;
        }
        .xia{
            width:100%;
            height:auto;
            margin-top:15px;
            border-radius:10px;
            float:left;
            background:#e8edff;
        }
        h1{
            text-align:center;
            color:#669;
        }

</style>
</head>
<body>
<div class="title">
    <h1>学生管理系统</h1>
</div>
<div class="ltitle">
    <li><a href="add.html" target="myframe">学生信息添加</a></li>
    <li><a href="edit.jsp" target="myframe">学生信息修改</a></li>
    
</div>

<div class="xia">
<iframe name="myframe"></iframe>
</div>
</body>
</html>