JDBC连接数据库实现增删改查

这里为了方便展览，我直接写到了一个类里面，其中也涉及了一些前端交互
要注意其中的sql语句的对象，可以根据自己的数据库内容名称进行修改
这个是原本的表

import javax.servlet.;
import javax.servlet.http.;
import javax.servlet.annotation.;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.;

public class CourseServlet extends HttpServlet {
    private Connection connect = null;

    public void init() {
        try {
            // 数据库连接信息
            String url = "jdbc:mysql://localhost:3306/data";
            String user = "root";
            String password = "root";

            // 加载 MySQL JDBC 驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("驱动加载成功");

            // 建立连接
            connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/data?characterEncoding=UTF-8","root","root");
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            System.out.printf("数据库连接失败");
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }
    }

    public void destroy() {
        try {
            if (connect != null) {
                connect.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");

        String action = request.getParameter("action");
        if ("add".equals(action)) {
            addCourse(request, response);
        } else if ("update".equals(action)) {
            updateCourse(request, response);
        } else if ("delete".equals(action)) {
            deleteCourse(request, response);
        }
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");

        String action = request.getParameter("action");
        if ("view".equals(action)) {
            viewCourses(request, response);
        } else if ("search".equals(action)) {
            searchCourses(request, response);
        }
    }

    private void addCourse(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String courseId = request.getParameter("courseId");
        String courseName = request.getParameter("courseName");
        String studentCount = request.getParameter("studentCount");
        String teacherName = request.getParameter("teacherName");
        String location = request.getParameter("location");
        String description=request.getParameter("description");
        String time=request.getParameter("leaveDate");
        try {
            String sql = "INSERT INTO courses (id, name, student_count, teacher_name, location,description,time) VALUES (?, ?, ?, ?, ?,?,?)";
            PreparedStatement stmt = connect.prepareStatement(sql);
            stmt.setString(1, courseId);
            stmt.setString(2, courseName);
            stmt.setString(3, studentCount);
            stmt.setString(4, teacherName);
            stmt.setString(5, location);
            stmt.setString(6,description);
            stmt.setString(7,time);
            int rowsAffected = stmt.executeUpdate();
            response.getWriter().write("添加工号为: " + courseId);
        } catch (SQLException e) {
            e.printStackTrace();
            response.getWriter().write("添加出差申请错误");
        }
    }

    private void updateCourse(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String courseId = request.getParameter("courseId");
        String courseName = request.getParameter("courseName");
        String description = request.getParameter("description");
        try {
            String sql = "UPDATE courses SET name = ?, description = ? WHERE id = ?";
            PreparedStatement stmt = connect.prepareStatement(sql);
            stmt.setString(1, courseName);
            stmt.setString(2, description);
            stmt.setString(3, courseId);
            int rowsAffected = stmt.executeUpdate();
            if (rowsAffected > 0) {
                response.getWriter().write("出差申请已经修改: " + courseId);
            } else {
                response.getWriter().write("没有找到该申请: " + courseId);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            response.getWriter().write("修改失败");
        }
    }

    private void deleteCourse(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String courseId = request.getParameter("courseId");
        try {
            String sql = "DELETE FROM courses WHERE id = ?";
            PreparedStatement stmt = connect.prepareStatement(sql);
            stmt.setString(1, courseId);
            int rowsAffected = stmt.executeUpdate();
            if (rowsAffected > 0) {
                response.getWriter().write("出差申请已删除 " + courseId);
            } else {
                response.getWriter().write("没有找到该申请 " + courseId);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            response.getWriter().write("删除错误");
        }
    }

    private void viewCourses(HttpServletRequest request, HttpServletResponse response) throws IOException {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        out.println("<html><body>");
        out.println("<h2>Courses</h2>");
        out.println("<ul>");
        try {
            String sql = "SELECT * FROM courses";
            Statement stmt = connect.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                out.println("<li>学号: " + rs.getString("id") + ",学院"+rs.getString("student_count")+",学生专业"+rs.getString("teacher_name")+",学生班级"+rs.getString("location")+",学生姓名"+ rs.getString("name") + ", 请假事由: " + rs.getString("description") +",请假时间"+rs.getString("time")+ "</li>");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        out.println("</ul>");
        out.println("</body></html>");
    }

    private void searchCourses(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String searchName = request.getParameter("searchName");
        String searchName2=request.getParameter("searchName2");
        String searchName3=request.getParameter("searchName3");
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        out.println("<html><body>");
        out.println("<h2>查找结果</h2>");
        out.println("<ul>");
        try {
            String sql = "SELECT * FROM courses WHERE id LIKE ? OR name LIKE ? OR description LIKE ?";
            PreparedStatement stmt = connect.prepareStatement(sql);
            stmt.setString(1,"%" + searchName + "%");
            stmt.setString(2,"%" +searchName2+ "%");
            stmt.setString(3,"%" +searchName3+ "%");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                out.println("<li>学生学号: " + rs.getString("id") + ", 学生姓名: " + rs.getString("name")+",学生学院"+rs.getString("student_count")+",学生专业" + rs.getString("teacher_name")+",学生班级"+rs.getString("location")+"请假时间"+rs.getString("time")+", 请假事由: " + rs.getString("description") + "</li>");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        out.println("</ul>");
        out.println("</body></html>");
    }
}