Using JDBC to extract data from a database and output to an XML document

By: Nicholas Chase

How mapping works

The goal of this tutorial is to show how to create an XML document out of data from
a database using a mapping file. In other words, the map file determines what data
is retrieved and how it is ultimately represented in the XML file.

One way to do this is to create a temporary XML document out of the data as it's
extracted from the database, then massage that data into the new format according
to the map file. The map file determines which data is extracted, the name and
structure of the new file, and what data is stored where.

The structure

The mapping file contains several pieces of information:

â€¢ The original data, in the form of a data element. For maximum flexibility,
this is in the form of an SQL statement. In this way, you can use the
mapping file to specify that data should be drawn from more than one
table.
â€¢ The overall structure of the new document. This is in the form of the root
element, which, through attributes, specifies the name of the destination
root element and the name of the elements that are to represent database
rows.
â€¢ The names and contents of data elements. These are contained in a
series of element elements. The elements include the name of the new
element and any attribute or content elements. These two elements
designate the data that should be added, and, in the case of attributes,
what it should be called. For example, if the description element
should have a product_number attribute that represents the
product_id column and the product_name as content, the map file
would represent it as:
<element name="description">
<attribute name="product_number">product_id</attribute>
<content>product_name</content>
</element>

The mapping file

The final mapping file is as follows:

<?xml version="1.0"?>
<mapping>
<data sql="select * from products" />
<root name="pricingInfo" rowName="product">
<element name="description">
<attribute name="product_number">product_id</attribute>
<content>product_name</content>
</element>
<element name="quantity">
<content>lower</content>
</element>
<element name="size">
<content>size</content>
</element>
<element name="sizeUnit">
<content>unit</content>
</element>
<element name="quantityPrice">
<content>unit_price</content>
</element>
</root>
</mapping>

Creating an XML document using SQL results

The algorithm

The process for creating the new XML document is as follows:

1. Parse the map file to make the necessary information, including the data
to be retrieved, available.
2. Retrieve the source query. This allows for the dynamic retrieval of data
based on the map file.
3. Store the data in a Document object. This temporary document will then
be available to pull the data from to create the destination document
according to the mapping.
4. Retrieve the data mapping to make it available to the application.
5. Loop through the original data. Each row of the data is analyzed and
re-mapped to the new structure.
6. Retrieve element mappings. The mapping file determines what data is
pulled from the temporary document, and in what order.
7. Add elements to the new document. Once the data is retrieved, add it to
the new document under new names.
8. Add attributes to the new document. Finally, add any attributes to the
appropriate elements.

Parse the map file

The first step in creating the new document is to retrieve the mapping, which can
only be accomplished by parsing the map file. Note that because this file also
contains references to the data that will be eventually retrieved, you must parse it before any database operations can be performed.

import org.w3c.dom.Document;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

