Servlet实现增删改查

发表于 更新于 分类于

　　在期末的时候需要使用Servlet实现增删改查功能，记录一下，可以参考Maven环境变量配置中使用maven创建web项目，使用maven可以很方便的管理项目中所使用的包

声明

　　使用jsp+servlet实现，项目并不是很完善，目前还有很多问题，只能是基本实现简单的增删改查

Maven创建web项目

参考Maven环境变量配置

项目目录结构

reDmNT.jpgreDEBq.jpg

类

StudentDao

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
package dao;

import model.Student;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Vector;

public class StudentDao extends Util{

    //返回所有学生的集合
    public static Vector<Student> getAllStudent() throws ClassNotFoundException, SQLException {
        Vector<Student> students=new Vector<>();
        Connection connection=getConnection();
        Statement statement=connection.createStatement();
        ResultSet resultSet=statement.executeQuery("select * from stu");
        while(resultSet.next()) {
            Student student=new Student();
            student.setStuNo(resultSet.getString(1));
            student.setName(resultSet.getString(2));
            student.setSex(resultSet.getString(3));
            student.setBirthday(resultSet.getString(4));
            student.setPro(resultSet.getString(5));
            student.setSalary(resultSet.getInt(6));
            students.add(student);
        }
        closeAll(connection, statement,resultSet);
        return students;
    }

    //通过学号查询并返回某个学生
    public static Student getOneStudent(String id) throws ClassNotFoundException, SQLException {
        Student student=null;
        Connection connection=getConnection();
        PreparedStatement statement=connection.prepareStatement("select * from stu where stuNo=?");
        statement.setString(1, id);
        ResultSet resultSet=statement.executeQuery();
        while(resultSet.next()) {
            student=new Student();
            student.setStuNo(resultSet.getString(1));
            student.setName(resultSet.getString(2));
            student.setSex(resultSet.getString(3));
            student.setBirthday(resultSet.getString(4));
            student.setPro(resultSet.getString(5));
            student.setSalary(resultSet.getInt(6));
        }
        closeAll(connection, statement,resultSet);
        return student;
    }

    //向学生列表添加某学生
    public static void addOneStudent(Student student) throws ClassNotFoundException, SQLException {
        Connection connection=getConnection();
        PreparedStatement statement=connection.prepareStatement(
                "insert into stu values(?,?,?,?,?,?)");
        statement.setString(1, student.getStuNo());
        statement.setString(2, student.getName());
        statement.setString(3, student.getSex());
        statement.setString(4, student.getBirthday());
        statement.setString(5, student.getPro());
        statement.setInt(6, student.getSalary());
        statement.executeUpdate();
        closeAll(connection, statement);
    }

    //通过学号删除某学生
    public static void delOneStudent(String id) throws ClassNotFoundException, SQLException {
        Connection connection=getConnection();
        PreparedStatement statement=connection.prepareStatement(
                "delete from stu where stuNo=?");
        statement.setString(1, id);
        statement.executeUpdate();
        closeAll(connection, statement);
    }

    //通过学号修改某学生信息
    public static void updateOneStudent(Student student) throws ClassNotFoundException, SQLException {
        Connection connection=getConnection();
        PreparedStatement statement=connection.prepareStatement(
                "update stu set name=?,sex=?,birthday=?,pro=?,salary=?"
                        + " where stuNo=?");
        statement.setString(1, student.getName());
        statement.setString(2, student.getSex());
        statement.setString(3, student.getBirthday());
        statement.setString(4, student.getPro());
        statement.setInt(5, student.getSalary());
        statement.setString(6, student.getStuNo());
        statement.executeUpdate();
        closeAll(connection, statement);
    }

}

Util

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
package dao;

import java.sql.*;

public class Util {

    private static String DRIVER="com.mysql.cj.jdbc.Driver";//数据库连接jar包5.0是com.mysql.jdbc.Driver，此为8.0的驱动路径
    private static String URL="jdbc:mysql://localhost:3306/student?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&autoReconnect=true";  //指定连接的数据库以及连接属性设置
    private static String USERNAME="root";//用户名
    private static String PWD="root";//密码

