Use Apache Jakarta poi to create an Excel report from multiple databases

Use Apache Jakarta poi to create an Excel report from multiple databases
By Casimir saternos

Use open source code technology to broadcast SQL to multiple Oracle databases. Create an Excel Workbook with only one worksheet based on each database result set.

Download this article: Sample Code and JRE Oracle Database 10G Oracle JDBC driver Apache Jakarta poi

Microsoft Excel workbooks are widely used in today's business environments. Although Excel can read and display separated files,. xls proprietary formats provide other expressive features, including the font format and multi-worksheet workbooks.

Many commercial and open-source tools provide the ability to generate reports that can be read by Excel. For example, you can use Oracle Reports to generate a workbook or use SQL * Plus to output a file separated by commas. Use existing tools to access multiple data sources. However, the report format is closely related to the results of a specific query. different data sources sometimes generate different results. (For example, for tables in two different databasesSelect * from EMPThe number and type of returned columns are different .) For various reasons, you may need to access several different data sources and then sort the returned results in a workbook:

• The database administrator may want to compare the configurations of different databases.
• Application developers may need to check the versions of PL/SQL objects in different databases.
• When porting data between databases, the database administrator may need to estimate the job progress.
• When programmers use a test database to debug an application, they may need to compare the current data with the data in the production environment.

Depending on the Structure and usage of the database, you may want to generate such workbooks for other reasons, but all the reasons depend on the functional requirements and definitions of the database.

 

This article describes how to use the Apache Jakarta poi open source project to generate a workbook from several different data sources. Each worksheet in the workbook displays the results returned from a given Oracle database. In the current project, you must meet the following requirements:

