XML APIs for databases

XML APIs for databases

－－Blend the power of XML and databases using custom SAX and DOM APIs

Ramnivas Laddad

JavaWorld columnist

February 2000

Most Web applications require the presentation of database-generated information. XML, because of its ability to separate content from presentation, is fast becoming an industry standard for data exchange. Most XML tools work with either the SAX or DOM API. This article presents a way to blend the power of a database with the features of XML. It also provides a simple, pure Java implementation of XML APIs for databases that works with any JDBC data source. With this approach, XML tools can treat a database as a virtual XML document.

Databases and XML offer complementary functionality for storing data. Databases store data for efficient retrieval, whereas XML offers an easy information exchange that enables interoperability between applications. To take advantage of XML's features we can convert database tables into XML documents. We can then use XML tools with such documents for further processing. For example, XML documents can be presented as HTML pages with XSLT stylesheets, can be searched with XML-based query languages such as XQL, can be used as a data-exchange format, and so on. However, converting a database into an XML document is an expensive approach, one that requires not only the initial cost of conversion but also the subsequent costs of synchronizing both information sources.

For processing XML documents, most XML tools work with the SAX or DOM API. In this article, we'll look at a way to implement the same APIs directly over a database, enabling XML tools to treat databases as if they were XML documents. That way, we can obviate the need of converting a database.

We'll see an implementation of the SAX API for Databases that should work with any database with a JDBC driver. Next, we'll examine an implementation of the DOM API for Databases that uses the SAX API internally. To demonstrate the SAX API for Databases, we'll look at its integration with XT (an XSLT processor). We'll also see an example of how such integration can create HTML pages that incorporate an XSLT stylesheet directly from a database and how it can convert a database into an XML document. Finally, we'll look at how the DOM API for Databases integrates with an XQL processor.

In this article, I make use of existing tools rather than create new tools to illustrate the applications of the SAX and DOM APIs for Databases. I show how to leverage a number of available XML tools to work with a database. All the XML tools I mention are either available for free or free for noncommercial use (though you should, of course, check licensing agreements).

Overview of the SAX and DOM APIs

SAX is an event-based API for XML. With it, the SAX parser reports events such as the start and end of elements to the application as it walks over the document. Because the parser reports events as it visits different parts of the document, it does not have to build any internal structure. That reduces the strain on system resources, which makes the parser attractive for large documents. For XML documents received as continuous streams, an event-based API is the only choice.

The DOM API, on the other hand, follows a tree-like construct. Elements have parent-child relations with other elements. With this API, the parser builds an internal structure such that an application can navigate it in a tree-like fashion. DOM allows an application to have random access to the tree-structured document at the cost of increased memory usage.

XML APIs for databases: The basics

Because of a database's highly regular data-storage structure, we can map it into data-centric XML documents. For example, we can transform a database table into an XML document with a DTD of the following form:

<!ELEMENT table rows*>

<!ELEMENT rows (column1, column2, ...)>

<!ELEMENT column1 #PCDATA>

<!ELEMENT column2 #PCDATA>
....

In other words, with an XML API for databases, we can make the database look like an XML document; these APIs present the database as a virtual XML document. We are at the most basic concept of object-oriented design: it is the interface -- not the implementation -- that matters. In our situation, the tools using such an XML API need not care whether they are operating on a database table or an XML file.

A SAX or DOM parser can enable XML tools to work directly with databases

Implementing the SAX API for databases

To implement the SAX API for Databases, we need to implement a parser that operates on a JDBC data source, iterates over each row and column, and generates appropriate SAX events while iterating. The SAX specification provides the org.xml.sax.InputSource class that models a data source representing a URL or a byte stream. To represent a database, we need a specialized form of it that can represent a table in a database. We therefore implement JDBCInputSource, which extends the org.xml.sax.InputSource class. Let's look at JDBCInputSource in more detail:

// JDBCInputSource.java
package dbxml.sax;
import java.sql.*;
import org.xml.sax.InputSource;
public class JDBCInputSource extends InputSource {
    private String_connectionURL;
    private String_userName;
    private String_passwd;
    private String_tableName;
    public JDBCInputSource(String connectionURL, String userName,
                           String passwd, String tableName) {
        super(connectionURL);
        _connectionURL = connectionURL;
        _userName = userName;
        _passwd = passwd;
        _tableName = tableName;
    }
    public String getTableName() {
        return_tableName;
    }
    public Connection getConnection() throws SQLException {
        return DriverManager.getConnection(_connectionURL, _userName, _passwd);
    }
}

