web学习记录15

今天继续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 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在浏览器访问即可