Jdbc+Servlet+Jsp+MySQL 实现简单的登陆功能 加增删改查

好的，下面是一个基于Jdbc+Servlet+Jsp+MySQL的增删改查示例，以用户信息管理为例：

1. 创建数据库表

首先，需要在MySQL数据库中创建一个用户表，例如：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 编写用户列表页面

在JSP中编写一个用户列表页面，包括用户信息的表格以及添加用户和编辑用户的按钮，例如：

<table>
  <tr>
    <th>ID</th>
    <th>用户名</th>
    <th>密码</th>
    <th>邮箱</th>
    <th>操作</th>
  </tr>
  <c:forEach items="${users}" var="user">
    <tr>
      <td>${user.id}</td>
      <td>${user.username}</td>
      <td>${user.password}</td>
      <td>${user.email}</td>
      <td>
        <a href="edit?id=${user.id}">编辑</a>
        <a href="delete?id=${user.id}">删除</a>
      </td>
    </tr>
  </c:forEach>
</table>
<p><a href="add">添加用户</a></p>

3. 编写Servlet

在Servlet中处理用户列表、添加用户、编辑用户、删除用户四个请求，分别对应查询用户列表、跳转到添加用户页面、从数据库中查询指定用户信息并跳转到编辑用户页面、从数据库中删除指定用户信息。例如：

public class UserServlet extends HttpServlet {
  @Override
  protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String uri = request.getRequestURI();
    if (uri.endsWith("/user")) {
      // 查询用户列表
      List<User> users = getUserList();
      request.setAttribute("users", users);
      request.getRequestDispatcher("/WEB-INF/user/list.jsp").forward(request, response);
    } else if (uri.endsWith("/add")) {
      // 跳转到添加用户页面
      request.getRequestDispatcher("/WEB-INF/user/add.jsp").forward(request, response);
    } else if (uri.endsWith("/edit")) {
      // 从数据库中查询用户信息
      int id = Integer.parseInt(request.getParameter("id"));
      User user = getUserById(id);
      request.setAttribute("user", user);
      request.getRequestDispatcher("/WEB-INF/user/edit.jsp").forward(request, response);
    } else if (uri.endsWith("/delete")) {
      // 从数据库中删除用户信息
      int id = Integer.parseInt(request.getParameter("id"));
      deleteUserById(id);
      response.sendRedirect("user");
    }
  }

  @Override
  protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    String uri = request.getRequestURI();
    if (uri.endsWith("/add")) {
      // 添加用户到数据库
      String username = request.getParameter("username");
      String password = request.getParameter("password");
      String email = request.getParameter("email");
      User user = new User(username, password, email);
      addUser(user);
      response.sendRedirect("user");
    } else if (uri.endsWith("/edit")) {
      // 更新用户信息到数据库
      int id = Integer.parseInt(request.getParameter("id"));
      String username = request.getParameter("username");
      String password = request.getParameter("password");
      String email = request.getParameter("email");
      User user = new User(id, username, password, email);
      updateUser(user);
      response.sendRedirect("user");
    }
  }

  private List<User> getUserList() throws ServletException {
    String sql = "SELECT * FROM user";
    try (Connection conn = getConnection();
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {
      List<User> users = new ArrayList<>();
      while (rs.next()) {
        User user = new User(rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getString("email"));
        users.add(user);
      }
      return users;
    } catch (SQLException e) {
      throw new ServletException(e);
    }
  }

  private User getUserById(int id) throws ServletException {
    String sql = "SELECT * FROM user WHERE id = ?";
    try (Connection conn = getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
      stmt.setInt(1, id);
      try (ResultSet rs = stmt.executeQuery()) {
        if (rs.next()) {
          User user = new User(rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getString("email"));
          return user;
        } else {
          throw new ServletException("User not found");
        }
      }
    } catch (SQLException e) {
      throw new ServletException(e);
    }
  }

  private void addUser(User user) throws ServletException {
    String sql = "INSERT INTO user (username, password, email) VALUES (?, ?, ?)";
    try (Connection conn = getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
      stmt.setString(1, user.getUsername());
      stmt.setString(2, user.getPassword());
      stmt.setString(3, user.getEmail());
      stmt.executeUpdate();
    } catch (SQLException e) {
      throw new ServletException(e);
    }
  }

  private void updateUser(User user) throws ServletException {
    String sql = "UPDATE user SET username = ?, password = ?, email = ? WHERE id = ?";
    try (Connection conn = getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
      stmt.setString(1, user.getUsername());
      stmt.setString(2, user.getPassword());
      stmt.setString(3, user.getEmail());
      stmt.setInt(4, user.getId());
      stmt.executeUpdate();
    } catch (SQLException e) {
      throw new ServletException(e);
    }
  }

  private void deleteUserById(int id) throws ServletException {
    String sql = "DELETE FROM user WHERE id = ?";
    try (Connection conn = getConnection();
         PreparedStatement stmt = conn.prepareStatement(sql)) {
      stmt.setInt(1, id);
      stmt.executeUpdate();
    } catch (SQLException e) {
      throw new ServletException(e);
    }
  }

  private Connection getConnection() throws SQLException {
    return DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "password");
  }
}

4. 编写添加用户页面

在JSP中编写一个添加用户页面，包括用户名、密码、邮箱的输入框以及提交按钮，例如：

<form action="add" method="post">
  <label>用户名：</label>
  <input type="text" name="username"><br>
  <label>密码：</label>
  <input type="password" name="password"><br>
  <label>邮箱：</label>
  <input type="email" name="email"><br>
  <input type="submit" value="添加">
</form>

5. 编写编辑用户页面

在JSP中编写一个编辑用户页面，和添加用户页面类似，但是需要在表单中加入用户ID作为隐藏域，例如：

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>编辑用户</title>
</head>
<body>
  <form action="edit" method="post">
    <input type="hidden" name="id" value="${user.id}">
    <label>用户名：</label>
    <input type="text" name="username" value="${user.username}"><br>
    <label>密码：</label>
    <input type="password" name="password" value="${user.password}"><br>
    <label>邮箱：</label>
    <input type="email" name="email" value="${user.email}"><br>
    <input type="submit" value="保存">
  </form>
</body>
</html>

6. 配置Web.xml

最后，在Web.xml中配置Servlet和JSP的映射关系，例如：

<servlet>
  <servlet-name>UserServlet</servlet-name>
  <servlet-class>com.example.UserServlet</servlet-class>
</servlet>
<servlet-mapping>
  <servlet-name>UserServlet</servlet-name>
  <url-pattern>/user/*</url-pattern>
</servlet-mapping>

这样就完成了一个基于Jdbc+Servlet+Jsp+MySQL实现增删改查的Web应用。