Java期中考试--行程信息统计--实现增删改查

硬件：微机环境：idea+mysql

数据库建立：

思路：建立数据库->建立实体类->实现增删改查方法（普通Java方法+Servlet）->页面（主页面+次页面）。

连接数据库

 1 package dao;
 2 
 3 import java.sql.*;
 4 
 5 public class DBUtil {
 6     static String url="jdbc:mysql://localhost:3306/db1";
 7     static String passwd="jia237106-";
 8     static String user="root";
 9 
10     public static Connection getConnection(){
11         Connection conn=null;
12         try {
13             Class.forName("com.mysql.jdbc.Driver");
14             conn= DriverManager.getConnection(url,user,passwd);
15             System.out.println("连接成功");
16         } catch (Exception e) {
17             System.out.println("连接失败");
18             e.printStackTrace();
19         }
20         return conn;
21     }
22     public static void close(Statement stmt, Connection conn, PreparedStatement ps, ResultSet rs){
23         if(stmt!=null){
24             try {
25                 stmt.close();
26             } catch (SQLException e) {
27                 e.printStackTrace();
28             }
29         }
30         if(conn!=null){
31             try {
32                 conn.close();
33             } catch (SQLException e) {
34                 e.printStackTrace();
35             }
36         }
37         if(ps!=null){
38             try {
39                 ps.close();
40             } catch (SQLException e) {
41                 e.printStackTrace();
42             }
43         }
44 
45         if(rs!=null){
46             try {
47                 rs.close();
48             } catch (SQLException e) {
49                 e.printStackTrace();
50             }
51         }
52     }
53 }

建立实体类，get和set方法

 1 package entity;
 2 
 3 public class Stud {
 4     private String stuname;
 5     private String id;
 6     private String stuclass;
 7     private String college;
 8     private String phone;
 9     private String health;
10     private String trip;
11     private String other0;
12 
13     public String getStuname() {
14         return stuname;
15     }
16 
17     public void setStuname(String stuname) {
18         this.stuname = stuname;
19     }
20 
21     public String getId() {
22         return id;
23     }
24 
25     public void setId(String id) {
26         this.id = id;
27     }
28 
29     public String getStuclass() {
30         return stuclass;
31     }
32 
33     public void setStuclass(String stuclass) {
34         this.stuclass = stuclass;
35     }
36 
37     public String getCollege() {
38         return college;
39     }
40 
41     public void setCollege(String college) {
42         this.college = college;
43     }
44 
45     public String getPhone() {
46         return phone;
47     }
48 
49     public void setPhone(String phone) {
50         this.phone = phone;
51     }
52 
53     public String getHealth() {
54         return health;
55     }
56 
57     public void setHealth(String health) {
58         this.health = health;
59     }
60 
61     public String getTrip() {
62         return trip;
63     }
64 
65     public void setTrip(String trip) {
66         this.trip = trip;
67     }
68 
69     public String getOther0() {
70         return other0;
71     }
72 
73     public void setOther0(String other0) {
74         this.other0 = other0;
75     }
76 }

实现增删改查方法和Servlet

1、增

 1 package dao;
 2 
 3 import entity.Stud;
 4 
 5 import java.sql.Connection;
 6 import java.sql.PreparedStatement;
 7 import java.sql.ResultSet;
 8 import java.sql.SQLException;
 9 
10 public class Add {
11     Connection conn=(Connection) DBUtil.getConnection();
12 
13     public boolean add(Stud stu){
14         boolean flag=false;
15         ResultSet rs=null;
16         String sql="insert into stud values(?,?,?,?,?,?,?,?)";
17         try {
18             PreparedStatement pstmt= conn.prepareStatement(sql);
19             pstmt.setString(1,stu.getStuname());
20             pstmt.setString(2, stu.getId());
21             pstmt.setString(3,stu.getStuclass());
22             pstmt.setString(4, stu.getCollege());
23             pstmt.setString(5, stu.getPhone());
24             pstmt.setString(6,stu.getHealth());
25             pstmt.setString(7,stu.getTrip());
26             pstmt.setString(8,stu.getOther0());
27             int i= pstmt.executeUpdate();
28             pstmt.close();
29             conn.close();
30             if(i>0) flag=true;
31         } catch (SQLException e) {
32             e.printStackTrace();
33         }
34         return flag;
35     }
36 }

 1 package Servlet;
 2 
 3 import dao.Add;
 4 import entity.Stud;
 5 
 6 import javax.servlet.*;
 7 import javax.servlet.http.*;
 8 import javax.servlet.annotation.*;
 9 import java.io.IOException;
