JSP+Servlet实现对数据库增删改查功能

前提概要

需要理解的重要概念

1. ​MVC模式：

   • Model（person类）：数据模型
   • View（JSP）：显示界面
   • Controller（Servlet）：处理业务逻辑

2. ​请求流程：

   浏览器 → Servlet → 数据库 → JSP → 浏览器

3. ​两种跳转方式：

   • 转发（forward）：服务器内部跳转，地址栏不变
   • 重定向（redirect）：告诉浏览器重新请求，地址栏变化

4. ​数据库操作步骤：

   加载驱动 → 获取连接 → 创建语句 → 执行SQL → 处理结果 → 关闭连接

代码框架： 

一、Person类

Person类：

这个类的作用：

1. 就像"用户信息模板"，每个person对象对应数据库中的一条用户记录
2. 包含三个属性：
   • id：用户唯一标识
   • name：用户名
   • pwd：密码
3. Getter/Setter方法用于外部访问这些私有属性（Java封装的特性）

   package com.Person;public class person {private int id;private String name;private String pwd;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getPwd() {return pwd;}public void setPwd(String pwd) {this.pwd = pwd;}
   }
   

6. 二、Servlet类

AddServlet类

1. 用户访问/add，GET请求会跳转到add.jsp（显示表单）
2. 用户提交表单时（POST请求）：
   • 接收用户名和密码
   • 连接数据库并插入新用户
   • 成功后自动跳转到用户列表页

package com.servlet;import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.sql.*;@WebServlet(name = "AddServlet", urlPatterns = "/add")
public class AddServlet extends HttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {response.sendRedirect("add.jsp"); // 跳转到添加页面}protected void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {request.setCharacterEncoding("UTF-8");String name = request.getParameter("username");String pwd = request.getParameter("userpwd");try {Class.forName("com.mysql.cj.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/users?useSSL=false&serverTimezone=UTC", "root", "cjl");PreparedStatement statement = connection.prepareStatement("INSERT INTO person (name, pwd) VALUES (?, ?)");statement.setString(1, name);statement.setString(2, pwd);statement.executeUpdate();response.sendRedirect("listall"); // 添加成功跳转列表statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}}
}

ListAllServlet类：

