Database-backed Application (part 2)Database-backed Application (part 2)
Using NetKernel with a DB to create an Address book application
Home > Books > Tutorials and Training Guides > Web > Database Application > Database-backed Application (part 2)

Rate this page:
Really useful
Satisfactory
Not helpful
Confusing
Incorrect
Unsure
Extra comments:


Summary

In part 1 we built and configured a module to connect to a database and setup the database and JDBC connection. The application framework is complete we now need to write the application code.

Specification part2

A master page will provide an alphabetically browsable view of the address book. The master page will also provide an embedded search form. The search results will be rendered back into the master page.

Using the mapper and xrl accessor we will modularize the design into components and use a template document in which the page components are combined.

Building the App

Master Template

The master template will be an XHTML document containing some static content common to all pages. It includes an <xrl:include/> element which is an xrl statement to include any dynamic content supplied from the mapper. Here it is

<html xmlns:xrl="http://1060.org/xrl">
  <body>
    <h1>AddressBook</h1>
    <xrl:include href="xrl:content" />
    <div style="font-size: 10px">(C) 2004, 1060 Research Ltd</div>
  </body>
</html>

Copy this into a file called mastertemplate.xml in the resources/ directory. Add the following link to the links.xml document.

<link>
  <name>main</name>
  <ext>/</ext>
  <int>active:xrl-html+operator@ffcpl:/links.xml+template@ffcpl:/resources/mastertemplate.xml</int>
  <args>links,param</args>
</link>

The link maps a request for the external root path to an internal execution of the xrl accessor with the mastertemplate.xml as template - since this is just testing we are not providing a content argument, also we are using the xrl-html varient of the xrl accessor, this simply tells xrl to return the XML with a mimetype of text/html. We've also specified <args>links,session</args> this tells the mapper to pass through these links and any param argument on any included resource requests (more on this later).

We can now test the contentless template http://localhost:8080/addressbook/.

Adding the alphabet browser bar

We require an alphabetized browser bar with each letter a link to a page which renders all entries beginning with that letter. We have said that the main page will provide the browser view. So it's REST interface will look like /addressbook/letter/a.

Here is a dpml script which uses an XQuery to generate the browser bar. We're creating this widget dynamically as a demonstration - the result of the script will be cached and only ever runs once for the life of the application. Copy the script in the resources/ directory as alphabetmenu.idoc.

<idoc>
  <seq>
    <instr>
      <type>xquery</type>
      <operator>
        <xquery> (: Create an alphabet link table :) &lt;table&gt; &lt;tr&gt; { for $letter in ("a","b","c","d","e","f","g","h","i","j","k","l","m", "n","o","p","q","r","s","t","u","v","w","x","y","z") return &lt;td&gt; &lt;a href="/addressbook/letter/{$letter}"&gt;{$letter}&lt;/a&gt; &lt;/td&gt; } &lt;/tr&gt; &lt;/table&gt; </xquery>
      </operator>
      <target>this:response</target>
    </instr>
    <instr>
      <type>copy</type>
      <operand>this:response#xpointer(/result:sequence/result:element/table)</operand>
      <target>this:response</target>
    </instr>
  </seq>
</idoc>

We want to include the browser bar in the mastertemplate. First we add a new named link to the links.xml file.

<link>
  <name>alphamenu</name>
  <int>active:dpml+operand@ffcpl:/resources/alphabetmenu.idoc</int>
  <args>links,param</args>
</link>

This link has no external part since it is purely an internal component. It's internal URI is an active:dpml which executes the alphabetmenu.idoc in the DPML runtime. Therefore any XRL Include of this link will execute the DPML process to generate the browser bar.

Edit the mastertemplate to xrl:include the alphamenu link like this...

<html xmlns:xrl="http://1060.org/xrl">
  <body>
    <h1>AddressBook</h1>
    <xrl:include href="xrl:alphamenu" />
    <xrl:include href="xrl:content" />
    <div style="font-size: 10px">(C) 2004, 1060 Research Ltd</div>
  </body>
</html>

Try the master template again http://localhost:8080/addressbook/.

Entering data with a form

So far our application is static - we can't add any data and all we're doing is looking at the dynamically generated template but with no content.

Add the following two links to the links.xml document.