10 import java.util.Arrays;
11 
12 @WebServlet(name = "addServlet", value = "/addServlet")
13 public class addServlet extends HttpServlet {
14     @Override
15     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
16         request.setCharacterEncoding("UTF-8");
17         response.setContentType("text/html;charset=utf-8");
18 
19         String stuname=request.getParameter("stuname");
20         String id=request.getParameter("id");
21         String stuclass=request.getParameter("stuclass");
22         String college=request.getParameter("college");
23         String phone=request.getParameter("phone");
24         String health=request.getParameter("health");
25         String [] h=request.getParameterValues("trip");
26         String trip=Arrays.toString(h);
27         trip=trip.substring(1,trip.length()-1);
28         String other0=request.getParameter("other0");
29 
30 
31         Stud stud=new Stud();
32         stud.setStuname(stuname);
33         stud.setId(id);
34         stud.setStuclass(stuclass);
35         stud.getStuclass();
36         stud.setCollege(college);
37         stud.setPhone(phone);
38         stud.setHealth(health);
39         stud.setTrip(trip);
40         stud.setOther0(other0);
41         Add s=new Add();
42         try{
43             s.add(stud);
44             response.sendRedirect(request.getContextPath()+"/success.jsp");
45         }catch(Exception e){
46             response.sendRedirect(request.getContextPath()+"/fail.jsp");
47         }
48     }
49 }

2、删

 1 package dao;
 2 
 3 import entity.Stud;
 4 
 5 import java.sql.Connection;
 6 import java.sql.PreparedStatement;
 7 import java.sql.SQLException;
 8 
 9 public class Delete {
10     Connection conn=DBUtil.getConnection();
11 
12     public boolean delete(Stud stu){
13         boolean flag=false;
14         String sql="delete from stud where id=?";
15         try {
16             PreparedStatement pstmt= conn.prepareStatement(sql);
17             pstmt.setString(1,stu.getId());
18             int i=pstmt.executeUpdate();
19             pstmt.close();
20             conn.close();
21             if(i>0) flag=true;
22         } catch (SQLException e) {
23             e.printStackTrace();
24         }
25         return flag;
26     }
27 }

 1 package deleteServlet;
 2 
 3 import dao.Delete;
 4 import entity.Stud;
 5 
 6 import javax.servlet.*;
 7 import javax.servlet.http.*;
 8 import javax.servlet.annotation.*;
 9 import java.io.IOException;
10 
11 @WebServlet(name = "delete", value = "/delete")
12 public class delete extends HttpServlet {
13     private static final long serialVersionUID = 1L;
14     @Override
15     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
16         doPost(request,response);
17     }
18 
19     @Override
20     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
21         request.setCharacterEncoding("UTF-8");
22         response.setContentType("text/html;charset=utf-8");
23 
24         String id=request.getParameter("id");
25         Stud stu=new Stud();
26         stu.setId(id);
27         Delete d=new Delete();
28         try{
29             d.delete(stu);
30             response.sendRedirect(request.getContextPath()+"/main.jsp");
31         }catch (Exception e){
32             System.out.println("删除失败");
33             e.printStackTrace();
34         }
35     }
36 }

3、改

 1 package dao;
 2 
 3 import entity.Stud;
 4 
 5 import java.sql.Connection;
 6 import java.sql.PreparedStatement;
 7 import java.sql.Statement;
 8 
 9 //根据学号修改