    public static Connection getConnection() throws ClassNotFoundException, SQLException {//获得连接
        Connection connection=null;
        Class.forName(DRIVER);
        connection=DriverManager.getConnection(URL, USERNAME, PWD);
        return connection;
    }

    public static void closeAll(Connection connection,Statement statement,ResultSet resultSet) throws SQLException {//关闭连接
        if(connection!=null) connection.close();
        if(statement!=null) statement.close();
        if(resultSet!=null) resultSet.close();
    }

    public static void closeAll(Connection connection,Statement statement) throws SQLException {//关闭连接
        if(connection!=null) connection.close();
        if(statement!=null) statement.close();
    }

}

Student

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
package model;


public class Student {

    private String stuNo;
    private String name;
    private String sex;
    private String birthday;
    private String pro;
    private int salary;

    public String getStuNo() {
        return stuNo;
    }
    public void setStuNo(String stuNo) {
        this.stuNo = stuNo;
    }
    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 getBirthday() {
        return birthday;
    }
    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }
    public String getPro() {
        return pro;
    }
    public void setPro(String pro) {
        this.pro = pro;
    }
    public int getSalary() {
        return salary;
    }
    public void setSalary(int salary) {
        this.salary = salary;
    }
}

AddServlet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
package servlets;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Vector;

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 dao.StudentDao;
import model.Student;

/**
 * Servlet implementation class AddServlet
 */
@WebServlet("/AddServlet")
public class AddServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public AddServlet() {
        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 {
        request.setCharacterEncoding("utf-8");
        Student student=new Student();
        student.setStuNo(request.getParameter("stuNo"));
        student.setName(request.getParameter("name"));
        student.setSex(request.getParameter("sex"));
        student.setBirthday(request.getParameter("birthday"));
        student.setPro(request.getParameter("pro"));
        student.setSalary(Integer.parseInt(request.getParameter("salary")));
        try {
            StudentDao.addOneStudent(student);
            Vector<Student> students=StudentDao.getAllStudent();
            request.getSession().setAttribute("students", students);
            response.sendRedirect("index.jsp");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

}

DeleteServlet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
package servlets;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Vector;

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 dao.StudentDao;
import model.Student;

/**
 * Servlet implementation class DeleteServlet
 */
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public DeleteServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String id=request.getParameter("id");
        try {
            StudentDao.delOneStudent(id);
            Vector<Student> students=StudentDao.getAllStudent();
            request.getSession().setAttribute("students", students);
            response.sendRedirect("index.jsp");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

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

}

InitServlet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
package servlets;


import java.io.IOException;
import java.sql.SQLException;
import java.util.Vector;

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 javax.websocket.Session;
import dao.StudentDao;
import model.Student;

/**
 * Servlet implementation class InitServlet
 */
@WebServlet("/InitServlet")
public class InitServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public InitServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            Vector<Student> students=StudentDao.getAllStudent();
            request.getSession().setAttribute("students", students);
            response.sendRedirect("index.jsp");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)  {

    }
}

SearchServlet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
package servlets;


import java.io.IOException;
import java.sql.SQLException;

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 dao.StudentDao;
import model.Student;

/**
 * Servlet implementation class SearchServlet
 */
@WebServlet("/SearchServlet")
public class SearchServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public SearchServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            Student one=StudentDao.getOneStudent(request.getParameter("id"));
            request.getSession().setAttribute("one", one);
            response.sendRedirect("update.jsp");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

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

}

UpdateServlet

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
package servlets;


import java.io.IOException;
import java.sql.SQLException;
import java.util.Vector;

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 dao.StudentDao;
import model.Student;

/**
 * Servlet implementation class UpdateServlet
 */
