多个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 的工作负担