December 18, 2014
Hot Topics:

Converting JDBC Result Sets to XML

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

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




Page 2 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