使用 Apache Jakarta POI 從多個資料庫建立一個 Excel 報表

idba發表於2008-08-19
Apache資料庫Excel

微軟的 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 1.5 應對措施

當結合 jdk1.5.0_04 使用該軟體時，系統將丟擲異常 java.lang.NoClassDefFoundError: org/apache/xpath/XPathAPI。 這是因為 org.apache.xpath.XPathAPI 類已經移植到了 JRE 1.5 中。 解決該問題的方法有兩種：

  1. 使用含 XPathAPI 類的 jar，例如 xalan-2.4.1.jar。 將該 jar 加入到 CLASSPATH（類路徑）。 這一方法同樣適用於不含 XPathAPI 類的老版本 JRE。 這一方法有個缺點，就是要重新引用 JRE 中已有的功能。
  2. 更改原始碼，移除包的限制條件。 將對 org.apache.xpath.XPathAPI 的引用變更為 XPathAPI。 （com.sun.org.apache.xpath.internal 中已經包含 XPathAPI ）本方法需要另外下載 jar，也是本文采取的方法。
該軟體用於執行客戶端並訪問遠端 Oracle 伺服器。 但請注意，從 Oracle 資料庫第 8 版本開始，其中包含的 Java 虛擬機器支援在資料庫內使用 Java 物件。 利用這一功能，您可以靈活地開發與資料庫環境緊密整合的客戶端應用程式。 您可以使用 JDBC 來建立到任何資料庫的連線，只要該資料庫支援 JDBC 驅動。 這樣一來，Oracle 資料庫的功用就不僅限於資料庫了，它可作為一個資料處理平臺處理從各類資料來源檢索而來的資料。 本文中所用的軟體將不使用這一功能，但當您根據自己的要求對本文所介紹的方法進行修改時，應該有意識的使用這一功能。

初始配置

如果系統中有幾種 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 的工作負擔。

http://www.oracle.com/technology/global/cn/pub/articles/saternos_broadcast.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/1384/viewspace-430041/，如需轉載，請註明出處，否則將追究法律責任。

相關文章