JDBC 核心增刪改查（CRUD）實戰：MySQL 連線、PreparedStatement 與 ResultSet 完整範例

JDBC 核心四步驟

1. 載入驅動：`Class.forName("com.mysql.jdbc.Driver")`
2. 建立連線：`DriverManager.getConnection(url, user, pwd)`
3. 預編譯 SQL：`conn.prepareStatement(sql)` → `PreparedStatement`
4. 執行並處理結果：`executeUpdate()` / `executeQuery()` → `ResultSet`
5. 釋放資源：關閉 `ResultSet` → `Statement` → `Connection`（反序）

完整程式碼結構

public class JdbcCrudDemo {
    // 連線配置
    private static final String URL = "jdbc:mysql://localhost:3306/samp_db";
    private static final String USER = "root";
    private static final String PWD = "";

    private static Connection getConn() throws SQLException {
        return DriverManager.getConnection(URL, USER, PWD);
    }

    // 新增
    public int insert(Student s) {
        String sql = "INSERT INTO students(Name, Sex, Age) VALUES(?,?,?)";
        try (Connection c = getConn();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, s.getName());
            ps.setString(2, s.getSex());
            ps.setString(3, s.getAge());
            return ps.executeUpdate();
        }
    }

    // 修改
    public int update(Student s) {
        String sql = "UPDATE students SET Age=? WHERE Name=?";
        try (Connection c = getConn();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, s.getAge());
            ps.setString(2, s.getName());
            return ps.executeUpdate();
        }
    }

    // 查詢所有
    public void listAll() {
        String sql = "SELECT * FROM students";
        try (Connection c = getConn();
             PreparedStatement ps = c.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            int cols = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= cols; i++) {
                    System.out.print(rs.getString(i) + "\t");
                }
                System.out.println();
            }
        }
    }

    // 刪除
    public int deleteByName(String name) {
        String sql = "DELETE FROM students WHERE Name=?";
        try (Connection c = getConn();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, name);
            return ps.executeUpdate();
        }
    }
}

關鍵 API 速查

進階思考

• 連線池：生產環境必須使用 HikariCP/Druid 避免頻繁建立/關閉開銷
• 型別映射：Java `String` → MySQL `VARCHAR` 自動轉換，`Integer` → `INT`，不匹配會報錯或截斷
• 事務控制：`conn.setAutoCommit(false)` 手動 `commit/rollback`
• DAO 模式：將 SQL 與業務邏輯分離，便於測試與維護