• Run the software to create a workbook containing data obtained from one or more Oracle databases.
• The user will define a random SQL query, and then the database of each configuration will process the query.
• You will define one or more database connections.
• Generate a configuration file, including data connection and SQL query.
• The configuration file is in XML format.
• A worksheet is generated to display the results returned by each database.
• Each worksheet is named based on the server name and the Oracle system identifier (also known as the Oracle SID.
• The workbook displays the column title in bold (based on database columns ).
• The software will be written in Java.
• You will use JDBC to access the database.
• A Workbook is generated using Jakarta poi.

You can add many requirements for the production system. Obviously, password encryption is missing from the above list. Although SQL data types can be processed now, a more robust solution should be able to process some special data types, such as blob (Binary Large Object) and clob (character large object) and long. Currently, only one xml configuration file and one script can be called to set the environment and execute Java classes. An easy-to-use graphical interface is not yet provided. Since only one query is executed, you must ensure that the SQL query has no syntax errors and return a result set based on each database object. There are very few records. Even so, if the program meets the above requirements, it is relatively easy to use, and it can quickly generate documents containing a lot of database data. If necessary, you can also use Excel to edit the document format. Because data from multiple data sources is included in a workbook, we can easily compare the data extracted from each database. In addition, the main mechanism you use is that poi application programming interface (API) calls are clearly identifiable, without being overwhelmed by other code and resources required to build a fully functional solution.

 

Testing and requirements

The software generated in this article is written and tested on a PC running Windows 2000 with Java Runtime Environment (JRE) 1.4.2 installed. (To understand issues related to JRE 1.5, see the sidebar) the original design of Java is to achieve platform independence, so it should be able to run properly on any machine installed with appropriate JRE. With this version of JRE, you do not need to access other external APIs (such as xerces and xalan) to use XML for processing.

JRE 1.5 Countermeasures When the software is used in combination with jdk1.5.0 _ 04, the system throws an exception.Java. Lang. noclassdeffounderror: ORG/Apache/XPath/xpathapi. This is because the org. Apache. XPath. xpathapi class has been transplanted to JRE 1.5. There are two ways to solve this problem: Use a jar containing the xpathapi class, for example, a xalan-2.4.1.jar. Add the jar to the classpath (class path ). This method also applies to earlier versions of JRE without the xpathapi class. This method has the disadvantage of re-referencing the existing functions in JRE. Modify the source code and remove the restriction of the package. Change the reference to org. Apache. XPath. xpathapi to xpathapi. (Com.sun.org. Apache. XPath. Internal already contains xpathapi) This method also needs to download jar, which is also the method used in this article.

This software is used to run the client and access the remote ORACLE Server. Note that, starting from Oracle Database 8th, the Java virtual machines included in the database support using Java objects. With this feature, you can flexibly develop client applications that are closely integrated with the database environment. You can use JDBC to create a connection to any database, as long as the database supports the JDBC driver. In this way, the Oracle database function is not limited to databases. It can be used as a data processing platform to process data retrieved from various data sources. The software used in this article will not use this function, but when you modify the methods described in this article according to your own requirements, you should consciously use this function.

Initial Configuration

If there are several types of JRE in the system, you must ensure that the PATH environment variable is correctly set to call 1.4.2 or the updated version of JRE, instead of the old version of JRE. You can obtain the javadoc document of JRE 1.4.2 from the java.sun.com/j2se/1.4.2/docs/api.

The information in the batch file contained in the software download has been changed and can be used in the Linux environment. The information has been tested on Red Hat Linux. Only the run. Sh is modified properly, and no changes are made and run. bat is executed. In Linux, you can use the workbook program of OpenOffice.org to display workbooks.

The environment setting is basically the setting of two environment variables: path and classpath. First, you must set the classpath correctly to use APIs that provide database access and Excel spreadsheet functions. Use the run. BAT file to set this variable so that you can access the corresponding Java document (. jar file ). You may need to change the path pointing to classes12.jar (including the oracle JDBC driver) to the correct path on your system. This file is automatically installed when many different Oracle products are installed. If this file does not exist in your system, a download link is provided before this article. If classpath does not contain this jar, an error occurs when the program tries to load the JDBC driver:

Driver not found: oracle.jdbc.driver.OracleDriver java.lang.ClassNotFoundException:  oracle.jdbc.driver.OracleDriver

The downloaded file contains a lib directory containing poi jar (poi-2.5.1-final-20040804.jar ). If classpath does not contain this file, the following error occurs when the program tries to create a worksheet object:

Exception in thread "main" java.lang.NoClassDefFoundError:org/apache/poi/hssf/usermodel/HSSFWorkbook

The broadcast. jar contains some files that can be used to centralize the functions of these APIs and then build workbooks Based on configured connections and SQL queries. The configuration file (config. XML) should be located in the directory where broadcast. jar is executed. If not, an error will occur (the file system path is correct ):

java.io.FileNotFoundException: C:/config.xml (The system cannot find the file specified)

Xml configuration

 

Java APIs for XML processing have been added to Java 2 Since version 1.4. This API can be used to process XML documents through a series of standard Java platform APIs. Therefore, the current project does not need to add an XML Processing Package. You can configure one SQL query and multiple database connections by using only one simple configuration file.

The XML format is extremely suitable for configuration files. It makes configuration data easy to understand and allows you to smoothly access configuration data through standard processing methods. Because the data stored in the configuration file is very limited, you do not need to worry about "file bloat. When you use an XML tag to mark a large amount of data, "file bloat" appears ". In addition, the hierarchical organization of our configuration data benefits from the organization allowed for through nesting entities in XML. Each connection node contains a single data, and then you can create a database connection.

The following is an example of a configuration file:

<?xml version="1.0"?> <broadcast><connection-config><database><server-name>server001</server-name>|<port>1521</port><sid>ORCL</sid><username>sysdba</username><password>my1manager</password></database><database><server-name>server002</server-name>|<port>1521</port><sid>PROD</sid><username>sysdba</username><password>my1manager</password></database></connection-config> <sql-statement>select * from v$parameter</sql-statement></broadcast>

XmlconfigvoClass contains and reads and analyzes the code of the XML document. After the constructor without parameters is called, the class will read the config. xml file and create a newDocumentbuilderfactoryInstance. In this example, DTD (Document Type Definition) or mode verification is not used. The factory generates a Document Object for analysis. If necessary, the document can also be operated.

Next, we will select// Broadcast/connection-config/DatabaseThe node of the XPath expression. These nodes contain the elements required to create a database connection: server name and port, Oracle SID, your name and password. Finally, we will use// Broadcast/SQL-StatementThe XPath expression is used to query the SQL statement. So far, the form of the getter method isGetxxxx ()) FromXmlconfigvoRetrieve all data.

Use JDBC

Java database connectivity (JDBC) technology is a Java API that provides connection capabilities to Oracle databases to retrieve data and fill workbooks. The jdbc api also has a special feature that allows you to establish connections between Oracle databases to send SQL statements and process results. The program in this article only uses some of the functions of this API, because you do not need to execute DML or SET transaction processing. Although all types of databases are supported, processing is limited to a specific range, which can be clearly mapped to an Excel worksheet.

The oracle JDBC driver depends on tnsnames. ora or other standard Oracle client connection information. As long as there is a JRE in the system and the oracle JDBC Driver Class exists in classpath (and there is a database in the Network), the Java class has enough resources to create a connection to the Oracle database.

Classes12.jar contains JDBC drivers and classes dedicated to Oracle, which are also necessary for this project. It should be included in classpath so that you can run the program. Generally, you can find the file in <ORACLE_HOME>/jdbc/lib. You can also download the file from OTN.

OracleconnectionvoClass contains information and processing related to the JDBC connection. Oracle. JDBC. driver. oracledriver is a URL in the form of JDBC: oracle: thin: @ <servername @ >:@ <Oracle listener port >:@ <Oracle SID>, it will be used to create a connection.

Jakarata poi

As I have mentioned in previous articles, Jakarta poi filesystem API implements the OLE 2 composite document format in pure Java mode, and uses the hssf API to read and write Excel files in Java. This API is used to read Excel files in this article. Here we will use it to write an Excel file. (POI also provides you with the ability to open and operate an existing workbook ). For more information about poi column functions, see the "hssf feature Guide", which provides code instances to demonstrate features that developers often need.

To create a worksheet object, call the following code (in the broadcastdriver class ):

HSSFWorkbook wb = new HSSFWorkbook(fs);

InPoisheetgeneratorClass to create a worksheet and add it to the worksheet:

HSSFSheet sheet = wb.createSheet(sheetname);

The column title is in bold and configured as follows:

HSSFFont boldFont = wb.createFont();boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);HSSFCellStyle boldStyle = wb.createCellStyle();boldStyle.setFont(boldFont);

