MySQL JDBC 連線與 CRUD 操作完整實戰：BaseDao 封裝與測試類範例

MySQL JDBC 連線與 CRUD 操作完整實戰

視頻教學參考

MySQL JDBC 連線操作視頻教學

核心元件：BaseDao 封裝類

package cn.wingfly.dao;

import java.sql.*;

public class BaseDao {
    private Connection con = null;
    private Statement st = null;
    private ResultSet rs = null;

    /** 取得資料庫連線 */
    public Connection getConnection() {
        try {
            // 載入驅動 (兩種寫法皆可)
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            // 建立連線
            con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/money_note?characterEncoding=UTF-8",
                "root", "root");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return con;
    }

    /** 關閉資源 (逆序關閉) */
    public void closeConnection(Connection con, Statement st, ResultSet rs) {
        try {
            if (rs != null) rs.close();
            if (st != null) st.close();
            if (con != null) con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

測試類：完整 CRUD 示範

package cn.wingfly.dao;

import java.sql.*;

public class Test extends BaseDao {
    private Connection con = null;
    private Statement st = null;
    private ResultSet rs = null;

    /** 查詢所有用戶 */
    public void find() {
        con = getConnection();
        try {
            st = con.createStatement();
            rs = st.executeQuery("SELECT * FROM app_user");
            while (rs.next()) {
                System.out.println("編號：" + rs.getInt("uuid") + 
                    "，姓名：" + rs.getString("userName") +
                    "，性別：" + rs.getString("sex") +
                    "，生日：" + rs.getString("birthday") +
                    "，住址：" + rs.getString("address"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(con, st, rs);
        }
    }

    /** 新增用戶 */
    public void add() {
        con = getConnection();
        try {
            st = con.createStatement();
            int result = st.executeUpdate(
                "INSERT INTO app_user(userName,passWord,sex,birthday,address) " +
                "VALUES('趙麗穎','wanying','女','1992-02-03','北京市')");
            System.out.println(result > 0 ? "插入成功" : "插入失敗");
        } catch (SQLException e) {
            System.out.println("插入失敗"); e.printStackTrace();
        } finally {
            closeConnection(con, st, rs);
        }
    }

    /** 更新用戶地址 */
    public void update() {
        con = getConnection();
        try {
            st = con.createStatement();
            int result = st.executeUpdate(
                "UPDATE app_user SET address='河南' WHERE uuid='3'");
            System.out.println(result > 0 ? "更新成功" : "更新失敗");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(con, st, rs);
        }
    }

    /** 刪除用戶 */
    public void delete() {
        con = getConnection();
        try {
            st = con.createStatement();
            int result = st.executeUpdate("DELETE FROM app_user WHERE uuid='3'");
            System.out.println(result > 0 ? "刪除成功" : "刪除失敗");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(con, st, rs);
        }
    }

    public static void main(String[] args) {
        Test test = new Test();
        // test.add();
        // test.update();
        test.delete();
        test.find();
    }
}

關鍵最佳實踐總結

進階延伸

• PreparedStatement：參數化查詢，防注入、效能更好
• 事務控制：con.setAutoCommit(false) + commit()/rollback()
• 批次處理：addBatch() + executeBatch()
• ORM 框架：MyBatis/Hibernate/JPA 簡化資料存取層開發