public class Pricing extends Object {
public static void main (String args[]){

//Create the Document object

Document mapDoc = null;

try {
//Create the DocumentBuilderFactory
DocumentBuilderFactory dbfactory =

DocumentBuilderFactory.newInstance();
//Create the DocumentBuilder
DocumentBuilder docbuilder = dbfactory.newDocumentBuilder();
//Parse the file to create the Document
mapDoc = docbuilder.parse("mapping.xml");

} catch (Exception e) {
System.out.println("Problem creating document: "+e.getMessage());
}

//For the JDBC-ODBC bridge, use
//driverName = "sun.jdbc.odbc.JdbcOdbcDriver"
//and
//connectURL = "jdbc:odbc:pricing"
String driverName = "JData2_0.sql.$Driver";
String connectURL = "jdbc:JDataConnect://127.0.0.1/pricing";
Connection db = null;

Retrieve the source query

Next retrieve the source query stored in the sql attribute of the data element.

import org.w3c.dom.Element;
import org.w3c.dom.Node;
System.out.println("Problem creating document: "+e.getMessage());
}

//Retrieve the root element

Element mapRoot = mapDoc.getDocumentElement();
//Retrieve the (only) data element and cast it to Element
Node dataNode = mapRoot.getElementsByTagName("data").item(0);
Element dataElement = (Element)dataNode;
//Retrieve the sql statement
String sql = dataElement.getAttribute("sql");

//Output the SQL statement
System.out.println(sql);

//For the JDBC-ODBC bridge, use
//driverName = "sun.jdbc.odbc.JdbcOdbcDriver"
//and
//connectURL = "jdbc:odbc:pricing"
String driverName = "JData2_0.sql.$Driver";
String connectURL = "jdbc:JDataConnect://127.0.0.1/pricing";
Connection db = null;

First determine the root element, then retrieve the data node. Because there is only
one data element, you can retrieve it directly. Similar techniques can also be
utilized to build a document from several queries run in sequence.

Finally, cast the Node to an Element so the Attribute value is available.

Remove the previous output statements and run the application to show the SQL
statement as output.

Store the data in a Document object

Once the data is successfully extracted from the database, it is stored in a temporary
Document. The generic method is to create a row element for each row of data,
with each column represented as an element named after that column, and with the
data itself as the content of the element.

public static void main (String args[]){

Document mapDoc = null;
//Define a new Document object

Document dataDoc = null;

try {
//Create the DocumentBuilderFactory
DocumentBuilderFactory dbfactory = DocumentBuilderFactory.newInstance();
//Create the DocumentBuilder
DocumentBuilder docbuilder = dbfactory.newDocumentBuilder();
//Parse the file to create the Document
mapDoc = docbuilder.parse("mapping.xml");

//Instantiate a new Document object

dataDoc = docbuilder.newDocument();

} catch (Exception e) {
System.out.println("Problem creating document: "+e.getMessage());
}
...
ResultSetMetaData resultmetadata = null;

//Create a new element called "data"

Element dataRoot = dataDoc.createElement("data");

try {
statement = db.createStatement();
resultset = statement.executeQuery("select * from products");

resultmetadata = resultset.getMetaData();
int numCols = resultmetadata.getColumnCount();

while (resultset.next()) {
//For each row of data
//Create a new element called "row"

Element rowEl = dataDoc.createElement("row");

for (int i=1; i <= numCols; i++) {
//For each column, retrieve the name and data
String colName = resultmetadata.getColumnName(i);
String colVal = resultset.getString(i);
//If there was no data, add "and up"
if (resultset.wasNull()) {

colVal = "and up";
}
//Create a new element with the same name as the column

Element dataEl = dataDoc.createElement(colName);
//Add the data to the new element
dataEl.appendChild(dataDoc.createTextNode(colVal));
//Add the new element to the row
rowEl.appendChild(dataEl);

}

//Add the row to the root element

dataRoot.appendChild(rowEl);

}

} catch (SQLException e) {
System.out.println("SQL Error: "+e.getMessage());

} finally {
System.out.println("Closing connections...");
try {

db.close();
} catch (SQLException e) {
System.out.println("Can't close connection.");
}
}

//Add the root element to the document

dataDoc.appendChild(dataRoot);

}
}

Specifically, and referring to the code example above, first create an empty
document along with the root element, data. For each row in the database, create a
row element, and for each column, create an individual element and add it to the
row. Finally, add each row element to the root, and the root to the Document.

Retrieve the data mapping

Once you have the data, it's time to work on mapping it to the new structure:
Retrieve the mapping from the parsed map document. First retrieve the information
on the root and row elements, then the element mappings themselves.

import org.w3c.dom.NodeList;

dataDoc.appendChild(dataRoot);

//Retrieve the root element (also called "root")

Element newRootInfo =

(Element)mapRoot.getElementsByTagName("root").item(0);
//Retrieve the root and row information
String newRootName = newRootInfo.getAttribute("name");
String newRowName = newRootInfo.getAttribute("rowName");
//Retrieve information on elements to be built in the new document
NodeList newNodesMap = mapRoot.getElementsByTagName("element");

}
}

