程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> 多個Oracle數據庫創建一個Excel報表

多個Oracle數據庫創建一個Excel報表

編輯：Oracle數據庫基礎

微軟的 Excel 電子表格廣泛應用於當今的商業環境中。盡管 Excel 能夠讀取和顯示分隔文件，但 .xls 專有格式提供了其他一些富於表現力的特性，其中包括字體格式和多工作表電子表格。

許多商業和開放源代碼的工具都提供了生成可由 Excel 讀取的報表的功能。比如，您可以使用 Oracle Reports 生成一個電子表格，或使用 SQL*Plus 輸出一個由逗號分隔的文件。利用現有的一些工具可以訪問多個數據源。但報表的格式與特定查詢的結果緊密聯系，而不同的數據源有時會生成不同的結果。（比如，對兩個不同數據庫中的表進行 SELECT * FROM emp 查詢，返回來的列的數量和類型將必然不同。）出於各種各樣的原因，您可能要訪問幾個不同的數據源，然後在一個電子表格中整理返回的結果：

數據庫管理員可能希望比較不同數據庫的配置。

應用開發人員可能需要檢查不同數據庫中 PL/SQL 對象的版本。

在數據庫間移植數據時，數據庫管理員可能需要估計作業的進度。

程序員使用測試數據庫調試應用程序時，可能需要將當前數據與生產環境中的數據進行比較。

根據數據庫的不同結構和用途，可能還有其它原因促使您想要生成此類電子表格，但所有原因都依賴於數據庫的功能要求和定義。

本文將說明如何使用 apache Jakarta POI 開放源代碼項目從幾個不同數據源生成一個電子表格。工作簿中的每一個工作表將顯示從某個給定 Oracle 數據庫返回的結果。在當前項目中您要達到的要求是：

運行該軟件創建一個包含從一個或多個 Oracle 數據庫獲得的數據的電子表格。

用戶將定義一個隨意 SQL 查詢，然後由各個配置的數據庫處理該查詢。

用戶將定義一個或多個數據庫連接。

生成一個配置文件，其中包括您定義的數據連接和 SQL 查詢。

該配置文件的格式為 XML 格式。

將生成一個工作表顯示每個數據庫返回的結果。

將依據服務器名稱和 Oracle 系統標識符（又稱為 Oracle SID）命名每個工作表。

電子表格將以粗體顯示列標題（基於數據庫列）。

該軟件將用 Java 編寫。

將使用 JDBC 訪問數據庫。

將使用 Jakarta POI 生成電子表格。

您可以為生產系統添加許多要求。很明顯，上面的列表中遺漏了口令加密方面的內容。雖然現在已經能夠處理 SQL 數據類型，但是一個更健全的解決方案還應該能夠處理一些特殊的數據類型，如 BLOB（二進制大對象）、CLOB（字符大對象）和 LONG。目前只能調用一個 XML 配置文件和一個腳本來設置環境和執行 Java 類，還未提供易於使用的圖形界面。由於只執行一個查詢，因此必須保證該 SQL 查詢無語法錯誤，並基於每個數據庫的對象返回一個結果集。且記錄的內容非常少。盡管如此，如果程序達到了上面的要求，那麼該程序還是相對容易使用的，並且它能夠快速生成包含許多數據庫數據的文檔。如果需要，還可以使用 Excel 編輯該文檔的格式。因為將多個數據源的數據納入了一個電子表格中，所以我們可以很方便對從各數據庫提取的數據進行比較了。此外，您使用的主要機制即 POI 應用程序編程接口 (API) 調用清晰可辨，而不會被構建一個功能齊備的解決方案所需的其他代碼和資源所淹沒。

測試和要求

本文所生成的軟件是在安裝有 Java 運行時環境 (JRE) 1.4.2、運行 Windows 2000 系統的 PC 機上編寫和測試完成的。（要了解 JRE 1.5 相關的問題，參見邊欄） Java 的設計初衷就是實現平台無關性，所以在任何安裝有適當 JRE 的機器上都應該能正常運行。使用該版本的 JRE，您無需訪問其他外部 API（如 Xerces 和 Xalan）就能利用 XML 處理。

