多个Oracle数据库创建一个Excel报表

多个Oracle数据库创建一个Excel报表

文章出处：设计前沿收集作者：未知发布时间：2006-09-03

关键词:多个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) 1.4.2、运行Windows 2000 系统的PC 机上编写和测试完成的。（要了解JRE 1.5 相关的问题，参见边栏）Java 的设计初衷就是实现平台无关性，所以在任何安装有适当JRE 的机器上都应该能正常运行。使用该版本的JRE，您无需访问其他外部API（如Xerces 和Xalan）就能利用XML 处理。

初始配置

如果系统中有几种JRE，那么必须确保正确设置PATH 环境变量，以调用1.4.2 或更新版本的JRE，而非老版本的JRE。可在https://www.wendangku.net/doc/9b19098532.html,/j2se/1.4.2/docs/api 中获取JRE 1.4.2 版的Javadoc 文档。

该软件下载中包含的批处理文件中的信息已经进行过更改，可用于Linux 环境，而且这些信息已经在Red Hat Linux 上测试通过。只是对run.sh 进行了适当的修改，并没有更改并执行run.bat。在Linux 环境中，可使用https://www.wendangku.net/doc/9b19098532.html, 的电子表格程序来显示电子表格。

环境设置基本上就是两个环境变量的设置：PATH 和CLASSPATH。首先要正确设置CLASSPATH，以利用提供数据库访问和Excel 电子表格功能的API。使用run.bat 文件设置这一变量，这样就可以访问相应的Java 文档（.jar 文件）。您可能需要将指向classes12.jar（其中包含Oracle JDBC 驱动）的路径更改您系统上的正确路径。在安装许多不同的Oracle 产品时都将自动安装该文件。如果您系统中没有该文件，本文前面

提供有文件的下载链接。如果CLASSPATH 不包含这一jar，当程序试图载入JDBC 驱动时，会出现错误：

下载的文件中有一个lib 目录，其中包含有POI jar

(poi-2.5.1-final-20040804.jar)。如果CLASSPATH 不包含这一文件，当程序试图创建一个工作表对象时，将出现以下错误：

The broadcast.jar 包含有一些文件，利用它们可以集中这些API 的功能，然后基于配置的连接和SQL 查询来构建电子表格。配置文件(config.xml) 应该位于执行broadcast.jar 的目录中。如果不是，将出现错误（文件系统路径正确）：

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. 每个连接节点包含单一的数据，合用后就可以创建一个数据库连接。

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

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 类）：

在POISheetGenerator 类完成调用，以创建工作表，然后它加入工作表中：

HSSFSheet sheet = wb.createSheet(sheetname);

列标题的字体为粗体，配置如下：

在与迭代与结果集相关的列时，将应用上面定义的单元格格式：

要将工作表写入磁盘中，调用以下代码：

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：

当找到正确的节点后，您可以调用getNodeValue() 来取回它的值。POISheetGenerator. 该类的populateSheet 方法完成大部分的工作。经过初始设置后，列标题将以粗体显示。数据库连接已经打开，查询将执行。

检索了元数据，我们需要元数据来确定将要迭代的列的数量。元数据还包含了一些列的名称，这些名称将作为正在处理的工作表中的列名。接下来将迭代结果集。本例中的对各种数据的处理都是很初级的。对于第个返回的对象，程序将试图以数字（特别是Java 长字符）填充单元格。如果出现NumberFormatException 错误（因为该值不能转化为数字），将调用toString，用于该返回对象。toString 功能是在Object 对象中定义的，Object 是Java 体系的最底层，所以不管返回的是什么对象，都可以保证该方法的有效性。但是，如果按照返回值对于对象的意义性和相关性来分析，那toString 返回的实际值将是极其多样的。

潜在应用

下面提代了一些查询示例，可将它们加入config.xml 文件中，以返回一些DBA 或应用开发人员想在数据库间进行比较的数据。

V$DATABASE 表包含了数据库的一般信息，当您在比较数据库配置或解决备份和恢复的问题时，可能需要这些信息。尽管不同版本的数据库的字段数和类型不同，以下的查询都可以顺利执行，并将显示合乎所指的表的结果：

如果一个DBA 想记录一些数据库的版本信息，那么很可能要用到表V$VERSION 的数据。在这种情况下，可能要使用下下查询：

在备份和恢复操作中，要关注的文件是主要有控制文件、重做日志文件和数据库数据文件本身。你可以使用以下查询检索这些文件的位置：

Oracle Recovery Manager (RMAN) 一般用于管理备份和恢复操作。当要检查备份活动，或相要实现备份活动的标准化时，你可能要用到数据库中存储的RMAN 相关信息。与RMAN 备份相关的请求有：

或

Oracle Applications DBA 在管理他们的环境时，可能会遇到一系列的问题。DBA 如果管理的实例很多，那他们可能希望比较已经应用的补丁：

这些只是几种可能出现的情况。任意的查询，只要没有句法错误，而且在数据库有其对应的对象，那么它就是可以执行的，返回的结果也可以显示在电子表格中。这里提供示

例对技术人员来说较有吸引力。您也可以构想一些业务用途，比如访问特定应用的数据库对象和查询。

结论

Java 社区中提供了大量的资源，利用它们可以快速的创建程序（得益于众多历经验证的API）。利用Oracle 的JDBC 驱动，您可以使用Java 编程语言方便的来访问数据库。而利用Jakarta 编程人员创建的POI，您可以直接创建带多表格式化和其他特性的Excel 电子表格。除了探讨这些技术外，文中所介绍的这个软件还能自动执行一些日常活动，可适当减轻DBA 的工作负担。