依賴準備
- Oracle JDBC 驅動:
ojdbc8.jar (JDK 8+) 或 ojdbc11.jar (JDK 11+)
- 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 專用優化技巧
- 游標關閉:Oracle 游標資源昂貴,務必在
finally 中關閉 ResultSet/Statement
- 批量操作:
PreparedStatement.addBatch() + executeBatch()
- 獲取自增主鍵:Oracle 用 Sequence,
PreparedStatement.getGeneratedKeys() 需指定列名
- CLOB/BLOB 處理:大對象需流式讀寫,避免一次性載入內存
- 隱式提交: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 不在 Classpath | Maven 依賴或手動添加 JAR |
| ORA-12514 | 服務名/ SID 錯誤 | 檢查 URL 中 SID 或 Service Name |
| ORA-01017 | 用戶名/密碼錯誤 | 確認帳號狀態、密碼大小寫 |
| ORA-28000 | 帳號鎖定 | DBA 解鎖或修改密碼 |
| 時區報警 | JDBC 時區不匹配 | URL 加 serverTimezone=Asia/Shanghai |