初始配置

如果系統中有幾種 JRE，那麼必須確保正確設置 PATH 環境變量，以調用 1.4.2 或更新版本的 JRE，而非老版本的 JRE。可在 java.sun.com/J2SE/1.4.2/docs/api 中獲取 JRE 1.4.2 版的 Javadoc 文檔。

該軟件下載中包含的批處理文件中的信息已經進行過更改，可用於 Linux 環境，而且這些信息已經在 Red Hat Linux 上測試通過。只是對 run.sh 進行了適當的修改，並沒有更改並執行 run.bat。在 Linux 環境中，可使用 OpenOffice.org 的電子表格程序來顯示電子表格。

環境設置基本上就是兩個環境變量的設置： PATH 和 CLASSPATH。首先要正確設置 CLASSPATH，以利用提供數據庫訪問和 Excel 電子表格功能的 API。使用 run.bat 文件設置這一變量，這樣就可以訪問相應的 Java 文檔（.jar 文件）。您可能需要將指向 classes12.jar（其中包含 Oracle JDBC 驅動）的路徑更改您系統上的正確路徑。在安裝許多不同的 Oracle 產品時都將自動安裝該文件。如果您系統中沒有該文件，本文前面提供有文件的下載鏈接。如果 CLASSPATH 不包含這一 jar，當程序試圖載入 JDBC 驅動時，會出現錯誤：

Driver not found: oracle.jdbc.driver.OracleDriver 
    Java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

下載的文件中有一個 lib 目錄，其中包含有 POI jar (poi-2.5.1-final-20040804.jar)。如果 CLASSPATH 不包含這一文件，當程序試圖創建一個工作表對象時，將出現以下錯誤：

Exception in thread "main" Java.lang.NoClassDefFoundError:
    org/apache/poi/hssf/usermodel/HSSFWorkbook

The broadcast.jar 包含有一些文件，利用它們可以集中這些 API 的功能，然後基於配置的連接和 SQL 查詢來構建電子表格。配置文件 (config.XML) 應該位於執行 broadcast.jar 的目錄中。如果不是，將出現錯誤（文件系統路徑正確）：

Java.io.FileNotFoundException: C:\config.XML 
    (The system cannot find the file specifIEd)

XML 配置

從 1.4 版本起，用於 XML 處理的 Java API 就已經加入了 Java 2 平台中。利用該 API，可通過一系列標准的 Java 平台 API 來處理 XML 文檔。因此，當前項目也就無需另外添加 XML 處理包了。只使用一個簡單的配置文件就可以配置一個 SQL 查詢和多個數據庫連接。

XML 格式極其適用於配置文件，它使得配置數據易於您理解，可以通過標准的處理方法來順利訪問配置數據。因為存儲在配置文件中的數據很有限，也就不需要擔心出現 "file bloat" 的情況。當使用 XML 標記符來標記大量數據時，會出現 "file bloat"。此外， the hierarchical organization of our configuration data benefits from the organization allowed for through nesting entitIEs in XML. 每個連接節點包含單一的數據，合用後就可以創建一個數據庫連接。

下面是一個配置文件的示例：


    
    
    
    server001|
    1521
    ORCL
    sysdba
    my1manager
    
    
    server002|
    1521
    PROD
    sysdba
    my1manager
    
    
    select * from v$parameter

XMLConfigVO 類包含與讀取和分析該 XML 文檔的代碼。調用沒有參數的構造器後，類將讀取 config.XML 文件並創建一個新的 DocumentBuilderFactory實例。本例沒有使用 DTD（文檔類型定義）或模式驗證。該廠生成了一個可供分析的文檔對象，如果需要，還可操作該文檔。

接下來將選擇與 //broadcast/connection-config/database XPath 表達式的節點。這些節點包含創建數據庫連接所需的元素：服務器名端口、Oracle SID、您名和密碼。最後，將使用//broadcast/sql-statement XPath 表達式進行查詢，以獲取 SQL 語句。至此，利用存取器（getter 方法，其形式為 getXXXX()）可以從 XMLConfigVO取回所有數據。

