More Examples (XSLT)

You can use XSLT extension mechanisms to push XSLT processing beyond text or markup generation and to read information from non-XML sources.

8.3.2. Accessing a Database with an Extension Element

In this example, we're going to build an extension element rather than an extension function. When we wrote our extension functions, all we had to worry about was the data passed to us on the function call. We weren't concerned with the document tree, the context, or anything else. With an extension element, though, we have to be much more aware of the document as a whole. Our code will use the attributes of the extension element to connect to a database, run a query, and then return the result set as a node set (specifically, a Xalan XNodeSet). That node-set will be inserted into the output tree, giving us the capability to build a document dynamically. Our XML document defines the parameters for the database access and the query, then the extension element does the work in the background magically. Here's how the XML document looks:

<?xml version="1.0"?>
<report>
  <title>HR employee listing</title>
  <section>
    <title>Employees by department</title>
    <dbaccess driver="COM.ibm.db2.jdbc.app.DB2Driver"
      database="jdbc:db2:sample" tablename="employee" where="*"
      fieldnames='workdept as "Department", lastname as "Last Name", 
                  firstnme as "First Name"'
      order-by="workdept" group-by="workdept, lastname, firstnme"/>
  </section>
</report>

Notice that all of the vendor-specific information about our database connection is contained in the attributes of our XML document. That means we can use our extension element with any JDBC-compliant database. The following documents work just as well:

<?xml version="1.0"?>
<report>
  <title>Sales Results</title>
  <section>
    <title>Top sales people - 3Q 2001</title>
    <dbaccess driver="com.sybase.jdbc2.jdbc.SybDriver""
      database="jdbc:sybase:Tds:localhost:5000/sales" 
      tablename="results" where="*"
      fieldnames='lastname as "Last Name", 
      firstnme as "First Name", sum(order) as "Totals"'
      order-by="sum(order)" />
  </section>
</report>

<?xml version="1.0"?>
<report>
  <title>Pets We Own</title> 
  <section>
    <title>Our pets</title>
    <dbaccess driver="org.gjt.mm.mysql.Driver"
      database="jdbc:mysql://localhost/test" tablename="pet" where="*"
      fieldnames='name as "Pet Name", species as "Species", sex as "M/F"'/>
  </section>
</report>

The first listing uses DB2, the second uses Sybase, and the final listing uses MySQL. Our stylesheet uses our database-accessing extension element to replace the <dbaccess> elements with HTML tables filled with the results of our database query. In our sample document, the XML input closely mirrors the SQL statements we'll use to interact with the database. Our extension element takes the elements and attributes of the <dbaccess> element, gets data out of the database, then formats it accordingly.

The stylesheet that invokes our extension element looks like this:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:db="DatabaseExtension"
  extension-element-prefixes="db">

  <xsl:output method="html"/>

  <xsl:template match="/">
    <html>
      <head>
        <title>
          <xsl:value-of select="report/title"/>
        </title>
      </head>
      <body>
        <h1>
          <xsl:value-of select="report/title"/>
        </h1>
        <xsl:for-each select="report/section">
          <h2>
            <xsl:value-of select="title"/>
          </h2>
          <xsl:for-each select="dbaccess">
            <db:accessDatabase/>
          </xsl:for-each>
        </xsl:for-each>
      </body>
    </html>
  </xsl:template>

</xsl:stylesheet>

The stylesheet is pretty straightforward. The namespace declaration xmlns:db="xalan://DatabaseExtension" associates the Java class DatabaseExtension with the namespace prefix db. Whenever our stylesheet processes an XML element with a namespace prefix of db, our code is invoked to do the processing. Notice that in our stylesheet, we used the extension element <db:accessDatabase>; this tells Xalan to invoke the accessDatabase() method of the DatabaseExtension class.

In this example, we want our extension element to look at the various attributes of the <dbaccess> element, build a SQL query from the information it finds there, connect to the requested database, and put in the result tree elements that represent the database query results. To keep our example simple, we'll have our extension element return those results in an HTML <table> element; you could write the extension element to generate other types of output, if you wanted. Our extension element returns an XNodeSet; the nodes in the returned XNodeSet are added to the result tree.

