12.20 java实战2019年错题本系统（2）

今天继续2019年错题本系统的练习，昨天已经完成了除增删改查和页面设计的其他部分。
1.新增请假申请
Servlet（AddLeaveServlet）
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.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

@WebServlet("/addLeave")
public class AddLeaveServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 读取数据库连接配置
Properties properties = new Properties();
try {
properties.load(getServletContext().getResourceAsStream("/WEB-INF/db.properties"));
String url = properties.getProperty("db.url");
String username = properties.getProperty("db.username");
String password = properties.getProperty("db.password");

        // 1. 建立数据库连接
        Connection connection = DriverManager.getConnection(url, username, password);

        // 2. 从请求中获取请假申请信息
        String studentId = request.getParameter("student_id");
        String studentName = request.getParameter("student_name");
        String gender = request.getParameter("gender");
        String grade = request.getParameter("grade");
        String college = request.getParameter("college");
        String major = request.getParameter("major");
        String className = request.getParameter("class_name");
        String leaveReason = request.getParameter("leave_reason");
        String leaveDateStr = request.getParameter("leave_date");
        java.sql.Date leaveDate = java.sql.Date.valueOf(leaveDateStr);

        // 3. 编写 SQL 插入语句
        String sql = "INSERT INTO leave_application_info (student_id, student_name, gender, grade, college, major, class_name, leave_reason, leave_date) VALUES (?,?,?,?,?,?,?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, studentId);
        preparedStatement.setString(2, studentName);
        preparedStatement.setString(3, gender);
        preparedStatement.setString(4, grade);
        preparedStatement.setString(5, college);
        preparedStatement.setString(6, major);
        preparedStatement.setString(7, className);
        preparedStatement.setString(8, leaveReason);
        preparedStatement.setDate(9, leaveDate);

        // 4. 执行插入操作
        int rowsAffected = preparedStatement.executeUpdate();
        if (rowsAffected > 0) {
            request.setAttribute("message", "请假申请新增成功");
        } else {
            request.setAttribute("message", "请假申请新增失败");
        }

        // 5. 关闭资源
        preparedStatement.close();
        connection.close();

        // 转发到结果页面展示消息
        request.getRequestDispatcher("/result.jsp").forward(request, response);
    } catch (SQLException | java.io.IOException e) {
        e.printStackTrace();
        request.setAttribute("message", "系统出现异常，请稍后再试");
        request.getRequestDispatcher("/result.jsp").forward(request, response);
    }
}

2.修改请假申请 Servlet（UpdateLeaveServlet）：
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.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

@WebServlet("/updateLeave")
public class UpdateLeaveServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Properties properties = new Properties();
try {
properties.load(getServletContext().getResourceAsStream("/WEB-INF/db.properties"));
String url = properties.getProperty("db.url");
String username = properties.getProperty("db.username");
String password = properties.getProperty("db.password");

        Connection connection = DriverManager.getConnection(url, username, password);

        int id = Integer.parseInt(request.getParameter("id"));
        String leaveReason = request.getParameter("leave_reason");
        String leaveDateStr = request.getParameter("leave_date");
        java.sql.Date leaveDate = java.sql.Date.valueOf(leaveDateStr);

        String sql = "UPDATE leave_application_info SET leave_reason =?, leave_date =? WHERE id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, leaveReason);
        preparedStatement.setDate(2, leaveDate);
        preparedStatement.setInt(3, id);

        int rowsAffected = preparedStatement.executeUpdate();
        if (rowsAffected > 0) {
            request.setAttribute("message", "请假申请修改成功");
        } else {
            request.setAttribute("message", "请假申请修改失败");
        }

        preparedStatement.close();
        connection.close();

        request.getRequestDispatcher("/result.jsp").forward(request, response);
    } catch (SQLException | java.io.IOException e) {
        e.printStackTrace();
        request.setAttribute("message", "系统出现异常，请稍后再试");
        request.getRequestDispatcher("/result.jsp").forward(request, response);
    }
}

}
3.删除请假申请
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.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;

@WebServlet("/deleteLeave")
public class DeleteLeaveServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Properties properties = new Properties();
try {
properties.load(getServletContext().getResourceAsStream("/WEB-INF/db.properties"));
String url = properties.getProperty("db.url");
String username = properties.getProperty("db.username");
String password = properties.getProperty("db.password");

        Connection connection = DriverManager.getConnection(url, username, password);

        int id = Integer.parseInt(request.getParameter("id"));

        String sql = "DELETE FROM leave_application_info WHERE id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);

        int rowsAffected = preparedStatement.executeUpdate();
        if (rowsAffected > 0) {
            request.setAttribute("message", "请假申请删除成功");
        } else {
            request.setAttribute("message", "请假申请删除失败");
        }

        preparedStatement.close();
        connection.close();

        request.getRequestDispatcher("/result.jsp").forward(request, response);
    } catch (SQLException | java.io.IOException e) {
        e.printStackTrace();
        request.setAttribute("message", "系统出现异常，请稍后再试");
        request.getRequestDispatcher("/result.jsp").forward(request, response);
    }
}

}
4.查询请假申请
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.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

@WebServlet("/queryLeave")
public class QueryLeaveServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Properties properties = new Properties();
try {
properties.load(getServletContext().getResourceAsStream("/WEB-INF/db.properties"));
String url = properties.getProperty("db.url");
String username = properties.getProperty("db.username");
String password = properties.getProperty("db.password");

        Connection connection = DriverManager.getConnection(url, username, password);

        String keyword = request.getParameter("keyword");
        String sql = "SELECT student_name, college, major, class_name, leave_date, leave_reason FROM leave_application_info WHERE student_id LIKE? OR leave_reason LIKE? OR leave_date LIKE?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "%" + keyword + "%");
        preparedStatement.setString(2, "%" + keyword + "%");
        preparedStatement.setString(3, "%" + keyword + "%");

        ResultSet resultSet = preparedStatement.executeQuery();
        List<LeaveApplication> resultList = new ArrayList<>();
        while (resultSet.next()) {
            LeaveApplication application = new LeaveApplication();
            application.setStudentName(resultSet.getString("student_name"));
            application.setCollege(resultSet.getString("college"));
            application.setMajor(resultSet.getString("major"));
            application.setClassName(resultSet.getString("class_name"));
            application.setLeaveDate(resultSet.getDate("leave_date"));
            application.setLeaveReason(resultSet.getString("leave_reason"));
            resultList.add(application);
        }

        request.setAttribute("resultList", resultList);
        resultSet.close();
        preparedStatement.close();
        connection.close();

        request.getRequestDispatcher("/query_result.jsp").forward(request, response);
    } catch (SQLException | java.io.IOException e) {
        e.printStackTrace();
        request.setAttribute("message", "系统出现异常，请稍后再试");
        request.getRequestDispatcher("/result.jsp").forward(request, response);
    }
}

}
之后就是页面的设计
主页面

石家庄铁道大学请假条管理系统

• 新增请假申请
• 修改申请
• 删除申请
• 查询申请

新增请假页面

新增请假申请

学号：
姓名：
性别：
年级：
学院：
专业：
班级：
请假事由：
请假日期：

修改申请页面

修改请假申请

<%-- 这里需要先从数据库获取要修改的记录信息填充到表单中，以下是简单示意，实际需要结合 Servlet 等处理 --%>

请假事由：
请假日期：

完成后用tomcat在浏览器访问即可