Web DbForms

Many developers find themselves writing similar JSP and servlet code again and again when creating Web-based database applications. The open source project DbForms provides a solution that reduces the amount of coding to an absolute minimum.

This article gives an overview of DbForms and its concepts and features, and shows some code examples.

Introduction

DbForms is a Java-based Rapid Application Development (RAD) environment which enables developers to build Web-based database applications in a very short time and with very little effort. DbForms applications are built in a conceptually very similar manner to RAD database-building tools like Microsoft Access (for Windows-based applications) or Sybase PowerSite (for Web-based applications). The basic principle of these RAD-Tools could be described as "placing database-aware components and action elements on templates (forms) which get executed at runtime."

DbForms builds on top of Java Servlets 2.2 and Java Server Pages 1.1 technologies by Sun Microsystems. It makes extensive use of the JSP Tag Library extension introduced in the JSP 1.1 specification.

The project's homepage is located at http://www.dbforms.org and contains a complete user manual, several technical articles, source and binary distributions, online examples, a CVS, mailing lists, and lots of other information related to DbForms.

The MVC Paradigm Incorporated by DbForms

DbForms implements the concepts of the Model-View-Controller (MVC) design pattern. (See Design Patterns: Elements of Reusable Object-Oriented Software, Erich Gamma, Richard Helm, Ralph Johnson, and John Vlissides, Addison Wesley, October 1994.) You do not need to provide any Controller-related code; just focus on defining the Model and creating the JSP view components of the application (mainly using DbForms custom tags).

The following discusses how Model, View and Controller interact in DbForms.

The Model: Database Objects Described by Database Metadata

The use of DbForms is to perform operations on databases. The database tables accessed by a DbForms application must be declared in a XML configuration file (usually stored as WEB-INF/dbforms-config.xml), which is parsed and evaluated at Web-application startup time.

Listing 1. Defining the model

<dbforms-config xmlns="http://www.wap-force.net/dbforms">

  <table name="customer">
    <field name="id" fieldType="int" isKey="true" />
    <field name="firstname" fieldType="char" />
    <field name="lastname" fieldType="char" />
    <field name="address" fieldType="char" />
  </table>

  <table name="orders">
    <field name="orderid" fieldType="int" isKey="true" />
    <field name="customerid" fieldType="int" isKey="true" autoInc="true" />
    <field name="date" fieldType="char" />
    <field name="annotation" fieldType="char" />
    <field name="amount" fieldType="int" />
  </table>

  <dbconnection
        name = "jdbc/dbformstest"
           isJndi = "true"
  />
</dbforms-config>

As shown in Listing 1, every table (or view) to be accessed by DbForms has to be declared inside of a <table> element. All relevant table fields need to be declared inside of a <field> element nested within its respective <table> element.

There exists a tool for generating this XML data automatically. The tool reads database metadata of a specified database and constructs a configuration file, as shown in Listing 1.

The Controller: Parsing, Dispatching, and Executing Events

As pointed out above, you do not need to provide any Controller-related code when the Controller is a true infrastructural component. However, it's useful if you have an idea of what's going on inside the Controller. The following lines provide that information.

The Controller includes several components:

• ControllerServlet: this servlet is the single point of entry for all incoming HTTP requests from clients.
• EventEngine: an assistant to the ControllerServlet, it focuses on filtering HTTP requests for WebEvents and instantiates them.
• WebEvent Objects: all Objects derived from the abstract super-class WebEvent have the ability to initialize themselves by reading a given HTTP request. These events get executed, either directly by the Controller or by the View.

The following description of the execution of a typical user action should give you a better picture of what the controller does and how it interacts with the other components:

