Developer

Format Java-extracted data using DOM and XSL

Java can pull data from a database rather easily, but formatting it into something useful is another matter. See how to take Java-captured data, load it into a DOM document, and use XSL style sheets to format the data into any fashion you require.


Java can extract data from any JDBC-compliant database, convert that data into a DOM object, and then use XSL to format that data in the desired fashion. In a previous article, we demonstrated how to programmatically extract the data from a database. Now, we'll look at how to build the DOM object and use an XSL style sheet to format the data. The resulting output can then be used for any number of applications by supplying the required inputs.

Building the DOM document.
The most recent versions of Java include JAXP XML processing capability and implement the DOM API defined by the World Wide Web Consortium (W3C). With these JAXP-compliant versions, a DOM document object is created with three lines of code using the JAXP factory and builder methods:
�DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
�DocumentBuilder builder = factory.newDocumentBuilder();
�Document document = builder.newDocument();

A root element, appropriately named resultset, is created and added to the document object:
�Element root = document.createElement("resultset");
�document.appendChild(root);

As the resultset cursor is moved through the result set, a new element containing row data is added to the root element:
�Element nextRow = document.createElement("row");

The column count has to be read from the ResultSetMetaData object, which must be defined during the database extraction phase of this project:
�int columnCount = rsmd.getColumnCount();

The column names are read by using a for loop to retrieve the names and adding a name node with a child TextNode containing the name value to a names element for each column name:
�String[] columnNames = new String[columnCount];
�Element names = document.createElement("names");
�for (int i = 0; i < columnCount; i++){
�/* the first column is 1, the second is 2, ... */
�columnNames[i] = rsmd.getColumnName(i + 1);
�Element nextNameNode = document.createElement("name");
�Text nextName = document.createTextNode(columnNames[i]);
�nextNameNode.appendChild(nextName);
�names.appendChild(nextNameNode);
�}

Column indices start with one instead of zero. As each row of data is read, the column values are retrieved generically as Strings in a for loop, which reads each column value:
�/* Move the cursor through the data one row at a time. */
�while(resultSet.next()){
�/* Create an Element node for each row of data. */
�Element nextRow = document.createElement("row");
�if (debug) System.out.println("new row");
�for (int i = 0; i < columnCount; i++){
�/* Create an Element node for each column value. */
�Element nextNode = document.createElement(columnNames[i]);
�/* the first column is 1, the second is 2, ... */
�/* getString() will retrieve any of the basic SQL types*/
�Text text = document.createTextNode(resultSet.getString(i + 1));
�nextNode.appendChild(text);
�nextRow.appendChild(nextNode);
�}
�root.appendChild(nextRow);
�}

After all data has been translated into a DOM document object, the connection can be closed. At no time was a file operation required by the DataBaseHandler. The XML document is created in memory.

A concrete DataBaseHandler object
A generic DefaultDataBaseHandler is now simple to construct with very few lines of code:
public class DefaultDataBaseHandler extends AbstractDataBaseHandler{

�public DefaultDataBaseHandler(String urlString, String userName,
�String password, String driverName){

�setUrlString(urlString);
�setUserName(userName);
�setPassword(password);
�setDriverName(driverName);
�}
}

An OracleDataBase handler is only a little more complicated:
public class OracleDataBaseHandler extends AbstractDataBaseHandler{

�private String thinOraclePrefix = "jdbc:oracle:thin:@";
�private String urlString;
�private String userName;
�private String password;
�private String driverName = "oracle.jdbc.OracleDriver";
�private String host;
�private String port;
�private String sid;

�public OracleDataBaseHandler(String host, String sid,
�String userName, String password){

�this.host = host;
�this.sid = sid;
�/* a valid url connection string format is: "host:port:sid" */
�setUrlString(thinOraclePrefix + host + ":1521:" + sid);
�setUserName(userName);
�setPassword(password);
�}

�public OracleDataBaseHandler(String host, String port, String sid,
�String userName, String password){

�this.host = host;
�this.sid = sid;
�this.port = port;
�/* a valid url connection string format is: "host:port:sid" */
�setUrlString(thinOraclePrefix + host + ":" + port + ":" + sid);
�setUserName(userName);
�setPassword(password);
�}
}