使用 JDBC

Java 數據庫連接性 (JDBC) technology 是一個 Java API，它向 Oracle 數據庫提供連接能力，以取回數據，用於填充電子表格。 JDBC API 還有一個特別之處，利用它還可在 Oracle 數據庫間建立連接，用於發送 SQL 語句並處理結果。本文中的程序只使用了這一 API 的部分功能，因為不需要執行 DML 或設置事務處理。盡管支持所有類型的數據庫，但這裡我們要將處理限定到一個范圍內，該范圍內可清晰地映射到 Excel 電子表格。

Oracle JDBC 驅動依賴於 tnsnames.ora 或其他標准的 Oracle 客戶連接信息。只要系統中有 JRE 且 Oracle JDBC 驅動類存在於 CLASSPATH （且網絡中有數據庫），那麼 Java 類就有足夠的資源來創建到 Oracle 數據庫連接。

classes12.jar 包含 Oracle 專用的 JDBC 驅動和類，它們也是本項目必須的要件。它應該包含在 CLASSPATH 中，這樣您才能夠運行該程序。你一般可以須 /jdbc/lib 找到該文件，你也可以從 OTN 中下載該文件。

OracleConnectionVO 類包含與 JDBC 連接相關的信息和處理。 oracle.jdbc.driver.OracleDriver 將是一個 URL，其形式為 jdbc:Oracle:thin:@:@:@，將通過它來創建連接。

Jakarata POI

正如我在以前的文章中所述，Jakarta POI FileSystem API 以純 Java 方式實施 OLE 2 復合文檔格式，且通過 HSSF API 可以用 Java 來讀寫 Excel 文件。在該篇文章中，使用了這一 API 來讀取 Excel 文件。這裡我們將使用它來寫一個 Excel 文件。（POI 也為您提供了打開已有的電子表格並對它進行操作的功能）。如果你想了解 POI 的列多功能，請參見 “HSSF 特性指南”，其中提供有代碼實例，演示了開發人員經常需要的功能。

要創建一個工作表對象，調用以下代碼（在 BroadcastDriver 類）：

HSSFWorkbook wb = new HSSFWorkbook(fs);

在 POISheetGenerator 類完成調用，以創建工作表，然後它加入工作表中：

HSSFSheet sheet = wb.createSheet(sheetname);

列標題的字體為粗體，配置如下：

HSSFFont boldFont = wb.createFont();
    boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle boldStyle = wb.createCellStyle();
    boldStyle.setFont(boldFont);

在與迭代與結果集相關的列時，將應用上面定義的單元格格式：

row.getCell((short) x).setCellStyle(boldStyle);

要將工作表寫入磁盤中，調用以下代碼：

OutputStream = new FileOutputStream("output.xls");
    wb.write(out);
    out.close();
 broadcast.jar

broadcast.jar 包含了組成項目本身的類：

BroadcastDriver. BroadcastDriver 包含了用於這一應用程序的主要方法。創建 HSSFWorkbook() 對象，該對象用於容納所有的工作表。然後創建 XMLConfigVO 對象，該對象用於從 config.XML 中讀取連接和 SQL 查詢。使用該對象中包含的信息，應用程序將迭代每個 OracleConnectionVO 並設置 JDBC 驅動，JDBC 數據庫 URL，數據庫名稱，數據庫密碼，SQL 查詢和工作表的名稱（形式為 -）。填充有數據的工作表將加入工作表。當所有連接迭代完成後，工作表將寫入到文件系統中。

OracleConnectionVO. OracleConnectionVO 中保存了 JDBC 驅動、JDBC 數據庫 URL、數據庫名稱、數據庫密碼和 SQL 查詢的值。

XMLConfigVO. XMLConfigVO 創建 DocumentBuilderFactory 以分析 config.XML 文檔。 XPath 查詢用於檢索包含所需值的 NodeList：

