November 27, 2014
Hot Topics:

Converting JDBC Result Sets to XML

  • March 22, 2004
  • By Jeff Ryan
  • Send Email »
  • More Articles »

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;
}




Page 1 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel