http://www.developer.com/

Back to article

Converting JDBC Result Sets to XML


March 22, 2004

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:

In order to build a utility that can convert any result set into XML, we need an abstract way of understanding what each result set contains. A ResultSetMetaData object provides just the right information. It will tell us the number of columns in a given row of data as well as the names and types of each column.

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>

Bonus Material!

Click here for the sample JDBCUtil and CustomerDAO classes. You'll also notice some bonus material included in the archive not covered in the article. I was curious about the performance difference in returning an XML DOM versus an XML string. It turns out that returning a string is four to five times faster. The JDBCUtil.toXML(ResultSet) method performs this function. The CustomerDAOTester class uses a StopWatch object to compare String and DOM method invocations.

Should you return a DOM or a String? It really depends on your situation. Often, you will need to merge the results from multiple result sets. You may need to transform the results via XSLT. If so, a DOM would be appropriate. In the situations where a String will do, be careful, because you will need to deal with encoding issues that the DOM API handles for you.

Disclaimer

Increasingly, relational databases are providing features for returning the results of queries as XML documents or strings. The features in these products would be more robust and optimized far beyond the simple example shown here. If you have such a database and have purchased this capability, I would encourage you to investigate it. If not, start with the example shown here and grow your own.

Summary

XML is a common data format for passing data between components on disparate platforms. Relational databases are the most common way of persisting operational data. How do you bridge the gap between the two standard structures? Your relational database may have such capability. If so, learn how to exploit this feature. If not, I've shown how to use JDBC and DOM API to build your own capability. The rest is up to you!

About the Author

Jeff Ryan is an enterprise architect for Hartford Financial Services. He has twenty years experience designing, developing, and delivering automated solutions to business problems. His current focus is on Java, XML, and Service Oriented Architecture. He may be reached at jeffreyjryan@aol.com.

Other Articles Written by Jeff Ryan

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date