Generating SVG Graphics in JSPs using JSTL & XSL(T) – from MySQL to Bar Chart and Pie Chart

This post discusses how data retrieved from a MySQL or Oracle database using the JSTL SQL tags is processed as XML document, transformed using XSL and rendered as SVG objects – Bar Chart and Pie Chart. It is a sequel to the post XSL Transforming XML generated from SQL results using JSTL tags in JSP pages – putting JSTL to the test that I published last week. In that post I discussed JSPs that – using JSTL – retrieve data from a database, render it as XML and transform it into HTML. It involved a few JSPs with many JSTL tags, a web.xml configuration and a simple XSLT-stylesheet. In this post we take it one step further: instead of rendering the data as plain HTML, we will render it in graph-format. Leaning heavily on several resources I found on internet, it turned out to be rather simple to publish reasonably fancy Line Charts, Bar Charts and Pie Charts as well as several lesser known Chart Types. This post will largely deal with line charts; in subsequent installments I hope to work on more exotic graphtypes as well. Download the JDeveloper project with all sources.

Enabling SVG in your browser

Note: to fully enjoy all parts of this post, you need an SVG renderer in your web browser, for example the Adobe SVG Plugin for Internet Explorer. To enable SVG viewing in Firefox on Windows – see Mozilla Plugin Support on Microsoft Windows, you need to download and install the Acrobat SVG Viewer Version 6.0 (beta); after installing the Adobe SVG viewer, Copy NPSVG6.dll and NPSVG6.zip to your browser’s plugins folder. These files are normally located in C:Program FilesCommon FilesAdobeSVG Viewer 6.0Plugins

Approach to generating SVG based Graphs

First I create SVG documents for the type of graph I want to generate. This SVG document is completely hardcoded, it is just a way to draw one instance of the type of graph I am working on. I have used different resources found on the internet for constructing this SVG image. One very helpful resource is SVG Learning by Coding – large collection of annotated SVG example, including Bar Chart and Pie Chart.

In a second step I create an XSLT stylesheet that will produce the SVG document that I crafted in the first step. This XSLT initially is nothing more than one template matching any XML Document’s root element and transforming it into one large chunk of SVG:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:xlink="http://www.w3.org/2000/xlink/namespace/"
                version="1.0">
  <xsl:output method="image/svg+xml" omit-xml-declaration="yes"/>
  <xsl:template match="/">
    <svg width="1200" height="1200">
       ... rest of the SVG document
    </svg>
  </xsl:template>
</xsl:stylesheet>

Along with this rudimentary XSLT sheet, I also setup a JSP that – using JSTL XML tags – will import an XML document with the data to eventually display in the graph and transform it to the SVG Document. I decide to adopt the following procedure for producing SVG from data in a database:

This means that the ‘clean’ XML as extracted from the database (described in a previous post XSL Transforming XML generated from SQL results using JSTL tags in JSP pages – putting JSTL to the test) is imported into a JSP that in turn transforms it twice. First a specific XSLT stylesheet is used to transform the data-XML to a generic format required by the second stylesheet. This second stylesheet is a generic stylesheet that will transform a source document in proper format to a linegraph or piechart or barchart – for each type of graph we will need such a generic stylesheet. With this line up, we can easily create many graphs: we only need to create XML documents containing data, transforming them into a generic data document and can then apply this generic graph-type stylesheet.

The JSP that drives all of this and produces an SVG document – EmpSalSVGLine.jsp – is the following:

<%@ page contentType="image/svg+xml" %>
<%@ taglib uri="/WEB-INF/x.tld" prefix="x" %>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c" %>
<%@ taglib uri="/WEB-INF/sql.tld" prefix="sql" %>
<c:import url="EmpSalXML.jsp" varReader="empdoc" >
  <x:parse var="empxmldoc" xml="${empdoc}" scope="session" />
