Instant XML feeds via the JSTL SQL tags

20 December 2007 » DB2, Java, MySQL, Web architecture, WebSphere, XML

A dusty old Java tag library can help conjure up siloed Web site data for new uses.

Some background
I’ve developed a number of server-side Java Web applications over the years, first with scriplets embedded in JSP, then with the template and tag driven paradigm offered by ATG Dynamo before the J2EE standards, and most recently with the Model-View-Controller architecture pattern in Struts and Spring MVC.

Each of those technologies (mostly) improved on its predecessor and enforced a better separation of concerns between the database, application logic, and presentation of the end result in the browser. This in turn has helped my teams divide and conquer Web application development among specialized job roles.

That’s why I’ve long been puzzled why the SQL tags in the JavaServer Pages Standard Tag Library exist as a standard part of J2EE 1.3 onward. These tags enable a front-end developer to embed SQL directly into a JSP page without the need for scriptlet code.

This tag library seemed an ill-conceived reversion (anti-pattern even) to the days before MVC took hold as a best practice in the Java world, and I’m pretty sure I skipped that section of the objectives when studying for the SCWCD exam.

That said, the SQL tags came in pretty handy this week for a particular challenge, and the more I think about how I can use them beyond their intended purpose, the more every new requirement I see looks like a nail.

My particular application context
I support a content management application which was designed, developed, and deployed circa Web 1.9. It’s stable, performant, and most importantly, met its functional requirements of the day.

In the two years that it’s been deployed, several new requirements have arisen that have expanded its anticipated scope as a traditional Web application.

In particular, the ubiquity of XML feeds have driven the need for it to present its core data outside of the templates existing in the confines of its own Web site. The rise of tagging and the popularity of multimedia as syndicatable content has also made it creak.

Compounding the architectural limitations of the application itself is its inflexible hosting environment. The data center that this site is deployed to is governed by CYA-driven restrictions (rightly so) which constitute a barrier to frequent application deployment cycles that add new functionality.

This environment makes it difficult to adopt nascent technological advances – the next big thing in “coolness” or usability – but have also kept it exceptionally stable and available to meet its codified requirements without introducing undue legal or financial risk.

The application itself consists of two subcomponents. There is a Web application module on the secured intranet for authors to generate new content, and a publicly accessible read-only Web application module to display published content.

It’s primarily this latter Internet application where the use of JSTL SQL tags comes in most handy, but I can imagine uses on the intranet side as well (ad hoc reports, for example).

The case for SQL tag driven XML feeds
The JSTL standard defines a tag library for issuing queries against a data source defined in the Web deployment descriptor without using JDBC in Java scriptlet code in a JSP.

If this sounds like a simple concept that harks back to the type 1 JSP days, that’s because it is. The documentation shows its own apprehension about the inappropriate use of these tags:

The JSTL SQL tags for accessing databases … are designed for quick prototyping and simple applications. For production applications, database operations are normally encapsulated in JavaBeans components.

But therein lies their simplicity, flexibility and power for this particular production application scenario.

In order to take any slice of your data that can be exposed via a SQL query to the authorized user mapped to the JNDI entry for that data source, all you need to do is is write your query and iterate through the result set in an XML template defined in your JSP.

Think about that outside of this technology’s intended use as a prototype or simple application building block. Instead, imagine how you could use these tags to improve the value of a complex existing production application.

For example, suppose you’ve always provided an RSS feed for your latest ten published news stories. You’ve written your Controller or Action in your chosen MVC framework of choice and deployed it.

But now your users are demanding the latest five thumbnails of images published with a story to accompany its syndicated title and abstract in their latest mashup. Or perhaps they only want to see the last 10 stories which contain a given keyword.

What do you do? You could write a new Action or Controller and proper Command class in Java to meet that requirement. That would require updating some configuration files or deploying an EAR or WAR.

But look, you have an existing deployed stable application. Why risk introducing new code or downtime to a perfectly good application? Why not just free your data for use by your users’ new requirements in a quick hitting, low risk way?

Reuse your data by plugging in new JSTL SQL tag driven JSP files, don’t rebuild your application for every new data usage requirement.

To the tag library!
Ok, so you’ve read this far. I promise, the implementation itself will be much shorter :)

