Entity relationship diagrams (ERD), while central to such development efforts, don't go far enough when it comes to explaining the actual use of a particular column. Some ERD design tools do allow you to store metadata in them, but this forces anybody who wants to look up metadata definitions later to either use those specific tools. Moreover, descriptions stored in design tools, while adequate for those involved in the design process, may not meet the needs of developers performing maintenance or creating enhancements after the application goes live. Consequently, the usage and meanings of columns and tables typically get modified, lost, or obscured over time.

A simple metadata dictionary can help you monitor and maintain this essential information. Just use the Apache POI library API to query your database for its metadata and then store it in a clear and organized way in Microsoft Excel. This article describes how to implement this solution.

The Apache POI overview page describes the project this way:

"The POI project is the master project for developing pure Java ports of file formats based on Microsoft's OLE 2 Compound Document Format. OLE 2 Compound Document Format is used by Microsoft Office Documents, as well as by programs using MFC property sets to serialize their document objects."

Figure 1: POI-Generated Excel Output File

Practically speaking, this means that the POI API enables you to write Java code that interacts with Microsoft Office applications without having to know anything about the underlying C++ Microsoft Foundation Classes. Figure 1 shows an Excel output file containing some of the metadata that you can pull out of your database tables using POI.

A closer look at POI and its code explains how it generated the Excel output. The data dictionary utility first connects to your database to collect the metadata from the tables. (To connect to any database using Java you need your database vendor's JDBC drivers. The code in this article uses Oracle, so if you try it on your computer, you need to have Oracle's classes12.jar in your classpath.) Lines 46-55 in the constructor make the connection to the database and set up the Excel output file:

System.out.print(" Loading JDBC Driver -> " + driverClass + "\n");
Class.forName(driverClass).newInstance();
System.out.print(" Connecting to -> " + connectionURL + "\n");
this.con = DriverManager.getConnection(connectionURL, userID, userPassword);
System.out.print(" Connected as -> " + userID + "\n");
wb = new HSSFWorkbook();
f = new File("c:\\poidatadict.xls");

Continue reading the article on DevX.com...