</c:import>
  <c:import var="xslt2" url="PrepareEmpXMLforLineChart.xsl" />
  <x:transform xml="${empxmldoc}" xslt="${xslt2}" var="empxmldoc2"/>
<c:import var="xslt3" url="buildSVGLineChart.xsl" />
<x:transform xml="${empxmldoc2}" xslt="${xslt3}" />

The stylesheet created from the static SVG document is now turned into a dynamic transformer by replacing step by step all static elements in the stylesheet by XSLT expressions. For example the hardcoded title is replaced with:

        <text x="5" y="-40" text-anchor="left" font-weight="bolder"
              font-size="40" fill="maroon" text-decoration="underline">
          <xsl:value-of select="title"/>
        </text>

Extract Data from Database into XML Document

EmpSalXML.jsp is the JSP that produces an XML document with the data extracted from the database.

<%@ page contentType="text/xml" %>
<%@ taglib uri="/WEB-INF/c.tld" prefix="c" %>
<%@ taglib uri="/WEB-INF/sql.tld" prefix="sql" %>
    <sql:query var="empdata" sql="select ename, -1*trunc(to_date('01-01-1984','DD-MM-YYYY') - hiredate) days, sal
    , 'Q'||to_char(hiredate, 'Q-YYYY') hirequarter, to_char(hiredate, 'DD MONTH YYYY') hireday from emp order by hiredate asc">
    </sql:query>
<emps>
  <c:forEach var="row" items="${empdata.rowsByIndex}">
<emp><ename><c:out value="${row [0]}"/></ename>
<sal><c:out value="${row [2]}"/></sal>
<days><c:out value="${row [1]}"/></days>
<hirequarter><c:out value="${row [3]}"/></hirequarter><hireday><c:out value="${row [4]}"/></hireday></emp>
</c:forEach>
</emps>

The resulting XML Document can be downloaded:here

Transform data into generic format fit for Graph-Generation

The first XSLT stylesheet to apply – PrepareEmpXMLforLineChart.xsl – transforms this data into the format required by the second stylesheet – buildSVGLineChart.xsl -, the generic stylesheet that produces Line Graphs. Note that PrepareEmpXMLforLineChart.xsl is tuned exactly to the structure of the XML document that contains the data from the database on the one hand, the format required by the second stylesheet and the specific requirements for the graph to produce (titles, colors, markers, annotations etc.).

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml"/>

