ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

MVC Heresy with SQLTags

by Steve A. Olson

Heresy: A deliberate deviation from the orthodox doctrine.
SQLTags (sqltags.org) is, indeed, a deliberate deviation from the orthodoxy of the Model-View-Control (MVC) design pattern; therefore, SQLTags is heresy, but is it necessarily "bad?" In this article, I will introduce the SQLTags toolkit and when it can be successfully employed.

Notwithstanding the recent criticism by N. Alex Rupp in "Beyond MVC," the MVC design pattern is, currently, the gold standard for highly complex web applications developed within the servlet environment. Just subscribe to any of the Struts mailing lists to witness how vibrant this community is. However, as stated on the Struts main page and paraphrased here, Struts (and, by extension, MVC) are not the best choice for every project. The Struts community asserts that only "very simple applications, with a handful of pages" are candidates for non-MVC implementations, and with this I agree; however, the threshold of "very simple" has been changed by the introduction of SQLTags.

SQLTags implements a very simple concept: Database schema as Tag Library. Each table is represented as a JSP tag, a tag that:

  • "Knows" all of the columns.
  • "Knows" how to automatically assign HTTP request parameters to those columns.
  • "Knows" its own primary key and uses it in update and delete statements.
  • "Knows" all of its imported and exported foreign keys and, more importantly, how to use them to join to the related tables.
  • "Knows" how to write insert, update, delete, and select statements.
  • "Knows" how to process several rows at once.
  • "Knows" how to automatically output the results in pages.

in short, a tag that "knows" the underlying table and how to integrate it into the servlet environment.

This simple concept extends the realm of "very simple" to what previously were moderately complex, data-driven web applications.

SQLTags Features

SQLTags, available at www.sqltags.org, is a free, open source, object-relational mapping toolkit that provides a new and innovative approach to data-driven web applications development. At the SQLTags core is a generator that builds a JavaBean and JSP tag for each table within a given JDBC schema. The SQLTags Generator packages everything (including built-in tags and support classes) into a single Java Archive (.jar) file for easy deployment into any Java 1.4/Servlet 2.3/JSP 1.2 (or later) J2EE application server. Best of all, SQLTags is quick and easy to learn and understand.

O'Reilly Open Source Convention.

The SQLTags generator accepts a valid (and available) JDBC Driver, JDBC URL, user name, password, schema name, and other options, which are used to reverse-engineer the given database schema into the corresponding .jar file. The resulting .jar file can be deployed into any complaint application server. On the target server, the deployment descriptor (or /WEB-INF/web.xml file) is updated to include context parameters that tell SQLTags how to connect to the same or an identical schema. Once the generated .jar file is successfully deployed into the target application server, JSP pages can use the generated (and built-in) tags contained within the .jar file to implement the desired functionality. Whenever the database is changed, simply use the SQLTags Generator to re-generate the .jar file.