<links> ...
  <link>
    <name>form_newentry_main</name>
    <ext>/newentry</ext>
    <int>active:xrl-html+operator@ffcpl:/links.xml+template@ffcpl:/resources/mastertemplate.xml+content@xrl:form_newentry</int>
    <args>links,param</args>
  </link>
  <link>
    <name>form_newentry</name>
    <int>active:dpml+operand@ffcpl:/resources/form_execute.idoc</int>
    <args>links,param</args>
  </link> ...
</links>

The first link maps the external path /newentry to an execution of the XRL runtime. It uses the mastertemplate.xml but it also declares a content resource xrl:form_newentry. This is a reference to the second link, an internal link, which executes a DPML script form_execute.idoc. The result is that a web-browser request for the path /addressbook/newentry will start a cascade of XRL includes, first for the mastertemplate which will then include the content causing the execution of the form_execute.idoc.

XRL recusively pulls URI referenced resources defined by xrl:include declarations - it uses the named links as the primary resource identifier, the resources can be static or, as in the case above, dynamically generated (here by DPML).

Below is the form_execute.idoc and below that is style_form.xsl, the stylesheet to generate the form it will process. Add these files to the resources/ directory.

<idoc>
  <seq>
    <instr>
      <type>copy</type>
      <operand>this:param</operand>
      <target>var:param</target>
    </instr>
    <exception>
      <instr>
        <type>copy</type>
        <operand>
          <nvp />
        </operand>
        <target>var:param</target>
      </instr>
    </exception>
    <if>
      <cond>
        <instr>
          <type>xpatheval</type>
          <operand>var:param</operand>
          <operator>
            <xpath>string-length(/nvp/lastname) &gt; 0</xpath>
          </operator>
          <target>this:cond</target>
        </instr>
      </cond>
      <then>
        <instr>
          <type>SQLEscapeXML</type>
          <operand>var:param</operand>
          <target>var:param</target>
        </instr>
        <if>
          <cond>
            <instr>
              <type>xpatheval</type>
              <operand>var:param</operand>
              <operator>
                <xpath>string-length(/nvp/id) &gt; 0</xpath>
              </operator>
              <target>this:cond</target>
            </instr>
          </cond>
          <then>
            <instr>
              <type>stm</type>
              <operand>
                <sql />
              </operand>
              <operator>
                <stm:group xmlns:stm="http://1060.org/stm">
                  <stm:set xpath="/sql"> UPDATE entries SET "id"=
                    <stm:param xpath="/nvp/id/text()" />, "firstname"='
                    <stm:param xpath="/nvp/firstname/text()" />', "lastname"='
                    <stm:param xpath="/nvp/lastname/text()" />', "add1"='
                    <stm:param xpath="/nvp/add1/text()" />', "add2"='
                    <stm:param xpath="/nvp/add2/text()" />', "add3"='
                    <stm:param xpath="/nvp/add3/text()" />', "add4"='
                    <stm:param xpath="/nvp/add4/text()" />', "region"='
                    <stm:param xpath="/nvp/region/text()" />', "country"='
                    <stm:param xpath="/nvp/country/text()" />', "zip"='
                    <stm:param xpath="/nvp/zip/text()" />', "phone1"='
                    <stm:param xpath="/nvp/phone1/text()" />', "phone2"='
                    <stm:param xpath="/nvp/phone2/text()" />', "phone3"='
                    <stm:param xpath="/nvp/phone3/text()" />', "fax"='
                    <stm:param xpath="/nvp/fax/text()" />', "email1"='
                    <stm:param xpath="/nvp/email1/text()" />', "email2"='
                    <stm:param xpath="/nvp/email2/text()" />', "email3"='
                    <stm:param xpath="/nvp/email3/text()" />', "web1"='
                    <stm:param xpath="/nvp/web1/text()" />', "web2"='
                    <stm:param xpath="/nvp/web2/text()" />', "notes"='
                    <stm:param xpath="/nvp/notes/text()" />' WHERE "id"=
                    <stm:param xpath="/nvp/id/text()" /> ;
                  </stm:set>
                </stm:group>
              </operator>
              <param>var:param</param>
              <target>var:sql</target>
            </instr>
            <instr>
              <type>sqlUpdate</type>
              <operand>var:sql</operand>
              <target>this:response</target>
            </instr>
          </then>
          <else>
            <instr>
              <type>stm</type>
              <operand>
                <sql />
              </operand>
              <operator>
                <stm:group xmlns:stm="http://1060.org/stm">
                  <stm:set xpath="/sql"> INSERT INTO entries VALUES ( null, '
                    <stm:param xpath="/nvp/firstname/text()" />', '
                    <stm:param xpath="/nvp/lastname/text()" />', '
                    <stm:param xpath="/nvp/add1/text()" />', '
                    <stm:param xpath="/nvp/add2/text()" />', '
                    <stm:param xpath="/nvp/add3/text()" />', '
                    <stm:param xpath="/nvp/add4/text()" />', '
                    <stm:param xpath="/nvp/region/text()" />', '
                    <stm:param xpath="/nvp/country/text()" />', '
                    <stm:param xpath="/nvp/zip/text()" />', '
                    <stm:param xpath="/nvp/phone1/text()" />', '
                    <stm:param xpath="/nvp/phone2/text()" />', '
                    <stm:param xpath="/nvp/phone3/text()" />', '
                    <stm:param xpath="/nvp/fax/text()" />', '
                    <stm:param xpath="/nvp/email1/text()" />', '
                    <stm:param xpath="/nvp/email2/text()" />', '
                    <stm:param xpath="/nvp/email3/text()" />', '
                    <stm:param xpath="/nvp/web1/text()" />', '
                    <stm:param xpath="/nvp/web2/text()" />', '
                    <stm:param xpath="/nvp/notes/text()" />' );
                  </stm:set>
                </stm:group>
              </operator>
              <param>var:param</param>
              <target>var:sql</target>
            </instr>
            <instr>
              <type>sqlUpdate</type>
              <operand>var:sql</operand>
              <target>this:response</target>
            </instr>
          </else>
        </if>
        <instr>
          <type>xslt</type>
          <operand>var:param</operand>
          <operator>
            <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
              <xsl:output method="xml" />
              <xsl:template match="/nvp">
                <nvp>
                  <search>
                    <xsl:value-of select="lastname" />
                  </search>
                </nvp>
              </xsl:template>
            </xsl:stylesheet>
          </operator>
          <target>var:param</target>
        </instr>
        <instr>
          <type>dpml</type>
          <operand>search.idoc</operand>
          <param>var:param</param>
          <target>this:response</target>
        </instr>
        <instr>
          <type>cutGoldenThread</type>
          <param>gt:db-update</param>
        </instr>
      </then>
      <else>
        <instr>
          <type>xslt</type>
          <operand>var:param</operand>
          <operator>style_form.xsl</operator>
          <target>this:response</target>
        </instr>
      </else>
    </if>
  </seq>
</idoc>

This script is actually written to support two modes - creating a new entry and editing an existing entry. It gets called from two different places in the web application. First it trys to copy the param argument to a variable. If no param has been received - the case when the form is first shown - then the copy instruction will cause an exception to be thrown. The exception block cactches this exception and copies an empty nvp xml fragment to the var:param variable.

The next stage is to test that the form data is valid - in this case we are just testing that the lastname field has a value - the checking can be much more complex. If there is no lastname - which is always the case when we first run this script - then we see that the last instruction of the script uses XSLT to style var:param resource to the HTML form using the style_form.xsl transform and returned as the response. The dynamically generated form submits to /addressbook/newentry each time - so submissions always get routed by the xrl mapper back to the form_execute.idoc script. Therefore the process will cycle until the validation stage succeeds.

If the form contains a valid submission the script then tests which mode to use - update or create an entry? It determines this by checking the value of the forms id field - this only has a value greater than zero for an existing entry that is being updated. We see that a non-zero condition causes a sql UPDATE statement to be prepared from the form submission data using the stm accessor (a simple value substitution pattern). Alternatively if the entry is new we see that a sql INSERT statement is prepared. again using stm. In both cases the sqlUpdate accessor executes the prepared sql statement to modify the database.

Upon successfully updating the database an xslt operation dynamically generates an nvp xml fragment containing a search tag with the value set to the lastname that was just entered into the database. The nvp fragment is used as a param in a call to DPML to run script search.idoc - this script runs a DB search query for the search term and returns the styled result. The form_execute.idoc returns this wrapped search as its response to show the user their newly entered/updated entry. (The cutGoldenThread instruction will be covered soon).

This is the XSL stylesheet for the new entry form - although it has many fields it is not very complicated!

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
  <xsl:output method="xml" />
  <xsl:template match="/nvp">
    <div>
      <br />
      <form action="newentry" method="POST">
        <xsl:if test="id">
          <input name="id" type="hidden" value="{id}" />
        </xsl:if>
        <table>
          <tr>
            <td colspan="10">
              <xsl:choose>
                <xsl:when test="id">
                  <h3>Edit Entry</h3>
                </xsl:when>
                <xsl:otherwise>
                  <h3>New Entry</h3>
                </xsl:otherwise>
              </xsl:choose>
            </td>
          </tr>
          <tr>
            <td>
              <table>
                <tr>
                  <td>First Name:</td>
                  <td>
                    <input name="firstname" type="text" value="{firstname}" />
                  </td>
                </tr>
                <tr>
                  <td>Last Name
                    <sup>*</sup>:
                  </td>
                  <td>
                    <input name="lastname" type="text" value="{lastname}" />
                  </td>
                </tr>
                <tr>
                  <td>Address:</td>
                  <td>
                    <input name="add1" type="text" value="{add1}" />
                  </td>
                </tr>
                <tr>
                  <td />
                  <td>
                    <input name="add2" type="text" value="{add2}" />
                  </td>
                </tr>
                <tr>
                  <td />
                  <td>
                    <input name="add3" type="text" value="{add3}" />
                  </td>
                </tr>
                <tr>
                  <td />
                  <td>
                    <input name="add4" type="text" value="{add4}" />
                  </td>
                </tr>
                <tr>
                  <td>Region:</td>
                  <td>
                    <input name="region" type="text" value="{region}" />
                  </td>
                </tr>
                <tr>
                  <td>Country:</td>
                  <td>
                    <input name="country" type="text" value="{country}" />
                  </td>
                </tr>
                <tr>
                  <td>Zip:</td>
                  <td>
                    <input name="zip" type="text" value="{zip}" />
                  </td>
                </tr>
              </table>
            </td>
            <td>
              <table>
                <tr>
                  <td>Phone:</td>
                  <td>
                    <input name="phone1" type="text" value="{phone1}" />
                  </td>
                </tr>
                <tr>
                  <td />
                  <td>
                    <input name="phone2" type="text" value="{phone2}" />
                  </td>
                </tr>
                <tr>
                  <td />
                  <td>
                    <input name="phone3" type="text" value="{phone3}" />
                  </td>
                </tr>
                <tr>
                  <td>Fax:</td>
                  <td>
                    <input name="fax" type="text" value="{fax}" />
                  </td>
                </tr>
                <tr>
                  <td>Email:</td>
                  <td>
                    <input name="email1" type="text" value="{email1}" />
                  </td>
                </tr>
                <tr>
                  <td />
                  <td>
                    <input name="email2" type="text" value="{email2}" />
                  </td>
                </tr>
                <tr>
                  <td />
                  <td>
                    <input name="email3" type="text" value="{email3}" />
                  </td>
                </tr>
                <tr>
                  <td>Web:</td>
                  <td>
                    <input name="web1" type="text" value="{web1}" />
                  </td>
                </tr>
                <tr>
                  <td />
                  <td>
                    <input name="web2" type="text" value="{web2}" />
                  </td>
                </tr>
              </table>
            </td>
          </tr>
          <tr>
            <td colspan="10">
              <table style="margin-top:10px;">
                <tr>
                  <td>Notes:</td>
                  <td>
                    <textarea name="notes" style="width:250px;">
                      <xsl:value-of select="notes" />
                    </textarea>
                  </td>
                  <td>
                    <input type="submit">
                      <xsl:choose>
                        <xsl:when test="id">
                          <xsl:attribute name="value">Update</xsl:attribute>
                        </xsl:when>
                        <xsl:otherwise>
                          <xsl:attribute name="value">Create</xsl:attribute>
                        </xsl:otherwise>
                      </xsl:choose>
                    </input>
                  </td>
                </tr>
              </table>
            </td>
          </tr>
          <tr>
            <td>
              <span style="font-size:80%;">* required field</span>
            </td>
          </tr>
        </table>
      </form>
    </div>
  </xsl:template>
</xsl:stylesheet>
[Part 3]
© 2003-2007, 1060 Research Limited. 1060 registered trademark, NetKernel trademark of 1060 Research Limited.