So your users want more information delivered via your feeds, or they wish to query by keyword or otherwise filter your data in a way you never anticipated.

Let’s see if we can free up that data for them.

  1. Write your query, with or without input parameters.

    SELECT ID, TITLE, ABSTRACT FROM NEWS_ARTICLES;

    SELECT ID, TITLE, ABSTRACT
      FROM NEWS_ARTICLES
      WHERE BODY LIKE ‘%?%’
      FETCH FIRST 5 ROWS ONLY;

    SELECT ID, TITLE, ABSTRACT, THUMBNAIL
      FROM NEWS_ARTICLES NA, NEWS_ARTICLE_IMAGES NAI
      WHERE NA.ID = NAI.NA_ID;

  2. Determine what XML format it should be in, whether a standard such as Atom or something custom like the following.

    <?xml version="1.0" encoding="UTF-8" ?>
    <results>
      <result id="">
        <title></title>
        <abstract></abstract>
        <thumbnail></thumbnail>
        <body></body>
      </result>
    </results>
  3. Tie the query to the format in a JSP file using the JSTL SQL tag library (and optionally, the Core tag library to escape output) and the JNDI name of the data source you already have configured in web.xml.

    Consult the documentation if you want to use placeholders.

    <%@ page contentType="text/xml; charset=UTF-8" pageEncoding="UTF-8" session="false"%>

    <?xml version="1.0" encoding="UTF-8" ?>

    <%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

    <sql:setDataSource dataSource="jdbc/yourdatasource"/>
    <sql:query var="items">
      SELECT ID id, TITLE title, ABSTRACT abstract, BODY body, THUMBNAIL thumbnail
        FROM NEWS_ARTICLES NA, NEWS_ARTICLE_IMAGES NAI
        WHERE NA.ID = NAI.NA_ID;
    </sql:query>

    <results>
     <c:forEach var="row" items="${items.rows}" >
      <result id="<c:out value="${row.id}"/>">
        <title><c:out value="${row.title}"/></title>
        <abstract><c:out value="${row.abstract}"/></abstract>
        <thumbnail><c:out value="${row.thumbnail}"/></thumbnail>
        <body><c:out value="${row.body}"/></body>
      </result>
     </c:forEach>
    </results>

  4. Deploy the JSP file as your application server requires. If reloading is not enabled, restart the application (consider setting a 15 minute timeout or similar, so you gain the performance boost but provide a hook for updating JSPs individually).

Conclusion
That’s it, you’ve now added an aspect of functionality to your application which frees up any data you can query for via SQL (or XQuery, if your data server is so enabled). You’ve done it in a pluggable fashion and haven’t needed to build any new Java code within your existing application and its framework.

Of course, the flip side is that you’ve done it outside of your application framework and may have circumvented some well-intended best practices. However, you may to prefer to think of this approach as a temporary, low-risk way to share the data available to the users of your application in novel ways that may justify investing in the development of longer term solutions.

Ironically enough, this reversion to single file deployment can make an application buzzword compliant with the one of the most touted recent enterprise targeted architectural pattern – SOA. It reduces the barrier between the value an application has – its data – and the consuming end point of that data – to a simple JSP.

All married up and honeymooned out

17 December 2007 » Potpourri, Travel

Ah, so it’s been close to two months since I last posted, but I have a good excuse.

Cat and I were married on November 24th, the Saturday after Thanksgiving in Tarrytown, New York. We were thrilled that so many of our friends and family could travel so far or otherwise be there on the holiday weekend. It meant a lot to us.

Our excellent photographers Justin and Mary have posted a few teasers up on their blog – we can’t wait to see how the rest came out. This shot in particular sums up both their creativity and their drive to know their clients ahead of the big day. We’d recommend them without a moment’s hesitation.

Our mutual friend and former colleague Jen König posted a pair of beautiful pics on Flickr as well; Cat’s mom lighting the unity candle during the ceremony and our entrance at the reception.

Speaking of Flickr, I finally broke down and signed up for an account to post close to 600 of the “best” shots from our honeymoon in New Zealand. It’s hard to believe that even that many is a mere quarter of the 2112 shots we took :)