An ODBCDataBaseHandler is not much different, except that it deals with data source names (DSNs) instead of a host name, port number, and SID. The host name, port number, and SID associated with an Oracle database are appropriate because we are dealing with a DataBase Server instead of a database file. But the Microsoft Access relational database uses .mdb files.
public class ODBCDataBaseHandler extends AbstractDataBaseHandler{

�private String urlString;
�private String userName;
�private String password;
�private String driverName = "sun.jdbc.odbc.JdbcOdbcDriver";
�private String dsn;
�private String odbcPrefix = "jdbc:odbc:";
�public ODBCDataBaseHandler(String dsn, String userName, String password){
�/* a valid url connection string format is: "jdbc:odbc:dsn" */
�this.dsn = dsn;
�setUrlString(odbcPrefix + dsn);
�setUserName(userName);
�setPassword(password);
�setDriverName(driverName);
�}
}

For details on accessing a specific database, consult the product documentation. All that is left to translate a database result into a useful alternative output type is another class that calls a concrete DataBaseHandler and then does the XSL transformation.

SQLMapper
The SQLMapper class uses a DataBaseHandler class to do its database work and a map method to transform a document object into the desired output type. The map method returns a String, since the original assumption was that the output would consist of character data. A StringBuffer would have also been appropriate.

