使用Servlet实现增删改查

在Java Web开发中，Servlet是一个非常重要的组件，它可以用来处理客户端的请求并生成响应。本文将介绍如何使用Servlet实现基本的增删改查（CRUD）操作，并提供示例代码和详细的分段解析。 

一、环境准备

在开始之前，请确保已安装以下开发工具：
- JDK（Java Development Kit）
- IDE（如Eclipse或IntelliJ IDEA）
- Apache Tomcat（Web服务器）
- 数据库（如MySQL）

二、创建数据库和表

首先，我们需要在MySQL中创建一个数据库和表，用于存储数据。

CREATE DATABASE chen;

USE student;

CREATE TABLE users (
    name VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL
);

 三、配置项目

在IDE中创建一个新的Web项目，并配置好Servlet和JDBC。将MySQL的JDBC驱动jar包添加到项目的类路径中。

四、实现增删改查操作的Servlet

我们将实现一个名为`UserServlet`的Servlet，它可以处理用户的增删改查操作。

1. 数据库连接工具类
 

package com.example.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/demo_db";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";

    public static Connection getConnection() throws SQLException {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }
}

解析：
- `DBUtil`类提供了一个静态方法`getConnection`，用于获取数据库连接。
- `Class.forName`方法用于加载MySQL的JDBC驱动。
- `DriverManager.getConnection`方法用于建立数据库连接。 2. UserServlet类
 

package com.example.servlet;

import com.example.util.DBUtil;
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 java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@WebServlet("/UserServlet")
public class UserServlet extends HttpServlet {
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String action = request.getParameter("action");
        try {
            switch (action) {
                case "list":
                    listUsers(request, response);
                    break;
                case "edit":
                    showEditForm(request, response);
                    break;
                case "delete":
                    deleteUser(request, response);
                    break;
                default:
                    listUsers(request, response);
                    break;
            }
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String action = request.getParameter("action");
        try {
            switch (action) {
                case "insert":
                    insertUser(request, response);
                    break;
                case "update":
                    updateUser(request, response);
                    break;
                default:
                    listUsers(request, response);
                    break;
            }
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }

    private void listUsers(HttpServletRequest request, HttpServletResponse response) throws SQLException, ServletException, IOException {
        Connection connection = DBUtil.getConnection();
        String sql = "SELECT * FROM users";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();

        request.setAttribute("userList", resultSet);
        request.getRequestDispatcher("user-list.jsp").forward(request, response);
    }

    private void insertUser(HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException {
        String name = request.getParameter("name");
        String email = request.getParameter("password");

        Connection connection = DBUtil.getConnection();
        String sql = "INSERT INTO users (name, password) VALUES (?, ?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, name);
        statement.setString(2, password);
        statement.executeUpdate();

        response.sendRedirect("UserServlet?action=list");
    }

    private void showEditForm(HttpServletRequest request, HttpServletResponse response) throws SQLException, ServletException, IOException {
        int id = Integer.parseInt(request.getParameter("id"));
        Connection connection = DBUtil.getConnection();
        String sql = "SELECT * FROM users WHERE id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, id);
        ResultSet resultSet = statement.executeQuery();

        if (resultSet.next()) {

request.setAttribute("user", resultSet);
            request.getRequestDispatcher("user-form.jsp").forward(request, response);
        }
    }

    private void updateUser(HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException {
        int id = Integer.parseInt(request.getParameter("id"));
        String name = request.getParameter("name");
        String email = request.getParameter("email");

        Connection connection = DBUtil.getConnection();
        String sql = "UPDATE users SET name = ?, email = ? WHERE id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, name);
        statement.setString(2, email);
        statement.setInt(3, id);
        statement.executeUpdate();

        response.sendRedirect("UserServlet?action=list");
    }

    private void deleteUser(HttpServletRequest request, HttpServletResponse response) throws SQLException, IOException {
        int id = Integer.parseInt(request.getParameter("id"));

        Connection connection = DBUtil.getConnection();
        String sql = "DELETE FROM users WHERE id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, id);
        statement.executeUpdate();

        response.sendRedirect("UserServlet?action=list");
    }
}

解析：UserServlet类继承自HttpServlet，并使用@WebServlet注解进行映射。doGet和doPost方法用于处理GET和POST请求，分别调用不同的CRUD操作方法。listUsers方法查询所有用户并将结果转发到user-list.jsp页面。insertUser方法从请求中获取用户输入的name和email，插入到数据库中。showEditForm方法根据用户ID查询用户信息，并转发到user-form.jsp页面。updateUser方法从请求中获取用户ID、name和email，更新数据库中的相应记录。deleteUser方法根据用户ID删除数据库中的相应记录。五、前端页面1. 用户列表页面（user-list.jsp）

<!DOCTYPE html>
<html>
<head>
    <title>User List</title>
</head>
<body>
<h2>User List</h2>
Add New User
<table border="1">
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Email</th>
        <th>Actions</th>
    </tr>
    <c:forEach var="user" items="${userList}">
        <tr>
            <td>${user.id}</td>
            <td>${user.name}</td>
            <td>${user.email}</td>
            <td>
                Edit
                Delete
            </td>
        </tr>
    </c:forEach>
</table>
</body>
</html>