10 public class update {
11     Connection conn=DBUtil.getConnection();
12     public boolean update(Stud stu){
13         boolean flag=false;
14         String sql="update stud  set stuname=?,stuclass=?,college=?,phone=?,health=?,trip=?,other0=?where id=?";
15         Statement state=null;
16         try{
17             PreparedStatement pstmt= conn.prepareStatement(sql);
18             pstmt.setString(1,stu.getStuname());
19             pstmt.setString(2,stu.getStuclass());
20             pstmt.setString(3,stu.getCollege());
21             pstmt.setString(4,stu.getPhone());
22             pstmt.setString(5,stu.getHealth());
23             pstmt.setString(6,stu.getTrip());
24             pstmt.setString(7,stu.getOther0());
25             pstmt.setString(8,stu.getId());
26             int i=pstmt.executeUpdate();
27             if(i>0) flag=true;
28             pstmt.close();
29             conn.close();
30         }catch(Exception e){
31             e.printStackTrace();
32         }
33         return flag;
34     }
35 }

 1 package UpdateServlet;
 2 
 3 import dao.update;
 4 import entity.Stud;
 5 
 6 import javax.servlet.*;
 7 import javax.servlet.http.*;
 8 import javax.servlet.annotation.*;
 9 import java.io.IOException;
10 import java.util.Arrays;
11 
12 @WebServlet(name = "udate", value = "/udate")
13 public class udate extends HttpServlet {
14     @Override
15     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
16 
17     }
18 
19     @Override
20     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
21         request.setCharacterEncoding("UTF-8");
22         response.setContentType("text/html,charset=utf-8");
23         String stuname=request.getParameter("stuname");
24         String id=request.getParameter("id");
25         String stuclass=request.getParameter("stuclass");
26         String college=request.getParameter("college");
27         String phone=request.getParameter("phone");
28         String health=request.getParameter("health");
29         String [] h=request.getParameterValues("trip");
30         String trip= Arrays.toString(h);
31         trip=trip.substring(1,trip.length()-1);
32         String other0=request.getParameter("other0");
33 
34         Stud stud=new Stud();
35         stud.setStuname(stuname);
36         stud.setId(id);
37         stud.setStuclass(stuclass);
38         stud.getStuclass();
39         stud.setCollege(college);
40         stud.setPhone(phone);
41         stud.setHealth(health);
42         stud.setTrip(trip);
43         stud.setOther0(other0);
44 
45         update u=new update();
46         try{
47             u.update(stud);
48             response.sendRedirect(request.getContextPath()+"/main.jsp");
49         }catch(Exception e){
50             System.out.println("修改失败");
51             e.printStackTrace();
52         }
53     }
54 }

4、查，根据id查询

 1 package dao;
 2 
 3 import entity.Stud;
 4 
 5 import java.sql.Connection;
 6 import java.sql.PreparedStatement;
 7 import java.sql.ResultSet;
 8 import java.util.ArrayList;
 9 import java.util.List;
10 
11 public class Search {
12     static int i=1;
13     public int searchid(ArrayList<Stud> studs,String id){
14         Connection conn=DBUtil.getConnection();
15 //        List<Stud> list=new ArrayList<Stud>();
16         try{
17             String sql="select * from stud where id=?";
18             PreparedStatement pstmt=conn.prepareStatement(sql);
19             pstmt.setString(1,id);
20             ResultSet rs= pstmt.executeQuery();
21             System.out.println(rs);
22             if(rs==null) i=0;
23             else {
24                 while (rs.next()) {
25                     Stud stu = new Stud();
26                     stu.setStuname(rs.getNString("stuname"));
27                     stu.setId(rs.getString("id"));
28                     stu.setStuclass(rs.getString("stuclass"));
29                     stu.setCollege(rs.getString("college"));
30                     stu.setPhone(rs.getString("phone"));
31                     stu.setHealth(rs.getString("health"));
32                     stu.setTrip(rs.getString("trip"));
33                     stu.setOther0(rs.getString("other0"));
34                     studs.add(stu);
35                 }
36             }
37             rs.close();
38             pstmt.close();
39             conn.close();
40         }catch(Exception e){
41             e.printStackTrace();
42         }
43         return i;
44     }
45 }

 1 package searchServlet;
 2 
 3 import dao.Search;
 4 import entity.Stud;
 5 
 6 import javax.servlet.*;
 7 import javax.servlet.http.*;
 8 import javax.servlet.annotation.*;
 9 import java.io.IOException;
