多個Oracle數據庫創建一個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) 運行 Windows 系統的 PC 機上編寫和測試完成的（要了解 JRE 相關的問題參見邊欄） Java 的設計初衷就是實現平台無關性所以在任何安裝有適當 JRE 的機器上都應該能正常運行 使用該版本的 JRE您無需訪問其他外部 API（如 Xerces 和 Xalan）就能利用 XML 處理

　　

　　初始配置

　　

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

　　

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

　　

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

　　

　　Driver not found: oraclejdbcdriverOracleDriver javalangClassNotFoundException: oraclejdbcdriverOracleDriver

　　

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

　　

　　Exception in thread main javalangNoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbook

　　

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

　　

　　javaioFileNotFoundException: C:\configxml (The system cannot find the file specified)

　　

　　 

　　XML 配置

　　

　　從 版本起用於 XML 處理的 Java API 就已經加入了 Java 平台中 利用該 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 每個連接節點包含單一的數據合用後就可以創建一個數據庫連接

　　

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

　　

　　<?xml version=?> <broadcast> <connectionconfig> <database> <servername>server</servername>| <port></port> <sid>ORCL</sid> <username>sysdba</username> <password>mymanager</password> </database> <database> <servername>server</servername>| <port></port> <sid>PROD</sid> <username>sysdba</username> <password>mymanager</password> </database> </connectionconfig> <sqlstatement>select * from v$parameter</sqlstatement> </broadcast>

　　

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

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

　　使用 JDBC

　　

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

　　

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

　　

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

　　

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

　　

　　Jakarata POI

　　

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

　　

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

　　

　　HSSFWorkbook wb = new HSSFWorkbook(fs);

　　

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

　　

　　HSSFSheet sheet = wbcreateSheet(sheetname);

　　

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

　　

　　HSSFFont boldFont = wbcreateFont(); boldFontsetBoldweight(HSSFFontBOLDWEIGHT_BOLD); HSSFCellStyle boldStyle = wbcreateCellStyle(); boldStylesetFont(boldFont);

　　

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

　　

　　rowgetCell((short) x)setCellStyle(boldStyle);

　　

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

　　

　　OutputStream = new FileOutputStream(outputxls); wbwrite(out); outclose(); broadcastjar

　　

　　

　　broadcastjar 包含了組成項目本身的類

　　

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

　　

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

　　

　　XMLConfigVO XMLConfigVO 創建 DocumentBuilderFactory 以分析 configxml 文檔 XPath 查詢用於檢索包含所需值的 NodeList

　　

　　NodeList nodelist = orgapachexpathXPathAPIselectNodeList(doc xpath);

　　

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

　　

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

　　潛在應用

　　

　　下面提代了一些查詢示例可將它們加入 configxml 文件中以返回一些 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 HH:MI:SS)
||**** FROM dual ) ORDER BY

　　

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

　　

　　SELECT * FROM V$BACKUP_SET

　　

　　或

　　

　　SELECT * FROM V$RMAN_CONFIGURATION

　　

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

　　

　　SELECT * FROM APPLSYSAD_APPLIED_PATCHES ORDER BY PATCH_NAME

　　

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

　　

　　結論

　　

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

From:http://tw.wingwit.com/Article/program/Oracle/201311/17009.html