XSL Transforming XML generated from SQL results using JSTL tags in JSP pages – putting JSTL to the test

While working on more advanced Statistics Reporting for our weblog, I did some investigations into JSTL and more specifically the XML and SQL tags. Yes, I know I have said one should never use the SQL tags in a real application. However…. this is read-only, prototpying, quick and dirty etc. And of course I am investigating the SQL tags so I have to use them.

The setup I created was the following: I invoke a JSP (running in Tomcat 4) from the browser. This JSP imports several other JSPs that return XML documents. These XML documents are constructed from data returned from a database (either Oracle or MySQL) using the JSTL SQL tags. The JSP passes some simple parameters to the imported data-generating-JSPs to influence the exact set of data returned from the database. In the main JSP, the XML documents are transformed using XSLT and XSL-stylesheets. Typically the transformation is to HTML but I also worked on transformations to plain text, PDF and SVG.

Environment

I worked in Oracle 10g JDeveloper with its internal OC4J engine, against an Oracle 10g R1 database. I deployed on Tomcat 4 running on a Linux Server with MySQL 3.23 and Oracle 10g R1 databases. I made use of JSTL 1.0.

1. In my web-application, I included the following fragment in my web.xml:

  <taglib>
    <taglib-uri>/WEB-INF/c.tld</taglib-uri>
    <taglib-location>/WEB-INF/c.tld</taglib-location>
  </taglib>
  <taglib>
    <taglib-uri>/WEB-INF/fmt.tld</taglib-uri>
  <taglib-location>/WEB-INF/fmt.tld</taglib-location>
</taglib>
  <taglib>
    <taglib-uri>/WEB-INF/x.tld</taglib-uri>
    <taglib-location>/WEB-INF/x.tld</taglib-location>
  </taglib>
  <taglib>
    <taglib-uri>/WEB-INF/sql.tld</taglib-uri>
    <taglib-location>/WEB-INF/sql.tld</taglib-location>
  </taglib>

2. I copied the following files to the WEB-INF directory of the web-application:
c.tld, x.tld, sql.tld, fmt.tld

