单表增删改查
学习前，请先搞定数据库的驱动和连接，具体看黑马课程《java web》。
idea
先配置好Tomcat才可以进行，具体可以在CSDN上找教程
差旅费的简单单表增删改查
以下是数据库
以下是控制台代码。
其中部分代码看不懂的话，可以在B站中《JAVA JDBC》中搞定
其中数据库的操作因人而异，根据自己的用户名和密码进行操作
import javax.servlet.; // 导入Servlet包，用于创建Servlet
import javax.servlet.http.; // 导入HTTP Servlet包，用于处理HTTP请求和响应
import java.io.IOException; // 导入IOException，用于处理输入输出异常
import java.io.PrintWriter; // 导入PrintWriter，用于向客户端发送字符文本
import java.sql.*; // 导入SQL包，用于数据库操作
import java.util.ArrayList;
import java.util.List;
public class CourseServlet extends HttpServlet { // 定义CourseServlet类，继承自HttpServlet
private Connection connect = null; // 声明一个Connection对象，用于数据库连接
public void init() { // Servlet初始化方法
try {
// 数据库连接信息
String url = "jdbc:mysql://localhost:3306/data"; // 数据库URL
String user = "root"; // 数据库用户名
String password = "167183"; // 数据库密码
// 加载MySQL JDBC驱动程序
Class.forName("com.mysql.cj.jdbc.Driver"); // 加载MySQL JDBC驱动
System.out.println("驱动加载成功"); // 打印驱动加载成功的消息
// 建立与数据库的连接
connect = DriverManager.getConnection(url, user, password); // 连接到数据库
System.out.println("数据库连接成功"); // 打印数据库连接成功的消息
} catch (SQLException e) { // 捕获SQL异常
System.out.printf("数据库连接失败"); // 打印连接失败的错误消息
e.printStackTrace(); // 打印堆栈跟踪，用于调试
} catch (ClassNotFoundException e) { // 捕获ClassNotFound异常
System.out.println("驱动加载失败"); // 打印驱动加载失败的错误消息
e.printStackTrace(); // 打印堆栈跟踪，用于调试
}
}
public void destroy() { // Servlet销毁方法
try {
if (connect != null) { // 检查连接是否不为null
connect.close(); // 关闭数据库连接
}
} catch (SQLException e) { // 捕获SQL异常
e.printStackTrace(); // 打印堆栈跟踪，用于调试
}
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8"); // 设置请求字符编码为UTF-8
response.setCharacterEncoding("UTF-8"); // 设置响应字符编码为UTF-8
response.setContentType("text/html; charset=UTF-8"); // 设置响应内容类型为HTML，并指定UTF-8字符集
String action = request.getParameter("action"); // 从请求中获取action参数
if ("add".equals(action)) { // 如果action为"add"
addCourse(request, response); // 调用addCourse方法
} else if ("update".equals(action)) { // 如果action为"update"
updateCourse(request, response); // 调用updateCourse方法
} else if ("delete".equals(action)) { // 如果action为"delete"
deleteCourse(request, response); // 调用deleteCourse方法
}
}
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8"); // 设置请求字符编码为UTF-8
response.setCharacterEncoding("UTF-8"); // 设置响应字符编码为UTF-8
response.setContentType("text/html; charset=UTF-8"); // 设置响应内容类型为HTML，并指定UTF-8字符集
String action = request.getParameter("action"); // 从请求中获取action参数
if ("view".equals(action)) { // 如果action为"view"
viewCourses(request, response); // 调用viewCourses方法
} else if ("search".equals(action)) { // 如果action为"search"
searchCourses(request, response); // 调用searchCourses方法
}
}
private void addCourse(HttpServletRequest request, HttpServletResponse response) throws IOException {
int id = Integer.parseInt(request.getParameter("id"));
String name= request.getParameter("name");
String department = request.getParameter("department");
String destination = request.getParameter("destination");
String departuredate = request.getParameter("departuredate");
String returndate = request.getParameter("returndate");
String type = request.getParameter("type");
String reason = request.getParameter("reason");
try {
String sql = "INSERT INTO courses (id, name,department ,destination, departuredate,returndate,type,reason) VALUES (?, ?, ?, ?, ?,?,?,?)";
PreparedStatement stmt = connect.prepareStatement(sql);
stmt.setInt(1, id);
stmt.setString(2, name);
stmt.setString(3, department);
stmt.setString(4,destination);
stmt.setString(5, departuredate);
stmt.setString(6, returndate);
stmt.setString(7, type);
stmt.setString(8, reason);
int rowsAffected = stmt.executeUpdate();
response.getWriter().write("ID为: " + id);
} catch (SQLException e) {
e.printStackTrace();
response.getWriter().write("出错");
}
}
private void updateCourse(HttpServletRequest request, HttpServletResponse response) throws IOException {
// 从请求参数中获取详情
String id = request.getParameter("id");
String name = request.getParameter("name");
String department = request.getParameter("department");
String destination = request.getParameter("destination");
String departuredate = request.getParameter("departuredate");
String returndate = request.getParameter("returndate");
String Type = request.getParameter("Type");
String Reason = request.getParameter("Reason");
try {
// SQL查询用于更新数据库中现有
String sql = "UPDATE courses SET destination = ?, departuredate = ?, returndate = ?, Type = ?, Reason = ? WHERE id = ?"; // SQL更新语句
// 使用 try-with-resources 自动管理资源
try (PreparedStatement stmt = connect.prepareStatement(sql)) {
stmt.setString(1, destination);
stmt.setString(2, departuredate);
stmt.setString(3, returndate);
stmt.setString(4, Type);
stmt.setString(5, Reason);
stmt.setString(6, id); // 设置ID
int rowsAffected = stmt.executeUpdate(); // 执行更新，并获取受影响的行数
if (rowsAffected > 0) { // 如果有行被更新
response.getWriter().write("更新成功，ID为 " + id); // 响应成功消息
} else {
response.getWriter().write("未找到ID为 " + id); // 响应未找到课程的消息
}
}
} catch (SQLException e) { // 捕获SQL异常
e.printStackTrace(); // 打印堆栈跟踪，用于调试
response.getWriter().write("出错: " + e.getMessage()); // 响应错误消息，包含异常信息
}
}
private void deleteCourse(HttpServletRequest request, HttpServletResponse response) throws IOException {
String name= request.getParameter("name");
try {
// SQL查询用于删除数据库中的课程
String sql = "DELETE FROM courses WHERE name = ?"; // SQL删除语句
PreparedStatement stmt = connect.prepareStatement(sql); // 准备SQL语句
stmt.setString(1, name);
int rowsAffected = stmt.executeUpdate(); // 执行更新，并获取受影响的行数
if (rowsAffected > 0) { // 如果有行被删除
response.getWriter().write("删除成功，名字为 " + name); // 响应成功消息
} else {
response.getWriter().write("未找到名字为 " + name); // 响应未找到的消息
}
} catch (SQLException e) { // 捕获SQL异常
e.printStackTrace(); // 打印堆栈跟踪，用于调试
response.getWriter().write("出错"); // 响应错误消息
}
}
private void viewCourses(HttpServletRequest request, HttpServletResponse response) throws IOException {
response.setContentType("text/html"); // 设置响应内容类型为HTML
PrintWriter out = response.getWriter(); // 获取用于输出的PrintWriter对象
out.println("<html><body>"); // 输出HTML开头
out.println("<h2>差旅费报销列表</h2>"); // 输出标题
out.println("<ul>"); // 开始无序列表
try {
// SQL查询用于从数据库中获取课程列表
String sql = "SELECT * FROM courses"; // SQL查询语句
PreparedStatement stmt = connect.prepareStatement(sql); // 准备SQL语句
ResultSet rs = stmt.executeQuery(); // 执行查询，并获取结果集
while (rs.next()) { // 遍历结果集
// 输出每一门课程的详细信息
out.println("<li>ID: " + rs.getString("id") + ", 姓名: " + rs.getString("name") + ", 部门: " + rs.getInt("department") +
", 目的地: " + rs.getString("destination") +
", 出差日期: " + rs.getString("departuredate") +
", 返回日期: " + rs.getString("returndate") +
",出差类别: " + rs.getString("Type")+
", 出差事由: " + rs.getString("Reason") + "</li>");
}
} catch (SQLException e) { // 捕获SQL异常
e.printStackTrace(); // 打印堆栈跟踪，用于调试
}
out.println("</ul>"); // 结束无序列表
out.println("</body></html>"); // 输出HTML结束
}
private void searchCourses(HttpServletRequest request, HttpServletResponse response) throws IOException {
String departuredate = request.getParameter("departuredate"); // Get departure date from request
String reason = request.getParameter("Reason"); // Get reason from request
response.setContentType("text/html"); // Set response content type to HTML
PrintWriter out = response.getWriter(); // Get PrintWriter object for output
out.println("<html><body>"); // Output HTML start
try {
// Start building SQL query
StringBuilder sql = new StringBuilder("SELECT * FROM courses WHERE 1=1"); // Use 1=1 for easier appending
List<String> parameters = new ArrayList<>();
// Add Reason parameter if provided
if (reason != null && !reason.isEmpty()) {
sql.append(" AND Reason LIKE ?");
parameters.add("%" + reason + "%");
}
// Add departuredate parameter if provided
if (departuredate != null && !departuredate.isEmpty()) {
sql.append(" AND departuredate LIKE ?");
parameters.add("%" + departuredate + "%");
}
// Prepare SQL statement
try (PreparedStatement stmt = connect.prepareStatement(sql.toString())) {
// Set parameters
for (int i = 0; i < parameters.size(); i++) {
stmt.setString(i + 1, parameters.get(i));
}
try (ResultSet rs = stmt.executeQuery()) { // Execute query and get result set
if (rs.next()) { // Process records if any
out.println("<h2>查询结果</h2>"); // Output results header
out.println("<ul>"); // Start unordered list
do {
out.println("<li>ID: " + rs.getString("id") + ", 姓名: " + rs.getString("name") + ", 部门: " + rs.getInt("department") +
", 目的地: " + rs.getString("destination") +
", 出差日期: " + rs.getString("departuredate") +
", 返回日期: " + rs.getString("returndate") +
", 出差类别: " + rs.getString("Type") +
", 出差事由: " + rs.getString("reason") + "</li>");
} while (rs.next()); // Loop through all results
out.println("</ul>"); // End unordered list
} else {
out.println("<h2>未找到符合条件的出差申请</h2>"); // No results found message
}
}
}
} catch (SQLException e) { // Catch SQL exceptions
e.printStackTrace(); // Print stack trace for debugging
out.println("<li>查询出错: " + e.getMessage() + "</li>"); // Output error message
}
out.println("</body></html>"); // Output HTML end
}
}
以下是页面
（1）
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
浙公网安备 33010602011771号