For our extension element to work, it has to do several things:

Find the <dbaccess> element we need to process.
Use the driver attribute of the <dbaccess> element to determine what JDBC driver to use. Once we have this value, we need to load the driver. Specifying the database driver allows us to use different databases in the same XML document. In our previous sample XML files, the three queries specify databases managed by DB2, Sybase, and MySQL; because JDBC itself is vendor-neutral, we can use our extension element with any JDBC-compliant database.
Examine the tablename, where, fieldnames, group-by, and order-by attributes of the <dbaccess> element to build the SQL query statement.
Connect to the database specified by the tablename attribute of the <dbaccess> element.
Execute the query statement.
Build the table based on the items in the JDBC ResultSet object. To build the table, we have to get a DOM Document object; we'll use that object as a factory method to create all the nodes in the node-set our extension element returns. We'll create a <table> element, then for each row in the result set, we'll create a <tr> element (with the appropriate <td> elements as its children) and append it to the table. For Xalan, we use the DOMHelper class to get the Document object that we'll use to create all nodes.
Return the result set. We create an XNodeSet, attach our <table> element (with all its children) to it, then return it. This result is added automatically to the output document.

Now that we've said what we're going to do, let's take a look at the code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import org.apache.xalan.extensions.XSLProcessorContext;
import org.apache.xalan.templates.ElemExtensionCall;
import org.apache.xpath.DOMHelper;
import org.apache.xpath.objects.XNodeSet;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class DatabaseExtension
{
   private static boolean getDriver(String driverName)
   {
     boolean gotTheDriver = false;
     try
     {
       Class.forName(driverName);
       gotTheDriver = true;
     }
     catch (Exception e)
     {
       System.out.println("Can't load the database driver " + driverName);
       e.printStackTrace();
     }
     return gotTheDriver;
   }

   public static XNodeSet accessDatabase(XSLProcessorContext context,
                                         ElemExtensionCall elem)
   {
     XNodeSet dbResult = null;
     DOMHelper dh = new DOMHelper();
     Document doc = dh.getDOMFactory();
     Element table = null, header = null, tr = null, td = null, th = null;

     Element contextNode = (Element) context.getContextNode();
     if (getDriver(contextNode.getAttribute("driver")))
     {
       try
       {
         StringBuffer query = new StringBuffer("select ");
         query.append(contextNode.getAttribute("fieldnames") + " ");
         query.append("from " + contextNode.getAttribute("tablename") + " ");
         String nextAttr = contextNode.getAttribute("group-by");
         if (nextAttr != null)
           query.append(" group by " + nextAttr);
         nextAttr = contextNode.getAttribute("order-by");
         if (nextAttr != null)
           query.append(" order by " + nextAttr);

         Connection con = DriverManager.
           getConnection(contextNode.getAttribute("database"));
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery(query.toString());

         ResultSetMetaData rsmd = rs.getMetaData();
         int columnCount = rsmd.getColumnCount();
         table = doc.createElement("table");
         table.setAttribute("border", "1");

         header = doc.createElement("tr");
         for (int i = 1; i <= columnCount; i++)

         {
           th = doc.createElement("th");
           th.appendChild(doc.createTextNode(rsmd.getColumnName(i)));
           header.appendChild(th);
         }
         table.appendChild(header);

         while (rs.next())
         {
           tr = doc.createElement("tr");
           for (int i = 1; i <= columnCount; i++)
           {
             td = doc.createElement("td");
             td.appendChild(doc.createTextNode(rs.getString(i)));
             tr.appendChild(td);
           }
           table.appendChild(tr);
         }
         dbResult = new XNodeSet(table);
         rs.close();
         stmt.close();
         con.close();
       }
       catch (java.sql.SQLException sqle)
       {
         System.out.println("Exception: " + sqle);
       }
     }
     else
       System.out.println("Couldn't load the driver!");

     return dbResult;
   }
}