@WebServlet("/UpdateServlet")
public class UpdateServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public UpdateServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            Student student=StudentDao.getOneStudent(request.getParameter("id"));
            request.getSession().setAttribute("one", student);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        Student student=new Student();
        student.setStuNo(request.getParameter("stuNo"));
        student.setName(request.getParameter("name"));
        student.setSex(request.getParameter("sex"));
        student.setBirthday(request.getParameter("birthday"));
        student.setPro(request.getParameter("pro"));
        student.setSalary(Integer.parseInt(request.getParameter("salary")));
        try {
            StudentDao.updateOneStudent(student);
            Vector<Student> students=StudentDao.getAllStudent();
            request.getSession().setAttribute("students", students);
            response.sendRedirect("index.jsp");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

web.xml

1
2
3
4
5
6
7
8
9
10
11
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xmlns="http://java.sun.com/xml/ns/javaee"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
 http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
         id="WebApp_ID" version="3.0">
  <display-name>Work</display-name>
  <welcome-file-list>
    <welcome-file>InitServlet</welcome-file>
  </welcome-file-list>
</web-app>

index.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt" %>
<!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">
    <title>主页</title>
    <link type="text/css" rel="stylesheet"  href="css/style.css" />
</head>
<body>
<table border="2">
    <tr>
        <th>学号</th>
        <th>姓名</th>
        <th>性别</th>
        <th>出生年月</th>
        <th>专业</th>
        <th>奖学金</th>
        <th>操作</th>
    </tr>
    <c:forEach items="${students}" var="student">
        <tr>
            <td>${student.stuNo}</td>
            <td>${student.name}</td>
            <td>${student.sex}</td>
            <td>${student.birthday}</td>
            <td>${student.pro}</td>
            <td>${student.salary}</td>
            <td><a href=DeleteServlet?id=${student.stuNo}>删除</a>
                <a href=SearchServlet?id=${student.stuNo}>修改</a></td>
        </tr>
    </c:forEach>
    <tr>
        <form action="AddServlet" method="post">
            <td><input type="text" name="stuNo" placeholder="学号" /></td>
            <td><input type="text" name="name" placeholder="姓名" /></td>
            <td><select name="sex">
                <option value="男">男</option>
                <option value="女">女</option>
            </select></td>
            <td><input type="date" name="birthday" /></td>

            <td><input type="text" name="pro" placeholder="专业"/></td>
            <td><input type="number" name="salary" placeholder="奖学金" /></td>
            <td><input type="submit" value="添加" /></td>
        </form>
    </tr>
</table>
</body>
</html>

update.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
<%@ page language="java" contentType="text/html; charset=UTF-8"
         pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!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">
    <title>修改</title>
    <link type="text/css" rel="styleSheet"  href="css/style.css" />
</head>
<body>
<form action=UpdateServlet?stuNo=${one.stuNo} method="post">
    <table border="1">
        <tr>
            <th></th>
            <th>修改前</th>
            <th>修改后</th>
        </tr>
        <tr>
            <th>姓名</th>
            <td> ${one.name} </td>
            <td><input type="text" name="name"></td>
        </tr>
        <tr>
            <th>性别</th>
            <td> ${one.sex}</td>
            <td><select name="sex">
                <option value="男">男</option>
                <option value="女">女</option>
            </select></td>
        </tr>
        <tr>
            <th>出生日期</th>
            <td> ${one.birthday}</td>
            <td><input type="date" name="birthday"></td>
        </tr>
        <tr>
            <th>专业</th>
            <td> ${one.pro}</td>
            <td><input type="text" name="pro"></td>
        </tr>
        <tr>
            <th>奖学金</th>
            <td> ${one.salary}</td>
            <td><input type="number" name="salary"></td>
        </tr>
        <tr>
            <th>操作</th>
            <td><input type="button" value="返回" onclick="window.location.href='index.jsp'">
            </td>
            <td><input type="submit" value="保存"></td>
        </tr>
    </table>
</form>

</body>
</html>

以下是所用依赖

reDeEV.jpg

访问项目

reDVH0.jpg