Java 連接 Oracle 數據庫完整實戰：從 JDBC 基礎到連接池最佳實踐

前言：Oracle JDBC 開發的現代化路徑

雖然 Spring Data JPA、MyBatis、JdbcTemplate 等 ORM 框架已成主流，但理解原生 JDBC 核心流程、配置管理、資源釋放、異常處理、連接池原理，是排查數據庫連接洩漏、死鎖、效能抖動的根本功力。本文以 Oracle 為例，構建從配置層、DAO 基類、事務管理到連接池整合的完整棧。

1. 配置層：屬性文件 + 單例管理器

database.properties

# Oracle JDBC 配置
jdbc.driverClassName=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@//localhost:1521/ORCLCDB
jdbc.username=scott
jdbc.password=tiger

# 連接池參數（HikariCP 推薦值）
pool.maximumPoolSize=20
pool.minimumIdle=5
pool.idleTimeout=300000
pool.connectionTimeout=30000
pool.maxLifetime=1800000
pool.poolName=OracleHikariPool

關鍵點：

• 驅動類：oracle.jdbc.OracleDriver（12c+ 也兼容 oracle.jdbc.driver.OracleDriver）
• URL 格式：thin:@//host:port/service_name（推薦）或 thin:@host:port:sid（舊版）
• 服務名 vs SID：RAC/PDB 環境必須用服務名

ConfigManager：類型安全配置訪問

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public final class ConfigManager {
    private static final ConfigManager INSTANCE = new ConfigManager();
    private final Properties props = new Properties();

    private ConfigManager() {
        try (InputStream is = getClass().getClassLoader().getResourceAsStream("database.properties")) {
            if (is == null) throw new IllegalStateException("database.properties 不在 classpath 根目錄");
            props.load(is);
        } catch (IOException e) {
            throw new ExceptionInInitializerError("加載配置失敗: " + e.getMessage());
        }
    }

    public static ConfigManager getInstance() { return INSTANCE; }

    public String getString(String key) { return props.getProperty(key); }
    public int getInt(String key) { return Integer.parseInt(props.getProperty(key)); }
    public long getLong(String key) { return Long.parseLong(props.getProperty(key)); }
    public boolean getBoolean(String key) { return Boolean.parseBoolean(props.getProperty(key)); }
}

2. DAO 基類：資源管理模板模式

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public abstract class BaseDao {

    /** 執行增刪改，返回影響行數 */
    protected int executeUpdate(String sql, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            bindParams(ps, params);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new DaoException("執行更新失敗: " + sql, e);
        }
    }

    /** 執行查詢，映射單條結果 */
    protected  T querySingle(String sql, RowMapper mapper, Object... params) {
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = executeQuery(ps, params)) {
            return rs.next() ? mapper.mapRow(rs) : null;
        } catch (SQLException e) {
            throw new DaoException("查詢單條失敗: " + sql, e);
        }
    }

    /** 執行查詢，映射列表結果 */
    protected  List queryList(String sql, RowMapper mapper, Object... params) {
        List list = new ArrayList<>();
        try (Connection conn = getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = executeQuery(ps, params)) {
            while (rs.next()) {
                list.add(mapper.mapRow(rs));
            }
            return list;
        } catch (SQLException e) {
            throw new DaoException("查詢列表失敗: " + sql, e);
        }
    }

    /** 獲取連接（子類可覆寫接入連接池） */
    protected Connection getConnection() throws SQLException {
        String url = ConfigManager.getInstance().getString("jdbc.url");
        String user = ConfigManager.getInstance().getString("jdbc.username");
        String pwd = ConfigManager.getInstance().getString("jdbc.password");
        return DriverManager.getConnection(url, user, pwd);
    }

    private void bindParams(PreparedStatement ps, Object[] params) throws SQLException {
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
        }
    }

    private ResultSet executeQuery(PreparedStatement ps, Object[] params) throws SQLException {
        bindParams(ps, params);
        return ps.executeQuery();
    }

    /** 行映射器函數式介面 */
    @FunctionalInterface
    public interface RowMapper {
        T mapRow(ResultSet rs) throws SQLException;
    }

    /** 自定義異常，保留堆疊 */
    public static class DaoException extends RuntimeException {
        public DaoException(String msg, Throwable cause) { super(msg, cause); }
    }
}

3. 具體 DAO 實現

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class UserDao extends BaseDao {

    public void insert(User user) {
        String sql = "INSERT INTO users (id, username, email, created_at) VALUES (seq_user.nextval, ?, ?, SYSTIMESTAMP)";
        executeUpdate(sql, user.getUsername(), user.getEmail());
    }

    public User findById(Long id) {
        String sql = "SELECT id, username, email, created_at FROM users WHERE id = ?";
        return querySingle(sql, this::mapRow, id);
    }

    public List findByEmailLike(String emailPattern) {
        String sql = "SELECT id, username, email, created_at FROM users WHERE email LIKE ?";
        return queryList(sql, this::mapRow, "%" + emailPattern + "%");
    }

    private User mapRow(ResultSet rs) throws SQLException {
        User u = new User();
        u.setId(rs.getLong("id"));
        u.setUsername(rs.getString("username"));
        u.setEmail(rs.getString("email"));
        u.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return u;
    }
}