Extension elements in Xalan are called with two arguments: an XSLProcessorContext object and an ElemExtensionCall object. In our code here, we'll use the XSLProcessorContext object to get the context node. Once we have the context node (the <dbaccess> element), we can get the values of the various <dbaccess> element attributes in the source tree.

The first thing we do in our extension element is declare the XNodeSet we're going to return to Xalan. After that, we create a DOMHelper object and use the getDOMFactory method to create the DOM Document object we'll use as a factory for creating new nodes:

XNodeSet dbResult = null;
DOMHelper dh = new DOMHelper();
Document doc = dh.getDOMFactory();

Our next task is to instantiate the JDBC driver. To make our code more flexible, we specify the driver in the driver attribute of the <dbaccess> element. In the previous XML examples, we used drivers for MySQL, Sybase, and DB2. Assuming everything to this point has succeeded, we'll build the query string. To simplify things, our example assumes we're going to build an SQL SELECT statement; feel free to extend this code to do more sophisticated things. The query is built from various attributes of the <dbaccess> element.

Once the query has been built, we connect to the appropriate database. The database is specified with the database attribute of the <dbaccess> element. (In our previous XML samples, notice that DB2, Sybase, and MySQL specify databases in different ways. Specifying this in an attribute makes our extension element more flexible.) We connect to the database, execute the query statement, and get a JDBC ResultSet object in return.

Once we have the ResultSet, our job is relatively simple. We need to create an HTML table, with each row in the table containing a row from the ResultSet. In the previous code, we call our Document object to create each new node. Here are some examples:

while (rs.next())
{
  tr = doc.createElement("tr");
  for (int i = 1; i <= columnCount; i++)
  {
    td = doc.createElement("td");
    td.appendChild(doc.createTextNode(rs.getString(i)));
    tr.appendChild(td);
  }
  table.appendChild(tr);
}
dbResult = new XNodeSet(table);

In this sample, we create the <tr> element with the DOM createElement method. Notice that when we want to add text to a node, we use the createTextNode method to create a text node and append it as a child. In the loop just shown, we take each row of the ResultSet and create a <tr> element for it. We create a <td> element for each column in the ResultSet, then append it to the <tr> element. When we're done with the row, we append the <tr> element to the <table>.

Once we've processed the entire ResultSet, we create a new XNodeSet by passing our <table> element to the XNodeSet constructor. This technique can be used to create any number of nodes, including elements, attributes, text, and comments.

For example, here's how we created the HTML <table> element and added the border="1" attribute to it:

Element table = doc.createElement("table");
table.setAttribute("border", "1");

Our final step is simply to clean up all of the JDBC stuff and return the XNodeSet to Xalan:

rs.close();
stmt.close();
con.close();

...

return dbResult;

The nodes in our XNodeSet are sent straight to the output document, where they appear as ordinary HTML nodes, as shown in Figure 8-7.

Figure 8-7. HTML file with output from an extension element

With this extension, we've been able to generate nodes dynamically, then add them to the output document. Every time this stylesheet is processed, the extension adds the latest data from various databases to the output. You could improve this extension element by adding caching, connection pooling, and other features for performance and scalability; the point of this example was to show you how extension elements work. Whatever its limitations, the best feature of our extension element is that we can use it with any JDBC-compliant database. You can use this code to generate HTML (or XML) from any database, whether the database vendor supports it or not.

TIP: As of this writing, efforts are underway to standardize extension functions and exension elements across XSLT processors. The EXSLT project is one such effort. Visit their web site (http://www.exslt.org) for more information on the EXSLT library of extensions.

Figure 8-7. HTML file with output from an extension element

8.3. More Examples

8.3.1. Generating JPEG Files from XML Content

Figure 8-4. Background JPEG image

Figure 8-5. Generated JPEG files for XML <title> elements

Figure 8-6. Generated image with too much text

8.3.2. Accessing a Database with an Extension Element