JDBC 編程完整指南：從環境搭建到工具類封裝與軟編碼實踐

一、為什麼需要 JDBC

數據庫是應用核心，Java 程序通過 JDBC（Java Database Connectivity）統一訪問各類數據庫。JDBC 定義了一組標準接口，廠商提供驅動實現，實現「寫一次，到處運行」。

二、SQL 三大分類

三、開發環境準備

1. IDE：MyEclipse / IntelliJ IDEA / Eclipse
2. 數據庫：MySQL（示例）
3. 驅動包：mysql-connector-java-x.x.x.jar → 放入項目 lib/ → Add to Build Path

四、標準 JDBC 六步走

// 1. 載入驅動
Class.forName("com.mysql.jdbc.Driver");

// 2. 定義 URL
String url = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8";

// 3. 獲取連接
Connection conn = DriverManager.getConnection(url, "root", "root");

// 4. 創建 Statement
Statement stmt = conn.createStatement();

// 5. 執行 SQL
ResultSet rs = stmt.executeQuery("SELECT * FROM WORKER");

// 6. 處理結果 / 釋放資源
while (rs.next()) {
    System.out.println(rs.getString("worker_num"));
}
rs.close(); stmt.close(); conn.close();

五、DQL 查詢完整示例

public class JdbcTest {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8";
        String user = "root", psw = "root";
        String sql = "SELECT * FROM WORKER";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, psw);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                System.out.println(rs.getString("worker_num"));
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 逆序關閉
            try { if (rs != null) rs.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (stmt != null) stmt.close(); } catch (SQLException e) { e.printStackTrace(); }
            try { if (conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }
}

六、改進一：配置文件外部化（軟編碼）

db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf-8
username=root
password=root

七、改進二：JDBC 工具類封裝

public class JDBCUtils {
    private static String driverClass, url, userName, passWord;

    static {
        Properties p = new Properties();
        try (InputStream is = JDBCUtils.class.getResourceAsStream("db.properties")) {
            p.load(is);
            driverClass = p.getProperty("driver");
            url = p.getProperty("url");
            userName = p.getProperty("username");
            passWord = p.getProperty("password");
            Class.forName(driverClass);
        } catch (IOException | ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, userName, passWord);
        } catch (SQLException e) {
            throw new RuntimeException("獲取連接失敗", e);
        }
    }

    public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
        closeQuietly(rs);
        closeQuietly(stmt);
        closeQuietly(conn);
    }

    public static void closeAll(PreparedStatement pstmt, Connection conn) {
        closeQuietly(pstmt);
        closeQuietly(conn);
    }

    private static void closeQuietly(AutoCloseable closeable) {
        if (closeable != null) {
            try { closeable.close(); } catch (Exception ignored) {}
        }
    }
}

八、改進後的業務代碼

public class NewGetWorker {
    public static void main(String[] args) {
        String sql = "SELECT * FROM WORKER";
        try (Connection conn = JDBCUtils.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                System.out.println(rs.getString("worker_num") + " " +
                                   rs.getString("worker_name") + " " +
                                   rs.getString("worker_address") + " " +
                                   rs.getString("worker_level"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        // try-with-resources 自動關閉，無需 finally
    }
}

九、最佳實踐總結

1. 使用 PreparedStatement：預編譯防 SQL 注入、提升批量性能。
2. 連接池：生產環境必須用 HikariCP / Druid / DBCP，避免頻繁創建銷毀連接。
3. 事務控制：conn.setAutoCommit(false)，業務完成 commit()，異常 rollback()。
4. 資源關閉：JDK 7+ 推薦 try-with-resources，簡潔安全。
5. 結果集流式處理：大數據量避免一次性載入內存，配合 setFetchSize。