10 import java.io.PrintWriter;
11 import java.io.Writer;
12 import java.util.ArrayList;
13 import java.util.List;
14 
15 @WebServlet("/search")
16 public class search extends HttpServlet {
17     private static final long serialVersionUID = 1L;
18     public search(){
19         super();
20     }
21     @Override
22     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
23         doPost(request,response);
24     }
25 
26     @Override
27     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
28         request.setCharacterEncoding("utf-8");
29         response.setContentType("text/html;charset=utf-8");
30 
31         int a=0;
32         String n=request.getParameter("id");
33         ArrayList<Stud> stu=new ArrayList<Stud>();
34         System.out.println(n);
35         Search search=new Search();
36 //        if(n!=null){
37             try{
38                 a=search.searchid(stu,n);
39             }catch(Exception e){
40                 e.printStackTrace();
41             }
42             if(a==1) {
43                 request.setAttribute("id", stu);
44                 request.getRequestDispatcher("search.jsp").forward(request, response);
45             }
46         else{
47             response.getWriter().write("输入数据不存在");
48         }
49     }
50 }

各个.jsp

主页面

 1 <%--
 2   Created by IntelliJ IDEA.
 3   User: 贾梓钊
 4   Date: 2021/11/11
 5   Time: 17:02
 6   To change this template use File | Settings | File Templates.
 7 --%>
 8 <%@ page contentType="text/html;charset=UTF-8" language="java" %>
 9 <html>
10 <head>
11     <title>主界面</title>
12 </head>
13 <body>
14 <div id="addSubjectForm" align="center">
15     <form action="addServlet" method="post">
16         <tr>
17             <td>行程信息管理系统</td>
18         </tr>
19         <ul>
20             <li><a href="add.jsp">信息添加</a></li>
21             <li><a href="delete.jsp">信息删除</a></li>
22             <li><a href="update.jsp">信息修改</a></li>
23             <li><a href="search1.html">信息查询</a> </li>
24         </ul>
25     </form>
26 
27 </div>
28 </body>
29 </html>

添加页面，有添加提示页面，这里就不展示了（特别简单）

 1 <%--
 2   Created by IntelliJ IDEA.
 3   User: 贾梓钊
 4   Date: 2021/11/5
 5   Time: 14:13
 6   To change this template use File | Settings | File Templates.
 7 --%>
 8 <%@ page contentType="text/html;charset=UTF-8" language="java" %>
 9 <html>
10 <head>
11     <meta charset="UTF-8">
12     <title>行程统计</title>
13 </head>
14 <body bgcolor="aqua">
15 <p align="center">行程信息统计系统</p>
16 <div id="addSubjectForm" align="center">
17     <form action="addServlet" method="post">
18         <tr>
19             <td>姓名:</td>
20             <td><input type="text" name="stuname" size="20"><br></td>
21         </tr>
22         <tr>
23             <td>学号:</td>
24             <td><input type="text" name="id" size="20"><br></td>
25         </tr>
26         <tr>
27             <td>学生类别:</td>
28             <td><input type="radio" name="stuclass" value="本科生">本科生
29                 <input type="radio" name="stuclass" value="研究生">研究生
30             </td>
31         </tr>
32         <br>
33         <tr>
34             <td>电话：</td>
35             <td><input type="text" name="phone" size="20">
36         </tr>
37         <br>
38         <tr>
39             <td>院系：</td>
40             <select name="college">
41                 <option value="土木学院">土木学院</option>
42                 <option value="机械学院" >机械学院</option>
43                 <option value="交通学院" >交通学院</option>
44                 <option value="信息学院" >信息学院</option>
45                 <option value="经管学院" >经管学院</option>
46             </select>
47         </tr>
48         <br>
49         <tr>
50             <td>健康码：</td>
51             <td><input type="checkbox" name="health" value="绿色">绿色
52                 <input type="checkbox" name="health" value="黄色">黄色
53                 <input type="checkbox" name="health" value="红色">红色</td>
54         </tr>
55         <br>
56         <tr>
57             <td>行程统计：</td>
58             <td>
59                 <input type="checkbox" name="trip" value="10月30日去过人民医院">10月30日去过人民医院<br>
60                 <input type="checkbox" name="trip" value="10月25日以来去过深泽县人民医院">10月25日以来去过深泽县人民医院<br>
61                 <input type="checkbox" name="trip" value="10月16日以来去过深泽县庄泽村">10月16日以来去过深泽县庄泽村<br>
62                 <input type="checkbox" name="trip" value="10月29日以来去过黑龙江哈尔滨市或者黑河市">10月29日以来去过黑龙江哈尔滨市或者黑河市<br>
63                 <input type="checkbox" name="trip" value="10月18日以来途径贵州遵义市；北京丰台、昌平">10月18日以来途径贵州遵义市；北京丰台、昌平<br>
64                 <input type="checkbox" name="trip" value="10月17日以来到过湖南长沙；青海海东市">10月17日以来到过湖南长沙；青海海东市<br>
65             </td>
66         </tr>
67         <br>
68         <tr>
69             <td>其他涉疫信息需要填报的:</td>
70             <td><input type="text" name="other0" size="50"><br></td>
71         </tr>
72         <tr>
73             <td colspan="2"><div align="center"><input type="submit" value="提交">
74             </div>
75     </form>
76 </div>
77 </body>
78 </html>

