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

advertisement

AddThis Social Bookmark Button

MVC Heresy with SQLTags
Pages: 1, 2

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:

  • The JDBC Driver and URL
  • The database user name and password
  • The JDBC Schema name
  • A Java package for the generated Java code
  • A .jar file name
  • Flags to control inclusion of the built-in tags and source code

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:

<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.

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" %>
<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
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" %>
<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
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:

  • Multi-row insert, update, and delete in HTML.
  • Application server deployment.
  • Using DataSources.
  • Using authentication and access control with allow, deny, and auth tags.
  • Using "handler" classes to easily deal with 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.