JDBC 連接 Oracle 完整範例：查詢、更新、資源管理

完整可運行代碼

package hello.ant;

import java.sql.*;

public class OracleJdbcDemo {

    // 連線參數 (請根據實際環境修改)
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USER = "admin";
    private static final String PASSWORD = "manager";

    public static void main(String[] args) {
        // 1. 單次查詢示範
        queryUsers();

        // 2. 參數化更新示範
        insertUser(100, "測試用戶");
    }

    /**
     * 查詢所有用戶
     */
    public static void queryUsers() {
        String sql = "SELECT id, name FROM test";
        // try-with-resources 自動關閉連線、Statement、ResultSet
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            System.out.println("--- 用戶列表 ---");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.printf("ID: %d, Name: %s%n", id, name);
            }
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    /**
     * 參數化插入用戶
     */
    public static void insertUser(int id, String name) {
        String sql = "INSERT INTO test (id, name) VALUES (?, ?)";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, id);
            ps.setString(2, name);
            int rows = ps.executeUpdate();
            System.out.printf("插入完成，影響行數: %d%n", rows);
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    /**
     * 統一異常輸出
     */
    private static void printSQLException(SQLException e) {
        System.err.println("SQL 錯誤:");
        while (e != null) {
            System.err.printf("  SQLState: %s, ErrorCode: %d, Message: %s%n",
                    e.getSQLState(), e.getErrorCode(), e.getMessage());
            e = e.getNextException();
        }
    }
}

關鍵點解析

1. 驅動載入：JDBC 4.0+ (Oracle 11g+ 驅動) 透過 SPI 自動載入，**無需** Class.forName()。
2. 連線 URL：thin 模式無需 Oracle Client，格式 jdbc:oracle:thin:@host:port:SID 或 @//host:port/service_name。
3. PreparedStatement：防 SQL 注入、預編譯提升批次效能、支援 Blob/Clob 流式讀寫。
4. try-with-resources：Java 7+ 標準寫法，保證 ResultSet → Statement → Connection 逆序自動關閉，杜絕洩漏。
5. 異常鏈處理：SQLException.getNextException() 遍歷鏈路，Oracle 批次操作常產生多重異常。

進階：連線池配置 (生產必備)

<!-- HikariCP 範例 (Spring Boot 默認) -->
spring.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl
spring.datasource.username=admin
spring.datasource.password=manager
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000

常見坑