删除页面

 1 <%--
 2   Created by IntelliJ IDEA.
 3   User: 贾梓钊
 4   Date: 2021/11/11
 5   Time: 16:56
 6   To change this template use File | Settings | File Templates.
 7 --%>
 8 <%@ page contentType="text/html;charset=UTF-8" language="java" %>
 9 <html>
10 <head>
11     <title>删除界面</title>
12 </head>
13 <body bgcolor="aqua">
14 <p align="center">行程信息删除</p>
15 <div id="addSubjectForm" align="center">
16     <form action="delete" method="post">
17         <tr>
18             <td>请输入要删除信息的学号：</td>
19             <td><input type="text" name="id" size="20"></td><br>
20         </tr>
21         <tr>
22             <td colspan="2">
23                 <div align="center">
24                     <input type="submit" value="删除">
25                 </div>
26 
27             </td>
28         </tr>
29     </form>
30 </div>
31 
32 </body>
33 </html>

修改页面

 1 <%--
 2   Created by IntelliJ IDEA.
 3   User: 贾梓钊
 4   Date: 2021/11/11
 5   Time: 18:01
 6   To change this template use File | Settings | File Templates.
 7 --%>
 8 <%@ page contentType="text/html;charset=UTF-8" language="java" %>
 9 <html>
