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.

  

4 Responses to 'Instant XML feeds via the JSTL SQL tags'

Subscribe to comments with RSS

  1. BorisPlus said,

    20 October 2008 at 2:11 pm

    hello,I use jstl sql query, but I have some trouble.if I
    select from one table twice
    Select t1.Id as Id1, t2.Id as id2 from tab t1,tab t2
    , so I have sql result with two columns “Id” and “id”, but not “id1″ and “id2″. I use mysql jdbc connector. in this I can’t use simple displaytag construction,because there is only one column “Id”. please, email me

  2. Art Jolin said,

    03 November 2009 at 7:36 pm

    “Why not just free your data for use by your users’ new requirements in a quick hitting, low risk way?”
    The answer is “The old style was abandonded for production for good reasons: 1) security exposures (what user is authorized to see ANY AND ALL data? no one in a production app), 2) you have just shifted what user is dissatisfied – use tags and it’s now the other components or apps who want the same access the humans have and you are back to implementing an action. You put forward the same old argument used when better engineering frameworks and practices were first introduced and the free spirits didn’t want to change. Sure, we all want to whip it out and see it run – that’s the fun part. When your business depends on it being robust and secure as well as being right and fast (and tags circumvent many of the caching solutions out there now so it may not even be fast except for the coder) you gotta bite the bullet and do it right. I’ve just been asked to help a large customer migrate from ColdFusion to better, sounder application architecture – ColdFusion has just such DB-access tags for JSPs and many (not all, certainly – if it ain’t broke…) that have lived with it for years are belatedly jumping on the J2EE wagon. What is ColdFusion’s market share? That should give anyone a clue on the longevity of these sorts of quicky techniques. Sorry if I sound really opinionated, but it’s from 15+ years consulting on web apps from CGI and C on standalone web servers to J2EE and Java on massive app server clusters and their minions today. Don’t mean to be rude, just been thru it before.

  3. Daniel Krook said,

    04 November 2009 at 12:22 am

    Hi Art,

    Thanks for the comments. Don’t worry, I don’t take them as rude. :)

    Before I address your points, let me just re-iterate a passage from the post. This should reinforce my view that this should be considered a temporary and unorthodox option – based on something that’s available in the spec – not necessarily a best practice. The goal is to provide some extra, low cost functionality quickly to justify further development using the proper approach.

    Of course, the flip side is that you’ve [provided data-powered functionality] 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.

    So in no way is this post meant to disparage architectural best practices in JEE development. I find it complementary to – and doesn’t compete with – our Struts and Spring MVC applications in production.

    In fact, it even becomes a necessary measure when your application is deployed to a hosting center that requires 4 months and $20k to deploy an updated EAR file, versus promoting a single JSP on demand.

    On to your points;

    To answer the first one, about security exposures. This is indeed a low risk approach. There is nothing in the tags that implies that the data source you are accessing from the tags has unlimited privileges on the database.

    In DB2, or any other database, you can control access through grants to particular users. Or even set up views to limit their access to the underlying table structure. In WebSphere, or any other app server, you’ll then associate the user id to a data source and make it available in your deployment descriptor to the Web application. So there are a lot of control points here.

    For example, you might have one read-write JNDI resource for your business logic, and another read only or restricted access enforced by the database and optimized through several modifications to your queries, such as those that increase concurrency through isolation levels.

    To address your second point, I don’t understand why doing this implies that the end user gains while a consuming application necessarily loses… You could serve different formats to each, or just bolt this on separate to the existing application without upsetting any existing interface.

    And what if one user or another now wants the same access to the data you’ve just freed up… that’s the point of cheaply prototyping in order to validate a business need and justify new development to meet the requirement.

    About your later point regarding caching, can you provide some more detail here how this defeats the cache? These JSPs are compiled like any other into servlets, and you can still use WebSphere’s DynaCache to set a policy for these URLs. Using prepared statements and the proper read-only optimization in your queries makes this as efficient as any other data access method you could deploy in a Web app.

    As for ColdFusion… I refuse to defend that platform, and I feel your pain… What’s the sounder platform you suggest?

    In any case, my experience has shown me that it’s wisest to choose the best tool for the job, even if that means integrating several platforms (e.g., WebSphere and PHP) or breaking down your system requirements into manageable chunks that can have their own independent technology implementation and link them together with an SOA, or even supplement an MVC architecture with good old functional, type one JSP scriptlet pages.

  4. Art Jolin said,

    07 November 2009 at 3:08 pm

    “this should be considered a temporary and unorthodox option ” — my reading was different. If nothing else then my post helped to make clear to all readers your position on this. The “temporary” part must always be carefully considered; we’ve all done “temporary” work which is still in use years later because we lack the time to fix it until it truely bites us hard (always at the worst possible time).

    “In DB2, or any other database, you can control access through grants to particular users.” — in my experience in enterprise applications (and that of all my collegues, based on discussions and papers written & reviewed by the community), it is very rare for DB admins to set up per-end-user access. WAS guidelines recommend a single userid/password for an application (often for an entire suite of apps) as the typical case. Also, r/w vs. r/o access isn’t enough since the vast majority of secured enterprise data must be secured from read as well (do you want your neighbors reading your bank statements?). There are indeed alot of control points but DB admins and their executives don’t want to use them. That’s why we have LDAP and ActiveDirectory. Getting very clever (and more verbose in your JSP logic) means more logic that is difficult to debug using today’s IDEs (anyone who has tried to debug JSPs would agree — if anyone doesn’t pls chime in). Until there is a revolution throwing out MVC as the most common design pattern there will be no motivation for IDEs to make this any easier.

    “while a consuming application necessarily loses… You could serve different formats to each” — my point is that putting such new function in the model (outside JSPs) with the minimum JSP view logic means that access by consuming apps is free – you don’t have to reimplement your SQL and related logic in a second place the apps can reach. The additional cost is very minor, only an extra method call or two from the JSP instead of the logic being inline. If this was “bolt[ed] on separate to the existing application” it’s still a JSP and thus not accessible to model (ie., plain Java) code; implement it in the model and its accessible to both.

    “cheaply prototyping ” — honestly I don’t see this as any cheaper to implement than doing it as MVC, except for those 1-2 method calls I mentioned. A prototype MVC implementation is fast because you don’t add in the full set of error checking, edge functions, etc. You still end up with the layering you’ll use in the final production-ready feature (assuming you’re adding a feature to a mature app which good layering standards already) and only have to add code to the existing classes. If you prototype as JSPs then you must create the basic classes for layering and refactor all your JSP logic into them. I happen to currently have a customer (not the first I’ve seen) that took a variation of your “all in the JSP” approach and they experienced exactly this no-faster and hard-to-debug situation. SO I’m saying you can definitely cheap-prototype to validate a need and still stick with good MVC.

    “About your later point regarding caching” — I am talking about data-level caching (Dynacache dynamic page content caching is great, but isn’t always the best choice). Dynacache command caching, for example, assumes you have MVC and actions/commands; it would be really messy stuffed inside a JSP. Beyond that are the more recent techniques (still 8-10 years old) of caching at the data layer (after you do a DB query and turn the data into a Java object, you caching it to avoid repeating the DB query). This includes simple single-JVM caching using java.util.Map and cross-JVM caching products like WebSphere Extreme Scale or Coherence. You also miss out on using Hibernate or the Java Persistence Architecture (JPA) feature in WAS to automate your DB access – as well as the caching points built into these frameworks. These are the sorts of caching technlogy you miss out on if you put your data access logic in a JSP (to be precise, it would look WAY WAY UGLY to stuff this into a JSP). Yet the primary benefit of JPA is to speed up development of data access logic (but in a production-ready way when you later add query tuning and other fine adjustments). Using the PreparedStatementCache does greatly speed up making a DB query a second or nth time, but it can’t compare to avoiding the query altogether by caching the data. About 70% of my gigs over the last 2 years have been involved at least partially with going beyond query optimization into actual data caching. Ask other currently-practicing consultants and I bet you get a number more like 40% (since caching is a special skill area of mine) but that still clearly shows that most apps benefit from some form of caching beyond DynaCache static and dynamic content caching.

    “best tool for the job” — I totally agree with you, as I am also a pragmatist. We seem to only disagree about the relative benefits and drawbacks of SQL-based tags in JSPs. I do fully recognize the one inescapable benefit of putting the entire function in a JSP – that JSPs are easier to upload on the fly (on the sly??? ;-) into a running application than non-JSP Java classes are. However, that is really a process problem – there SHOULD be a way in any web application environment to easily try out potentially useful new function. It is totally technically possible, in several ways. It will always be controlled and that’s as it should be – you can’t have any developer with wild hair adding function to a public web app which is the public face of a company. If the only reason to put business logic into a JSP is to hack around a self-defeating corporate IT policy then call it a hack and perhaps use the above drawbacks to justify fixing the policy so you can prototype the way you should.

Leave a Reply