Java + MySQL 學生管理系統：從零構建完整 CRUD 桌面應用

專案概覽

• **開發語言**：Java (Swing GUI)
• **資料庫**：MySQL
• **IDE**：Eclipse / IntelliJ IDEA
• **架構**：單表操作，四類核心類分工明確
• **功能**：學生信息增刪改查、條件查詢、表格展示

資料庫設計

CREATE TABLE stu (
    stuId VARCHAR(20) PRIMARY KEY,
    stuName VARCHAR(50),
    stuSex VARCHAR(10),
    stuAge INT,
    stuJG VARCHAR(50),   -- 籍貫
    stuDept VARCHAR(50)  -- 門派/班級
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

核心類架構

關鍵代碼解析

1. 主視窗 Test3 - 事件分發與模型綁定

public class Test3 extends JFrame implements ActionListener {
    private StuModel sm;
    private JTable jt;
    
    public Test3() {
        // 初始化模型與表格
        sm = new StuModel();
        jt = new JTable(sm);
        add(new JScrollPane(jt));
        
        // 工具欄按鈕
        jb1.addActionListener(this); // 查詢
        jb2.addActionListener(this); // 新增
        jb3.addActionListener(this); // 修改
        jb4.addActionListener(this); // 刪除
    }
    
    @Override
    public void actionPerformed(ActionEvent e) {
        if (e.getSource() == jb1) { // 查詢
            String name = jtf.getText().trim();
            sm = new StuModel("SELECT * FROM stu WHERE stuName = '" + name + "'");
            jt.setModel(sm);
        } else if (e.getSource() == jb2) { // 新增
            new StuAddDiag(this, "新增學生", true);
            refreshTable();
        } else if (e.getSource() == jb4) { // 刪除
            int row = jt.getSelectedRow();
            if (row == -1) return;
            String id = (String) sm.getValueAt(row, 0);
            deleteStudent(id);
            refreshTable();
        } else if (e.getSource() == jb3) { // 修改
            int row = jt.getSelectedRow();
            if (row == -1) return;
            new StuUpDiag(this, "修改學生", true, sm, row);
            refreshTable();
        }
    }
    
    private void refreshTable() {
        sm = new StuModel();
        jt.setModel(sm);
    }
}

2. 數據模型 StuModel - 封裝 JDBC 查詢

public class StuModel extends AbstractTableModel {
    private Vector<Vector<Object>> rowData;
    private Vector<String> columnNames;
    
    public StuModel() { this(""); }
    public StuModel(String sql) { init(sql); }
    
    private void init(String sql) {
        if (sql.isEmpty()) sql = "SELECT * FROM stu";
        
        columnNames = new Vector<>(Arrays.asList("學號", "姓名", "性別", "年齡", "籍貫", "班級"));
        rowData = new Vector<>();
        
        try (Connection ct = getConnection();
             Statement st = ct.createStatement();
             ResultSet rs = st.executeQuery(sql)) {
            
            while (rs.next()) {
                Vector<Object> row = new Vector<>();
                row.add(rs.getString("stuId"));
                row.add(rs.getString("stuName"));
                row.add(rs.getString("stuSex"));
                row.add(rs.getInt("stuAge"));
                row.add(rs.getString("stuJG"));
                row.add(rs.getString("stuDept"));
                rowData.add(row);
            }
        } catch (Exception ex) { ex.printStackTrace(); }
    }
    
    // 實現 AbstractTableModel 三個抽象方法...
    @Override public int getRowCount() { return rowData.size(); }
    @Override public int getColumnCount() { return columnNames.size(); }
    @Override public Object getValueAt(int r, int c) { return rowData.get(r).get(c); }
    @Override public String getColumnName(int c) { return columnNames.get(c); }
    
    private Connection getConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        return DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/spdb1?useSSL=false&serverTimezone=UTC",
            "root", "password");
    }
}

3. 新增對話框 StuAddDiag

public class StuAddDiag extends JDialog implements ActionListener {
    private JTextField[] fields = new JTextField[6];
    private String[] labels = {"學號", "姓名", "性別", "年齡", "籍貫", "班級"};
    
