Convert Whole Table Record into XML Document in Java

Introduction

XML has become the common format for passing data between components residing on different platforms. With the move to XML-based services, developers often find themselves converting various data structures to and from XML. Arguably, the most prevalent form of persisting data is in relational databases. How does one go about converting relational data structures into XML? In this article, we'll do just that and build a utility to expose relational data as XML documents. I'll focus on this problem from a Java programmer's perspective, although the concepts here could be implemented in other languages as well. JDBC (Java Data Base Connectivity) provides a common API for accessing a multitude of relational databases. It provides all the methods we need to create our result-set-to-XML utility. Of course, we will need a relational database to work through this example. I decided to use Pointbase. Any relational database you have on hand such as Access, MySQL, Oracle, or DB2 may be used to follow along. Because we will be building a generic routine, any table can be used as well. I used the sample customer_tbl that comes with the Pointbase sample database.


JDBC Result Set Overview

Relational data is represented in tables of rows and columns. In JDBC, the ResultSet object is used to work with tabular data. The ResultSet object has methods to iterate through rows of tabular data, and to access the columns that make up each row. Here is an example result set from the customer_tbl in the PBPUBLIC schema in Pointbase:


Document Object Model (DOM) Overview

An XML document is a tree structure of elements that contain other elements, attributes, and data. It is a very suitable data structure for storing the rows and columns of a result set. The following diagram shows how we might model the result set displayed above into an XML document. At the top of the tree is a Results node. It contains one or more rows. Each Row contains values of the named columns in that row.



The DOM API provides an object representation of an XML document. It can be used to create, read, update, and delete elements. We'll use the methods that DOM provides for creating a new document, and then for adding elements to the document. The standard JAXP (Java API for XML Processing) and DOM API are used in this example. I used the Apache Xerxes implementation. You could use any implementation to follow along.



Result Set to XML

With the background concepts on JDBC result sets and XML documents, we're ready to begin coding our example. We'll name our class JDBCUtil.
public class JDBCUtil
{

We'll create a method to convert any JDBC result set to a DOM with the following method signature:
public static Document toDocument(ResultSet rs)
throws ParserConfigurationException, SQLException
{

A JDBC result set is passed in as the only argument, and a XML DOM or Document is returned.

We begin by creating a new document using the standard JAXP API:
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document doc = builder.newDocument();

Next, the Results element of the document is created.
Element results = doc.createElement("Results");
doc.appendChild(results);

We get the meta data for the passed result set to know how many columns are in each row and what the column names are:
ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();

Now, we are ready to loop through the rows in the result set:
while (rs.next())
{
Element row = doc.createElement("Row"
results.appendChild(row);

A Row element is added to the results element for each result set row.

Next, we are ready to iterate through all of the columns in a given row. The column count from the meta data tells us how many columns are in the row. The ResultSetMetaData.getColumnName() method tells us the name of a given column. The value of a column object is accessed via the ResultSet.getObject() method. An element is created for each column and placed under its row.
for (int i = 1; i <= colCount; i++)
{
String columnName = rsmd.getColumnName(i);
Object value = rs.getObject(i);

Element node = doc.createElement(columnName);
node.appendChild(doc.createTextNode(value.toString()));
row.appendChild(node);
}

Now, we've created our XML document and can return it:
return doc;
}

Here is the completed method:
public static Document toDocument(ResultSet rs)
throws ParserConfigurationException, SQLException
{
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document doc = builder.newDocument();

Element results = doc.createElement("Results");
doc.appendChild(results);

ResultSetMetaData rsmd = rs.getMetaData();
int colCount = rsmd.getColumnCount();

while (rs.next())
{
Element row = doc.createElement("Row");
results.appendChild(row);

for (int i = 1; i <= colCount; i++)
{
String columnName = rsmd.getColumnName(i);
Object value = rs.getObject(i);

Element node = doc.createElement(columnName);
node.appendChild(doc.createTextNode(value.toString()));
row.appendChild(node);
}
}
return doc;
}



Sample Client

To test our result-set-to-XML method, we need a sample client program. I've created a simple class called CustomerDAO. DAO is a J2EE pattern that stands for "Data Access Object." It is used to encapsulate a resource such as a customer database. When a CustomerDAO object is constructed, it connects to our Pointbase database. When it is finalized, it closes the connection.Its public API is a getCustomerList() method that is implemented as follows:

public Document getCustomerList()
{
Document doc = null;

try
{
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * from customer_tbl");

doc = JDBCUtil.toDocument(rs);

rs.close();
stmt.close();
}
catch (Exception e)
{
e.printStackTrace();
}

return doc;
}

This method executes a very simple query asking for all rows from the customer table. The result set returned from the query is converted to a Document by the JDBCUtil.toDocument() method.

Here is a sample method for testing the CustomerDAO and JDBCUtil objects:
public static void main(String argv[]) throws Exception
{
CustomerDAO dao = new CustomerDAO();

Document doc = dao.getCustomerList();
System.out.println(JDBCUtil.serialize(doc));

}

We simply construct a CustomerDAO, and ask it to return a customer list as a DOM. JDBCUtil.serialize() is another method in the utility class that serializes the DOM as a string.

Here is sample output from our test:
<?xml version="1.0" encoding="UTF-8">
<Results>
<Row>
<CUSTOMER_NUM>1</CUSTOMER_NUM>
<DISCOUNT_CODE>N</DISCOUNT_CODE>
<ZIP>33015</ZIP>
<NAME>SuperCom</NAME>
<ADDR_LN1>490 Rivera Drive</ADDR_LN1>
<ADDR_LN2>Suite 678</ADDR_LN2>
<CITY>Miami</CITY>
<STATE>FL</STATE>
<PHONE>305-777-4632</PHONE>
<FAX>305-777-4635</FAX>
<EMAIL>supercom@aol.com</EMAIL>
<CREDIT_LIMIT>100000</CREDIT_LIMIT>
<LAST_SALE_DATE>1998-01-02</LAST_SALE_DATE>
<LAST_SALE_TIME>08:00:00</LAST_SALE_TIME>
</Row>
<Row>
<CUSTOMER_NUM>2</CUSTOMER_NUM>
<DISCOUNT_CODE>M</DISCOUNT_CODE>
<ZIP>33055</ZIP>
<NAME>Livingston Enterprises</NAME>
<ADDR_LN1>9754 Main Street</ADDR_LN1>
<ADDR_LN2>P.O. Box 567</ADDR_LN2>
<CITY>Miami</CITY>
<STATE>FL</STATE>
<PHONE>305-456-8888</PHONE>
<FAX>305-456-8889</FAX>
<EMAIL>www.tsoftt.com</EMAIL>
<CREDIT_LIMIT>50000</CREDIT_LIMIT>
<LAST_SALE_DATE>1998-01-02</LAST_SALE_DATE>
<LAST_SALE_TIME>09:00:00</LAST_SALE_TIME>
</Row>
...
</Results>