<xsl:template match="/emps">
   <!-- find the extreme values along both axes: mininum and maximum salary as well as minimum and maximum 'number of days'-->
   <xsl:variable name="the_max_sal">
     <xsl:for-each select="emp/sal">
       <xsl:sort data-type="number" order="descending"/>
       <xsl:if test="position()=1"><xsl:value-of select="."/></xsl:if>
     </xsl:for-each>
   </xsl:variable>
   <xsl:variable name="the_min_sal">
     <xsl:for-each select="emp/sal">
       <xsl:sort data-type="number" order="ascending"/>
       <xsl:if test="position()=1"><xsl:value-of select="."/></xsl:if>
     </xsl:for-each>
   </xsl:variable>
   <xsl:variable name="the_max_days">
     <xsl:for-each select="emp/days">
       <xsl:sort data-type="number" order="descending"/>
       <xsl:if test="position()=1"><xsl:value-of select="."/></xsl:if>
     </xsl:for-each>
   </xsl:variable>
   <xsl:variable name="the_min_days">
     <xsl:for-each select="emp/days">
       <xsl:sort data-type="number" order="ascending"/>
       <xsl:if test="position()=1"><xsl:value-of select="."/></xsl:if>
     </xsl:for-each>
   </xsl:variable>
    <xsl:element name="graphData">
      <xsl:element name="sets"> <!-- the graphData may contain more than one set; each set results in a different line in the graph -->
        <xsl:element name="set">
          <xsl:attribute name="title">Initial Salary per hiredate</xsl:attribute>
          <xsl:attribute name="marker-type">star</xsl:attribute> <!-- several marker-types are supported, such as rectangle, square, circle, diamond and none -->
          <xsl:attribute name="color">red</xsl:attribute> <!-- color for the line and the markers for this set -->
          <xsl:for-each select="emp">
               <xsl:element name="measure">
                 <xsl:element name="xvalue"><xsl:value-of select="days"/></xsl:element>
                 <xsl:element name="yvalue"><xsl:value-of select="sal"/></xsl:element>
                 <!-- only add an xlabel if it is different from the previous xlabel -->
                 <xsl:if test="(preceding-sibling::emp[position()=1]/hirequarter != hirequarter) or (position()=1)">
                   <xsl:element name="xlabel"><xsl:value-of select="hirequarter"/></xsl:element> <!-- xlabel is the value displayed for this measure on the x-axis -->
                 </xsl:if>
                 <xsl:if test="ename = 'KING'" >
                   <xsl:element name="xgrid">true</xsl:element> <!-- show a vertical gridline -->
                 </xsl:if>
                 <xsl:if test="ename = 'SMITH'" >
                   <xsl:element name="xgrid">true</xsl:element>
                   <xsl:element name="ygrid">true</xsl:element> <!-- show a horizontal grid-line to the y-axis -->
                   <xsl:element name="ylabel"><xsl:value-of select="sal"/></xsl:element> <!-- label to display on the y-axis -->
                 </xsl:if>
               </xsl:element>
          </xsl:for-each>
      </xsl:element> <!-- set -->
    </xsl:element> <!-- sets -->
    <xsl:element name="xvalues"> <!-- set of values to display along the x-axis -->
      <xsl:element name="xvalue">
        <xsl:element name="value">-1461</xsl:element> <!-- value at which to place this x-axis marker -->
        <xsl:element name="label">1st January 1980</xsl:element> <!-- label to show -->
        <xsl:element name="gridline">true</xsl:element> <!-- show a vertical gridline -->
      </xsl:element>
      <xsl:element name="xvalue">
        <xsl:element name="value">-1</xsl:element>
        <xsl:element name="label">31st December 1983</xsl:element>
        <xsl:element name="gridline">true</xsl:element>
      </xsl:element>
    </xsl:element>
    <xsl:element name="yvalues">
      <xsl:element name="yvalue">
        <xsl:element name="value">1600</xsl:element>
        <xsl:element name="label">Union Lower Limit</xsl:element>
        <xsl:element name="gridline">true</xsl:element> <!-- show a horizontal gridline -->
      </xsl:element>
      <xsl:element name="ymarkers"> <!-- show markers along the y-axis starting at 0, moving in 5 steps up to 5000 -->
        <xsl:element name="minvalue">0</xsl:element>
        <xsl:element name="maxvalue">5000</xsl:element>
        <xsl:element name="steps">5</xsl:element>
        <xsl:element name="gridline">true</xsl:element>
      </xsl:element>
    </xsl:element> <!-- yvalues -->
    <xsl:element name="miny">0
      <!--  <xsl:value-of select="$the_min_sal"/> -->
    </xsl:element>
    <xsl:element name="maxy">
      <xsl:value-of select="$the_max_sal"/>
    </xsl:element>
    <xsl:element name="minx">-1461  <!-- 1st January 1980 -->
      <!-- <xsl:value-of select="$the_min_days"/> -->
    </xsl:element>
    <xsl:element name="maxx">-1 <!-- 31st December 1983 -->
    </xsl:element>
    <xsl:element name="title">Salaries of Employees vs. Hiredate</xsl:element>
    <xsl:element name="xtitle">Hiredates</xsl:element>
    <xsl:element name="ytitle">Salary in US$</xsl:element>
     </xsl:element> <!-- end of graphData -->
</xsl:template>

</xsl:stylesheet>

The output from this stylesheet on the source document can be seen here:EMP Data Transformed in Sets and Measures for Line Graph