HomeDigital EditionSys-Con RadioSearch Java Cd
Advanced Java AWT Book Reviews/Excerpts Client Server Corba Editorials Embedded Java Enterprise Java IDE's Industry Watch Integration Interviews Java Applet Java & Databases Java & Web Services Java Fundamentals Java Native Interface Java Servlets Java Beans J2ME Libraries .NET Object Orientation Observations/IMHO Product Reviews Scalability & Performance Security Server Side Source Code Straight Talking Swing Threads Using Java with others Wireless XML

By now, everyone knows that XML is all about data. Unfortunately, that's about all most people know about it. Depending on whom you talk to, XML is projected to be the framework for replacing all software currently in existence or it's seen as an interesting niche technology.

The future of XML is certainly debatable, but it's clear that XML is a "hot" standards-based technology for defining the interchange of data. One of the problems faced by many companies is that the people in the company who deal with data are used to programming to DBCs - JDBC and ODBC. The XML native programming models - XSLT, DOM, SAX, and JAXP - all sound like formidable hurdles to clear in order to take advantage of XML. This article provides you with some clear guidelines on how to quickly extend existing Java applications to leverage XML using the data access standards you're familiar with, without learning any of the native XML programming models.

Can Our Applications Integrate with XML?
How many times have we all been asked if our existing Java applications can integrate with incoming XML documents over HTTP? We know we can make our applications work with any relational database that has a JDBC driver, but XML documents are something different. Surprisingly, most, if not all, of the work to accomplish this inside our existing applications has already been done by several JDBC driver vendors. Just as there are JDBC drivers for DB2, Oracle, and Microsoft SQL Server, there are also JDBC drivers for XML documents. Listing 1 provides some JDBC source code for reading in a purchase order from an Oracle table.

This particular purchase order model assumes that someone inside the company has entered order information through an application, and all pertinent data has been saved into a table called ORDERS in our Oracle database. Could we easily expand this model to also allow us to gather customer orders over HTTP directly from customer sites?

Our Applications Can Integrate with XML
There are several ways in which we can expand our existing purchase order process to include entering orders from the outside, but let's focus on a methodology in which we scan customer sites and enter orders directly into our ORDERS table. Now the ORDERS table will contain information that someone inside our company entered directly, as well as orders that we've obtained by polling customer sites. Clearly, there are several other ways to obtain this information, but all those methods could be accomplished with similar coding requirements (see Listing 2).

With Listing 2 we expand our existing application by integrating it with dynamic XML content, without writing a single line of DOM, SAX, or XSLT code. Our XML JDBC driver makes the HTTP XML data appear as just another relational database. We can issue SQL SELECT queries against the XML document and program using JDBC methods without understanding the difference between a DOM tree and a SAX event.

Can We Expose Our Data as XML?
We've shown how to integrate XML documents from outside our company into existing applications using JDBC programming. But how do we expose data as XML for others to consume? Currently, JDBC has no methods to expose relational data as XML; however, there are various ways to do this from a JDBC application without leveraging the JDBC specification and without learning an entire XML API.

For example, suppose we keep our catalog information internally in some DB2 tables and once a month we send out a printed copy of the items we have available, along with their prices. We could decide to publish the catalog electronically using HTML, but then the information would lose its semantics (a price would appear as a string of characters, not as a money field that could be automatically summed, for example). Instead, we could publish a daily update to our catalog in XML format that could be dynamically downloaded over the Web and leveraged by any standards-based applications (XML-, ODBC-, or JDBC-based) that the company wants to use.

Most every database or JDBC driver vendor offers ways to expose relational data as XML, although many of the mechanisms used require that application developers know quite a lot about XML formatting. Using two of the easier approaches, we'll show examples of how to expose a product catalog that's stored in a relational database into XML format through JDBC.

We Can Distribute Our Data as XML Documents
Suppose the data we want to expose is stored in a Microsoft SQL Server 2000 database. The code in Listing 3 retrieves the data in XML format, which can then be posted to the corporate Web server for download by external customers or partners.

The FOR XML AUTO clause used in the SQL grammar works only for Microsoft SQL Server. To show a difference in approaches, Listing 4 provides another example of similar functionality that's provided by jXTransformer, a product by DataDirect Technologies that's currently in beta. This second approach works against all databases, not just SQL Server.

We exposed our internal relational data externally on the Web via XML. Again, we didn't have to learn much about XML to do this, and we didn't have to do any XML API programming. We used JDBC with SQL grammar changes or a JDBC object wrapper API.

What About Web Services?
We can read XML data and integrate it with our relational databases; we can store relational data as XML; however, are there better ways to use relational assets within the context of the Web while still leveraging standards-based data access protocols like JDBC? Although many might think the answer is "no," the JDBC RowSet interface provides all the mechanisms necessary to use relational data assets within a Web services environment.

A JDBC RowSet object is essentially a cache of data retrieved from a relational data source. The RowSet object extends the JDBC ResultSet interface so programmers can scroll through data, update data, or do anything that a ResultSet object can do with data. The beauty of the RowSet object is that it contains all the logic necessary to interact with the data it holds without being connected to a data source. That is, the RowSet object can be disconnected and function without maintaining an open JDBC connection to its originating data. In addition, the RowSet interface can be serialized or sent over the network to a remote object while still maintaining all its properties.

There are several different implementations of the RowSet interface, but the one that has the most interest for Web applications is the WebRowSet implementation. It's a RowSet implementation that happens to use HTTP for communication and XML for data storage. When a programmer codes to the JDBC RowSet interfaces and deploys a WebRowSet instance, then any of the underlying features of XML and HTTP can be leveraged.