Additionally, SQLTags works well with other Java and JSP industry standards, such as:

  • JavaBeans: each SQLTags tag is a JavaBean.
  • JSTL: SQLTags beans are easily accessible within JSTL's Expression Language (EL).
  • JSP 2.0: SQLTags tags are easily accessible using the ${ syntax.
  • Struts: SQLTags tags work well with the Struts html tags.

SQLTags is both application-server- and database-independent.

Functional Overview

There are three steps required to use a SQLTags-generated .jar file:

  1. Run the generator to create the .jar file,
  2. Deploy the generated .jar file into the target web application.
  3. Author JSP pages using the tags within the target web application.

This section will provide an overview of the functionality available to the JSP developer when using a generated .jar file.

Built-In Tags

SQLTags adds a number of built-in tags to the generated .jar file. These can be broken down into three main categories:

  • Paging support: first, last, next, and previous.
  • Database support: connection, commit, rollback, exception, cursor, statement, fetch, where, and orderby.
  • Authorization: authorize, allow, and deny.

The most commonly used built-in tags are connection, exception, where, and fetch. The connection tag is required in every page that needs a connection to the database (almost every page). Connections can come from the built-in SQLTags connection pool defined within the deployment descriptor or from a container-managed DataSource. The exception tag outputs JDBC error messages only when things go awry with database operations. The where and fetch tags are provided as a mechanism to more easily compose complex where clauses and explicitly iterate through the ResultSets. The cursor, statement, and fetch tags are available to provide functionality similar to the JSTL sql tag library. (At some point in the future, perhaps, SQLTags will integrate with the JSTL sql tag library.)

Generated Tags

As noted above, each table is represented as a tag, and each of those tags have common attributes and methods, as well as unique, per-column attributes and methods. The common attributes available to all generated tags are: id, tableAlias, buttonName, columns, displaySize, foreignKey, handlerClass, hasFetch, maxRows, operation, orderBy, parentName, paging, preInsertSQL, preUpdateSQL, properties, startRowParameter, where, and from.


Additionally, for each column (COL), there are four generated tag attributes:

  • COL

The COL attributes allow the value of the column to be explicitly set. COL_FORMAT provides a mechanism for specifying the display format for date and number columns, COL_SELECT provides a mechanism to override the select-list item for the column, and a COL_BIND provides a mechanism to override the bind expression used in a PreparedStatement (every SQLTags SQL statement uses the PreparedStatement). Some of the more commonly used attributes are:

  • id: defines the snippet variable that can be used within JSP snippets or EL expressions.
  • operation: defines an explicit operation to be carried out on the table (insert, update, delete, deleteChildren, or select).
  • buttonName: defines a request parameter that contains the operation.
  • foreignKey combined with parentName: used to nest tags in a parent-child configuration.
  • paging="true" and displaySize: define paging behavior.
  • properties="true": tells the tag to scan the HTTP request for parameter names that match column names.
  • where: defines the where condition and causes the tag body to iterate once for each row that satisfies the given condition.


Some of the more useful methods available to all generated tags are insert(), update(), delete(), and delete(where). Additionally, because each table tag is a JavaBean, each column, COL, has setCOL() and getCOL() methods, and each foreign key, FK, has a getFK() method — again, very useful for EL expressions.

Foreign Keys

SQLTags makes extensive use of foreign keys. Take a look at any Entity-Relationship Diagram; the glue that holds any database together is the foreign keys. Indeed, in my experience, the majority of the navigation within a data-driven web application centers on the foreign keys, either through parent table lookups or through parent-child nesting. For example, given an employee, "What department is she assigned to?" Given a department, "Who are the employees contained within it?" Given an invoice, "List the invoice line items and related product names." These questions are all answered by foreign keys.

SQLTags provides powerful ways to leverage foreign keys using "declarative access." For example, imagine two tables, EMP and DEPT, with a foreign key, FK_DEPTNO, defined within EMP referencing DEPT, as illustrated by Figure 1:

Figure 1
Figure 1. Sample data model

When SQLTags generates our hypothetical EMP JavaBean, it would contain a getFK_DEPTNO() method that returns a DEPT JavaBean. Consider the following JSP 2.0 snippet.

<x:emp id="e" where="order by ENAME">
   ${e.ENAME} belongs to Department

As expected, the column values are easily accessible using EL (the beginning of the second line) based on standard JavaBean syntax; however (and perhaps not expectedly), the foreign keys are also available in exactly the same way! Need the department name, DNAME, for a given employee? Simply use the foreign key FK_DEPTNO as the getter for the related department. Wow!

SQLTags also provides a mechanism for nesting children records within their parents, again, using foreign keys. Consider the following JSP 2.0 snippet.

<x:dept id="d" where="order by DNAME">
 <p>${d.DNAME} has the following employees:
   <x:emp id="e" foreignKey="FK_DEPTNO"

Again, note how easily the list of employees for a given department can be obtained simply by knowing the foreign key name and the id of the DEPT tag.

Finally, consider how simple it is for a programmer (or even a designer, heaven forbid) to look at an Entity-Relationship Diagram and start connecting the dots.

Pages: 1, 2

Next Pagearrow