這裡為了方便展覽，我直接寫到了一個類裡面，其中也涉及了一些前端互動
要注意其中的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>");
}
}