Published on ONJava.com (http://www.onjava.com/)
 See this if you're having trouble printing code examples

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:

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:

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:

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:

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:


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.

Running the SQLTags Generator

Download sqltags-1-0-6.war (or the latest version) web application file from the SQLTags web site at sqltags.org/downloads.jsp and deploy it into your favorite J2EE application server. Note that SQLTags requires Java 1.4, Servlet 2.3 or later, JSP 1.2 or later, and a suitable JDBC driver for your specific database.

The SQLTags web application comes with pre-installed JDBC drivers for Oracle, MySQL, and Sybase. The JDBC Driver for Microsoft SQL Server is also included, but is in the /WEB-INF/extras directory and must be manually moved into /WEB-INF/lib in order to access a SQL Server database.

Note: the javac executable must be accessible via the PATH variable (by the application server) in order to compile the generated JavaBean classes.

To deploy the sqltags-1-0-6.war file into the Tomcat application server, for example, simply copy the .war file into the CATALINA_HOME/webapps directory, restart the server, and access the /sqltags-1-0-6/ URI on your deployment host using your browser. For example, if you were running Tomcat on your local workstation on port 8080, you would use the following URL to run the SQLTags web application: http://localhost:8080/sqltags-1-0-6/. The SQLTags web application contains information and samples for running the generator, deploying the .jar files, and building JSP pages.

To execute the generator from your browser, simply click on the "run ..." link near the top of the page (using the above example, the URL would be http://localhost:8080/sqltags-1-0-6/generate.jsp). Fill in all of the fields and click on the "Generate!" button. That's it, really!

The generate screen contains detailed descriptions for each input field. Basically, you'll need to specify:

Once the generator completes, you should see output in the browser window showing that each table was processed. The generated .jar file is saved into the /WEB-INF/tmp directory using the file name specified in the JAR Filename input field. (Note that future releases of SQLTags will likely place the generated .jar file in the /tmp directory under the web application.)

Note: SQLTags will not generate foreign key references to tables that are either not contained within the generated .jar file due to wildcard exclusion, or do not have a primary key.

Deploying a Generated Tag Library

Deployment of the generated .jar file is accomplished in two steps:

  1. Copy the generated .jar file into the /WEB-INF/lib directory of the target web application.
  2. Modify /WEB-INF/web.xml (the deployment descriptor) to define the SQLTags context parameters.

You'll likely need to restart the web application for the changes to take effect.

Context Parameters

Context parameters are added to the /WEB-INF/web.xml deployment descriptor immediately after the <web-app> tag, using the following syntax:


The list of SQLTags context parameters is as follows:

The following parameters are only used when SQLTags.useCM is true. When useCM is false, the JDBC parameters are defined externally within a DataSource.

Authoring JSP Pages with SQLTags

SQLTags development starts with a well-defined data model. So for the purposes of this article, I will refer to the "classic" EMP-DEPT data model that was shown above in Figure 1.

Sample 1. Listing all EMP data with related DEPT name.

In this first example, I will demonstrate a simple JSP page that displays a listing of all rows from the EMP table and their assigned DEPTs.

<%@ taglib uri="demoTags.jar" prefix="sqltags" %>
<%@ taglib uri="http://java.sun.com/jstl/core"
    prefix="c" %>
<head><title>JSP Page</title></head>
<%-- uses configuration from web.xml.  --%>
<sqltags:connection id="connect" >
   <sqltags:emp id="e" where="order by ename">
        <c:out value="${e.ENAME}"/> assigned to 
        <c:out value="${e.FK_DEPTNO.DNAME}"/>

The first two lines introduce the two tag libraries used within the JSP page. The <sqltags:connection> tag is used to access the JDBC connection pool that must wrap all of the other SQLTags tags. The next line introduces the EMP tag and defines a where clause that matches all rows and orders the results by ENAME. The JSTL <c:out> tags are used to output the ENAME property from the EMP JavaBean (ENAME is a column from the EMP table). Finally, the DNAME property from the related DEPT table is output for each EMP in the table.

Viewing the above page from the browser would yield the output shown in Figure 2, provided we have suitable data in the database:

Figure 2
Figure 2. Sample output: single table query

Sample 2. Parent-child nesting of EMP within DEPT.

In the second example, I will demonstrate a simple JSP page that implements the parent-child configuration listing EMPs within each DEPT.

<%@ taglib uri="demoTags.jar" prefix="sqltags" %>
<%@ taglib uri="http://java.sun.com/jstl/core"
    prefix="c" %>
<head><title>JSP Page</title></head>
<%-- uses configuration from web.xml.  --%>
<sqltags:connection id="connect" >
 <sqltags:dept id="d" where="order by DNAME">
   <c:out value="${d.DNAME}" />
     <sqltags:emp id="e" foreignKey="FK_DEPTNO"
       <c:out value="${e.ENAME}"/>

Again, the first two lines introduce the two tag libraries used within the JSP page. The <sqltags:connection> tag is used to access the JDBC connection pool, and must wrap all of the other SQLTags tags. The DEPT tag defines a where clause that matches all rows and orders the results by DNAME. The next line outputs the department name (DNAME) and the blockquote provides a visual indentation showing employees that belong to each department. The EMP tag provides the mechanism to nest the employees within the related DEPT. The foreignKey attribute tells the EMP tag which foreign to use to reference (join to) the DEPT tag, and the parentName attribute identifies which DEPT tag instance should be referenced. The next line simply outputs each employee name, one per line.

Figure 3 shows sample output from this page:

Figure 3
Figure 3. Sample output: parent-child nesting

Summary and Preview of Advanced Topics

I've just scratched the surface of what is possible with SQLTags. There is a lot more functionality available within the tool set, including:

Hopefully, you've seen enough to whet your interest to investigate further. MVC is a great design pattern, but SQLTags can be successfully employed when used to develop a data-driven web application that closely follows a well-defined data model with limited processing logic.

Basically, for data-in, data-out applications, SQLTags is just the ticket.

Steve A. Olson is a founder and chairman of the board of Applied Information Technologies, Inc. (http://ait-inc.com)

Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.