多個Oracle數(shù)據(jù)庫創(chuàng)建一個Excel報表.docx

多個Oracle數(shù)據(jù)庫創(chuàng)建一個Excel報表文章出處：設(shè)計前沿收集 作者：未知發(fā)布時間：2006-09-03關(guān)鍵詞:多個Oracle數(shù)據(jù)庫創(chuàng)建一個Excel報表微軟的 Excel 電子表格廣泛應用于當今的商業(yè)環(huán)境中。 盡管 Excel 能夠讀取和顯示分隔文件，但 .xls 專有格式提供了其他一些富于表現(xiàn)力的特性，其中包括字體格式和多工作表電子表格。許多商業(yè)和開放源代碼的工具都提供了生成可由 Excel 讀取的報表的功能。 比如，您可以使用 Oracle Reports 生成一個電子表格，或使用 SQL*Plus 輸出一個由逗號分隔的文件。 利用現(xiàn)有的一些工具可以訪問多個數(shù)據(jù)源。 但報表的格式與特定查詢的結(jié)果緊密聯(lián)系，而不同的數(shù)據(jù)源有時會生成不同的結(jié)果。 （比如，對兩個不同數(shù)據(jù)庫中的表進行 SELECT * FROM emp 查詢，返回來的列的數(shù)量和類型將必然不同。） 出于各種各樣的原因，您可能要訪問幾個不同的數(shù)據(jù)源，然后在一個電子表格中整理返回的結(jié)果：數(shù)據(jù)庫管理員可能希望比較不同數(shù)據(jù)庫的配置。應用開發(fā)人員可能需要檢查不同數(shù)據(jù)庫中 PL/SQL 對象的版本。在數(shù)據(jù)庫間移植數(shù)據(jù)時，數(shù)據(jù)庫管理員可能需要估計作業(yè)的進度。程序員使用測試數(shù)據(jù)庫調(diào)試應用程序時，可能需要將當前數(shù)據(jù)與生產(chǎn)環(huán)境中的數(shù)據(jù)進行比較。根據(jù)數(shù)據(jù)庫的不同結(jié)構(gòu)和用途，可能還有其它原因促使您想要生成此類電子表格，但所有原因都依賴于數(shù)據(jù)庫的功能要求和定義。本文將說明如何使用 Apache Jakarta POI 開放源代碼項目從幾個不同數(shù)據(jù)源生成一個電子表格。 工作簿中的每一個工作表將顯示從某個給定 Oracle 數(shù)據(jù)庫返回的結(jié)果。 在當前項目中您要達到的要求是：運行該軟件創(chuàng)建一個包含從一個或多個 Oracle 數(shù)據(jù)庫獲得的數(shù)據(jù)的電子表格。用戶將定義一個隨意 SQL 查詢，然后由各個配置的數(shù)據(jù)庫處理該查詢。用戶將定義一個或多個數(shù)據(jù)庫連接。生成一個配置文件，其中包括您定義的數(shù)據(jù)連接和 SQL 查詢。該配置文件的格式為 XML 格式。將生成一個工作表顯示每個數(shù)據(jù)庫返回的結(jié)果。將依據(jù)服務器名稱和 Oracle 系統(tǒng)標識符（又稱為 Oracle SID）命名每個工作表。電子表格將以粗體顯示列標題（基于數(shù)據(jù)庫列）。該軟件將用 Java 編寫。將使用 JDBC 訪問數(shù)據(jù)庫。將使用 Jakarta POI 生成電子表格。您可以為生產(chǎn)系統(tǒng)添加許多要求。 很明顯，上面的列表中遺漏了口令加密方面的內(nèi)容。 雖然現(xiàn)在已經(jīng)能夠處理 SQL 數(shù)據(jù)類型，但是一個更健全的解決方案還應該能夠處理一些特殊的數(shù)據(jù)類型，如 BLOB（二進制大對象）、CLOB（字符大對象）和 LONG。 目前只能調(diào)用一個 XML 配置文件和一個腳本來設(shè)置環(huán)境和執(zhí)行 Java 類，還未提供易于使用的圖形界面。 由于只執(zhí)行一個查詢，因此必須保證該 SQL 查詢無語法錯誤，并基于每個數(shù)據(jù)庫的對象返回一個結(jié)果集。 且記錄的內(nèi)容非常少。 盡管如此，如果程序達到了上面的要求，那么該程序還是相對容易使用的，并且它能夠快速生成包含許多數(shù)據(jù)庫數(shù)據(jù)的文檔。 如果需要，還可以使用 Excel 編輯該文檔的格式。 因為將多個數(shù)據(jù)源的數(shù)據(jù)納入了一個電子表格中，所以我們可以很方便對從各數(shù)據(jù)庫提取的數(shù)據(jù)進行比較了。 此外，您使用的主要機制即 POI 應用程序編程接口 (API) 調(diào)用清晰可辨，而不會被構(gòu)建一個功能齊備的解決方案所需的其他代碼和資源所淹沒。測試和要求本文所生成的軟件是在安裝有 Java 運行時環(huán)境 (JRE) 1.4.2、運行 Windows 2000 系統(tǒng)的 PC 機上編寫和測試完成的。（要了解 JRE 1.5 相關(guān)的問題，參見邊欄） Java 的設(shè)計初衷就是實現(xiàn)平臺無關(guān)性，所以在任何安裝有適當 JRE 的機器上都應該能正常運行。 使用該版本的 JRE，您無需訪問其他外部 API（如 Xerces 和 Xalan）就能利用 XML 處理。初始配置如果系統(tǒng)中有幾種 JRE，那么必須確保正確設(shè)置 PATH 環(huán)境變量，以調(diào)用 1.4.2 或更新版本的 JRE，而非老版本的 JRE。 可在 /j2se/1.4.2/docs/api 中 獲取 JRE 1.4.2 版的 Javadoc 文檔。該軟件下載中包含的批處理文件中的信息已經(jīng)進行過更改，可用于 Linux 環(huán)境，而且這些信息已經(jīng)在 Red Hat Linux 上測試通過。 只是對 run.sh 進行了適當?shù)男薷?，并沒有更改并執(zhí)行 run.bat。 在 Linux 環(huán)境中，可使用 OpenO 的電子表格程序來顯示電子表格。環(huán)境設(shè)置基本上就是兩個環(huán)境變量的設(shè)置： PATH 和 CLASSPATH。 首先要正確設(shè)置 CLASSPATH，以利用提供數(shù)據(jù)庫訪問和 Excel 電子表格功能的 API。 使用 run.bat 文件設(shè)置這一變量，這樣就可以訪問相應的 Java 文檔（.jar 文件）。 您可能需要將指向 classes12.jar（其中包含 Oracle JDBC 驅(qū)動）的路徑更改您系統(tǒng)上的正確路徑。 在安裝許多不同的 Oracle 產(chǎn)品時都將自動安裝該文件。如果您系統(tǒng)中沒有該文件，本文前面提供有文件的下載鏈接。 如果 CLASSPATH 不包含這一 jar，當程序試圖載入 JDBC 驅(qū)動時，會出現(xiàn)錯誤：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 不包含這一文件，當程序試圖創(chuàng)建一個工作表對象時，將出現(xiàn)以下錯誤：Exception in thread main java.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbookThe broadcast.jar 包含有一些文件，利用它們可以集中這些 API 的功能，然后基于配置的連接和 SQL 查詢來 構(gòu)建電子表格。 配置文件 (config.xml) 應該位于執(zhí)行 broadcast.jar 的目錄中。 如果不是，將出現(xiàn)錯誤（文件系統(tǒng)路徑正確）：java.io.FileNotFoundException: C:config.xml (The system cannot find the file specified)XML 配置從 1.4 版本起，用于 XML 處理的 Java API 就已經(jīng)加入了 Java 2 平臺中。 利用該 API，可通過一系列標準的 Java 平臺 API 來處理 XML 文檔。 因此，當前項目也就無需另外添加 XML 處理包了。 只使用一個簡單的配置文件就可以配置一個 SQL 查詢和多個數(shù)據(jù)庫連接。XML 格式極其適用于配置文件，它使得配置數(shù)據(jù)易于您理解，可以通過標準的處理方法來順利訪問配置數(shù)據(jù)。 因為存儲在配置文件中的數(shù)據(jù)很有限，也就不需要擔心出現(xiàn) file bloat 的情況。當使用 XML 標記符來標記大量數(shù)據(jù)時，會出現(xiàn) file bloat。 此外， the hierarchical organization of our configuration data benefits from the organization allowed for through nesting entities in XML. 每個連接節(jié)點包含單一的數(shù)據(jù)，合用后就可以創(chuàng)建一個數(shù)據(jù)庫連接。下面是一個配置文件的示例： server001| 1521 ORCL sysdba my1manager server002| 1521 PROD sysdba my1manager select * from v$parameter XMLConfigVO 類包含與讀取和分析該 XML 文檔的代碼。 調(diào)用沒有參數(shù)的構(gòu)造器后，類將讀取 config.xml 文件并創(chuàng)建一個新的 DocumentBuilderFactory實例。 本例沒有使用 DTD（文檔類型定義）或模式驗證。 該廠生成了一個可供分析的文檔對象，如果需要，還可操作該文檔。接下來將選擇與 /broadcast/connection-config/database XPath 表達式的節(jié)點。 這些節(jié)點包含創(chuàng)建數(shù)據(jù)庫連接所需的元素： 服務器名端口、Oracle SID、您名和密碼。 最后，將使用/broadcast/sql-statement XPath 表達式進行查詢，以獲取 SQL 語句。 至此，利用存取器（getter 方法，其形式為 getXXXX()）可以從 XMLConfigVO取回所有數(shù)據(jù)。使用 JDBCJava 數(shù)據(jù)庫連接性 (JDBC) technology 是一個 Java API，它向 Oracle 數(shù)據(jù)庫提供連接能力，以取回數(shù)據(jù)，用于填充電子表格。 JDBC API 還有一個特別之處，利用它還可在 Oracle 數(shù)據(jù)庫間建立連接，用于發(fā)送 SQL 語句并處理結(jié)果。 本文中的程序只使用了這一 API 的部分功能，因為不需要執(zhí)行 DML 或設(shè)置事務處理。 盡管支持所有類型的數(shù)據(jù)庫，但這里我們要將處理限定到一個范圍內(nèi)，該范圍內(nèi)可清晰地映射到 Excel 電子表格。Oracle JDBC 驅(qū)動依賴于 tnsnames.ora 或其他標準的 Oracle 客戶連接信息。 只要系統(tǒng)中有 JRE 且 Oracle JDBC 驅(qū)動類存在于 CLASSPATH （且網(wǎng)絡中有數(shù)據(jù)庫），那么 Java 類就有足夠的資源來創(chuàng)建到 Oracle 數(shù)據(jù)庫連接。classes12.jar 包含 Oracle 專用的 JDBC 驅(qū)動和類，它們也是本項目必須的要件。 它應該包含在 CLASSPATH 中，這樣您才能夠運行該程序。 你一般可以須/jdbc/lib 找到該文件，你也可以從 OTN 中下載 該文件。OracleConnectionVO 類包含與 JDBC 連接相關(guān)的信息和處理。 oracle.jdbc.driver.OracleDriver 將是一個 URL，其形式為 jdbc:oracle:thin:，將通過它來創(chuàng)建連接。Jakarata POI正如我在 以前的文章中所述，Jakarta POI FileSystem API 以純 Java 方式實施 OLE 2 復合文檔格式，且通過 HSSF API 可以用 Java 來讀寫 Excel 文件。 在該篇文章中，使用了這一 API 來讀取 Excel 文件。 這里我們將使用它來寫一個 Excel 文件。 （POI 也為您提供了打開已有的電子表格并對它進行操作的功能）。 如果你想了解 POI 的列多功能，請參見 “HSSF 特性指南”，其中提供有代碼實例，演示了開發(fā)人員經(jīng)常需要的功能。要創(chuàng)建一個工作表對象，調(diào)用以下代碼（在 BroadcastDriver 類）：HSSFWorkbook wb = new HSSFWorkbook(fs);在 POISheetGenerator 類完成調(diào)用，以創(chuàng)建工作表，然后它加入工作表中：HSSFSheet sheet = wb.createSheet(sheetname);列標題的字體為粗體，配置如下：HSSFFont boldFont = wb.createFont(); boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle boldStyle = wb.createCellStyle(); boldStyle.setFont(boldFont);在與迭代與結(jié)果集相關(guān)的列時，將應用上面定義的單元格格式：row.getCell(short) x).setCellStyle(boldStyle);要將工作表寫入磁盤中，調(diào)用以下代碼：OutputStream = new FileOutputStream(output.xls); wb.write(out); out.close(); broadcast.jarbroadcast.jar 包含了組成項目本身的類：BroadcastDriver. BroadcastDriver 包含了用于這一應用程序的主要方法。 創(chuàng)建 HSSFWorkbook() 對象，該對象用于容納所有的工作表。 然后創(chuàng)建 XMLConfigVO 對象，該對象用于從 config.xml 中讀取連接和 SQL 查詢。 使用該對象中包含的信息，應用程序?qū)⒌總€ OracleConnectionVO 并設(shè)置 JDBC 驅(qū)動，JDBC 數(shù)據(jù)庫 URL，數(shù)據(jù)庫名稱，數(shù)據(jù)庫密碼，SQL 查詢和工作表的名稱（形式為-）。 填充有數(shù)據(jù)的工作表將加入工作表。 當所有連接迭代完成后，工作表將寫入到文件系統(tǒng)中。OracleConnectionVO. OracleConnectionVO 中保存了 JDBC 驅(qū)動、JDBC 數(shù)據(jù)庫 URL、數(shù)據(jù)庫名稱、數(shù)據(jù)庫密碼和 SQL 查詢的值。XMLConfigVO. XMLConfigVO 創(chuàng)建 DocumentBuilderFactory 以分析 config.xml 文檔。 XPath 查詢用于檢索包含所需值的 NodeList：NodeList nodelist = org.apache.xpath.XPathAPI.selectNodeList(doc, xpath);當找到正確的節(jié)點后，您可以調(diào)用 getNodeValue() 來取回它的值。POISheetGenerator. 該類的 populateSheet 方法完成大部分的工作。 經(jīng)過初始設(shè)置后，列標題將以粗體顯示。數(shù)據(jù)庫連接已經(jīng)打開，查詢將執(zhí)行。檢索了元數(shù)據(jù)，我們需要元數(shù)據(jù)來確定將要迭代的列的數(shù)量。 元數(shù)據(jù)還包含了一些列的名稱，這些名稱將作為正在處理的工作表中的列名。 接下來將迭代結(jié)果集。 本例中的對各種數(shù)據(jù)的處理都是很初級的。 對于第個返回的對象，程序?qū)⒃噲D以數(shù)字（特別是 Java 長字符）填充單元格。 如果出現(xiàn) NumberFormatException 錯誤（因為該值不能轉(zhuǎn)化為數(shù)字），將調(diào)用 toString，用于該返回對象。 toString 功能是在 Object 對象中定義的， Object 是 Java 體系的最底層，所以不管返回的是什么對象，都可以保證該方法的有效性。 但是，如果按照返回值對于對象的意義性和相關(guān)性來分析，那 toString 返回的實際值將是極其多樣的。潛在應用下面提代了一些查詢示例，可將它們加入 config.xml 文件中，以返回一些 DBA 或應用開發(fā)人員想在數(shù)據(jù)庫間進行比較的數(shù)據(jù)。V$DATABASE 表包含了數(shù)據(jù)庫的一般信息，當您在比較數(shù)據(jù)庫配置或解決備份和恢復的問題時，可能需要這些信息。 盡管不同版本的數(shù)據(jù)庫的字段數(shù)和類型不同，以下的查詢都可以順利執(zhí)行，并將顯示合乎所指的表的結(jié)果：SELECT * FROM v$database如果一個 DBA 想記錄一些數(shù)據(jù)庫的版本信息，那么很可能要用到表 V$VERSION 的數(shù)據(jù)。 在這種情況下，可能要使用下下查詢：SELECT Name: |name Database Information FROM v$database UNION ALL SELECT DBID: |dbid FROM v$database UNION ALL SELECT * FROM v$version在備份和恢復操作中，要關(guān)注的文件是主要有控制文件、重做日志文件和數(shù)據(jù)庫數(shù)據(jù)文件本身。 你可以使用以下查詢檢索這些文件的位置：SELECT * FROM ( SELECT Tablespace ,Name File Name FROM V$CONTROLFILE union SELECT , Member FROM V$LOGFILE union SELECT Tablespace_Name, File