1. User presses the button "delete row" on his/her DbForms application.
2. Client browser submits data via HTTP-POST to the Controller-servlet.
3. The ControllerServlet delegates the incoming request to the EventEngine, which determines the main event (the user explicitly triggered the event by clicking a button). However, there may occur implicit events, too -- i.e., automatic updating of all changed input fields of all data rows.
4. The EventEngine component parses that request and determines the kind of action the user wants to be executed.
5. It then creates the appropriate WebEvent (in our case, a DeleteEvent) and delegates the request Object to this newly-created WebEvent, which finalizes its own initialization. After that, the EventEngine returns the recently-created and -initialized event back to the ControllerServlet.
6. The ControllerServlet tells the event to execute its built-in operation, if it is a DatabaseEvent. Other events (NavigationEvent, etc.) are delegated to the appropriate View component.
7. The ControllerServlet invokes EventEngine again to check if there are additional (implicit) events to be executed. If so, the appropriated WebEvent Objects are created and executed in the same manner as the main event described above.
8. The ControllerServlet determines the View component to which the request should be forwarded. If found, the ControllerServlet invokes the component and forwards the request.
9. If the View component is a JSP page containing DbForms tags, those tags will search for navigation events to be executed, and will finally generate the response for the user.
10. The response is rendered by the user's Web browser.

The View: JSP Templates Provided by the Application Developer

The View portion of a DbForms application is generally constructed using JSP technology. JSP files may contain static HTML elements, as well as dynamic elements containing Java code (definitions, statements, expressions). For more information about JSP, please see Sun's JSP page.

With release 1.1 of the JSP API, a powerful facility called JSP tag libraries was added. With these custom tags, you can encapsulate even most sophisticated Java code into lightweight JSP tags.

DbForms makes use of the great potential of JSP tag libraries. It contains an extensive custom tags library for rendering and manipulating database data.

The Structure of a DbForms View

Figure 1 gives a conceptual overview of the main components of a typical DbForms view.

The Basic Concepts of Forms

Each DbForms-view JSP may have one or more root tags of the type form. Every form tag has to contain exactly one header tag, exactly one body tag, and exactly one footer tag, in exactly that order.

Each of those tags may contain sub-elements like data fields, input fields, action buttons, and -- of course -- plain HTML text and JSP code.

header and footer tags are commonly used for titles of pages, labelling tables, placing action and navigation buttons, input fields to enter new data, etc. header and footer tags get evaluated only once.

The body tag is used for showing data rows coming from the database, and for providing the user with the functionality to edit that data. How many times the body tag and its sub-elements get rendered depends on the value of the maxRows attribute of the form element (and of course, on the number of datasets actually fetched from the database).

• maxRows = n -- body gets executed n times at maximum (with n in N)
• maxRows = "*" -- body gets executed for every row in the table ("endless" form)

Nested Forms

Every form may contain one or more nested sub-forms inside its body element.

The "orders" form is nested within the body element of the "customer" form, as shown in Figure 2. The user will see one customer per page (because maxRows is set to "1") and all the orders (because maxRows = "*") the customer has taken. The user may navigate through the list of customers by clicking the navigation buttons.

OK, Let's See Some Code!

As stated before, JSP views are the only parts of a DbForms application a developer usually needs to put his/her hands on. DbForms provides an extensive custom tag library which makes this an easy task, so that it can be performed even by non-programmers.

As we will see later, much of this code can be generated automatically by tools included in DbForms. However, it is useful to understand the basics of DbForms views, even if much of the work can be done automatically.

The following two sections will show a simple and a more advanced example of a DbForms view. Both examples can be thought as being parts of a little CRM application of a (virtual) agency.

A Simple Example

Listing 1. -- service.jsp

<%-- import DbForms tag library --%>
<%@ taglib uri="/WEB-INF/taglib.tld" prefix="db" %>

<html>
<head>
 <db:base/>
</head>
<body>
 <db:errors/> <%-- show eventually occured errors --%>

 <db:dbform tableName="service" maxRows="*" followUp="/service.jsp">

  <%-- the header gets rendered one time --%>
  <db:header>
  <db:gotoButton caption="Menu" destination="/menu.jsp" />
  <h1>Services we provide</h1>
   <center><h3>Our existing services</h3></center>
   <table border="5" width="60%" align="CENTER">
    <tr>
     <th>ID</th>
     <th>Name</th>
     <th>Description</th>
     <th>Actions</th>
    </tr>
  </db:header>

  <%-- the body gets rendered for each data row in the query
     it contains textfields and action buttons for manipulating data --%>
  <db:body>
    <tr>
     <td><db:textField fieldName="id" size="5"/><;/td>
     <td><db:textField fieldName="name" size="20" maxlength="30"/></td>
     <td><db:textField fieldName="description" size="24" maxlength="255"/></td>
     <td>
      <db:updateButton caption="Update"/>
      <db:deleteButton caption="Delete"/>
     </td>
    </tr>
  </db:body>

  <%-- the footer gets rendered 1 time
    it contains a textfields for entering new datasets --%>
  <db:footer>
    </table>
   <center><h3>Enter new service:</h3></center>

   <table align="center" border="3">
     <tr>
     <td>Id</td>
     <td><db:textField size="5" fieldName="id"/></td>
    </tr>
     <tr>
      <td>Name</td>
      <td><db:textField size="20" maxlength="30" fieldName="name"/></td>
     </tr>
     <tr>
      <td>Description</td>
      <td><db:textArea rows="4" cols="20" wrap="virtual" fieldName="description"/></td>
     <tr>
   </table>

    <br><center><db:insertButton caption="Insert new service!"/></center>
   </db:footer>

 </db:dbform>
