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:
update and delete statements.insert, update, delete, and select statements.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, 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.
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:
${ syntax.html tags.SQLTags is both application-server- and database-independent.
There are three steps required to use a SQLTags-generated .jar file:
This section will provide an overview of the functionality available to the JSP developer when using a generated .jar file.
SQLTags adds a number of built-in tags to the generated .jar file. These can be broken down into three main categories:
first, last, next, and previous. connection, commit, rollback, exception, cursor, statement, fetch, where, and orderby. 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.)
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 COL_FORMAT COL_SELECT COL_BIND 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.
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. 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
${e.FK_DEPTNO.DNAME}!<br/>
</x:emp>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"
parentName="d">
${e.ENAME}
</x:emp>
</p>
</x:dept>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.
|
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.
Deployment of the generated .jar file is accomplished in two steps:
You'll likely need to restart the web application for the changes to take effect.
Context parameters are added to the /WEB-INF/web.xml deployment descriptor immediately after the <web-app> tag, using the following syntax:
<context-param>
<param-name>SQLTags.useCM</param-name>
<param-value>true</param-value>
</context-param>The list of SQLTags context parameters is as follows:
SQLTags.useCM: Short for "use internal Connection Manager." Valid values are true or false.
When false, SQLTags will use a DataSource and ignore the other SQLTags JDBC context parameters (listed below).
SQLTags.dataSource: Identifies the default dataSource to be used by the ConnectionTag when no dataSource attribute is supplied and useCM is false; default value is jdbc/SQLTagsDS. Note: a DataSourceis used when useCM is false or when a connectionTag explicitly references a
dataSource from the dataSource attribute.
SQLTags.bindStrings: Should almost always be set to false. When true, all database
bindings are processed as strings;
when false, all numerical and date data types are processed as the correct native Java type.
Greatly helps with compatibility among different databases.
The following parameters are only used when SQLTags.useCM is true. When useCM is false, the JDBC parameters are defined externally within a DataSource.
SQLTags.databaseDriver: The JDBC database driver as specified by JDBC vendor.
Examples: oracle.jdbc.OracleDriver, org.gjt.mm.mysql.Driver.
SQLTags.connectionUrl: The JDBC connection URL as specified by JDBC vendor.
Examples: jdbc:mysql://localhost/scott?user=root, jdbc:oracle:thin:@localhost:1521:ORCL.
SQLTags.maxPoolSize: Maximum number of JDBC connections for the connection pool.
SQLTags.poolSize: Default size of the JDBC connection pool.
SQLTags.userName: Database user for JDBC connections.
SQLTags.password: Database user's password.
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.
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" %>
<html>
<head><title>JSP Page</title></head>
<body>
<%-- 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}"/>
<br>
</sqltags:emp>
</sqltags:connection>
</body>
</html>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. Sample output: single table query
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" %>
<html>
<head><title>JSP Page</title></head>
<body>
<%-- uses configuration from web.xml. --%>
<sqltags:connection id="connect" >
<sqltags:dept id="d" where="order by DNAME">
<c:out value="${d.DNAME}" />
<blockquote>
<sqltags:emp id="e" foreignKey="FK_DEPTNO"
parentName="d">
<c:out value="${e.ENAME}"/>
<br>
</sqltags:emp>
</blockquote>
</sqltags:dept>
</sqltags:connection>
</body>
</html>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. Sample output: parent-child nesting
I've just scratched the surface of what is possible with SQLTags. There is a lot more functionality available within the tool set, including:
insert, update, and delete in HTML.DataSources.allow, deny, and auth tags.BLOB and CLOB column types, paging of results, etc.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.