In the code above, the constructor takes the information needed to connect to a database and the name of the table to be parsed. The method getConnection() connects to the database and returns a Connection object.

Next, we need to implement the SAX parser that uses JDBCInputSource to iterate over database table rows and columns and generates SAX events along the way. To simplify the code, we create an abstract ParserBase class, which implements the org.xml.sax.Parser and has responsibility only for managing various handlers. We then create our SAX parser for the JDBC source JDBCSAXParser that extends the ParserBase class.

(To view the code for ParserBase.java, click here.)

// JDBCSAXParser.java
package dbxml.sax;
import java.io.IOException;
import java.sql.*;
import org.xml.sax.*;
import org.xml.sax.helpers.AttributeListImpl;
public class JDBCSAXParser extends ParserBase {
    private static final AttributeList_stockEmptyAttributeList
        = new AttributeListImpl();
    //------------------------------------------------------------------
    // Methods from the Parser interface
    //------------------------------------------------------------------
    public void parse (InputSource source) throws SAXException, IOException {
        if (! (source instanceof JDBCInputSource)) {
            throw new SAXException("JDBCSAXParser can work only with source "
                                   + "of JDBCInputSource type");
        }
        parse((JDBCInputSource)source);
    }

    public void parse (String systemId) throws SAXException, IOException {
        throw new SAXException("JDBCSAXParser needs more information to "
                               + "connect to database");
    }

    //------------------------------------------------------------------
    // Additional methods
    //------------------------------------------------------------------
    public void parse(JDBCInputSource source)
        throws SAXException, IOException {
        try {
            Connection connection = source.getConnection();
            if (connection == null) {
                throw new SAXException("Could not establish connection with "
                                       + "database");
            }

            String sqlQuery = getSelectorSQLStatement(source.getTableName());
            PreparedStatement pstmt = connection.prepareStatement(sqlQuery);

            ResultSet rs = pstmt.executeQuery();
            parse(rs, source.getTableName());
            rs.close();

            connection.close();
        } catch (SQLException ex) {
            throw new SAXException(ex);
        }
    }

    public void parse(ResultSet rs, String tableName)
        throws SAXException, SQLException, IOException {
        if (_documentHandler == null) {
            return; // nobody is interested in me, no need to sweat!
        }

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

        String tableMarker = getTableMarker(tableName);
        String rowMarker = getRowMarker();

        _documentHandler.startDocument();
        _documentHandler.startElement(tableMarker, _stockEmptyAttributeList);
        while(rs.next()) {            _documentHandler.startElement(rowMarker,
                                          _stockEmptyAttributeList);
            for (int i = 1; i <= numCols; i++) {
                generateSAXEventForColumn(rsmd, rs, i);
            }
            _documentHandler.endElement(rowMarker);
        }
        _documentHandler.endElement(tableMarker);
        _documentHandler.endDocument();
    }

    public void parse(String connectionURL, String userName, String passwd,
                      String tableName) throws SAXException, IOException {
        parse(new JDBCInputSource(connectionURL, userName, passwd, tableName));
    }

    //------------------------------------------------------------------
    // Protected methods that derived classes could override to
    // customize the parsing.
    //------------------------------------------------------------------
    protected void generateSAXEventForColumn(ResultSetMetaData rsmd,
                                             ResultSet rs,
                                             int columnIndex)
        throws SAXException, SQLException {
        String columnValue = rs.getString(columnIndex);
        if (columnValue == null) {
            return;
        }
        String columnMarker
            = getColumnMarker(rsmd.getColumnLabel(columnIndex));
        char[] columnValueChars = columnValue.toCharArray();
        _documentHandler.startElement(columnMarker,
                                      _stockEmptyAttributeList);
        _documentHandler.characters(columnValueChars,
                                    0, columnValueChars.length);
        _documentHandler.endElement(columnMarker);
    }