When the column related to the result set is iterated, the cell format defined above will be applied:

row.getCell((short) x).setCellStyle(boldStyle);

To write a worksheet to a disk, call the following code:

OutputStream  = new FileOutputStream("output.xls");wb.write(out);out.close();

Broadcast. Jar

 

Broadcast. jar contains the classes that constitute the project itself:

• Broadcastdriver. Broadcastdriver contains the main method used for this application. CreateHssfworkbook ()Object used to hold all worksheets. Then createXmlconfigvoObject used to read connections and SQL queries from config. xml. The application iterates eachOracleconnectionvoAnd set the JDBC driver, JDBC database URL, database name, Database Password, SQL query and worksheet name (in the form<Server Name>-<Oracle SID>). A worksheet filled with data is added to the worksheet. After all the connection iterations are completed, the worksheet is written to the file system.
• Oracleconnectionvo. Oracleconnectionvo stores the JDBC driver, JDBC database URL, database name, database password, and SQL query value.
• Xmlconfigvo. Xmlconfigvo CreationDocumentbuilderfactoryTo analyze the config. XML document. The XPath query is used to retrieve nodelist containing the required values:

  NodeList nodelist = org.apache.xpath.XPathAPI.selectNodeList(doc, xpath);

  After finding the correct node, you can callGetnodevalue ()To retrieve its value.

• Poisheetgenerator.PopulatesheetMethod to complete most of the work. After initial settings, the column title is displayed in bold. The database connection is enabled and the query is executed.

   

  Metadata is retrieved. We need metadata to determine the number of columns to be iterated. The metadata also contains the names of some columns that are used as the names of the columns in the worksheet being processed. Next we will iterate the result set. In this example, the processing of various data is very elementary. For the first returned object, the program will try to fill the cell with numbers (especially Java long characters. IfNumberformatexceptionError (because the value cannot be converted to a number ),TostringUsed to return the object.TostringThe function is inObjectDefined in the object,ObjectIs the bottom layer of the Java System, so no matter what objects are returned, the validity of this method can be guaranteed. However, if we analyze the meaning and relevance of the returned values to objectsTostringThe actual values returned are extremely diverse.

Potential Applications

 

Some query examples are provided below, which can be added to the config. xml file to return the data that DBA or application developers want to compare between databases.

The V $ database table contains general information about the database. This information may be required when you compare database configurations or solve backup and recovery problems. Although the number and type of fields in different database versions are different, the following queries can be executed smoothly and the results of the tables that match the description are displayed:

SELECT * FROM v$database

If a DBA wants to record the version information of some databases, it is likely to use the V $ version data table. In this case, the following query may be used:

SELECT 'Name: '||name "Database Information" FROM v$database UNION ALLSELECT 'DBID: '||dbid FROM v$database UNION ALLSELECT * FROM v$version

In backup and recovery operations, the files to be concerned with are mainly control files, redo log files, and database data files themselves. You can use the following query to retrieve the location of these files:

SELECT * FROM (SELECT  ' ' "Tablespace" ,Name "File Name" FROM V$CONTROLFILEunionSELECT  ' ', Member FROM V$LOGFILEunionSELECT  Tablespace_Name, File_Name FROM DBA_DATA_FILESUNIONSELECT ' ' ,'****AS OF: ' ||to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS')||'****' FROM dual)ORDER BY 1

Oracle recovery Manager (RMAN) is generally used to manage backup and recovery operations. When you want to check the backup activity or standardize the backup activity, you may need to use the RMAN information stored in the database. RMAN backup-related requests include:

SELECT * FROM V$BACKUP_SET 

Or

SELECT * FROM  V$RMAN_CONFIGURATION

Oracle Applications DBAs may encounter a series of problems when managing their environments. If the DBA manages many instances, they may want to compare the patches that have been applied:

SELECT * FROM APPLSYS.AD_APPLIED_PATCHES ORDER BY PATCH_NAME

These are only several possible situations. Any query can be executed as long as there are no syntax errors and corresponding objects in the database, and the returned results can also be displayed in the workbook. Examples provided here are attractive to technicians. You can also imagine some business purposes, such as accessing database objects and queries for specific applications.

Conclusion

The Java Community provides a large amount of resources to use to quickly create programs (thanks to a large number of verified APIs ). With the JDBC driver of Oracle, you can use the Java programming language to conveniently access the database. With the poi created by Jakarta programmers, you can directly create an Excel worksheet with multiple table formatting and other features. In addition to exploring these technologies, the software described in this article can also automatically execute some daily activities, which can properly reduce the workload of DBAs.

 

Casimir saternos is an oracle-certified dBA, IBM-certified enterprise developer, and sun-certified Java programmer who lives in Allentown, Pennsylvania. Http://www.oracle.com/technology/global/cn/pub/articles/saternos_broadcast.html