Armed with this information, you can build the new Document.

Loop through the original data

Each of the original rows is stored in the temporary document as a row element.
You will want to loop through these elements, so retrieve them as a NodeList.

NodeList newNodesMap = mapRoot.getElementsByTagName("element");

//Retrieve all rows in the old document

NodeList oldRows = dataRoot.getElementsByTagName("row");
for (int i=0; i < oldRows.getLength(); i++){

//Retrieve each row in turn
Element thisRow = (Element)oldRows.item(i);

}

Retrieve element mappings

Now that you have the data and the mapping information, it's time to begin building
the new Document. For each row, cycle through the map to determine the order in
which the data columns should be retrieved from the temporary Document, and to
determine what they should be called when added to the new Document.

for (int i=0; i < oldRows.getLength(); i++){
//Retrieve each row in turn
Element thisRow = (Element)oldRows.item(i);
for (int j=0; j < newNodesMap.getLength(); j++) {
//For each node in the new mapping, retrieve the information
//First the new information...
Element thisElement = (Element)newNodesMap.item(j);
String newElementName = thisElement.getAttribute("name");
//Then the old information
Element oldElement =
(Element)thisElement.getElementsByTagName("content").item(0);
String oldField = oldElement.getFirstChild().getNodeValue();
}
}
...
For each element in the newNodesMap, the application retrieves the new element
name, then the name of the old element to retrieve.

Add elements to the new document

Adding the new elements to the document is a simple matter of creating a new
element with the proper name, then retrieving the appropriate data and setting it as
the content of the element.

public static void main (String args[]){
Document mapDoc = null;
Document dataDoc = null;
//Create the new Document
Document newDoc = null;
try {
//Create the DocumentBuilderFactory
DocumentBuilderFactory dbfactory = DocumentBuilderFactory.newInstance();
//Create the DocumentBuilder
DocumentBuilder docbuilder = dbfactory.newDocumentBuilder();
//Parse the file to create the Document
mapDoc = docbuilder.parse("mapping.xml");
//Instantiate a new Document object
dataDoc = docbuilder.newDocument();
//Instantiate the new Document
newDoc = docbuilder.newDocument();
} catch (Exception e) {
System.out.println("Problem creating document: "+e.getMessage());
}

//Retrieve the root element (also called "root")
Element newRootInfo = (Element)mapRoot.getElementsByTagName("root").item(0);
//Retrieve the root and row information
String newRootName = newRootInfo.getAttribute("name");
String newRowName = newRootInfo.getAttribute("rowName");
//Retrieve information on elements to be built in the new document
NodeList newNodesMap = mapRoot.getElementsByTagName("element");

//Create the final root element with the name from the mapping file

Element newRootElement = newDoc.createElement(newRootName);

NodeList oldRows = dataRoot.getElementsByTagName("row");
for (int i=0; i < oldRows.getLength(); i++){

//For each of the original rows
Element thisRow = (Element)oldRows.item(i);

//Create the new row

Element newRow = newDoc.createElement(newRowName);

for (int j=0; j < newNodesMap.getLength(); j++) {

//Get the mapping information for each column
Element thisElement = (Element)newNodesMap.item(j);
String newElementName = thisElement.getAttribute("name");

Element oldElement =
(Element)thisElement.getElementsByTagName("content").item(0);
String oldField = oldElement.getFirstChild().getNodeValue();

//Get the original values based on the mapping information
Element oldValueElement =
(Element)thisRow.getElementsByTagName(oldField).item(0);
String oldValue =
oldValueElement.getFirstChild().getNodeValue();

//Create the new element
Element newElement = newDoc.createElement(newElementName);
newElement.appendChild(newDoc.createTextNode(oldValue));
//Add the new element to the new row
newRow.appendChild(newElement);

}
//Add the new row to the root

newRootElement.appendChild(newRow);

}
//Add the new root to the document

newDoc.appendChild(newRootElement);

}
}

First, create the new Document, then create the new root element, which takes its
name from the mapping information. For each row in the temporary Document,
create an element for a new row using the newRowName specified in the map.