SQLMapper needs a SQL query string, an output type set, and a DataBaseHandler to perform its job. These are initialized with its setter methods and retrieved with its getter methods:
�if ((getSQL() != null)
�&& (getSQL().length() > 0)
�&& (getOutputType() != null)
�&& (isValidOutputType(getOutputType()))
�&& (getDataBaseHandler() != null)){
�Document document = dataBaseHandler.getDocument(getSQL());

To transform to the desired output, an XSL style sheet is specified in a setter method. A Transformer object is created simply with a private getTransformer method that obtains either a default style sheet or specified style sheet. If needed, Java's TransformerFactory methods are used to build a style sheet:
�TransformerFactory transformerfactory = TransformerFactory.newInstance();
�transformer = transformerfactory.newTransformer(getStylesheet());

The transformation is also just a few lines of Java code:
�Transformer transformer = getTransformer();
�StringWriter sw = new StringWriter();
�StreamResult result = new StreamResult(sw);
�if (transformer != null) {
�transformer.transform(new DOMSource(document.getDocumentElement()), result);
�output = sw.toString();
�System.err.println("output: " + output);
�}else{
�System.err.println("No Transformer");
�}

The Transformer object needs a DOMSource object, which is obtained by feeding the Transformer's constructor with the root element of a DOM document.

It is left up to the implementer to devise his or her own XSL style sheets. Some default style sheets for transforming raw data to HTML or XML are available. The following is a generic XSL style sheet for translating the generated data into an XML document using what is referred to as an identity transformation and ensuring that the output is compliant with the UTF-8 standard and nicely indented for readability:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
�<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" />
�<xsl:template match="@*|node()">
�<xsl:copy>
�<xsl:apply-templates select="@*|node()"/>
�</xsl:copy>
�</xsl:template>
</xsl:stylesheet>

The following is a generic XSL style sheet for translating the generated data into an HTML table:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
�<xsl:output method="html" indent="yes" />

�<xsl:template match="resultset">
�<h2 align="center">Default HTML Transform Result</h2>
�<table border="1" align="center"><xsl:apply-templates/></table>
�</xsl:template>
�<xsl:template match="names">
�<tr><xsl:apply-templates/></tr>
�</xsl:template>
�<xsl:template match="name">
�<td><xsl:apply-templates/></td>
�</xsl:template>
�<xsl:template match="row">
�<tr><xsl:apply-templates/></tr>
�</xsl:template>
�<xsl:template match="*">
�<td><xsl:apply-templates/></td>
�</xsl:template>

</xsl:stylesheet>

The following is a generic XSL style sheet for translating the generated data into a Wireless Markup Language (WML) table:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
�<xsl:output method="html" indent="yes" />
�<xsl:template match="resultset">
�<wml>
�<card id="index" title="Default WML Transform Result">
�<xsl:apply-templates/></card>
�</wml>
�</xsl:template>
�<xsl:template match="names">
�Names: <xsl:apply-templates/>
�</xsl:template>
�<xsl:template match="name">
�<i><xsl:apply-templates/></i>
�</xsl:template>
�<xsl:template match="row">
�<card><xsl:apply-templates/></card>
�</xsl:template>
�<xsl:template match="*">
�<i><xsl:apply-templates/></i>
�</xsl:template>

</xsl:stylesheet>

The following is a generic XSL style sheet for translating the generated data into a table of comma-separated values (CSVs), which can be read directly with Excel:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
�<xsl:output method="text" indent="yes"/>
�<xsl:template match="names">
�<xsl:for-each select="*">
�<xsl:if test="position() != last()"><xsl:value-of select="."/>,</xsl:if>
�<xsl:if test="position() = last()"><xsl:value-of select="."/></xsl:if>
�</xsl:for-each>
�</xsl:template>
�<xsl:template match="row">
�<xsl:for-each select="*">
�<xsl:if test="position() != last()"><xsl:value-of select="."/>,</xsl:if>
�<xsl:if test="position() = last()"><xsl:value-of select="."/></xsl:if>
�</xsl:for-each>
�</xsl:template>
</xsl:stylesheet>

Some ideas for the application of SQLMapper
A back-end database could have a table defining the form elements of a Web page user interface, which could be retrieved in a JSP page with just a few lines of code. Perhaps your Web server has an Oracle database installed with no front-end graphical user interface. Now you can put the database to work to define the elements of a GUI:
<%@ page language="java" contentType="text/html" import="sqlmapper.*, mywebapp.* %>
<%@ page errorPage="errorPage.jsp" %>
<html>
�<!— getUserArea.jsp executed on <%= (new java.util.Date()) %> —>
�<!— @Author: Charles Bell —>
�<!— @Version: April 22, 2003 —>
�<head>
� <title>Your company name - <%= dynamicTitle %></title>
�</head>
�<body background="<%= dynamicBackgroundImageFileName%>">
�<%
�WebAppUtility myWebAppUtility = new WebAppUtility();
�String host = myWebAppUtility.getDatabaseHost();
�String sid = myWebAppUtility.getDatabaseSID();
�String userName = (String) session.getAttribute("validatedUserName");;
�String password = myWebAppUtility.getDatabasePassord();
�SQLMapper mapper = new SQLMapper();
�DataBaseHandler dataBaseHandler=
�new OracleDataBaseHandler(host, sid, userName, password);

�mapper.setSQL("select * from FormDataElements");
�mapper.setOutputType("html");
�mapper.setDataBaseHandler(dataBaseHandler);
�mapper.setXSLTranformStyleSheet("stylesheets/formdata.xsl");
�out(mapper.map);
�%>
�<%@include file="footer.jsp" %>
�</body>
</html>

Using these techniques, a JSP Web page could pop up the latest report generated dynamically from a live database with the click of a button. The CSV output could be used to generate dynamic Excel spreadsheets. The XML output could be served to another Web application that communicates with its own back-end database.

The big picture
We have described a process of creating an interface that defines a generic behavior for a DataBaseHandler, and implemented it in an abstract class that can be extended to a custom-built, concrete database handler using only a few lines of Java code. A SQLMapper class utilized this behavior to connect transparently to a relational database, execute a SQL query, and translate the data into a DOM document object. The DOM object was then transformed by applying an XSL style sheet to obtain the desired output. The output can now be used for any number of applications by supplying the required inputs in an efficient, easy-to-implement way.

Editor's Picks

Free Newsletters, In your Inbox