    protected String getTableMarker(String tableName) {
        return tableName;
    }
    protected String getRowMarker() {
        return "row";
    }
    protected String getColumnMarker(String columnName) {
        return columnName;
    }
    protected String getSelectorSQLStatement(String tableName) {
        return "select * from " + tableName;
    }
}

Let's examine the code in more detail. JDBCSAXParser includes several overloaded parse() methods. In the list below, the org.xml.sax.Parser interface requires implementing the parse(InputSource) and parse(String) methods. The other parse() methods simplify the code and allow derived classes to override them to modify the parser behavior.

The parse(InputSource) method calls the parse(JDBCInputSource) method if the argument is of type JDBCInputSource; otherwise, it throws a SAXException as it cannot deal with it.

The parse(String) method throws a SAXException as the information supplied is not sufficient to access the database.

The parse(JDBCInputSource) method gets a Connection object from the input source and executes a query to obtain a ResultSet object. It then calls parse(ResultSet) with this object.

The parse(ResultSet, String) method performs the core parsing logic. It iterates over each row in the result set and each column in the rows. The row iteration loop is surrounded by calls to startElement() and endElement() with a table marker as the element-name argument. Similarly, each column iteration loop is surrounded by calls to startElement() and endElement() with a row marker as the element-name argument. In both cases an empty attribute list passes as the second argument to the startElement() methods. On each visit to a column, the generateSAXEventForColumn() method is called with column-name and column-value arguments. The value of a column is accessed by the getString() method on the result-set object, as we need a string representation of the column data to be notified in the characters() SAX event.

The convenience method parse(String, String, String, String) simply creates a JDBCInputSource object with the arguments passed to it and then calls the parse(JDBCInputSource) method with it.

The protected methods of JDBCSAXParser offer some customization possibilities through overriding:

The generateSAXEventForColumn() method generates events for column data. A null value for a column in a database has a different meaning from a column with an empty string. We capture the difference by not firing any events for a column that has a null value. Another choice for representing a null value in a database is to use a binary attribute like isNull. With this option, a true value will be set for null data; otherwise it will be false.

The getTableMarker(), getRowMarker(), and getColumnMarker() methods return reasonable defaults for table, row, and column markers. Derived classes may override these to provide custom markups.

The getSelectorSQLStatement() method returns a "select * from <tableName>" string. Derived classes can override it to provide a different select query string to offer database-level filtering.

The JDBCSAXUtils convenience class provides two methods for creating a JDBCInputSource: it can be done from either a property file or a Property object. There's no need to supply a long list of parameters that describe a database to an application that uses either the SAX or DOM APIs for database. The class expects the user to supply a property file that contains entries for a database URL, a user name and password to connect to the database, a JDBC driver to establish a connection, and a table name. The code below demonstrates a typical property file:

#  portfolio.prop
# JDBCSAXSource property file
URL=jdbc:odbc:db1
user=jw
password=jw-passwd
table=portfolio
driver=sun.jdbc.odbc.JdbcOdbcDriver

 The story so far ...

We now have a simple parser that can generate appropriate SAX events for the information in a database table. It takes care of null data and offers some marker customization. While such functionality may be sufficient for some applications, the complete solution will consider additional functionality because:

The parser does not incorporate relational information. That can be solved by using a XPointer/XLink to set the reference to a foreign key in a table.

A text column in a database may contain marked-up data. A SAX parser for databases should parse those data as well and generate appropriate SAX events. If such functionality is important for an application, it could override generateSAXEventForColumn() and parse the content of the column and generate additional SAX events.

In databases, a table contains an unordered list of columns; the order in which columns are stored is not important. An XML DTD, on the other hand, does not have a way to describe an unordered collection of child elements.

We can deal with this problem in a few ways. If the task is to convert a database into another XML document, say an HTML page, the XSLT stylesheet written for that purpose can create output in the correct order. We could also override the getSelectorSQLStatement() method to supply an explicit list of columns in the correct order.

It is desirable to present only a selected part of a table as a document based on some query. While XML tools can do the filtering, databases are better at it. The getSelectorSQLStatement() method can be overridden to return the appropriate select query string.

The parser uses the getString() method on the result-set object to obtain the string representation of the value in a column. This works fine for columns with text, numbers, and so on, but it does not work well with binary data. While binary data can be represented as text, that may not be suitable for certain tasks. The parser also does not deal with user-defined types available with SQL3/JDBC 2.0.