For each row, loop through each new element specified in the mapping and retrieve
the original data. In the previous panel, you retrieved the text of the content
elements in order. Use this information to determine which nodes to retrieve from the
temporary rows, and in what order. Once you have the old data and the new name,
create the new element and add it to the row.

Finally, add the new row to the root, and the root to the Document. The only thing
left to add is any attributes.

Add attributes to the new document

The new Document is almost complete. You've added the new elements, but not
any attributes that may have been specified. Adding them is similar to adding the
new elements themselves. The possibility exists, however, that there is more than
one attribute for an element, so this must be accounted for in the code. The easiest
way to accomplish this is to retrieve the attribute elements into a NodeList,
then iterate through this list to deal with each one. For each desired attribute,
determine the field name from the original Document, and the name in the new
Document.

Element newElement = newDoc.createElement(newElementName);
newElement.appendChild(newDoc.createTextNode(oldValue));

//Retrieve list of new elements

NodeList newAttributes =
thisElement.getElementsByTagName("attribute");

for (int k=0; k < newAttributes.getLength(); k++) {
//For each new attribute
//Get the mapping information
Element thisAttribute = (Element)newAttributes.item(k);
String oldAttributeField = thisAttribute.getFirstChild().getNodeValue();
String newAttributeName = thisAttribute.getAttribute("name");

//Get the original value
oldValueElement =
(Element)thisRow.getElementsByTagName(oldAttributeField).item(0);
String oldAttributeValue =
oldValueElement.getFirstChild().getNodeValue();

//Create the new attribute
newElement.setAttribute(newAttributeName, oldAttributeValue);
}

//Add the element to the new row

newRow.appendChild(newElement);
}
//Add the new row to the root
newRootElement.appendChild(newRow);

The final document

At the end of this process, newDoc holds the old information in the new format. From
here, it can be used in another application or transformed further using XSLT or
other means.

<pricingInfo>

<product>
<description product_number="1">Filet Mignon</description>
<quantity>1</quantity>
<size>1</size>
<sizeUnit>item</sizeUnit>
<quantityPrice>40</quantityPrice>
</product>

<product>
<description product_number="2">Filet Mignon</description>
<quantity>11</quantity>
<size>1</size>
<sizeUnit>item</sizeUnit>
<quantityPrice>30</quantityPrice>
</product>

<product>
<description product_number="3">Filet Mignon</description>
<quantity>101</quantity>
<size>1</size>
<sizeUnit>item</sizeUnit>
<quantityPrice>20</quantityPrice>
</product>

<product>
<description product_number="4">Prime Rib</description>
<quantity>1</quantity>
<size>1</size>
<sizeUnit>lb</sizeUnit>
<quantityPrice>20</quantityPrice>
</product>

<product>
<description product_number="5">Prime Rib</description>
<quantity>101</quantity>
<size>1</size>
<sizeUnit>lb</sizeUnit>
<quantityPrice>15</quantityPrice>
</product>
...
JDBC data extraction summary

Summary

This tutorial discussed the details of connecting to a database using JDBC and
extracting data, which was then used to create an XML file. Discussion included
making the application generic so that it could be used with any query and any
structure. This was accomplished partly through the use of metadata.

The structure of the XML destination file was determined by an XML mapping file.
The tutorial also covered the details of reading the mapping file and using it to
massage the temporary XML file into the desired structure.

About the author

Nicholas Chase has been involved in Web site development for companies including
Lucent Technologies, Sun Microsystems, Oracle Corporation, and the Tampa Bay
Buccaneers. Nick has been a high school physics teacher, a low-level radioactive
waste facility manager, an online science fiction magazine editor, a multimedia
engineer, and an Oracle instructor. More recently, he was the Chief Technology
Officer of Site Dynamics Interactive Communications in Clearwater, Florida. He is the
author of three books on Web development, including Java and XML From Scratch
(Que). He loves to hear from readers and can be reached at
[email protected]