4. 事務管理：ThreadLocal 綁定連接

原生 JDBC 事務核心：conn.setAutoCommit(false) → 業務邏輯 → conn.commit()/rollback()。多 DAO 共享同一連接需用 ThreadLocal。

import java.sql.Connection;
import java.sql.SQLException;

public final class TransactionManager {
    private static final ThreadLocal CONN_HOLDER = new ThreadLocal<>();

    private TransactionManager() {}

    /** 開啟事務 */
    public static void begin() {
        if (CONN_HOLDER.get() != null) throw new IllegalStateException("事務已開啟");
        try {
            Connection conn = ConfigManager.getInstance().getString("jdbc.url"); // 簡化：實際應從池獲取
            conn.setAutoCommit(false);
            CONN_HOLDER.set(conn);
        } catch (SQLException e) {
            throw new RuntimeException("開啟事務失敗", e);
        }
    }

    /** 提交事務 */
    public static void commit() {
        Connection conn = CONN_HOLDER.get();
        if (conn == null) throw new IllegalStateException("無活動事務");
        try {
            conn.commit();
        } catch (SQLException e) {
            throw new RuntimeException("提交失敗", e);
        } finally {
            closeQuietly(conn);
            CONN_HOLDER.remove();
        }
    }

    /** 回滾事務 */
    public static void rollback() {
        Connection conn = CONN_HOLDER.get();
        if (conn == null) return;
        try {
            conn.rollback();
        } catch (SQLException e) {
            // log error
        } finally {
            closeQuietly(conn);
            CONN_HOLDER.remove();
        }
    }

    /** 獲取當前事務連接（DAO 內部調用） */
    public static Connection getCurrentConnection() {
        Connection conn = CONN_HOLDER.get();
        if (conn == null) {
            throw new IllegalStateException("非事務調用請使用 BaseDao.getConnection()");
        }
        return conn;
    }

    private static void closeQuietly(Connection conn) {
        try { if (conn != null) conn.close(); } catch (SQLException ignore) {}
    }
}

DAO 適配事務連接

@Override
protected Connection getConnection() throws SQLException {
    Connection txConn = TransactionManager.getCurrentConnection();
    return txConn != null ? txConn : super.getConnection();
}

業務層使用

public void transfer(Long fromId, Long toId, BigDecimal amount) {
    TransactionManager.begin();
    try {
        userDao.deductBalance(fromId, amount);
        userDao.addBalance(toId, amount);
        TransactionManager.commit();
    } catch (Exception e) {
        TransactionManager.rollback();
        throw e;
    }
}

5. 生產級升級：HikariCP 連接池整合

強烈建議生產環境替換 DriverManager 為 HikariCP，效能、監控、洩漏檢測全方位碾壓。

依賴

    com.zaxxer
    HikariCP
    5.0.1


    com.oracle.database.jdbc
    ojdbc11
    23.4.0.0

DataSource 工廠

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public final class DataSourceFactory {
    private static final DataSource DATA_SOURCE;

    static {
        HikariConfig config = new HikariConfig();
        ConfigManager cfg = ConfigManager.getInstance();
        config.setJdbcUrl(cfg.getString("jdbc.url"));
        config.setUsername(cfg.getString("jdbc.username"));
        config.setPassword(cfg.getString("jdbc.password"));
        config.setDriverClassName(cfg.getString("jdbc.driverClassName"));

        config.setMaximumPoolSize(cfg.getInt("pool.maximumPoolSize"));
        config.setMinimumIdle(cfg.getInt("pool.minimumIdle"));
        config.setIdleTimeout(cfg.getLong("pool.idleTimeout"));
        config.setConnectionTimeout(cfg.getLong("pool.connectionTimeout"));
        config.setMaxLifetime(cfg.getLong("pool.maxLifetime"));
        config.setPoolName(cfg.getString("pool.poolName"));

        // Oracle 特有優化
        config.addDataSourceProperty("oracle.net.CONNECT_TIMEOUT", "10000");
        config.addDataSourceProperty("implicitCachingEnabled", "true");
        config.addDataSourceProperty("maxStatements", "100");

        DATA_SOURCE = new HikariDataSource(config);
    }

    public static DataSource getDataSource() { return DATA_SOURCE; }

    public static void shutdown() { if (DATA_SOURCE instanceof HikariDataSource) ((HikariDataSource)DATA_SOURCE).close(); }
}

BaseDao 切換數據源

@Override
protected Connection getConnection() throws SQLException {
    return DataSourceFactory.getDataSource().getConnection();
}

6. Oracle 特有最佳實踐

總結

本文構建了完整的 Oracle JDBC 技術棧：

1. 配置層：類型安全、單例、失敗快
2. DAO 基類：模板模式、函數式映射、資源自動關閉
3. 事務管理：ThreadLocal 綁定、聲明式邊界
4. 連接池：HikariCP 生產級配置、Oracle 優化參數
5. 異常體系：RuntimeException 包裝、保留堆疊、業務可感知

這套代碼零框架依賴，可直接用於微服務、批處理、舊系統改造。若項目已引入 Spring/MyBatis，請優先使用框架提供的 JdbcTemplate/SqlSession，本文原理部分仍適用於問題排查與調優。