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

9

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

&lt;x:set> and &lt;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:

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

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

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

Empno Ename
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD

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:

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

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

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

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

9 Comments

  1. Hi there,
    great article – very detailed written and at the same time quite easy to understand.
    Just one question that remains to me:
    I am also getting this Exception “javax.servlet.jsp.JspException: System property org.xml.sax.driver not specified”, but i have not yet been able to resolve it. You are describing the neccessary steps for OC4J, but not for Tomcat – and this is what I am running. I’ve added Xalan and Xerces JARs to the Tomcat libs, and have added all properties (“-D…”) I could find for using SAX/JAXP to the CATALINA_OPTS env variable.
    So far no go. If I set org.xml.sax.driver to the Xerces SAXParser, I get a NullPointer Exception, if I omit it I get the famous “javax.servlet.jsp.JspException: System property org.xml.sax.driver not specified” error message.
    Do you have any ideas what I am doing wrong?

    Thanks + best whishes,
    Melle

  2. I have a large XML file which has many nodes and I need to extract a piece of information from nodes .The file is about 20k lines and each article is 176 lines.Each article is identified by a unique number and by giving the unique number the jsp page should redierect to another page displaying all the info in nodes and by clicking the nodes i should be able to extract information how can I do this?

  3. Leon van Tegelen on

    Fun stuff. I see a very cheap , general query tool coming up. Very poor mans ISQLplus

    “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).” You could put the datasource in the web.xml as a context parameter. There you can put a reference to an URL or to JNDI Datasource
    see here

  4. Pingback: » SVG, XSLT and JSP/JSTL: Generating Digital Gauge or Speedometer Chart based on dynamic data

  5. Pingback: » SVG, XSLT and JSP/JSTL: Generating Digital Gauge or Speedometer Chart based on dynamic data

  6. Pingback: » AMIS Technology Weblog - Statistics, Graphics and Becoming an Author (’blogger’)

  7. Pingback: » Generating SVG Graphics in JSPs using JSTL & XSL(T) - from MySQL to Bar Chart and Pie Chart