一、通过 MVC 进行查询和删除操作
1. 准备一个数据表（examstudent）
2. 创建一个 查询 页面（test.jsp）
通过连接进入 Servlet（listAllStudents.java）
1 <%@ page language="java" contentType="text/html; charset=UTF-8"
2 pageEncoding="UTF-8"%>
3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
4 <html>
5 <head>
6 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
7 <title>Insert title here</title>
8 </head>
9 <body>
10 <a href="listAllStudents">List All Students</a>
11 </body>
12 </html>
3. 创建一个 Servlet（listAllStudents.java）
创建一个 StudentDao 对象，调用 StudentDao 中的 getAll() 方法，得到 Student 的 List 集合，并设置给 request 对象， request 通过转发把信息 传递到 students.jsp 页面
1 package com.panku.mvc;
2
3 import java.io.IOException;
4 import java.util.List;
5
6 import javax.servlet.ServletException;
7 import javax.servlet.annotation.WebServlet;
8 import javax.servlet.http.HttpServlet;
9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11
12 @WebServlet("/listAllStudents")
13 public class ListAllStudents extends HttpServlet {
14 private static final long serialVersionUID = 1L;
15
16 protected void doGet(HttpServletRequest request, HttpServletResponse response)
17 throws ServletException, IOException {
18
19 StudentDao studentDao = new StudentDao();
20 List<Student> students = studentDao.getAll();
21 request.setAttribute("students", students);
22
23 request.getRequestDispatcher("/students.jsp").forward(request, response);
24
25 }
26 }
4. 创建 StudentDao.java 文件
在 StudentDao,java 中 创建 getAll()、 deleteByFlowId(Integer flowId) 两个方法 分别实现 查询全部数据和删除一条数据的 操作
1 package com.panku.mvc;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.PreparedStatement;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.util.ArrayList;
9 import java.util.List;
10
11 public class StudentDao {
12
13 public void deleteByFlowId(Integer flowId) {
14
15 Connection connection = null;
16 PreparedStatement preparedStatement = null;
17
18 try {
19 String driverClass = "com.mysql.jdbc.Driver";
20 String url = "jdbc:mysql:///java_jdbc";
21 String user = "root";
22 String password = "123456";
23
24 Class.forName(driverClass);
25 connection = DriverManager.getConnection(url, user, password);
26
27 String sql = "DELETE FROM examstudent WHERE flow_id = ?";
28 preparedStatement = connection.prepareStatement(sql);
29
30 preparedStatement.setInt(1, flowId);
31 preparedStatement.executeUpdate();
32
33 } catch (Exception e) {
34 e.printStackTrace();
35 } finally {
36 try {
37 if (preparedStatement != null) {
38 preparedStatement.close();
39 }
40 } catch (SQLException e) {
41 e.printStackTrace();
42 }
43 try {
44 if (connection != null) {
45 connection.close();
46 }
47 } catch (SQLException e) {
48 e.printStackTrace();
49 }
50 }
51
52 }
53
54 public List<Student> getAll() {
55
56 List<Student> students = new ArrayList<>();
57
58 Connection connection = null;
59 PreparedStatement preparedStatement = null;
60 ResultSet resultSet = null;
61
62 try {
63 String driverClass = "com.mysql.jdbc.Driver";
64 String url = "jdbc:mysql:///java_jdbc";
65 String user = "root";
66 String password = "123456";
67
68 Class.forName(driverClass);
69 connection = DriverManager.getConnection(url, user, password);
70
71 String sql = "SELECT flow_id, type, id_card, exam_card, student_name, location, "
72 + "grade FROM examstudent";
73 preparedStatement = connection.prepareStatement(sql);
74 resultSet = preparedStatement.executeQuery();
75
76 while (resultSet.next()) {
77 int flowId = resultSet.getInt(1);
78 int type = resultSet.getInt(2);
79 String idCard = resultSet.getString(3);
80 String examCard = resultSet.getString(4);
81 String studentName = resultSet.getString(5);
82 String location = resultSet.getString(6);
83 int grade = resultSet.getInt(7);
84
85 Student student = new Student(flowId, type, idCard, examCard, studentName, location, grade);
86 students.add(student);
87 }
88
89 } catch (Exception e) {
90 e.printStackTrace();
91 } finally {
92 try {
93 if (resultSet != null) {
94 resultSet.close();
95 }
96 } catch (SQLException e) {
97 e.printStackTrace();
98 }
99 try {
100 if (preparedStatement != null) {
101 preparedStatement.close();
102 }
103 } catch (SQLException e) {
104 e.printStackTrace();
105 }
106 try {
107 if (connection != null) {
108 connection.close();
109 }
110 } catch (SQLException e) {
111 e.printStackTrace();
112 }
113 }
114
115 return students;
116 }
117
118 }
5. 创建一个Student.java 实体类
用来实例数据库的数据
1 package com.panku.mvc;
2
3 public class Student {
4
5 private Integer flowId;
6 private Integer type;
7 private String idCard;
8 private String examCard;
9 private String studentName;
10 private String location;
11 private Integer grade;
12
13 public Integer getFlowId() {
14 return flowId;
15 }
16
17 public void setFlowId(Integer flowId) {
18 this.flowId = flowId;
19 }
20
21 public Integer getType() {
22 return type;
23 }
24
25 public void setType(Integer type) {
26 this.type = type;
27 }
28
29 public String getIdCard() {
30 return idCard;
31 }
32
33 public void setIdCard(String idCard) {
34 this.idCard = idCard;
35 }
36
37 public String getExamCard() {
38 return examCard;
39 }
40
41 public void setExamCard(String examCard) {
42 this.examCard = examCard;
43 }
44
45 public String getStudentName() {
46 return studentName;
47 }
48
49 public void setStudentName(String studentName) {
50 this.studentName = studentName;
51 }
52
53 public String getLocation() {
54 return location;
55 }
56
57 public void setLocation(String location) {
58 this.location = location;
59 }
60
61 public Integer getGrade() {
62 return grade;
63 }
64
65 public void setGrade(Integer grade) {
66 this.grade = grade;
67 }
68
69 public Student(Integer flowId, Integer type, String idCard, String examCard, String studentName, String location,
70 Integer grade) {
71 super();
72 this.flowId = flowId;
73 this.type = type;
74 this.idCard = idCard;
75 this.examCard = examCard;
76 this.studentName = studentName;
77 this.location = location;
78 this.grade = grade;
79 }
80
81 public Student() {
82 super();
83 }
84
85 }
6. 创建一个 Student.jsp 页面通过表格方式显示查询结果
显示查询的结果，添加一个删除操作
1 <%@page import="com.panku.mvc.Student"%>
2 <%@page import="java.util.List"%>
3 <%@ page language="java" contentType="text/html; charset=UTF-8"
4 pageEncoding="UTF-8"%>
5 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
6 <html>
7 <head>
8 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
9 <title>Insert title here</title>
10 </head>
11 <body>
12
13 <%
14 List<Student> stus = (List) request.getAttribute("students");
15 %>
16
17 <table border="1" cellpadding="10" cellspacing="0">
18 <tr>
19 <th>FlowId</th>
20 <th>Type</th>
21 <th>IdCard</th>
22 <th>ExamCard</th>
23 <th>StudentName</th>
24 <th>Location</th>
25 <th>Grade</th>
26 <th>Delete</th>
27 </tr>
28
29 <%
30 for (Student student : stus) {
31 %>
32 <tr>
33 <td><%=student.getFlowId()%></td>
34 <td><%=student.getType()%></td>
35 <td><%=student.getIdCard()%></td>
36 <td><%=student.getExamCard()%></td>
37 <td><%=student.getStudentName()%></td>
38 <td><%=student.getLocation()%></td>
39 <td><%=student.getGrade()%></td>
40 <td><a href="deleteStudent?flowId=<%=student.getFlowId()%>">Delete</a></td>
41 </tr>
42 <%
43 }
44 %>
45
46 </table>
47
48 </body>
49 </html>
7. 创建一个 Servlet (DeleteStudent.java )
获取 Student.jsp 页面传过来的参数， 创建 StudentDAO 对象， 调用 StudentDAO中 删除方法（deleteByFlowId() 方法），并把 request 转发给 success.jsp 页面
1 package com.panku.mvc;
2
3 import java.io.IOException;
4 import javax.servlet.ServletException;
5 import javax.servlet.annotation.WebServlet;
6 import javax.servlet.http.HttpServlet;
7 import javax.servlet.http.HttpServletRequest;
8 import javax.servlet.http.HttpServletResponse;
9
10 @WebServlet("/deleteStudent")
11 public class DeleteStudent extends HttpServlet {
12 private static final long serialVersionUID = 1L;
13
14 protected void doGet(HttpServletRequest request, HttpServletResponse response)
15 throws ServletException, IOException {
16 String flowId = request.getParameter("flowId");
17
18 StudentDao studentDao = new StudentDao();
19 studentDao.deleteByFlowId(Integer.parseInt(flowId));
20
21 request.getRequestDispatcher("/success.jsp").forward(request, response);
22 }
23
24 }
8. 创建一个 success.jsp 页面
显示删除成功， 并可以点击链接 查看删除之后的数据
1 <%@ page language="java" contentType="text/html; charset=UTF-8"
2 pageEncoding="UTF-8"%>
3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
4 <html>
5 <head>
6 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
7 <title>Insert title here</title>
8 </head>
9 <body>
10 删除成功!!!
11 <br>
12 <br>
13 <a href="listAllStudents">List All Students</a>
14 </body>
15 </html>