We can solve both problems by overriding the generateSAXEventForColumn() method and providing a suitable implementation.

Implementing the DOM API for databases

To build a DOM tree for a database table, we could iterate over rows and columns and build nodes for a tree as we visit them. Or we could employ another library, like Sun's JAXP implementation, that builds a DOM tree from a SAX event stream. The latter approach is simpler, and it requires less coding because it reuses an existing facility. To implement the DOM API using such an approach we need just a clever reuse of the SAX parser for databases we implemented earlier.

Class JDBCDOMParser implements the DOM API for Databases:

// JDBCDOMParser.java
package dbxml.dom;
import java.io.IOException;
import org.w3c.dom.Document;
import org.xml.sax.SAXException;
import com.sun.xml.tree.XmlDocumentBuilder;
import dbxml.sax.*;
public class JDBCDOMParser {
    public static Document createDocument(JDBCInputSource inputSource)
        throws SAXException, IOException {
        XmlDocumentBuilder documentBuilder = new XmlDocumentBuilder();
        JDBCSAXParser saxParser = new JDBCSAXParser();
        documentBuilder.setParser(saxParser);
        saxParser.parse(inputSource);
        return documentBuilder.getDocument();
    }
}

The implementation for class JDBCDOMParser is simple. It uses the XmlDocumentBuilder class provided by JAXP to build a DOM document from a SAX event stream. The JDBCDOMParser has only one method: createDocument(), which takes a JDBC data source as the argument. The method creates a JDBCSAXParser and sets it as a parser for an XmlDocumentBuilder object. It then fires the parsing and returns the resulting document from the XmlDocumentBuilder object. Internally, the XmlDocumentBuilder object responds to the SAX events generated by the JDBCSAXParser object by building a DOM document.

Using the SAX API for databases

We have already looked at one example of using the SAX API for Databases to implement a DOM API for Databases. Here we look at another example of using the SAX API for Databases. In this section, we look at using the SAX API for Databases to integrate with XT -- an XSLT processor written in Java. With such integration, we can apply an XSLT stylesheet directly to the virtual XML documents stored in a database.

We wrap the logic of creating a SAX source for a given database source and processing it with the given XSLT stylesheet to produce an output file in class JDBCXSLProcessor, which is based on com.jclark.xsl.sax.Driver from XT. The main method takes three arguments: a database property file, an XSLT stylesheet file, and an output file.

As we'll see below, we can use this approach to generate HTML pages directly without incurring the penalty of creating an intermediate XML file. Moreover, we'll look at how we can use the integration of the SAX API for databases and XT to convert a database into a nonvirtual XML document.

