Java 連接 Oracle 兩大驅動模式：JDBC-ODBC 橋接 vs Thin 驅動深度對比

模式一：JDBC-ODBC Bridge (已棄用，僅歷史參考)

JDK 8 起移除，需配置系統 DSN，性能差、平台相關、不支持高級特性。

String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
String url    = "jdbc:odbc:test1";  // 系統 DSN 名稱
String user   = "scott";
String pwd    = "tiger";
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, pwd);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM emp");

缺點：雙重轉換 (JDBC→ODBC→Oracle Net)，需客戶端安裝 Oracle Client + ODBC 驅動，部署極其麻煩。

模式二：Oracle JDBC Thin Driver (生產環境標準)

純 Java 實現，無需 Oracle Client，通過 TCP/IP 直連監聽器，支持所有 JDBC 4.x 特性。

String driver = "oracle.jdbc.OracleDriver";  // 10g+ 統一類名
String url    = "jdbc:oracle:thin:@localhost:1521:orcl";  // SID 格式
// 或 Service Name 格式 (推薦 RAC/PDB)
String url    = "jdbc:oracle:thin:@//host:1521/pdb1.service";
String user   = "scott";
String pwd    = "tiger";

Class.forName(driver);
try (Connection conn = DriverManager.getConnection(url, user, pwd);
     PreparedStatement ps = conn.prepareStatement("SELECT * FROM emp WHERE deptno = ?")) {
    ps.setInt(1, 10);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println("Name: " + rs.getString("ename"));
        }
    }
}

PreparedStatement vs Statement 核心差異

連接池最佳實踐 (HikariCP / Druid)

HikariConfig cfg = new HikariConfig();
cfg.setJdbcUrl("jdbc:oracle:thin:@//prod-scan:1521/pdb1.service");
cfg.setUsername("app_user");
cfg.setPassword("***");
cfg.setMaximumPoolSize(20);
cfg.setMinimumIdle(5);
cfg.setConnectionTimeout(5000);
cfg.setIdleTimeout(300000);
cfg.setMaxLifetime(1800000);
cfg.addDataSourceProperty("oracle.net.CONNECT_TIMEOUT", "5000");
cfg.addDataSourceProperty("oracle.jdbc.ReadTimeout", "30000");

DataSource ds = new HikariDataSource(cfg);

高可用連接字符串 (RAC/Data Guard)

// SCAN + 負載均衡 + 故障轉移
jdbc:oracle:thin:@(DESCRIPTION=
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=TCPS)(HOST=scan1)(PORT=1522))
    (ADDRESS=(PROTOCOL=TCPS)(HOST=scan2)(PORT=1522))
    (ADDRESS=(PROTOCOL=TCPS)(HOST=scan3)(PORT=1522))
    (LOAD_BALANCE=on)
    (FAILOVER=on)
  )
  (CONNECT_DATA=(SERVICE_NAME=pdb1.service))
)

驅動版本選擇建議

• Oracle 19c/21c 數據庫 → ojdbc11.jar (JDK 11/17/21)
• Oracle 12c/18c 數據庫 → ojdbc8.jar (JDK 8)
• 從 Maven Central 獲取：com.oracle.database.jdbc:ojdbc11:23.4.0.0