As an example, we might choose to initially populate a WebRowSet with inventory information stored in a centrally located DB2 system. We use JDBC RowSet methods to read the data, but behind the scenes the data is cached in an XML container. We can then make the inventory cache available to customers, partners, or even internal sites over HTTP via standard RowSet methods. Consumers can then read, update, or even send the data to other sites through standard JDBC calls without having a JDBC driver loaded or a live connection to DB2. The data reflects information that's stored in a DB2 database far from where you're using it. After making changes, we can send the WebRowSet back over HTTP to the originator, where the data can then be resynchronized inside the DB2 database.

Standards-Based Data Access APIs and XML Are Compatible
Through the use of some simple techniques with existing standards-based APIs, you can leverage the benefits of XML without learning any new XML APIs. Extending existing applications to the Web might not be as difficult as you first thought. You can integrate your applications with XML by using an XML JDBC driver to make the HTTP XML data appear as just another relational database. You can distribute data stored in a Microsoft SQL Server 2000 database as XML documents by using the FOR XML AUTO clause in your application. You can use relational data assets within a Web services environment by using the JDBC RowSet interface. And, when you use any of these approaches, you don't have to learn the XML native programming models - XSLT, DOM, SAX, and JAXP - to take advantage of XML.

Author Bio
John Goodson is vice president of product operations for DataDirect Technologies, a supplier of components for connecting data and applications across diverse environments. John leads the product strategy and development efforts for the company's connectivity technologies. He has active memberships in the JDBC Specification Expert Group and Java Rowset Expert Group. john.goodson@datadirect-technologies.com


Listing 1

//  Get list of orders placed since yesterday
ResultSet PurchaseOrder = stmt.executeQuery (
                from ORDERS
                where ORDERDATE > {fn CURDATE()} - 1");

while (PurchaseOrder.next() ) {
        int CustomerId = PurchaseOrder.getInt ("CUSTOMERID");
        int OrderId = PurchaseOrder.getInt ("ORDERID");
        int Quantity = PurchaseOrder.getInt ("QUANTITY");
        String Item = PurchaseOrder.getString ("ITEM");
        String Item = PurchaseOrder.getString ("EXTRADETAIL");

        // internal business rules dictate how purchase order is processed


Listing 2

//  Iterate through customer sites to see if there are orders

// we'll use two JDBC connections ... one for our internal Oracle tables and one to the
//     XML streaming information from the web

// Oracle ...
Connection  Oraclecon = DriverManager.getConnection (OracleDriverURL, uid, pwd);
Statement stmt = Oraclecon.createStatement()

// XML ...
Connection XMLCon = DriverManager.getConnection (XMLDriverURL, Xuid, Xpwd);
Statement XMLstmt = XMLCon.createStatement();

//  Internally, we'll have an Oracle table called CUSTOMERS that tracks all our customers
//    as well as from which URL we can obtain their order information ... provided they can
//    post e-orders

//  For Customers that can process orders online ... get the URL where we collect order info.
//    The URL will have a form similar to HTTP://www.acme.com/MyCoorders.asp
ResultSet Customer = stmt.executeQuery (
                from CUSTOMERS
                where ONLINEORDERS = 'T' ");

while (Customer.next() ) {
        int CustomerId = Customer.getInt ("CUSTOMERID");
        String CustomerURL = Customer.getString ("CUSTOMERURL");

// see if the customer placed any orders by reading a dynamically generated XML document
//   that contains their order information from their website and, if so,
//   insert the order into our ORDERS table
        ResultSet  NewOrder = XMLstmt.executeQuery (
                "select QUANTITY, ITEM, DETAIL from " + CustomerURL +
                        "where ORDERDATE > {fn CURDATE()} - 1");

        while (NewOrder.next() ) {

                // customer placed order ... gather data and insert the order into our Oracle ORDERS table



Listing 3

//   Build a copy of the catalog as an XML Document that can be exposed to customers

// Query for the products currently available and include the FOR XML AUTO clause
//  which will instruct SQL Server to return the result as an XML Document
ResultSet OurCatalog  = stmt.executeQuery (
              where PRODUCTFORSALE = 1 FOR XML AUTO");

while (OurCatalog..next() ) {
        // 3 columns selected but only 1 returned ... the XML Document that corresponds
        //   to the result set produced from the query
        String XMLCatalog = OurCatalog.getString (1);

        // the XML Document now sits in our XMLCatalog data structure which can
        //  be written to a file


Listing 4

//   Build a copy of the catalog as an XML Document that can be exposed to customers

// Query for the products currently available, which will result in an XML-formatted result
StringBuffer OurCatalog = new StringBuffer();
OurCatalog.append ("select xml_element ('product-id',p.PRODUCTID),");
OurCatalog.append (" xml_element('product-description',p.DESCRIPTION),");
OurCatalog.append (" xml_element('product-price',p.PRICE) ");
OurCatalog.append (" from PRODUCTS p where p.PRODUCTFORSALE = 1");

// Construct object to execute
JXTRQuery ProductCatalog = new JXTRQuery (conn, new String (OurCatalog));

// Execute, generate XML, and write to file
ProductCatalog.executeWriter (systemOutWriter,


All Rights Reserved
Copyright ©  2004 SYS-CON Media, Inc.
  E-mail: info@sys-con.com

Java and Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries. SYS-CON Publications, Inc. is independent of Sun Microsystems, Inc.