(To view the source code for JDBCXSLProcessor.java,  click here.）

Generating HTML pages directly from a database using an XSLT stylesheet

Here we look at a simple stylesheet that formats a highly regular XML document represented by a database table. The database table is formatted as an HTML table. The generic stylesheet createTable.xsl can be used to transform any XML document with a tablelike structure. The stylesheet uses names of markers for columns as table headers.

(To view the stylesheet createTable.xsl,  click here.)

Converting a database to XML with an XSLT stylesheet

While most XML applications work with the SAX or DOM API, we may need to obtain an XML document file in some situations. For example, we need an XML document to use a tool that does not work with either API. Here, I suggest a way to convert a database into an XML file. With this approach, we write an XSLT stylesheet to do the identity transformation. Using such a stylesheet with the SAX API for Databases will yield an XML document that represents a table in the database. I have provided a stylesheet -- identity.xsl -- for the identity transformation that works with the current implementation of XT.

identity.xsl
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="@*|*">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>
</xsl:stylesheet>

 Note that while an XSLT stylesheet easily performs the identity transformation, it is not very efficient as it goes through the complex general-purpose logic of applying the full stylesheet. This inefficiency can be a particular problem for database tables with large numbers of records. An alternative approach is to write an application to perform the identity transformation. Such an application would listen to SAX events and create XML elements and data in response to them, resulting in an XML document that represents the table in the database.

Using the DOM API for databases

For most situations, the SAX API for Databases is more memory efficient than the DOM API for Databases. However, some applications need random access to the XML documents and therefore require the tree-like structure that the DOM API for Databases offers.

Integrate the DOM API for Databases with the XQL processor

XML Query Language (XQL), a query language for XML documents, has a syntax similar to that of XPath patterns. Here, we integrate our DOM parser for database with GMD-IPSI's XQL Engine. With such integration, we can perform SQL-like queries on the XML document representing a database table.

As an example of integration, I provide a simple shell for querying the XML document obtained from a database table. The class JDBCXQLProcessor creates a shell-like environment, which takes the queries from the user and prints the resulting document. The method processQueries() can work with any Document object -- not just objects created by the JDBCDOMParser. It reads the System.in for the query string, performs the query, and prints the result on System.out. The main() method creates a JDBCInputSource object from its argument and uses it with JDBCDOMParser to obtain a Document object corresponding to the given database table.

(To view the code for JDBCXQLProcessor.java, click here.）

As a side note, writing a database-to-XML converter is a snap with XMLWriter and JDBCDOMParser. Just get a Document object from JDBCDOMParser and write it to the desired file with XMLWriter.write(Document).

Conclusion

In this article, we discussed using the XML APIs for database to tap the information residing in databases. With such APIs, we can avoid the costs associated with converting a database into XML documents and the cost of keeping them synchronized. We presented a Java implementation of SAX and DOM APIs for databases, which can work with any database with a JDBC driver. We then presented an integration of SAX API for Databases with XT. We illustrated the uses of such integration to create HTML pages directly from a database and to convert databases into XML documents. Finally, we integrated the DOM API for Databases with an XQL processor.

Resources

The complete source code for the implementation of the SAX and DOM APIs for Databases, as well as for the examples discussed in the article, can be downloaded in zip format. The zip file also includes self-executing jar files containing compiled class files. The README.txt file provided in the top-level directory contains instructions to set up and run the examples:

http://www.javaworld.com/jw-01-2000/dbxml/jw-01-dbxml.zip

The class documentation for this article:

http://www.javaworld.com/jw-01-2000/dbxml/docs/index.html

"XML for the Absolute Beginner," Mark Johnson (JavaWorld, April 1999), provides a thorough introduction to XML:

http://www.javaworld.com/javaworld/jw-04-1999/jw-04-xml.html

The W3C's XML standard:

http://www.w3.org/TR/REC-xml

The W3C's proposed XSLT standard:

http://www.w3.org/TR/1999/PR-xslt-19991008

For more about the SAX API and information on parsers and applications that support this API:

http://www.megginson.com/SAX/

W3C's Website for detailed information on the current Document Object Model (Level 1) specification:

http://www.w3.org/TR/REC-DOM-Level-1/level-one-core.html

Sun's Java API for XML Parsing (JAXP) (formerly known as Project X) includes an XML parser with optional validation and a mechanism for creating a DOM structure from a SAX data source. I have used it to implement a DOM parser for databases that uses the SAX driver for databases that we developed. Chances are that a version of JAXP or something similar to it will become part of a future JDK:

http://developer.java.sun.com/developerWorks/earlyAccess/xml

XT is a popular XSLT processor written in Java by James Clark. It quickly tracks changes in the proposed XSLT standard:

http://www.jclark.com/xml/xt.html

The GMD-IPSI XQL engine allows XML document searches with XQL. The engine works with any conformant DOM implementation. We could therefore use it to implement an XQL engine over the database:

http://xml.darmstadt.gmd.de/xql/index.html

"XML and Databases," a paper by Ronald Bourret (Technical University of Darmstadt, Germany, December 1999) discusses several issues and solutions for the exchange of data between an XML document and a database:

http://www.informatik.tu-darmstadt.de/DVS1/staff/bourret/xml/xmlAndDatabases

About the author

Ramnivas Laddad is a Sun-certified architect of Java technology (Java 2). He has a master's in electrical engineering with a specialization in communication engineering. He has six years of experience with several software projects involving GUI, networking, and distributed systems. He has developed object-oriented software systems in Java for the last two years and in C++ for the last five years.

Ramnivas works at Real-Time Innovations, where he is working to design and develop ControlShell, a component-based programming framework for complex real-time systems.

Reprinted with permission from JavaWorld magazine. Copyright Web Publishing Inc., an IDG Communications company. Register for JavaWorld alerts.