</body>
</html>

Remarks

• We have set maxRows to "*" which has the effect that all rows will be shown at once. If there exist hundreds of services, we would like to set maxRows to "10," "20," or another limited number, and we would instantiate navigation buttons for scrolling between the pages. We will use that pattern later.

• The errors tag shows a list of errors, if any occurred (i.e. duplicate key error, etc.).

• The updateButton and deleteButton tags are placed in the body and therefore rendered for each row.

The result is shown in Figure 3:

An Example of Nested Forms

The following page (customer_order.jsp) gives you the functionality to manage the incoming orders of a customer. You are able to edit both orders of a customer and the customer data itself. Furthermore, you do not need to struggle with plain service IDs, but will be able to conveniently select the services from a select box by name.

Listing 2. customer_order.jsp

<%-- import DbForms tag library --%>
<%@ taglib uri="/WEB-INF/dbforms.tld" prefix="db" %>

<html>
<head>
 <db:base/>
</head>
<body>
<db:errors/> <%-- show eventually occured errors --%>

 <%-- the root form --%>
 <db:dbform tableName="customer" maxRows="1" followUp="/customer_orders.jsp"
 autoUpdate="false">

  <db:header>
   <db:gotoButton caption="Menu" destination="/menu.jsp" />
   <h1>Customer</h1>
  </db:header>

  <db:body> <%-- the body shows the data of the current customer --%>

  <table align="center">
   <tr>
    <td>Id </td>
    <td><db:textField fieldName="id" size="4"/></td>
   </tr>
   <tr>
    <td>First Name</td>
    <td><db:textField fieldName="firstname" size="18"/></td>
   </tr>
   <tr>
    <td>Last Name</td>
    <td><db:textField fieldName="lastname" size="18"/></td>
   </tr>
   <tr>
    <td>Address:</td>
    <td><db:textField fieldName="address" size="25" /></td>
   </tr>
   <tr>
    <td>Postal code/City</td>
    <td><db:textField fieldName="pcode" size="6"/> -
      <db:textField fieldName="city" size="16"/> </td>
    </tr>
   </table>
  <br>

   <%-- table embedding the subform --%>
   <table align="center" border="1">
    <tr>
     <td>
      <center><p><b>Orders</b></p></center>

    <%-- this is the begin of the subform:
      the subform renders all the services the current customer has ordered --%>
    <db:dbform tableName="orders" maxRows="3" parentField="id" childField="customer_id"
   followUp="/customer_orders.jsp" autoUpdate="false">
     <db:header>
     <%-- code for showing existing orders of services for that customer --%>
     <table>
      <tr>
       <td width="40"></td>
       <td>service</td>
       <td>orderdate</td>
      </tr>
     </db:header>

     <db:body allowNew="false">
      <tr>
       <td width="40"><db:associatedRadio name="radio_order" /></td>
       <td>
        <db:select fieldName="service_id"> <%-- this allows the --%>
         <db:tableData           <%-- users of the application to --%>
          name = "our_services"      <%-- select services conveniently --%>
           foreignTable = "service"   <%-- from a select-box --%>
           visibleFields = "name"
           storeField = "id"
          />
        </db:select>
       </td>
       <td><db:dateField fieldName="orderdate" size="14"/></td>
      </tr>
     </db:body>

     <db:footer>

     <tr>
       <td width="40"></td>
       <td><db:updateButton caption="Update Order" associatedRadio="radio_order"/></td>
       <td><db:deleteButton caption="Delete Order" associatedRadio="radio_order"/></td>
      </tr>
    </table>

    <%-- code for entering new orders of services --%>
     <br><hr>
     <table>
      <tr>
       <td>service</td>
       <td>date</td>
       <td></td>
      </tr>
      <tr>
       <td>
        <db:select fieldName="service_id">
         <db:tableData
         name = "our_services"
         foreignTable = "service"
         visibleFields = "name"
         storeField = "id"
         />
        </db:select>
        </td>
        <td><db:dateField fieldName="orderdate" size="10" /></td>
       <td><db:insertButton caption="insert order" /></td>
      </tr>
     </table>

     <center> <%-- navigating in the subform (in the list of orders of a customer --%>
      <db:navFirstButton caption="<< First" />
      <db:navPrevButton caption="< Previous" />
      <db:navNextButton caption="Next >" />
      <db:navLastButton caption="Last >>" />
    </center>

     </db:footer>
    </db:dbform>
    <%-- subform end --%>

     </td>
    </tr>
    </table>
    <%-- end of table embedding the subform --%>

   <br><center>
    <db:insertButton caption="Store this new Customer!" /> <%-- action buttons for --%>
    <db:updateButton caption="Update Customer" />      <%-- editing data of --%>
    <db:deleteButton caption="Delete Customer" />      <%-- customers --%>
    </center>
   </db:body>

   <db:footer>
     <br><center>
     <db:navFirstButton caption="<< First" />  <%-- navigating in the --%>
     <db:navPrevButton caption="< Previous" />   <%-- list of customers --%>
     <db:navNextButton caption="Next >" />
     <db:navLastButton caption="Last >>" />
     <db:navNewButton caption"*"/>
    </center>
   </db:footer>
 </db:dbform>
</body>
</html>

Remarks

With this page, we have demonstrated another couple of major features of DbForms:

• Nested Forms: The structure of this page is similar to the structure shown in Figure 2: a main form has a sub-form inside its body. The sub-form is linked to its parent by the equality of data fields defined in the child form's parentField and childField attributes. (If there is more than one field defining this mapping, a list of fields may be provided, with each field separated from the other by characters like "," or ";" or "~".)

• Navigation Buttons: Because only one customer is visible at a time, the user needs a way to navigate between records. This functionality is provided by navFirstButton, navLastButton, navPrevButton, navNextButton elements.

• Insert Button: the navNewButton element navigates the user to an empty form to enter new data.

• select Tag: In addition to textField and textArea tags, more complex elements like select, radio, and checkbox can be used for data visualization and manipulation. I have chosen a select tag to select the type of service a customer orders.

• External data fetched by a tableData tag: This tag provides external data to radio, checkbox, or select tags. It may be used for cross-references to other tables. In our case, we initalized the select box with external data from the table "service." Be aware that you have to distinguish between the field(s) to be shown to the user and those to be stored in the associated field in the table. In our case, we have shown the field service.name and stored the value service.id in the associated field orders.service_id! The name our_services was defined to enable internal caching of data, which increases performance.

• Using associatedRadio elements to mark rows of data for certain actions (in our case we mark "orders" for the actions "update" and "delete") saves a lot of space and makes the interface clearer. If we had to draw a button for all possible actions for each row of data, the page would not look very friendly.

Advanced Features

Fine-grained Security

DbForms' security model builds on top of the Java Servlet security model, with its concept of users (principals) and roles.

DbForms provides fine-grained declarative definition of rights for data access and manipulation. DbForms can attach security constraints to each table defined in the XML configuration, telling DbForms which kind of database operations may be executed by which user groups.

Listing 4. Defining privileges

<dbforms-config>

  <table name="customer" >
    <field name="id" fieldType="int" isKey="true" />
    <field name="firstname" fieldType="char" />
    <field name="lastname" fieldType="char" />
    <field name="address" fieldType="char" />

    <granted-privileges
      select = "A,B"
      insert = "A"
      update = "A,B"
      delete = "A" />
  </table>

</dbforms-config>

The attributes of the <granted-privileges> element tell DbForms: "Members of group A may select, insert, update and delete customers, and members of B may read and update customers." All other groups (for example, a group C) may not access this table at all.

File Uploads

Managing BLOB Fields is a very easy task when using DbForms; first you have to tell DbForms about BLOB-Fields in the XML configuration file:

Listing 5. Defining fields of type "BLOB"

<dbforms-config>

  <table name="pets">
    <field name="pet_id" fieldType="int" isKey="true" autoInc="true" />
    <field name="name" fieldType ="char" />
    <field name="portrait_pic" fieldType ="blob" />
    <field name="story" fieldType ="blob" />
 </table>

</dbforms-config>

The configuration code-snippet shown in Listing 5 tells DbForms that the fields portrait_pic and story are BLOBs. As you can see, DbForms allows more than one field in a row to be a BLOB.

After defining our BLOB-powered table, we would want to build a JSP for managing the BLOB fields. For this purpose, a new custom tag is introduced:

Listing 6. Implementing a file tag

<db:file fieldName="portrait_pic">

The attribute fieldName refers to the name of the field the file needs to be uploaded to. (There exist additional attributes available for this element that are not shown here.)

This custom tag gets rendered as a HTML <input type="file"> tag, as shown in Figure 6.

This HTML element enables multipart-enabled browsers to submit files to the server.

If we were using BLOBs for storing images in a database, we could write the following JSP code to retrieve and render such a field:

Listing 7. Rendering images using a blobURL

<img src="<db:blobURL fieldName="portrait_pic"/>" width="100" height="80" border="0">

Special DbForms Feature: DISKBBLOBs

There are situations where BLOBs are not an option: if the application uses a RDBMS or JDBC driver without BLOB support, if BLOB support is too slow or even buggy, or if the files should be accessible by other applications without using a database layer.

DbForms also manages uploads to a file system instead of a database. This is completely transparent to the JSP view developer! For uploading and retrieving file-system-stored objects, the same tags and attributes are used as for uploading and retrieving regular BLOBs.

The only difference lies in the definition of the Model, where a server directory for storing the files must additionally be specified.

Listing 8. Defining fields of type "DISKBLOB"

<field name="story" fieldType ="diskblob" directory="x:\stories" />

Sorting, Searching and Filtering

Sorting

Sorting is an important, even essential, feature for many database applications. For example, if you would like to give the user the ability to sort the virtual agency's services by ID, you can use the following DbForms sort tag:

Listing 9. Instantiating a sort tag

<db:sort fieldName="id" />

Filtering

Just like sorting, filtering of data rows is an important functional requirement for many database applications. In its simplest case, the "filter" is nothing more than a part of the WHERE clause of the SQL SELECT statement used to retrieve the rows.

The filter criteria may be passed to the filter attribute of the DbForm tag:

Listing 10. Filtering

<db:dbform tableName="employee" maxRows="*" followUp="/employees.jsp" autoUpdate="false" filter="deptno=101 AND salery>3000">
...
</db: dbform>

Searching

Searching is another "must-have" functionality for a database application framework like DbForms.

DbForms allows you to create search forms very quickly. The number of fields to be searched, the kind of input widgets (textfield, textArea, select box, etc.), the kind of search algorithm to use, and the boolean combination of matches of search criteria are completely flexible.

All you have to do is:

• Decide which fields you want to make searchable
• Decide if you want the user to choose the criteria-combination mode himself/herself or if you want to use a (hidden) default
• Decide if you want the user to choose the kind of search algorithm to be used himself/herself or if you want to use a (hidden) default

Figure 7 shows a fully functional search panel. It is included in the example that comes with the DbForms distribution. You may also test it on the running live samples at the DbForms Website.

Implicit Scriping Variables

DbForms tags like <db:dbform> and <db:body> make some information available to the embedded JSP code, where the values are accessible as simple scripting variables.

The mechansim used for passing these values over is called "Tag Extra Info" and is part of the Java Server Pages specification 1.1.

Two useful scripting variables are listed in Table 1:

These (and many other) scripting variables can be used by a JSP developer to add advanced functionality to her/his DbForms-driven JSP page.

Application Hookups

Introduction

Along with a lot of built-in functionality, DbForms offers many optional features, which may be configured and utilized via the XML configuration file, attributes of custom tags, etc.

But it would be neither possible nor useful to create a system which has a built-in hardcoded answer for every problem or requirement which could appear during the development process (and life cycle) of a database application. Because it is impossible to foresee all eventual use cases and user's needs, such a monolithic approach would certainly restrict the application developer sooner or later.

Interface DbEventInterceptor

Every system which wants to get around this problem offers a kind of "programming facility" or "Application Programming Interface." So does DbForms.

DbForms provides an interface, DbEventInterceptor, which intercepts database operations DbForms is about to perform or has finished. This interface provides the following methods.

Listing 11. Methods definied in interface DbEventInterceptor

public int preInsert(HttpServletRequest request, Hashtable fieldValues, DbFormsConfig config, Connection con)
throws ValidationException;

public void postInsert(HttpServletRequest request, DbFormsConfig config, Connection con);

public int preUpdate(HttpServletRequest request, Hashtable fieldValues, DbFormsConfig config, Connection con)
throws ValidationException;

public void postUpdate(HttpServletRequest request, DbFormsConfig config, Connection con);

public int preDelete(HttpServletRequest request, Hashtable fieldValues,DbFormsConfig config, Connection con)
throws ValidationException;

public void postDelete(HttpServletRequest request, DbFormsConfig config, Connection con);

public int preSelect(HttpServletRequest request, DbFormsConfig config, Connection con)
throws ValidationException;

public void postSelect(HttpServletRequest request, DbFormsConfig config, Connection con);

As the names indicate, the preXxx() methods get called before the respective database operation is performed, the postXxx() methods get called after the operation has finished.

PreXxx() methods return a value indicating if the operation should be performed or not:

• DbEventInterceptor.GRANT_OPERATION
• DbEventInterceptor.DENY_OPERATION

PostXxx() methods do not return a value, as the operation is already done.

Installing the Hookups

How do we tell DbForms when to invoke which interface implementation?

This information must be provided by the DbForms XML configuration file. Similar to the granted-privileges security constraint (as described above), the XML element defining an Interceptor has to be placed inside of a table element.

  <table name="customer">
    <field name="id" fieldType="int" isKey="true"/>
    <field name="firstname" fieldType="char" />
    <field name="lastname" fieldType="char" />
    <field name="address" fieldType="char" />
    <field name="pcode" fieldType="char" />
    <field name="city" fieldType="char" />

    <interceptor
      className = "com.foo.bar.CustomerValidatonChecker"
    />

    <interceptor
      className = "com.foo.bar.TransactionLogger"
    />
  </table>

The semantics of these declarations could be described as "Invoke com.foo.bar.CustomerValidatonChecker and com.foo.bar.TransactionLogger, if the user is about to read, insert, update or delete data from table customer and call the appropriate methods of those objects"

Rapidly Generating Standard Views using XSL

As demonstrated previously, DbForms can help speed up development of Web-based database applications. But there are still many monotonous tasks to do -- you still have to write the JSP code for each view. The more views you have to write, the more monotonous work you have to do.

Because in many cases the JSPs are very similar to each other, you'll probably do much of the work using copy-and-paste and then apply the necessary changes by hand. This works well, but do we really want this kind of development? I don't think so!

For instance, say you have a database containing 40 tables and you want to write a Web-based data maintenance tool against it. Imagine you want for each table a "list view" (allowing the user to view all elements of the table at one time) and a "single view" (viewing only one row at a time), so that the user may view and edit the data in two different ways. Well, having 40 tables and two views for each indicates that you will have to create (40x2 =) 80 JSP files, and an application menu as well. This means that at least 81 JSP files need to be written!

Thanks to XML and XSL, DbForms provides a pretty simple and straightforward solution:

• For every "standard layout" (in our application we have "listview layout," "singleview layout," and "menu layout") we create one XSL file. Each XSL file gets applied to your application's XML configuration file (this file contains detailed information about all tables and fields of the database).
• The result of the XSL transformations are JSP files, created according to the style rules defined in the XSL file.

The very good news is that the whole process shown in Figure 8 is encapsulated by a SWING-based tool called DevGui, shown in Figure 9. You can use this application to generate the XML-formatted database metadata, to apply XSL stylesheets to it, and to deploy and test the resulting JSP files.

View Figure 9. DevGui: a tool for automatically generating JSP views.

DbForms Custom Tag Library

Joachim Peer is a Sun certified programmer and independent J2EE developer. He holds a masters degree in Management Information Systems.

Return to ONJava.com.

Copyright © 2009 O'Reilly Media, Inc.