    public StuAddDiag(Frame owner, String title, boolean modal) {
        super(owner, title, modal);
        setLayout(new BorderLayout());
        
        // 輸入面板
        JPanel inputPanel = new JPanel(new GridLayout(6, 2));
        for (int i = 0; i < 6; i++) {
            inputPanel.add(new JLabel(labels[i]));
            fields[i] = new JTextField(10);
            inputPanel.add(fields[i]);
        }
        
        // 按鈕面板
        JPanel btnPanel = new JPanel();
        JButton btnAdd = new JButton("確定添加");
        btnAdd.addActionListener(this);
        btnPanel.add(btnAdd);
        btnPanel.add(new JButton("取消"));
        
        add(inputPanel, BorderLayout.CENTER);
        add(btnPanel, BorderLayout.SOUTH);
        pack(); setLocationRelativeTo(owner); setVisible(true);
    }
    
    @Override
    public void actionPerformed(ActionEvent e) {
        try (Connection ct = getConnection();
             PreparedStatement ps = ct.prepareStatement(
                 "INSERT INTO stu VALUES (?,?,?,?,?,?)")) {
            
            for (int i = 0; i < 6; i++) {
                ps.setString(i + 1, fields[i].getText().trim());
            }
            ps.executeUpdate();
            dispose(); // 關閉對話框
        } catch (Exception ex) { ex.printStackTrace(); }
    }
}

4. 修改對話框 StuUpDiag - 數據回填

public class StuUpDiag extends JDialog implements ActionListener {
    public StuUpDiag(Frame owner, String title, boolean modal, 
                     StuModel model, int row) {
        super(owner, title, modal);
        // 構建輸入框並回填數據
        fields[0] = new JTextField(model.getValueAt(row, 0).toString(), 10);
        fields[1] = new JTextField((String) model.getValueAt(row, 1), 10);
        fields[2] = new JTextField(model.getValueAt(row, 2).toString(), 10);
        fields[3] = new JTextField(model.getValueAt(row, 3).toString(), 10);
        fields[4] = new JTextField((String) model.getValueAt(row, 4), 10);
        fields[5] = new JTextField((String) model.getValueAt(row, 5), 10);
        // ... 佈局同新增對話框
    }
    
    @Override
    public void actionPerformed(ActionEvent e) {
        // 注意：此處原代碼用 INSERT 應改為 UPDATE
        String sql = "UPDATE stu SET stuName=?, stuSex=?, stuAge=?, stuJG=?, stuDept=? WHERE stuId=?";
        try (Connection ct = getConnection();
             PreparedStatement ps = ct.prepareStatement(sql)) {
            ps.setString(1, fields[1].getText());
            ps.setString(2, fields[2].getText());
            ps.setString(3, fields[3].getText());
            ps.setString(4, fields[4].getText());
            ps.setString(5, fields[5].getText());
            ps.setString(6, fields[0].getText()); // 學號作為 WHERE 條件
            ps.executeUpdate();
            dispose();
        } catch (Exception ex) { ex.printStackTrace(); }
    }
}

優化建議與最佳實踐

• 連接池：引入 HikariCP/Druid 替換逐次獲取連接
• DAO 層分離：將 SQL 操作抽離至 StudentDao 介面與實現
• 參數化查詢：查詢按鈕使用 PreparedStatement 防注入
• 異步載入：大數據量時用 SwingWorker 避免界面凍結
• 輸入驗證：年齡數字校驗、學號唯一性檢查
• 異常處理：統一異常彈窗提示，記錄日誌
• 分頁支持：表格模型實現分頁邏輯或使用 JXTable

運行截圖說明

1. **主界面**：工具欄含查詢框、增刪改按鈕，中央 JTable 顯示數據
2. **按姓名查詢**：輸入姓名點擊查詢，表格刷新顯示匹配行
3. **選中行刪除**：確認對話框後執行 DELETE，表格自動刷新
4. **選中行修改**：彈出模態框預填數據，修改後保存更新
5. **點擊新增**：空白模態框輸入完整信息，保存後表格新增一行