3. I copied the standard.jar file to the WEB-INF/lib directory (this file can be downloaded from http://java.sun.com/webservices/webservicespack.html or is found in the JDeveloper 10g install: JDEV_HOMEjakarta-taglibsjstl-1.0lib.

4. I included the following page level directives in all JSPs:

<%@ taglib uri="/WEB-INF/c.tld" prefix="c" %>
<%@ taglib uri="/WEB-INF/fmt.tld" prefix="fmt" %>
<%@ taglib uri="/WEB-INF/x.tld" prefix="x" %>

The Application

I started out in a very simple manner. My XML generating JSP is the following:

<%@ page contentType="text/xml" %>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c" %>
<%@ taglib uri="/WEB-INF/sql.tld" prefix="sql" %>
   <sql:setDataSource user="scott" password="tiger"
                       driver="oracle.jdbc.driver.OracleDriver"
                       url="jdbc:oracle:thin:@localhost:1521:orcl" var="ds"/>
    <sql:query var="emps" dataSource="${ds}" sql="select ename, empno  from emp where deptno = ?">
      <sql:param value="${param.deptno}"/>
    </sql:query>
<hr>
  <c:forEach var="row" items="${emps.rowsByIndex}">
<emp><empno><c:out value="${row [1]}"/></empno><ename><c:out value="${row [0]}"/></ename></emp>
</c:forEach>
</hr>

With

<%@ page contentType="text/xml" %>

we indicate that the output for this JSP will be an XML Document. Note the somewhat lazy definition of the database connection (not a datasource configured at application server level but quick and dirty in the JSP itself; an administration nightmare waiting to happen). Deptno is passed as bind-variable into the query to only select employees from the department that was specified in a parameter (${param.deptno}. This parameter can be provided in the URL used to invoke this jsp. If we were to invoke this JSP directly in the browser (…/HrXML.jsp?deptno=20) , this would be the outcome:

	<hr>
	<emp>
<empno>7369</empno>
<ename>SMITH</ename>
</emp>
	<emp>
<empno>7566</empno>
<ename>JONES</ename>
</emp>
	<emp>
<empno>7788</empno>
<ename>SCOTT</ename>
</emp>
	<emp>
<empno>7876</empno>
<ename>ADAMS</ename>
</emp>
	<emp>
<empno>7902</empno>
<ename>FORD</ename>
</emp>
</hr>

The main JSP, the one called from the browser, is this one:

<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c" %>
<%@ taglib uri="/WEB-INF/fmt.tld" prefix="fmt" %>
<%@ taglib uri="/WEB-INF/x.tld" prefix="x" %>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>Employee Report for Department 20</title>
  </head>
  <body>
  <c:set var="department" value="20" />
  <c:import url="HrXml.jsp?deptno=${department}" varReader="hrdoc" >
    <x:parse var="hrxmldoc" xml="${hrdoc}" scope="session" />
  </c:import>
  <x:set var="emps" select="$hrxmldoc//emp" />
  <x:forEach select="$emps" >
    <x:out select="ename" /><br>
  </x:forEach>
</body>
</html>

The interesting part: using

<c:import>

, I import the output of the HrXml.jsp – which is the JSP discussed earlier in this article – into the Reader object hrdoc. This Reader is parsed into the XML Document hrxmldoc by the

<x:parse>

tag.

The HrXml.jsp is in the same directory as the calling jsp. The HrXml.jsp expects a parameter department; this is passed in the url. I tried to pass the deptno parameter in a more structured fashion:

  <c:import url="HrXml.jsp" varReader="hrdoc" >
    <c:param name="deptno" value="${department}" />
    <x:parse var="hrxmldoc" xml="${hrdoc}" scope="session" />
  </c:import>

However, that syntax is not acceptable. Apparently, when you use <c:import> in combination with a varReader the tag-body may only contain a parser. See for example: JSTL Core Library — Tag in the On Line Help for JDeveloper

Using simple XPath expressions and the tags

<x:set> and <x:forEach> and &lt:x:out>

we extract a nodeset from the XML Document (all employees), traverse all nodes and write the value of the ename element to the output.

This results in a highly uneventful list of all employees (their names) working in Department 20. No transformation of the XML document has taken place, it has only been parsed and is traversed using the XPath expression $hrxmldoc//emp, which simply returns all EMP elements. On each EMP element we write the ENAME using the <x:out select="ename" /> expression.

JONES
SCOTT
ADAMS
FORD

Adding a piece of transformation – not too exciting, just to show you how all pieces are linked together. The XSL Styleheet looks as follows:

<?xml version='1.0' encoding='windows-1252'?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="hr">Employees
   <table border="1">
   <tr><th>Empno</th><th>Ename</th></tr>
   <xsl:apply-templates/>
   </table>
 </xsl:template>
 <xsl:template match="emp">
  <tr>
   <td><xsl:value-of select="empno"/></td>
   <td><xsl:value-of select="ename"/></td>
  </tr>
   </xsl:template>
</xsl:stylesheet>

To use this stylesheet to produce the output from my main JSP, I modify it to the following:

<%@ page contentType="text/html;charset=windows-1252"%>
<%@ page contentType="text/html;charset=windows-1252"%>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c" %>
<%@ taglib uri="/WEB-INF/fmt.tld" prefix="fmt" %>
<%@ taglib uri="/WEB-INF/x.tld" prefix="x" %>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
    <title>Employee Report for Department 20</title>
  </head>
  <body>
  <c:set var="department" value="20" />
  <c:import url="HrXml.jsp?deptno=${department}" varReader="hrdoc" >
    <x:parse var="hrxmldoc" xml="${hrdoc}" scope="session" />
  </c:import>
  <c:import var="xslt" url="EmpReport.xsl" />
    <x:transform xml="${hrxmldoc}" xslt="${xslt}" />
</body>
</html>

When I try to run the JSP with the transformation defined, I receive in my browser the following exception:javax.servlet.jsp.JspException: System property org.xml.sax.driver not specified. Looking on the internet, I found the following explanation/instruction (in 13 Oracle Application Server Containers for J2EE (OC4J):

13.2.2 Set the SAX Driver When Starting OC4J

For the filter functionality of Java Server Pages Standard Tag Library (JSTL) XML tags to work in OC4J, you must set the SAX driver when starting OC4J.

When starting OC4J standalone, use the following option in the command line:

-Dorg.xml.sax.driver=oracle.xml.parser.v2.SAXParser

When starting the Oracle Application Server, you can specify this setting through the system properties. See the Oracle Application Server documentation for details on where and how to make this specification.

If you do not set the property, then the JSTL demo Filter.jsp gives the following exception:

javax.servlet.jsp.JspException: System property org.xml.sax.driver not specified

Now I am trying to figure out how I can set this system property. You would expect this to be done in the Tools | Embedded OC4J Server Preferences. dialog. However, I cannot seem te find a way to set system properties. Ok. Solved it. Go to the Project Properties dialog of the project from which you want to run the JSP. Go to the Runner tab. Add -Dorg.xml.sax.driver=oracle.xml.parser.v2.SAXParser to the Java Options specified for the chosen Virtual Machine.

The final result is a simple HTML table with empnos and enames listed side by side.
Employees

Useful Resources

Not as many as I thought there would be. Many articles with the basic stuff, few with the next level of complexity and detail – and of course that is the level I required.

The JavaTM Web Services Tutorial from SUN on the SQL Tags
A JSTL primer, Part 4: Accessing SQL and XML content (IBM Developer Works)
Formatting XML with JSTL and XSLT – By Jeff Heaton.

Alternative definition of DataSource

Based on the comment from Leon, I have extended this post with an alternative way of defining the DataSource. I have added the following entries to the web.xml file:

  <context-param>
    <param-name>javax.servlet.jsp.jstl.sql.dataSource</param-name>
    <param-value>jdbc:oracle:thin:@localhost:1521:orcl,oracle.jdbc.driver.OracleDriver,scott,tiger</param-value>
  </context-param>

Now the JSP that generates the XML Document is simplified to the following:

<%@ page contentType="text/xml" %>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c" %>
<%@ taglib uri="/WEB-INF/sql.tld" prefix="sql" %>
    <sql:query var="emps" sql="select ename, empno  from emp where deptno = ?">
      <sql:param value="${param.deptno}"/>
    </sql:query>
<hr>
  <c:forEach var="row" items="${emps.rowsByIndex}">
<emp><empno><c:out value="${row [1]}"/></empno><ename><c:out value="${row [0]}"/></ename></emp>
</c:forEach>
</hr>

No DataSource is defined in the JSP, nor is one referenced in the sql:query tag. In that case the default data source, specified through a web-context parameter exactly named javax.servlet.jsp.jstl.sql.dataSource is used.