NodeList nodelist = org.apache.xpath.XPathAPI.selectNodeList(doc, xpath);

當找到正確的節點後，您可以調用 getNodeValue() 來取回它的值。POISheetGenerator. 該類的 populateSheet 方法完成大部分的工作。經過初始設置後，列標題將以粗體顯示。數據庫連接已經打開，查詢將執行。

檢索了元數據，我們需要元數據來確定將要迭代的列的數量。元數據還包含了一些列的名稱，這些名稱將作為正在處理的工作表中的列名。接下來將迭代結果集。本例中的對各種數據的處理都是很初級的。對於第個返回的對象，程序將試圖以數字（特別是 Java 長字符）填充單元格。如果出現 NumberFormatException 錯誤（因為該值不能轉化為數字），將調用 toString，用於該返回對象。 toString 功能是在 Object 對象中定義的， Object 是 Java 體系的最底層，所以不管返回的是什麼對象，都可以保證該方法的有效性。但是，如果按照返回值對於對象的意義性和相關性來分析，那 toString 返回的實際值將是極其多樣的。

潛在應用

下面提代了一些查詢示例，可將它們加入 config.XML 文件中，以返回一些 DBA 或應用開發人員想在數據庫間進行比較的數據。

V$DATABASE 表包含了數據庫的一般信息，當您在比較數據庫配置或解決備份和恢復的問題時，可能需要這些信息。盡管不同版本的數據庫的字段數和類型不同，以下的查詢都可以順利執行，並將顯示合乎所指的表的結果：

SELECT * FROM v$database

如果一個 DBA 想記錄一些數據庫的版本信息，那麼很可能要用到表 V$VERSION 的數據。在這種情況下，可能要使用下下查詢：

SELECT 'Name: '||name "Database Information" FROM v$database
    UNION ALL
    SELECT 'DBID: '||dbid FROM v$database
    UNION ALL
    SELECT * FROM v$version

在備份和恢復操作中，要關注的文件是主要有控制文件、重做日志文件和數據庫數據文件本身。你可以使用以下查詢檢索這些文件的位置：

SELECT * FROM
    (
    SELECT ' ' "Tablespace" ,Name "File Name" FROM V$CONTROLFILE
    union
    SELECT ' ', Member FROM V$LOGFILE
    union
    SELECT Tablespace_Name, File_Name FROM DBA_DATA_FILES
    UNION
    SELECT ' ' ,'****AS OF: ' ||to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS')||'****' FROM dual
    )
    ORDER BY 1

Oracle Recovery Manager (RMAN) 一般用於管理備份和恢復操作。當要檢查備份活動，或相要實現備份活動的標准化時，你可能要用到數據庫中存儲的 RMAN 相關信息。與 RMAN 備份相關的請求有：

SELECT * FROM V$BACKUP_SET

或

SELECT * FROM V$RMAN_CONFIGURATION

Oracle Applications DBA 在管理他們的環境時，可能會遇到一系列的問題。 DBA 如果管理的實例很多，那他們可能希望比較已經應用的補丁：

SELECT * FROM APPLSYS.AD_APPLIED_PATCHES ORDER BY PATCH_NAME

這些只是幾種可能出現的情況。任意的查詢，只要沒有句法錯誤，而且在數據庫有其對應的對象，那麼它就是可以執行的，返回的結果也可以顯示在電子表格中。這裡提供示例對技術人員來說較有吸引力。您也可以構想一些業務用途，比如訪問特定應用的數據庫對象和查詢。

結論

Java 社區中提供了大量的資源，利用它們可以快速的創建程序（得益於眾多歷經驗證的 API）。利用 Oracle 的 JDBC 驅動，您可以使用 Java 編程語言方便的來訪問數據庫。而利用 Jakarta 編程人員創建的 POI，您可以直接創建帶多表格式化和其他特性的 Excel 電子表格。除了探討這些技術外，文中所介紹的這個軟件還能自動執行一些日常活動，可適當減輕 DBA 的工作負擔。