JDBC 測試程式完整範例：Oracle 連線、PreparedStatement 批次新增、ResultSetMetaData 列資訊

完整可執行代碼

package com.example.jdbc;

import java.sql.*;
import java.util.Date;

public class OracleJdbcTest {

    // 連線參數 (請依實際環境修改)
    private static final String URL = "jdbc:oracle:thin:@localhost:1521:XE";
    private static final String USER = "test";
    private static final String PASS = "test";
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";

    public static void main(String[] args) {
        try {
            // 1. 載入驅動 (JDBC 4.0+ 可省略，但保留相容)
            Class.forName(DRIVER);

            // 2. 測試批次新增
            batchInsert();

            // 3. 測試查詢與 MetaData
            queryWithMetaData();

        } catch (ClassNotFoundException e) {
            System.err.println("找不到驅動類別: " + DRIVER);
            e.printStackTrace();
        } catch (SQLException e) {
            printSQLException(e);
        }
    }

    // ========== 批次新增 ==========
    private static void batchInsert() throws SQLException {
        String sql = "INSERT INTO student(id, name, birthday) VALUES (?, ?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false); // 開啟手動提交

            // 模擬資料
            int[] ids = {101, 102, 103};
            String[] names = {"Alice", "Bob", "Charlie"};
            Date[] birthdays = {
                Date.valueOf("2000-01-15"),
                Date.valueOf("1999-05-20"),
                Date.valueOf("2001-11-03")
            };

            for (int i = 0; i < ids.length; i++) {
                ps.setInt(1, ids[i]);
                ps.setString(2, names[i]);
                ps.setDate(3, birthdays[i]);
                ps.addBatch(); // 加入批次
            }

            int[] results = ps.executeBatch(); // 執行批次
            conn.commit(); // 提交事務
            System.out.println("批次新增完成，影響行數: " + java.util.Arrays.toString(results));
        } catch (SQLException e) {
            // 這裡無法直接 rollback conn，因為 try-with-resources 會自動關閉
            // 生產環境建議手動管理 Connection 生命週期
            throw e;
        }
    }

    // ========== 查詢 + ResultSetMetaData ==========
    private static void queryWithMetaData() throws SQLException {
        String sql = "SELECT * FROM student WHERE id >= 101";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // 1. 取得 MetaData
            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();

            // 2. 印出欄位標題
            for (int i = 1; i <= columnCount; i++) {
                System.out.printf("%-20s", meta.getColumnLabel(i));
            }
            System.out.println();

            // 3. 印出分隔線
            for (int i = 1; i <= columnCount; i++) {
                System.out.print("--------------------");
            }
            System.out.println();

            // 4. 遍歷資料列
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    Object val = rs.getObject(i);
                    System.out.printf("%-20s", val != null ? val.toString() : "NULL");
                }
                System.out.println();
            }
        }
    }

    // ========== 通用資源關閉 (Java 7 前寫法參考) ==========
    @SuppressWarnings("unused")
    private static void closeQuietly(AutoCloseable... resources) {
        for (AutoCloseable r : resources) {
            if (r != null) {
                try { r.close(); } catch (Exception ignore) {}
            }
        }
    }

    private static void printSQLException(SQLException e) {
        System.err.println("SQLState: " + e.getSQLState());
        System.err.println("Error Code: " + e.getErrorCode());
        System.err.println("Message: " + e.getMessage());
        Throwable cause = e.getCause();
        while (cause != null) {
            System.err.println("Cause: " + cause);
            cause = cause.getCause();
        }
    }
}

關鍵技術點解析

Oracle 專屬提示

• 驅動版本：Oracle 19c+ 建議用 ojdbc10.jar (Java 10/11) 或 ojdbc11.jar (Java 11+)，Maven 坐標 com.oracle.database.jdbc:ojdbc10。
• 連線字串：
  • SID 模式：jdbc:oracle:thin:@host:port:SID
  • Service Name 模式：jdbc:oracle:thin:@host:port/service_name
  • TNS 別名：jdbc:oracle:thin:@tns_alias (需 oracle.net.tns_admin 系統屬性)
• 分頁查詢：Oracle 12c+ 支援標準 OFFSET ... ROWS FETCH NEXT ... ROWS ONLY；舊版需用 ROWNUM 子查詢包裝。
• 日期型別：java.sql.Date 對應 DATE (無時間)；java.sql.Timestamp 對應 TIMESTAMP。

連線池升級版 (HikariCP) 片段

// Maven: com.zaxxer:HikariCP:5.0.1
HikariConfig config = new HikariConfig();
config.setJdbcUrl(URL);
config.setUsername(USER);
config.setPassword(PASS);
config.setDriverClassName(DRIVER);
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.setPoolName("OraclePool");

HikariDataSource ds = new HikariDataSource(config);

// 使用
try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement("SELECT * FROM student WHERE id = ?")) {
    ps.setInt(1, 101);
    try (ResultSet rs = ps.executeQuery()) { ... }
}

總結

此範例涵蓋 JDBC 核心流程：載入驅動 → 連線池 → PreparedStatement 參數化 → 批次事務 → ResultSetMetaData 動態讀取 → try-with-resources 安全關閉。掌握此範本，可應對 90% 的資料庫操作場景。