萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 腳本程式專題 >> JavaScript

Java 單獨程序連接 Oracle 數據庫完整示例

依賴準備

  1. Oracle JDBC 驅動:ojdbc8.jar (JDK 8+) 或 ojdbc11.jar (JDK 11+)
  2. Maven 依賴:


    com.oracle.database.jdbc
    ojdbc8
    21.9.0.0


完整連接代碼


import java.sql.*;

public class OracleConnectionDemo {
    
    // 數據庫連接配置
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@192.168.1.1:1521:orcl";
    // 服務名模式:jdbc:oracle:thin:@//host:port/service_name
    private static final String USER = "scott";
    private static final String PASSWORD = "tiger";
    
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            // 1. 加載驅動 (JDBC 4.0+ 可選,Class.forName 觸發 SPI 自動註冊)
            Class.forName(DRIVER);
            System.out.println("驅動加載成功");
            
            // 2. 建立連接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            if (!conn.isClosed()) {
                System.out.println("數據庫連接成功!");
            }
            
            // 3. 創建 Statement
            stmt = conn.createStatement();
            
            // 4. 執行查詢
            String sql = "SELECT password FROM users WHERE id = 'test1'";
            rs = stmt.executeQuery(sql);
            
            // 5. 處理結果集
            while (rs.next()) {
                String password = rs.getString("password");
                System.out.println("查詢結果 - 密碼: " + password);
            }
            
        } catch (ClassNotFoundException e) {
            System.err.println("找不到驅動類: " + e.getMessage());
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("SQL 執行錯誤: " + e.getMessage());
            e.printStackTrace();
        } finally {
            // 6. 關鍵:逆序關閉資源
            closeQuietly(rs);
            closeQuietly(stmt);
            closeQuietly(conn);
            System.out.println("資源已釋放");
        }
    }
    
    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try { resource.close(); } catch (Exception ignored) {}
        }
    }
}

進階:連接池配置


// HikariCP 配置 (推薦)
HikariConfig config = new HikariConfig();
config.setJdbcUrl(URL);
config.setUsername(USER);
config.setPassword(PASSWORD);
config.setDriverClassName(DRIVER);
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");

DataSource dataSource = new HikariDataSource(config);

// 使用
try (Connection conn = dataSource.getConnection()) {
    // 業務邏輯
}

Oracle 專用優化技巧

  1. 游標關閉:Oracle 游標資源昂貴,務必在 finally 中關閉 ResultSet/Statement
  2. 批量操作:PreparedStatement.addBatch() + executeBatch()
  3. 獲取自增主鍵:Oracle 用 Sequence,PreparedStatement.getGeneratedKeys() 需指定列名
  4. CLOB/BLOB 處理:大對象需流式讀寫,避免一次性載入內存
  5. 隱式提交:DDL 操作會隱式提交事務,注意事務邊界

Spring Boot 整合配置


# application.yml
spring:
  datasource:
    url: jdbc:oracle:thin:@//192.168.1.1:1521/orcl
    username: scott
    password: tiger
    driver-class-name: oracle.jdbc.driver.OracleDriver
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
  jpa:
    database-platform: org.hibernate.dialect.Oracle12cDialect
    show-sql: true
    hibernate:
      ddl-auto: none

常見錯誤排查

錯誤原因解決
ClassNotFoundException驅動 JAR 不在 ClasspathMaven 依賴或手動添加 JAR
ORA-12514服務名/ SID 錯誤檢查 URL 中 SID 或 Service Name
ORA-01017用戶名/密碼錯誤確認帳號狀態、密碼大小寫
ORA-28000帳號鎖定DBA 解鎖或修改密碼
時區報警JDBC 時區不匹配URL 加 serverTimezone=Asia/Shanghai
copyright © 萬盛學電腦網 all rights reserved