Java Web Servlet 處理外鍵關聯的增刪改查 (CRUD) 實戰範例

場景：學生-班級 (多對一)

-- 班級表
CREATE TABLE class (
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(50)
);

-- 學生表 (外鍵關聯班級)
CREATE TABLE student (
    sid INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(50),
    cid INT,
    FOREIGN KEY (cid) REFERENCES class(cid)
);

實體類

public class Student {
    private int sid;
    private String sname;
    private Class clazz; // 關聯對象，而非簡單 cid
    // getter/setter/toString
}

public class Class {
    private int cid;
    private String cname;
    // getter/setter
}

DAO 層 (使用 PreparedStatement 防注入)

public class StudentDAO {
    // 查詢所有學生 (含班級名) - 多表連接
    public List findAll() throws SQLException {
        String sql = "SELECT s.sid, s.sname, c.cid, c.cname " +
                     "FROM student s LEFT JOIN class c ON s.cid = c.cid";
        List list = new ArrayList<>();
        try (Connection conn = DBUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Student s = new Student();
                s.setSid(rs.getInt("sid"));
                s.setSname(rs.getString("sname"));
                Class c = new Class();
                c.setCid(rs.getInt("cid"));
                c.setCname(rs.getString("cname"));
                s.setClazz(c);
                list.add(s);
            }
        }
        return list;
    }
    
    // 新增學生 (外鍵 cid)
    public void add(Student s) throws SQLException {
        String sql = "INSERT INTO student (sname, cid) VALUES (?, ?)";
        try (Connection conn = DBUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, s.getSname());
            ps.setInt(2, s.getClazz().getCid()); // 設置外鍵值
            ps.executeUpdate();
        }
    }
    
    // 刪除學生 (注意：若班級下有學生，直接刪班級會違反外鍵約束)
    public void delete(int sid) throws SQLException {
        String sql = "DELETE FROM student WHERE sid = ?";
        try (Connection conn = DBUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, sid);
            ps.executeUpdate();
        }
    }
    
    // 修改學生班級
    public void update(Student s) throws SQLException {
        String sql = "UPDATE student SET sname=?, cid=? WHERE sid=?";
        try (Connection conn = DBUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, s.getSname());
            ps.setInt(2, s.getClazz().getCid());
            ps.setInt(3, s.getSid());
            ps.executeUpdate();
        }
    }
}

Servlet 控制器

@WebServlet("/student")
public class StudentServlet extends HttpServlet {
    StudentDAO dao = new StudentDAO();
    
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String action = req.getParameter("action");
        try {
            if ("list".equals(action)) {
                req.setAttribute("students", dao.findAll());
                req.getRequestDispatcher("/student_list.jsp").forward(req, resp);
            } else if ("delete".equals(action)) {
                int sid = Integer.parseInt(req.getParameter("sid"));
                dao.delete(sid);
                resp.sendRedirect(req.getContextPath() + "/student?action=list"); // PRG 模式
            } else if ("toAdd".equals(action) || "toEdit".equals(action)) {
                // 轉發到表單頁，下拉框需班級列表
                req.setAttribute("classes", new ClassDAO().findAll());
                req.getRequestDispatcher("/student_form.jsp").forward(req, resp);
            }
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }
    
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        String action = req.getParameter("action");
        try {
            Student s = new Student();
            s.setSname(req.getParameter("sname"));
            s.setClazz(new Class());
            s.getClazz().setCid(Integer.parseInt(req.getParameter("cid")));
            
            if ("add".equals(action)) {
                dao.add(s);
            } else if ("edit".equals(action)) {
                s.setSid(Integer.parseInt(req.getParameter("sid")));
                dao.update(s);
            }
            resp.sendRedirect(req.getContextPath() + "/student?action=list");
        } catch (SQLException e) {
            throw new ServletException(e);
        }
    }
}

JSP 表單頁關鍵片段 (下拉框綁定外鍵)

    
    
    
    姓名: 
    
    班級: 
    
        
            
                ${c.cname}
            
        
    
    
    提交

外鍵約束處理策略

最佳實踐總結

• 實體類使用**對象關聯** (`Class clazz`) 而非單純 `int cid`，便於 OOP 與 JSP EL 表達式 (`${student.clazz.cname}`)。
• DAO 使用 `try-with-resources` 自動關閉資源 (JDBC 4.0+)。
• Servlet 採用 **PRG 模式** (Post-Redirect-Get) 防止表單重複提交。
• 生產環境建議引入 **連接池** (HikariCP/Druid) + **事務管理** (Spring `@Transactional` 或手動 `conn.setAutoCommit(false)`)。