10 <head>
11     <title>修改界面</title>
12     <meta charset="UTF-8">
13 </head>
14 <body>
15 <p align="center">行程信息修改</p>
16 <div id="addSubject" align="center">
17     <form action="udate" method="post">
18         <tr>
19             <td>姓名:</td>
20             <td><input type="text" name="stuname" size="20"><br></td>
21         </tr>
22         <tr>
23             <td>学号:</td>
24             <td><input type="text" name="id" size="20"><br></td>
25         </tr>
26         <tr>
27             <td>学生类别:</td>
28             <td><input type="radio" name="stuclass" value="本科生">本科生
29                 <input type="radio" name="stuclass" value="研究生">研究生
30             </td>
31         </tr>
32         <br>
33         <tr>
34             <td>电话：</td>
35             <td><input type="text" name="phone" size="20">
36         </tr>
37         <br>
38         <tr>
39             <td>院系：</td>
40             <select name="college">
41                 <option value="土木学院">土木学院</option>
42                 <option value="机械学院" >机械学院</option>
43                 <option value="交通学院" >交通学院</option>
44                 <option value="信息学院" >信息学院</option>
45                 <option value="经管学院" >经管学院</option>
46             </select>
47         </tr>
48         <br>
49         <tr>
50             <td>健康码：</td>
51             <td><input type="checkbox" name="health" value="绿色">绿色
52                 <input type="checkbox" name="health" value="黄色">黄色
53                 <input type="checkbox" name="health" value="红色">红色</td>
54         </tr>
55         <br>
56         <tr>
57             <td>行程统计：</td>
58             <td>
59                 <input type="checkbox" name="trip" value="10月30日去过人民医院">10月30日去过人民医院<br>
60                 <input type="checkbox" name="trip" value="10月25日以来去过深泽县人民医院">10月25日以来去过深泽县人民医院<br>
61                 <input type="checkbox" name="trip" value="10月16日以来去过深泽县庄泽村">10月16日以来去过深泽县庄泽村<br>
62                 <input type="checkbox" name="trip" value="10月29日以来去过黑龙江哈尔滨市或者黑河市">10月29日以来去过黑龙江哈尔滨市或者黑河市<br>
63                 <input type="checkbox" name="trip" value="10月18日以来途径贵州遵义市；北京丰台、昌平">10月18日以来途径贵州遵义市；北京丰台、昌平<br>
64                 <input type="checkbox" name="trip" value="10月17日以来到过湖南长沙；青海海东市">10月17日以来到过湖南长沙；青海海东市<br>
65             </td>
66         </tr>
67         <br>
68         <tr>
69             <td>其他涉疫信息需要填报的:</td>
70             <td><input type="text" name="other0" size="50"><br></td>
71         </tr>
72         <tr>
73             <td colspan="2"><div align="center"><input type="submit" value="修改">
74             </div>
75     </form>
76 </div>
77 </body>
78 </html>

删除页面，一个.jsp页面和一个.html界面，html前端跳转到.jsp（输出格式），接受servlet的信息并输出

.html

 1 <!DOCTYPE html>
 2 <html lang="en">
 3 <head>
 4     <meta charset="UTF-8">
 5     <title>Title</title>
 6 </head>
 7 <body>
 8 <div id="addSubjectForm" align="center">
 9     <form action="search" method="post">
10         <tr>
11             <h2>请输入学号：</h2>
12             <td><input type="text" name="id" size="20"></td>
13         </tr>
14         <td>
15             <input type="submit" value="查询">
16         </td>
17     </form>
18 </div>
19 </body>
20 </html>

.jsp

 1 <%--
 2   Created by IntelliJ IDEA.
 3   User: 贾梓钊
 4   Date: 2021/11/12
 5   Time: 9:04
 6   To change this template use File | Settings | File Templates.
 7 --%>
 8 <%@ page contentType="text/html;charset=UTF-8" language="java" %>
 9 <%@page import="dao.DBUtil" %>
10 <%@page import="searchServlet.search" %>
11 <%@page import="java.util.ArrayList" %>
12 <%@page import="java.util.List" %>
13 <%@page import="entity.Stud" %>
14 <html>
15 <head>
16     <title>查询</title>
17     <meta charset="UTF-8">
18 </head>
19 <body>
20 <table border="1">
21     <tr>
22         <th>学生姓名</th>
23         <th>学号</th>
24         <th>学生类别</th>
25         <th>学院</th>
26         <th>电话</th>
27         <th>健康码</th>
28         <th>行程</th>
29         <th>其他</th>
30     </tr>
31     <%
32         ArrayList<Stud> stu=(ArrayList<Stud>) request.getAttribute("id");
33         for(int i=0;i< stu.size();i++){
34             Stud m=stu.get(i);
35     %>
36     <tr>
37         <td><%=m.getStuname()%></td>
38         <td><%=m.getId()%></td>
39         <td><%=m.getStuname()%></td>
40         <td><%=m.getCollege()%></td>
41         <td><%=m.getPhone()%></td>
42         <td><%=m.getHealth()%></td>
43         <td><%=m.getTrip()%></td>
44         <td><%=m.getOther0()%></td>
45     </tr>
46 
47     <%
48         }
49     %>
50 </table>
51 </body>
52 </html>

运行界面

这次的期中考试收获还是很多的，对于.html和.jsp的应用更加熟练，但是对于Servlet的操作还是不太熟悉。

posted on 2021-11-13 10:44 跨越&尘世阅读(136) 评论(0) 编辑收藏举报