JDBC 連線 Oracle 資料庫完整範例與最佳實踐

依賴準備

• Oracle JDBC 驅動：ojdbc8.jar（JDK 8+）或 ojdbc11.jar
• Maven：com.oracle.database.jdbc:ojdbc8:21.9.0.0

連線工具類

public final class OracleJdbcUtil {
    private static final String URL = "jdbc:oracle:thin:@//localhost:1521/ORCL";
    private static final String USER = "system";
    private static final String PASS = "password";

    static {
        try { Class.forName("oracle.jdbc.OracleDriver"); }
        catch (ClassNotFoundException e) { throw new ExceptionInInitializerError(e); }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASS);
    }

    public static void close(AutoCloseable... resources) {
        for (AutoCloseable r : resources) {
            if (r != null) try { r.close(); } catch (Exception ignored) {}
        }
    }
}

CRUD 範本

public class StudentDao {
    public void insert(Student s) {
        String sql = "INSERT INTO student(id, name, class_grent, result) VALUES (seq.nextval, ?, ?, ?)";
        try (Connection c = OracleJdbcUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, s.getName());
            ps.setString(2, s.getClassGrent());
            ps.setString(3, s.getResult());
            ps.executeUpdate();
        } catch (SQLException e) { throw new RuntimeException(e); }
    }

    public List selectAll() {
        String sql = "SELECT id, name, class_grent, result FROM student";
        try (Connection c = OracleJdbcUtil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            List list = new ArrayList<>();
            while (rs.next()) list.add(map(rs));
            return list;
        } catch (SQLException e) { throw new RuntimeException(e); }
    }
    // update/delete 同理
}

關鍵最佳實踐

1. 必須使用 PreparedStatement：防 SQL 注入、預編譯效能、型別安全
2. try-with-resources：自動關閉 Connection/Statement/ResultSet
3. 連線池：生產環境用 HikariCP、Druid，勿手動管理
4. 批次操作：addBatch() + executeBatch() 大量寫入
5. 事務控制：conn.setAutoCommit(false) + commit/rollback

常見問題排查