1. 查询数据库获取所有用户
2. 把每个用户数据封装成person对象
3. 把用户列表存入request（就像把数据装进快递盒）
4. 转发到list.jsp显示（快递员把盒子送到JSP）

   package com.servlet;import com.Person.person;
   import javax.servlet.*;
   import javax.servlet.http.*;
   import javax.servlet.annotation.*;
   import java.io.IOException;
   import java.sql.*;
   import java.util.ArrayList;
   import java.util.List;@WebServlet(name = "ListAllServlet", urlPatterns = "/listall")
   public class ListAllServlet extends HttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {try {Class.forName("com.mysql.cj.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/users?useSSL=false&serverTimezone=UTC", "root", "cjl");Statement statement = connection.createStatement();ResultSet resultSet = statement.executeQuery("SELECT * FROM person");// 将数据存入 List<person>List<person> userList = new ArrayList<>();while (resultSet.next()) {person p = new person();p.setId(resultSet.getInt("id"));       // 确保列名与数据库一致p.setName(resultSet.getString("name"));p.setPwd(resultSet.getString("pwd"));userList.add(p);}// 关键修复点：将数据存入 request 并转发到 JSPrequest.setAttribute("userList", userList);request.getRequestDispatcher("/list.jsp").forward(request, response);// 关闭资源resultSet.close();statement.close();connection.close();} catch (Exception e) {e.printStackTrace();response.getWriter().println("数据库错误，请检查控制台日志");}}
   }

   DeleteServlet类

1. 通过URL参数获取要删除的用户id（如/del?id=3）
2. 执行数据库删除操作
3. 完成后跳回列表页

   package com.servlet;import javax.servlet.*;
   import javax.servlet.http.*;
   import javax.servlet.annotation.*;
   import java.io.IOException;
   import java.sql.*;@WebServlet(name = "DeleteServlet", urlPatterns = "/del")
   public class DeleteServlet extends HttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {String id = request.getParameter("id");try {Class.forName("com.mysql.cj.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/users?useSSL=false&serverTimezone=UTC", "root", "cjl");PreparedStatement statement = connection.prepareStatement("DELETE FROM person WHERE id=?");statement.setInt(1, Integer.parseInt(id));statement.executeUpdate();response.sendRedirect("listall"); // 直接跳转，不处理失败情况statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}}
   }

   UpdateServlet类（显示修改页面）：

1. 根据id查询要修改的用户
2. 把用户数据传给update.jsp显示在表单里

   package com.servlet;import com.Person.person;
   import javax.servlet.*;
   import javax.servlet.http.*;
   import javax.servlet.annotation.*;
   import java.io.IOException;
   import java.sql.*;@WebServlet(name = "UpdateServlet", urlPatterns = "/update")
   public class UpdateServlet extends HttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {String id = request.getParameter("id");if (id == null || id.isEmpty()) {response.sendRedirect("error.jsp?msg=Invalid+ID");return;}Connection connection = null;PreparedStatement statement = null;ResultSet resultSet = null;try {Class.forName("com.mysql.cj.jdbc.Driver");connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/users?useSSL=false&serverTimezone=UTC", "root", "cjl");statement = connection.prepareStatement("SELECT * FROM person WHERE id=?");statement.setInt(1, Integer.parseInt(id));resultSet = statement.executeQuery();if (resultSet.next()) {person p = new person();p.setId(resultSet.getInt("id"));p.setName(resultSet.getString("name"));p.setPwd(resultSet.getString("pwd"));request.setAttribute("user", p);} else {response.sendRedirect("error.jsp?msg=User+not+found");return;}request.getRequestDispatcher("/update.jsp").forward(request, response);} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();response.sendRedirect("error.jsp?msg=Database+error");} finally {try {if (resultSet != null) resultSet.close();if (statement != null) statement.close();if (connection != null) connection.close();} catch (SQLException e) {e.printStackTrace();}}}
   }

   UpdateDoServlet类（处理修改提交）

1. 接收修改后的用户名和密码
2. 执行数据库更新操作
3. 返回列表页

   package com.servlet;import javax.servlet.*;
   import javax.servlet.http.*;
   import javax.servlet.annotation.*;
   import java.io.IOException;
   import java.sql.*;@WebServlet(name = "UpdateDoServlet", urlPatterns = "/updateDo")
   public class UpdateDoServlet extends HttpServlet {protected void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {request.setCharacterEncoding("UTF-8");String id = request.getParameter("id");String name = request.getParameter("username");String pwd = request.getParameter("userpwd");try {Class.forName("com.mysql.cj.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/users?useSSL=false&serverTimezone=UTC", "root", "cjl");PreparedStatement statement = connection.prepareStatement("UPDATE person SET name=?, pwd=? WHERE id=?");statement.setString(1, name);statement.setString(2, pwd);statement.setInt(3, Integer.parseInt(id));statement.executeUpdate();response.sendRedirect("listall"); // 直接跳转列表statement.close();connection.close();} catch (Exception e) {e.printStackTrace();}}
   }

   Q：为什么修改用户要分成两个Servlet？
   A：一个负责显示修改页面（GET），一个处理修改提交（POST），符合单一职责原则

三、JSP 

add页面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>新增用户</title>
</head>
<body>
<h1>新增用户</h1>
<form action="add" method="post">姓名：<input type="text" name="username"><br>密码：<input type="password" name="userpwd"><br><input type="submit" value="提交">
</form>
</body>
</html>

list 页面

<%@ page import="com.Person.person" %>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>用户列表</title>
</head>
<body>
<h1>用户列表</h1>
<a href="add">新增用户</a>
<table border="1"><tr><th>ID</th><th>姓名</th><th>密码</th><th>操作</th></tr><%List<person> userList = (List<person>) request.getAttribute("userList");if (userList != null) {for (person user : userList) {%><tr><td><%= user.getId() %></td><td><%= user.getName() %></td><td><%= user.getPwd() %></td><td><a href="del?id=<%= user.getId() %>">删除</a><a href="update?id=<%= user.getId() %>">修改</a></td></tr><%}} else {%><tr><td colspan="4">暂无用户数据</td></tr><% } %>
</table>
</body>
</html>

update页面

<%@ page import="com.Person.person" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>修改用户</title>
</head>
<body>
<h1>修改用户信息</h1>
<%person user = (person) request.getAttribute("user");if (user != null) {%>
<form action="updateDo" method="post"><input type="hidden" name="id" value="<%= user.getId() %>">姓名：<input type="text" name="username" value="<%= user.getName() %>"><br>密码：<input type="password" name="userpwd" value="<%= user.getPwd() %>"><br><input type="submit" value="提交修改">
</form>
<% } else { %>
<p>错误：用户信息不存在！</p>
<% } %>
</body>
</html>

error页面

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>错误页面</title>
</head>
<body>
<h1>错误：<%= request.getParameter("msg") %></h1>
<a href="listall">返回用户列表</a>
</body>
</html>