Presentation

Database Web Applications Using XSLT

If you haven't read the pages on architecture, you may be lost here. This section about database applications is just an adaptation of the application that is based on an XML document in a file. The big difference is that an XML document is first made from an SQL query. Then the resulting document is handled in much the same way as the file based application.

Having said that, there are some other important differences between the database and file based applications:

• Database query results are cached as local files
• Session persistence is used for database results file caching
• Session persistence is used for HTTP query parameters
• Session persistence is used to cache the entire XSL transform engine for dramatically improved speed
• The primary document for the XSLT transform is no longer the document that contains the end-user data.

Advantages of use XSLT for database applications

Not only are the advantages of using XSLT for web applications in effect, much of the database access and caching process is written in XSLT. That means you can control when and what data gets cached by writing XSLT templates. There is no need to use Java although you do need to write SQL queries.

The end result is that lots of SQL queries and the associated user presentations can be written quickly. This is possible because all the SQL source is kept in an XML document file you can easily edit with any editor anywhere. The changes are available immediately without any recompiles or restarting a server. Adding more database resultset 'components' in XSLT just amounts making another call to a template that does the hard work. Writing more SQL queries only takes adding a simple node to an XML document. That node contains the actual SQL query along with the SQL query parameters that comes from other documents available in the transform such as the user inputs document node or results of previous operations in the user session.

Not only that, the XSLT template that does the work is really fairly small if you want to modify it yourself. It's easy to understand, taking only 30 lines of code including comments and blank lines. And the Java servlet that connects to the database is also small and quite commonly available. The specific implementation of that servlet I'm presenting here was written in just a few hours and can be replaced with other servlets that do almost identical functions. Very similar servlets are available from IBM and Oracle as free utilities.

Sample Code for Database Access and Display

Just to show how easy it is to write SQL and put the result set into the HTML page, here are a few snippets of code. This recordset contains a list of payments made by a previously selected contributor (to US presidential candidates). The selected contributor is identified by an HTTP query parameter in the URL http://localhost/xsl/xslappsd/?id=2345. Since the servlet that runs the XSLT transform (not the servlet that connects to the database) gets the HTTP query string and converts it to a document, that contributor id can be refered to in an Xpath expression like $param/id. Then all that's needed is a way to put the result of that Xpath expression into the SQL query. That bit of magic is taken care of by the 30 line template and the servlet that connects to the database.

To make all that happen easily, the query and the Xpath expression are written right into an XML document that will be read by the XSTL transform. Here is a single SQL query in that document. (All the queries used in the application are in the same file)

<prepared-statement name="payments-list">
  <statement>
    select * from payments where trans_id = ?
  </statement>
  <param>
    <path>$param/id</path>
    <type>string</type>
  </param>
</prepared-statement>

Then, to actually display that recordset just takes the following XSLT template code. If you copy this code for use in your own application, you just need to change the names in bold below

<!-- ****** get the recordset ****** -->
<xsl:variable name="payments">
  <xsl:call-template name="live-recordset">
    <xsl:with-param name="statement-name" select="'payments-list'"/>
  </xsl:call-template>
</xsl:variable>

<tr>
  <td colspan="2" align="center" bgcolor="silver">
  Payment History
  </td>
</tr>

<!-- ***** iterate through the variable and display the columns **** -->
<xsl:for-each select="$payments/table/row">
  <tr>
    <td>
      <!-- **** refer to the database column name 'trans_date' **** -->
      <xsl:value-of select="trans_date"/>
    </td>
    <td align="right">
      <xsl:value-of select="amt"/>
    </td>
  </tr>
</xsl:for-each>

Notice that the live-recordset template is called with a parameter that names the SQL query that will be used which is payments-list. That matches the name attribute in the <prepared-statement> node in the code above.

Here is the display that is produced from the code above

Top