Chapter 12. Advanced XML in Reporting Systems
 | CONTENTS |  |
IN THIS CHAPTER
- Multiple-Page Reports
- Reports on Data with One-to-Many Relationships
- Real-World Reporting Systems
- Well-Formed Documents Revisited
- Summary
In the previous chapter, all the necessary components to output HTML from database records using XML were created. Some components will be reused in this chapter. However, other components, such as RStoXML.java from Listing 11.4, will not. Others, such as DBtoXML.jsp from Listing 11.5, will be modified to better suit the specific report being created. This chapter's examples depend on the examples from Chapter 11, "Using XML in Reporting Systems." As a result, reused portions of these components will not be explained here. However, explanations of changes and additions will be included, as well as references to the original examples of the component.
This chapter builds on the examples from the previous chapter. The first example report will build on the table sort report from the previous chapter to add the functionality of paging. This causes the report of a very large set of data to be broken down into viewable pages, which enables the timely display and processing of large data sets and makes viewing the data more manageable.
Next, a report will be created that shows a one-to-many relationship. In this example, each ailment that a specific client has will be displayed beneath the information about the client. Any number of ailments can exist per client, and this report will handle all these cases. This example illustrates how to create reports that show a one-to-many relationship in the database.
Multiple-Page Reports
The final goal of most reporting systems is the presentation of data in a useful way. One aspect to this goal is how to handle reports containing so much data that they are no longer useful. This could be because of the slow speed of report creation or the large output. Both of these factors reduce the usefulness of a report and might frustrate the end user.
One way to handle this problem is through the use of a report that pages through the data. Such a report displays a subset of the entire report at any time. Each page can be sorted by any of the columns on the report. This results in the report being generated more quickly and presenting a more manageable set of data to view.
The output of the paging report is an HTML table with headers and labels. Some of these labels include linked numbers that go to each of the possible report pages and navigation links that will flip the report forward or back a page. Also, linked column labels will redisplay the report sorted on the appropriate column.
The JSP for the Multiple-Page Report
The report is built on the JSP named DBtoXML.jsp found in Listing 11.5. The modifications to the JSP include the following:
-
The addition of a query string parameter containing the page number requested of the report
-
The modification of the SQL statement executed against the database to return only a select subset of the report data
-
The addition of a second SQL select statement to query the total number of records that exist in the report, which enables the dynamic creation of the correct number of report pages
The JSP begins by parsing the requesting query string to obtain the information necessary for the creation of the proper report page. Next, a statement is executed against the database to obtain the data from the ReportClientData table found in the xmlbook database for the appropriate page of the output report. At this point, another database statement is executed to obtain the total number of records for this report. This number is necessary to calculate the total number of possible report pages.
The database ResultSet is then converted into a JDOM representation using the class RStoXML.java found in Listing 11.4. Finally, the JDOM structure is transformed using the TblPageSort.xsl stylesheet, and the report page is output.
The JSP code begins with some HTML output. Following this, the query string is parsed within a try block for the value of the pagenum parameter, as shown in the following code. If the parameter is not found, the catch block will set the Java variable rpt_page to the default value of 1. This indicates that the default first page of the report will be displayed.
int rpt_page; try{ rpt_page = Integer.parseInt(request.getParameter("pagenum")); } catch(NumberFormatException e){ rpt_page = 1; } After some other processing, the SQL statement to select the client records will be created as shown in the following code. This statement must only select those records that should be displayed on the requested report page. This is done with the use of the LIMIT keyword.
String db_query = "select RCD_lname, RCD_fname, "; db_query +="DATE_FORMAT(RCD_dob, '%Y-%m-%d') as RCD_dob , "; db_query +="RCD_clinic, RCD_color from reportclientdata "; db_query +="order by RCD_lname LIMIT "; db_query += (rpt_page - 1) * recPerPage + ", " + recPerPage;
The LIMIT keyword selects a specific subset of the records returned by the statement. The two numbers separated by a comma following the LIMIT keyword determine this subset. The first of these numbers indicates the record number from which to start the selection in this case, that depends on the report page being requested whereas the second number indicates the number of records to select, starting from that point.
When the preceding statement is executed, the selected records are first sorted according to the RCD_lname field. Once completed, the appropriate subset is selected using the LIMIT keyword and some calculations.
NOTE
It is important to note that the LIMIT keyword is database implementation specific. You can obtain the same information from the database using a stored procedure.
The recPerPage variable contains the number of records to be displayed per page. This variable and the calculations depending on it allow the number of records output per page to be easily altered without any other stylesheet or JSP required alterations.
Now that the appropriate SQL statement has been created for the report page being displayed, the stylesheet variable is set to the stylesheet for this report.
After the creation of the database connection, the SQL statement is executed, and the ResultSet is placed in the rs object as shown in the following code. Notice that another ResultSet is created and placed into the rsCnt object. This second ResultSet contains the total number of records in all pages of this report. This information is obtained and placed in the recordCnt variable:
//get a resultset or two rs = db_statement.executeQuery(db_query); rsCnt = db_statementCnt.executeQuery ("select count(*) from reportclientdata"); rsCnt.first(); int recordCnt = rsCnt.getInt(1); After the total number of records has been obtained, this information will be used to calculate the total number of pages that this report will have. The result of this calculation is placed into the rpt_pages variable as shown in the following:
rpt_pages = (int) java.lang.Math.ceil((float) recordCnt / recPerPage);
After the various error handling and database related statements, the ResultSet is transformed into a JDOM representation using the RStoXML class as shown in the following code. This class was created and demonstrated in Listings 11.4 and 11.5, respectively.
// create a resultsetbuilder to transform resultset to XML RStoXML rsxml = new RStoXML(rs, "ROOT", "RECORD"); //create the XML from recordset org.jdom.Document jdomdoc = rsxml.build();
Next, the stylesheet is obtained and loaded into a Transformation object. Once completed, four stylesheet parameters are set. The two new parameters are the currently displayed page number found in PageNum and the total number of possible report pages found in Total as follows:
transformer.setParameter("PageNum", new Integer(rpt_page)); transformer.setParameter("Total", new Integer(rpt_pages)); The stylesheet uses these parameters to create the appropriate links for the resulting report page.
The rest of the JSP transforms the JDOM representation with the stylesheet, performs various error handling, and outputs the results like the original JSP.
This JSP depends on the existence of the RStoXML.java class found in Listing 11.4 and saved in the webapps\xmlbook\WEB-INF\classes\xmlbook\chapter11 directory.
The complete JSP can be seen in Listing 12.1 and should be saved as webapps\xmlbook\chapter12\PagingRpt.jsp. The additions and changes from DBtoXML.jsp found in Listing 11.5 appear in boldface type.
Listing 12.1 PagingRpt.jsp
<%@page import = "java.sql.*, org.jdom.*, org.jdom.output.*, javax.xml.transform.*, javax.xml.transform.stream.*, org.jdom.transform.*, xmlbook.chapter11.*" %> <html> <head><title>Paging Report</title></head> <body> <div> <% // 1. get parameters from url int sort_by; int rpt_page; try{ rpt_page = Integer.parseInt(request.getParameter("pagenum")); } catch(NumberFormatException e){ rpt_page = 1; } try{ sort_by = Integer.parseInt(request.getParameter("sort")); } catch(NumberFormatException e){ //if invalid or nonexistent parameter, sort by RCD_lname sort_by = 1; } // 2. Initialize other variables int recPerPage = 20; String final_status = ""; String db_query = "select RCD_lname, RCD_fname, "; db_query +="DATE_FORMAT(RCD_dob, '%Y-%m-%d') as RCD_dob , "; db_query +="RCD_clinic, RCD_color from reportclientdata "; db_query +="order by RCD_lname LIMIT "; db_query += (rpt_page - 1) * recPerPage + ", " + recPerPage; String db_location = "jdbc:mysql://localhost/xmlbook"; String db_driver = "org.gjt.mm.mysql.Driver"; String stylesheet = "TblPageSort.xsl"; int rpt_pages = 0; // 3. Make a database connection Connection db_connection = null; Statement db_statement = null; Statement db_statementCnt = null; ResultSet rs = null; ResultSet rsCnt = null; try{ Class.forName(db_driver); db_connection = DriverManager.getConnection(db_location); db_statement = db_connection.createStatement(); db_statementCnt = db_connection.createStatement(); //get a resultset or two rs = db_statement.executeQuery(db_query); rsCnt = db_statementCnt.executeQuery ("select count(*) from reportclientdata"); rsCnt.first(); int recordCnt = rsCnt.getInt(1); rpt_pages = (int) java.lang.Math.ceil((float) recordCnt / recPerPage) ; } catch (ClassNotFoundException e){ final_status = "Error: Unable to create database drive class."; final_status += " <br />" + e.toString(); } catch(SQLException e){ final_status = "Error: Unable to make database connection"; final_status += "or execute statement."; final_status += " <br />" + e.toString(); } finally { /* We must close the database connection now */ try { if (db_connection != null) { db_connection.close(); } } catch (SQLException e) { final_status = "Error: Unable to close database connection."; final_status += " <br />" + e.toString(); } } try{ // create a resultsetbuilder to transform resultset to XML RStoXML rsxml = new RStoXML(rs, "ROOT", "RECORD"); //create the XML from recordset org.jdom.Document jdomdoc = rsxml.build(); rs = null; //get the stylesheet String path = request.getServletPath(); path = path.substring(0,path.indexOf("PagingRpt.jsp")) ; String xsl = application.getRealPath(path + stylesheet); //create transformer Transformer transformer = TransformerFactory.newInstance() .newTransformer(new StreamSource(xsl)); transformer.setParameter("Sort", new Integer(sort_by)); transformer.setParameter("Page", "PagingRpt.jsp"); transformer.setParameter("PageNum", new Integer(rpt_page)); transformer.setParameter("Total", new Integer(rpt_pages)); JDOMResult jdomresults = new JDOMResult(); transformer.transform(new JDOMSource(jdomdoc), jdomresults); jdomdoc = null; //create outputter to output results XMLOutputter output = new XMLOutputter(" ", true); //output the results Document docResults = jdomresults.getDocument(); output.output(docResults, out); } catch (TransformerFactoryConfigurationError e) { final_status = "Error: Unable to create factory to transform "; final_status = "XSL and XML."; final_status += "<br />" + e.toString(); } catch (TransformerException e) { final_status = "Error: Unable to transform XSL and XML."; final_status += "<br />" + e.toString(); } catch (JDOMException e) { final_status = "Error: Unable to create XML from database query."; final_status += "<br />" + e.toString(); } if(final_status != "") out.print("<br><font color=\"red\"><H2>" + final_status + "</H2></font>"); %> </div> </body> </html>
Now that the JSP has been completed, it's time to look over the stylesheet necessary for the paging report. Built on the TableSort.xsl stylesheet found in Listing 11.6, this stylesheet contains four templates.
The Stylesheet for the Multiple-Page Report
The first template matches the root of the JDOM representation. As a result of this matching, the processing of the XML document begins through the various calls to other templates. This template hasn't changed from its use in Listing 11.6.
The second template, named header, creates the first five rows of the output HTML table. These rows contain the various links that allow the navigation through the report pages and the sorting on each column.
Next, the recursive PageLinks template outputs one linked number for each page of the complete report. When followed, these links will call the JSP and, as a result, the appropriate report page will be displayed.
The last template outputs a table row for each record of the JDOM document. This template is the same as it was in TableSort.xsl found in Listing 11.6.
The stylesheet code begins with the addition of two new parameters as shown in the following code. The stylesheet obtains the number of pages that make up the entire report and the page number of this page through these parameters. These numbers are necessary for the output of the correct links and labels.
<xsl:param name="PageNum"/> <xsl:param name="Total" />
Next comes the template that matches the root; it hasn't changed from Listing 11.6. This template calls the header template and applies the other templates within an HTML table element.
The header template is next and starts with the beginning of a table row as shown here:
<!-- creates all of the table header, 5 rows --> <xsl:template name="header"> <!-- prev and next page links created here --> <tr bgColor="#fffeee">
Within the table row is an xsl:choose tag that allows the creation of a Java-like select switching structure. That is, it allows one of a number of choices to be selected based on some test condition. The xsl:choose element can contain any number of xsl:when elements, only one of which will be selected. The following code only shows one xsl:when child element, but there are more to come:
<xsl:choose> <!-- if first page, don't show 'prev page' link --> <xsl:when test="$PageNum = 1"> <th colspan="2">First Page</th> <th> Page <xsl:value-of select="$PageNum" /></th> <th colspan="2"><a href="{$Page} ?pagenum={$PageNum + number(1)}"> Next Page</a></th> </xsl:when> The xsl:when structure selected is the one whose test expression is the first to resolve to true. If there are no xsl:when test attributes that resolve to true, the xsl:otherwise element is selected, if it is present.
The preceding xsl:choose element is used to pick one of three possible outputs for the Previous Page and Next Page links. The expression found in the test attribute of the first xsl:when element tests whether the PageNum parameter is equal to 1. If it is, text will read First Page instead of a Previous Page link being added.
The following xsl:when element tests to see whether PageNum is equal to Total. If they are equal, this is the last page of the report pages. As a result, the Next Page link will instead read Last Page.
<!-- if last page, don't show 'next page' link --> <xsl:when test="$PageNum = $Total"> <th colspan="2"><a href="{$Page} ?pagenum={$PageNum - number(1)}"> Previous Page</a></th> <th> Page <xsl:value-of select="$PageNum" /></th> <th colspan="2">Last Page</th> </xsl:when> Finally, the xsl:otherwise element will match all other cases, as seen in the following code. When selected, this element will output both Previous Page and Next Page links. If the PageNum doesn't equal 1 or the Total number of pages, this xsl:otherwise element will be selected.
<!-- in all cases, show both links --> <xsl:otherwise> <th colspan="2"><a href="{$Page} ?pagenum={$PageNum - number(1)}"> Previous Page</a></th> <th> Page <xsl:value-of select="$PageNum" /></th> <th colspan="2"><a href="{$Page} ?pagenum={$PageNum + number(1)}"> Next Page</a></th> </xsl:otherwise> Last, the xsl:choose element is closed, and the first row of the resulting report table has been created.
Next, the stylesheet outputs the start of the next table row. Contained in this row are linked numbers for each possible page of the report. This will permit the user to click a number and be taken to the appropriate page.
The following XSL begins the recursion of the PageLinks template that will result in the creation of those linked page numbers. Notice that contained in the body of this xsl:call-template element is an xsl:with-param element. This causes the template PageLinks to be called with the Cnt parameter set to 1.
<!-- create linked page number for each possible report page --> <tr bgColor="#fffeee"> <td align="left" colspan="5"> <xsl:call-template name="PageLinks"> <xsl:with-param name="Cnt" select="1"/> </xsl:call-template> </td> </tr>
Some HTML table tags are output, and the final table header row is created in the following code. This row becomes the linked column headers that will cause the newly sorted report page to display. When these links are followed, they place parameters on the query string that the JSP will parse for and process.
<!-- create each linked column label --> <tr bgColor="#fffeee"> <xsl:for-each select="*/*[1]/*"> <td> <A href="{$Page} ?pagenum={$PageNum} &sort={position()}">    <xsl:value-of select="local-name(.)" />    </A> </td> </xsl:for-each> </tr> With the addition of the closing xsl:template tag, the header template is complete. Next on the stylesheet is the PageLinks template. This template was called from the header template and results in the creation of the linked page numbers.
As in the following code, this template begins by declaring the Cnt parameter using an xsl:param tag. Next, an xsl:if element tests to see whether the current value of the Cnt parameter is the same as the PageNum parameter. If they are equal, the value of Cnt is output without a link because a link to the same page of the report is useless.
<!-- recursive linked page number creation --> <xsl:template name="PageLinks"> <xsl:param name="Cnt"/> <xsl:if test="$Cnt = $PageNum" > <xsl:value-of select="$Cnt" />   </xsl:if>
The next xsl:if element of this template, shown as follows, tests to make sure that Cnt and PageNum are not equal to each other. When this condition is true, the body of the xsl:if element is selected, and the value of the Cnt parameter is output linked to the appropriate report page.
<xsl:if test="$Cnt != $PageNum" > <a href="{$Page} ?pagenum={$Cnt}"> <xsl:value-of select="$Cnt" /> </a>   </xsl:if> The last part of the PageLinks template is the final xsl:if element, as shown in the following. When matched, this element causes the recursion by recalling this template when the value of Cnt is less than Total.
<xsl:if test="$Cnt < $Total"> <xsl:call-template name="PageLinks"> <xsl:with-param name="Cnt" select="$Cnt + 1"/> </xsl:call-template> </xsl:if>
The last template is the same as that of TableSort.xsl found in Listing 11.6. This template creates a table row for each child of the root. Within that row, each table cell contains the text data of that selected element's children.
Besides the closing template and stylesheet tags, this completes the stylesheet. TblPageSort.xsl is shown in its entirety in Listing 12.2 and should be saved as webapps\xmlbook\chapter12\TblPageSort.xsl. All additions and changes to TableSort.xsl, found in Listing 11.6, appear in boldface type except for changes to HTML markup.
Listing 12.2 TblPageSort.xsl
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:param name="Sort"/> <xsl:param name="Page"/> <xsl:param name="PageNum"/> <xsl:param name="Total" /> <xsl:output method="html" /> <xsl:template match="/"> <table border="1"> <xsl:call-template name="header"/> <!-- select and format each record --> <xsl:apply-templates select="*/*"> <xsl:sort select="*[$Sort]" /> </xsl:apply-templates> </table> </xsl:template> <!-- creates all of the table header, 5 rows --> <xsl:template name="header"> <!-- prev and next page links created here --> <tr bgColor="#fffeee"> <xsl:choose> <!-- if first page, don't show 'prev page' link --> <xsl:when test="$PageNum = 1"> <th colspan="2">First Page</th> <th> Page <xsl:value-of select="$PageNum" /></th> <th colspan="2"><a href="{$Page} ?pagenum={$PageNum + number(1)}"> Next Page</a></th> </xsl:when> <!-- if last page, don't show 'next page' link --> <xsl:when test="$PageNum = $Total"> <th colspan="2"><a href="{$Page} ?pagenum={$PageNum - number(1)}"> Previous Page</a></th> <th> Page <xsl:value-of select="$PageNum" /></th> <th colspan="2">Last Page</th> </xsl:when> <!-- in all cases, show both links --> <xsl:otherwise> <th colspan="2"><a href="{$Page} ?pagenum={$PageNum - number(1)}"> Previous Page</a></th> <th> Page <xsl:value-of select="$PageNum" /></th> <th colspan="2"><a href="{$Page} ?pagenum={$PageNum + number(1)}"> Next Page</a></th> </xsl:otherwise> </xsl:choose> </tr> <tr bgColor="#eeefff"><td colspan="5"> </td></tr> <!-- create linked page number for each possible report page --> <tr bgColor="#fffeee"> <td align="left" colspan="5"> <xsl:call-template name="PageLinks"> <xsl:with-param name="Cnt" select="1"/> </xsl:call-template> </td> </tr> <tr bgColor="#eeefff"><td colspan="5"> </td></tr> <!-- create each linked column label --> <tr bgColor="#fffeee"> <xsl:for-each select="*/*[1]/*"> <td> <A href="{$Page} ?pagenum={$PageNum} &sort={position()}">    <xsl:value-of select="local-name(.)" />    </A> </td> </xsl:for-each> </tr> </xsl:template> <!-- recursive linked page number creation --> <xsl:template name="PageLinks"> <xsl:param name="Cnt"/> <xsl:if test="$Cnt = $PageNum" > <xsl:value-of select="$Cnt" />   </xsl:if> <xsl:if test="$Cnt != $PageNum" > <a href="{$Page} ?pagenum={$Cnt}"> <xsl:value-of select="$Cnt" /> </a>   </xsl:if> <xsl:if test="$Cnt < $Total"> <xsl:call-template name="PageLinks"> <xsl:with-param name="Cnt" select="$Cnt + 1"/> </xsl:call-template> </xsl:if> </xsl:template> <!-- creates a table row for each record, and cell for each column --> <xsl:template match="*/*"> <tr bgColor="#eeefff"> <xsl:for-each select="*"> <td><xsl:value-of select="." /></td> </xsl:for-each> </tr> </xsl:template> </xsl:stylesheet>
The first page of the report is shown in Figure 12.1, and the fourth page is shown in Figure 12.2. The actual results will vary depending on how much data is contained in your database table. To add more data, use the JSP named CreateDBData.jsp, which was created in Listing 11.1.
Figure 12.1. The first page of PagingRpt.jsp and TblPageSort.xsl.
Figure 12.2. The fourth page of PagingRpt.jsp and TblPageSort.xsl.
Notice that the table headers include the appropriate links to flip to the next and previous pages. Also, links to each page except the current page of the report have been created. They are dynamically created, and thus will function no matter how large the dataset becomes or how many records are displayed per page. Finally, a table row with linked column labels has been created to allow every report page to be sorted by any column.
The database records displayed on each page will always be the same, regardless of the column on which the data is sorted. The only change that occurs is the order in which the records of that page are displayed.
At this point, I need to mention that when writing reports for a system, it is very important to keep the end user in mind. If the end user does not have input on the reports that are created, they can easily become creations of the programmer instead of being beneficial to people who actually use them.
A possible example of this relates to sorting. In the preceding paging report, the data is separated into pages according to the last name of the clients. Although this might appear useful to the programmer, it is possible that this report will be viewed most frequently sorted by date of birth. This might appear to be a minor distinction, but the difference can make a report useless. It is impossible for the creator of this report to know exactly how a report would be best designed unless he has access to the people using it or has amazing specifications to work from.
On that note, it would be a relatively simple addition to this stylesheet and JSP to allow the user to select which column the report data is sorted to before being broken into pages. This could be done with a radio button or some other HTML form element, which would allow the user to access the exact report format he needs.
Now that one method to handle large reports has been covered, it's time to demonstrate another common occurrence in reporting namely ways to report on the common one-to-many relationships contained in databases.
Reports on Data with One-to-Many Relationships
An example of the one-to-many relationship can be seen between the ReportClientData and ReportClientAilment tables. In the ReportClientData table, all the data about each client is contained in one record. A unique integer is associated with each record to make it distinct.
Each ailment is contained in its own record found in the ReportClientAilment table. That record contains the unique client index of the related client who has the ailment described in the record. This causes the possibility of multiple ailments to be associated with one client, and thus we have a one-to-many relationship.
With the following one-to-many report, we will create a stylesheet that displays this relationship. The default page of this report can be seen in Figure 12.3. When this report is initially loaded, it looks a lot like the report shown in Figure 11.1, except for some HTML markup differences.
Figure 12.3. The default page of OneMany.jsp and OneMany.xsl.
However, once the View Ailments link is selected, the report is reloaded with the ailments for each client listed as shown in Figure 12.4. The ailment records can be hidden again by using the Hide Ailments link.
Figure 12.4. View Ailments displayed in OneMany.jsp.
Similar to the paging report we looked at earlier in this chapter, this report will sort the data by the column selected when the column label links are followed. When the report is redisplayed, the ailment records will remain in their current state (viewable or hidden).
The JSP for the One-to-Many Report
To create this one-to-many report, the JSP begins by parsing parameters from the query string and then obtaining data from the database. The data selected will depend on the query string parameters. If the ailments will be visible on the resulting report, an additional SQL statement is executed to retrieve these records.
When the ailment records are visible, both sets of records will be used to each create a JDOM representation using RStoXML.java, which was created in Listing 11.4. Next, a new JDOM document is created and the content of the other two JDOMs is added. On the other hand, when there are no ailment records, only the client records are added to the third JDOM document. After the creation of the resulting JDOM, it is transformed with a stylesheet.
The JSP code begins with the parsing of the URL to find the value of the show parameter, as shown in the following. When this parameter is unavailable, the JSP variable is set to 0; otherwise, it is set to 1. The ailment records are only fetched from the database and added to the resulting JDOM document when the value of show is 1. In all other cases, the ailment records are not retrieved.
String show = request.getParameter("show"); show = (show) != null ? "1" : "0"; The rest of this JSP does the various database and XML processing as before. The only difference is that depending on the value of show, a second database query, ResultSet, and a JDOM document will be processed.
A third JDOM Document will be created regardless of whether the second ResultSet exists. As shown in the following, this is done by creating an Element and using it to create a Document. This Element then becomes the root Element of the Document.
Element rootElement = new Element("ROOT"); Document resultdoc = new Document(rootElement); Next, the JDOM document representing the first ResultSet is added to this newly created JDOM Document as shown in the following. This is done by getting the root Element of the JDOM and invoking detach() to remove it from its parent Document. After this has been done, it can be added to the resulting JDOM Document using the addContent() method, as can the second JDOM.
rootElement.addContent(jdomdoc.getRootElement().detach());
The rest of the JSP is the same as PagingRpt.jsp found in Listing 12.1. The final JDOM document is transformed using the OneMany.xsl stylesheet and then output. Then there is some error handling and the page is finished.
The complete JSP is shown in Listing 12.3 and should be saved as webapps\xmlbook\chapter12\OneMany.jsp. The differences between OneMany.jsp and DBtoXML.jsp from Listing 11.5 appear in boldface type.
Listing 12.3 OneMany.jsp
<%@page import = "java.sql.*, org.jdom.*, org.jdom.output.*, org.jdom.input.*, java.io.*, javax.xml.transform.*, javax.xml.transform.stream.*, org.jdom.transform.*, xmlbook.chapter11.*" %> <html> <head><title>One to Many</title></head> <body> <div> <% // 1. get parameters from url String show = request.getParameter("show"); show = (show) != null ? "1" : "0"; int sort_by; try{ sort_by = Integer.parseInt(request.getParameter("sort")); } catch(NumberFormatException e){ //if invalid or nonexistent parameter, sort by RCD_lname sort_by = 2; } // 2. Initialize other variables String final_status = ""; String db_query = "select * from reportclientdata "; String db_query2 = "select * from reportclientailments"; String db_location = "jdbc:mysql://localhost/xmlbook"; String db_driver = "org.gjt.mm.mysql.Driver"; String stylesheet = "OneMany.xsl"; // 3. Make a database connection Connection db_connection = null; Statement db_statement = null; ResultSet rs = null; Statement db_statement2 = null; ResultSet rs2 = null; try{ Class.forName(db_driver); db_connection = DriverManager.getConnection(db_location); db_statement = db_connection.createStatement(); rs = db_statement.executeQuery(db_query); if (show != "0"){ db_statement2 = db_connection.createStatement(); rs2 = db_statement2.executeQuery(db_query2); } } catch (ClassNotFoundException e){ final_status = "Error: Unable to create database drive class."; final_status += " <br />" + e.toString(); } catch(SQLException e){ final_status = "Error: Unable to make database connection"; final_status += "or execute statement."; final_status += " <br />" + e.toString(); } finally { /* We must close the database connection now */ try { if (db_connection != null) { db_connection.close(); } } catch (SQLException e) { final_status = "Error: Unable to close database connection." ; final_status += " <br />" + e.toString(); } } try{ // create a resultsetbuilder to transform resultset to XML RStoXML rsxml = new RStoXML(rs, "RS1", "REC1"); Document jdomdoc = rsxml.build(); Document jdomdoc2 = null; if (show != "0"){ RStoXML rsxml2 = new RStoXML(rs2, "RS2", "REC2"); jdomdoc2 = rsxml2.build(); rs2 = null; rsxml2 = null; } rs = null; rsxml = null; //get the stylesheet String path = request.getServletPath(); path = path.substring(0,path.indexOf("OneMany.jsp")) ; String xsl = application.getRealPath(path + stylesheet); Element rootElement = new Element("ROOT"); Document resultdoc = new Document(rootElement); rootElement.addContent(jdomdoc.getRootElement().detach()); if (show != "0") rootElement.addContent(jdomdoc2.getRootElement().detach()); jdomdoc2 = null; jdomdoc = null; //create transformer Transformer transformer = TransformerFactory.newInstance() .newTransformer(new StreamSource(xsl)); transformer.setParameter("Sort", new Integer(sort_by)); transformer.setParameter("Page", "OneMany.jsp"); transformer.setParameter("Show", show); JDOMResult jdomresults = new JDOMResult(); transformer.transform(new JDOMSource(resultdoc), jdomresults); resultdoc = null; //create outputter to output results XMLOutputter output = new XMLOutputter(" ", true); //output the results Document docResults = jdomresults.getDocument(); output.output(docResults, out); } catch (TransformerFactoryConfigurationError e) { final_status = "Error: Unable to create factory to transform "; final_status = "XSL and XML."; final_status += "<br />" + e.toString(); } catch (TransformerException e) { final_status = "Error: Unable to transform XSL and XML."; final_status += "<br />" + e.toString(); } catch (JDOMException e) { final_status = "Error: Unable to create XML from database query." ; final_status += "<br />" + e.toString(); } if(final_status != "") out.print("<br><font color=\"red\"><H2>" + final_status + "</H2></font>"); %> </div> </body> </html>
Now that the JSP has been completed, it's time to look at the stylesheet. This stylesheet produces the table either with or without ailment records displayed and is named OneMany.xsl.
The Stylesheet for the One-to-Many Report
The stylesheet contains three templates. The first template matches on the root and does almost all the processing of the page. The other two templates are both for the creation of the table column labels. One is used to create the header when the ailment records are present, whereas the other is used in their absence.
The stylesheet begins with the template that matches the document root. This template starts out by using the following two xsl:if elements to choose which headers should be displayed. It does this by counting the number of children of the root. If more than one child exists, the ailment records are present, and the headershow template is selected. Otherwise, the headerhide template is selected.
<!-- create the proper link and headers according to content --> <xsl:if test="count(/*/*) > 1"> <tr><td colspan="100%"><a href="{$Page}">Hide Ailments</a></td></tr> <xsl:call-template name="headershow"/> </xsl:if> <xsl:if test="count(/*/*) = 1"> <a href="{$Page} ?show=1">View Ailments</a> <xsl:call-template name="headerhide"/> </xsl:if> Next, an xsl:for-each element is encountered as shown in the following. This structure iterates through each element that is both a grandchild of the root and descends from the first child of the root. In this case, each record of the client ResultSet is in turn matched within this element. Notice that the first element within the iterating structure is an xsl:sort element. This causes the sorting on the numbered column before the iteration begins.
<!-- iterate through each client record found in the first child of root --> <xsl:for-each select="*/*[1]/*" > <xsl:sort select="*[$Sort]" />
As each client record is iterated, a variable is created to hold the text data of the child RCD_index element. This is done so that this value can be used to select the corresponding ailment records.
<!-- hold index of current client record --> <xsl:variable name="index" select="RCD_index" />
Next, some HTML markup is output. Then, the text data of each child of the currently selected client record is iterated through and output as shown here:
<!-- select all client data except index to show --> <xsl:for-each select = "*[position() > 1]" > <td><xsl:value-of select = "." /></td> </xsl:for-each>
All the client data has been neatly wrapped in a table row. At this point, if there are any ailment records related to this client, they need to be output. This is done by using an xsl:for-each element to iterate through the set of Elements that match the select expression of /*/*[2]/*[RCA_clientID = $index] as shown here:
<!-- select all ailment records whose client ID --> <!-- is the same as the current client --> <xsl:for-each select="/*/*[2]/*[RCA_clientID = $index]">
Notice the leading forward slash in the preceding select statement. This forward slash causes the selection to begin at the root of the JDOM representation. This statement selects all grandchild elements that descend from the second child of the root.
Each of these grandchildren must have text data in their RCA_clientID elements that is equal to the value of the $index variable.
This causes the selection of each ailment record that corresponds to the client record currently being processed. If no ailment records are present, nothing gets selected for output.
After the records have been selected, they are output within a table row as shown in the following. The text data of RCA_medicalailment and RCA_datefound are output in table cells with the use of xsl:value-of tags.
<xsl:sort select="RCA_medicalailment" /> <tr bgcolor="#eeefff"><td /><td /><td /><td /><td /> <td ><xsl:value-of select = "RCA_medicalailment" /></td> <td ><xsl:value-of select = "RCA_datefound" /></td> </tr>
That concludes the first template. The next two templates output the table head depending on the presence of the second ResultSet. Each is similar to the header template found in TableSort.xsl from Listing 11.6. The techniques are used throughout each stylesheet in this and previous chapters.
Listing 12.4 shows the complete stylesheet. This file should be saved as webapps\xmlbook\chapter12\OneMany.xsl.
Listing 12.4 OneMany.xsl
<?xml version="1.0" encoding="UTF-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:param name="Sort" /> <xsl:param name="Page" /> <xsl:output method="html" /> <xsl:template match="/"> <table border="0" cellpadding="3"> <!-- create the proper link and headers according to content--> <xsl:if test="count(/*/*) > 1"> <tr><td colspan="100%"><a href="{$Page}">Hide Ailments</a></td></tr> <xsl:call-template name="headershow"/> </xsl:if> <xsl:if test="count(/*/*) = 1"> <a href="{$Page} ?show=1">View Ailments</a> <xsl:call-template name="headerhide"/> </xsl:if> <!-- iterate through each client record found in the first child of root --> <xsl:for-each select="*/*[1]/*" > <xsl:sort select="*[$Sort]" /> <!-- hold index of current client record --> <xsl:variable name="index" select="RCD_index" /> <tr bgcolor="#fffeee"> <!-- select all client data except index to show --> <xsl:for-each select = "*[position() > 1]" > <td><xsl:value-of select = "." /></td> </xsl:for-each> <td /><td /> </tr> <!-- select all ailment records whose client ID --> <!-- is the same as the current client --> <xsl:for-each select="/*/*[2]/*[RCA_clientID = $index]"> <xsl:sort select="RCA_medicalailment" /> <tr bgcolor="#eeefff"><td /><td /><td /><td /><td /> <td ><xsl:value-of select = "RCA_medicalailment" /></td> <td ><xsl:value-of select = "RCA_datefound" /></td> </tr> </xsl:for-each> </xsl:for-each> </table> </xsl:template> <!-- creates the table headers from the tag names --> <xsl:template name="headershow"> <tr> <xsl:for-each select="*/*[1]/*[1]/*[position() > 1]"> <th> <A href="{$Page} ?sort={position() + 1} &show=1"> <xsl:value-of select="local-name(.)" /> </A> </th> </xsl:for-each> <th>Medical Ailment</th> <th>Date Found</th> </tr> </xsl:template> <xsl:template name="headerhide"> <tr> <xsl:for-each select="*/*[1]/*[1]/*[position() > 1]"> <th> <A href="{$Page} ?sort={position() + 1}"> <xsl:value-of select="local-name(.)" /> </A> </th> </xsl:for-each> </tr> </xsl:template> </xsl:stylesheet> The output of OneMany.jsp and OneMany.xsl can be seen in Figures 12.3 and 12.4, found earlier in this chapter.
Real-World Reporting Systems
The examples found in this and the previous chapter are not adequate for production reporting systems. Why do I say this? The reason has to do with the lack of scalability contained in these JSPs. They were designed to be to the point to exemplify the stylesheet concepts that are important for reporting.
A production reporting system would have several differences. The most obvious is database connection pooling that would most likely be accessed through a JavaBean. Another difference would be the lack of SQL statements. Instead, precompiled stored procedures would be used for faster results. Also, the RStoXML.java class used to convert ResultSets to JDOM representations would be utilized through either a JavaBean or a tag library. Finally, the error handling would be better than displaying large red non-user-friendly text on the screen.
Well-Formed Documents Revisited
One more thing that requires comment is the structure of the output from these reporting examples. When a stylesheet and JDOM are transformed, the results are stored in another JDOM document and then output. This means that the results of the transformation must be well-formed; otherwise, an error will occur, and potentially unpredictable results will follow.
In all of our examples, the output was an HTML table. This table element forms the root of the resulting JDOM document. However, if an element is added before or after the table element, the resulting JDOM document no longer has a root element. Go ahead and add an HTML font element with some text before the start of the table to one of the stylesheets. When the results are loaded, is it there? No, it has been dropped from the resulting JDOM document. Go ahead and add it after the close of the table. Again, unpredictable results will occur. This time the entire body of the report disappears.
The point I'm trying to make is to be sure your resulting HTML will be well-formed XML, or parts of the report might disappear and other difficult problems might crop up that will need to be traced. Along the same lines, if mysterious problems occur, think about the output in terms of XML instead of HTML.
Summary
XML can be a useful reporting tool that enables the creation of many different kinds of reports with sorting capabilities. Some of those reports include the paging report and the one-to-many report found in this chapter. The previous chapter demonstrated a generic sorting table report and a cross tab report.
A good reporting system combines the capabilities of the database and stored procedures with the filtering and formatting capabilities of XSL. Throw in some well-written JSPs, and you can quickly develop a highly useful reporting system.
The majority of this book focuses on server-side XML. However, XML can be successfully used on the client-side to various advantages. These uses will be introduced and examined in the next chapter.
| CONTENTS | |
![JSP and XML[c] Integrating XML and Web Services in Your JSP Application](/icons/blank_book.jpg "JSP and XML[c] Integrating XML and Web Services in Your JSP Application"){kind=icon}
EAN: N/A
Pages: 26