學習前，請先搞定資料庫的驅動和連線，具體看黑馬課程《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" %>