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]




Archived Comments


Most Viewed Articles (in JDBC )

Latest Articles (in JDBC)

Comment on this tutorial