Development Techniques | Professional XML Development with Apache Tools: Xerces, Xalan, FOP, Cocoon, Axis, Xindice (Wrox Professional Guides)

In this section, we’ll focus on two major topics: how to get Cocoon to interact with a relational database, and how to tie together all the concepts we’ve discussed to get a Web application.

Database Access

Most interesting Web applications interact with a database system. The data retrieved from the database is used to generate the Web pages dynamically, and this data, which changes as users interact with it, makes the Web application interesting.

Cocoon provides two mechanisms for interacting with relational databases. First, you can use XSPs to generate XML data from a relational database by using the ESQL logicsheet, which allows you to embed SQL commands into your XSP pages. This allows you to generate reports and fill out forms with data from the database so that the data can be edited. Second, you can use actions. Cocoon contains two sets of database actions—you’ll hear them referred to as the original database actions and the modular database actions. We’ll focus on the modular database actions, because the Cocoon developers recommend that all new applications begin with them.

Getting a Connection

The first thing you need to do to work with a database in Cocoon is get a database connection. Cocoon leverages the Java DataBase Connectivity (JDBC) APIs and drivers to access the database. You need to download a JDBC driver for your database in order to get going. You should install the jar file(s) for this driver in the WEB-INF/lib directory of the Cocoon Web application. Cocoon needs to be told to load the database driver. You can do this by adding a load-class init-param entry to the Web application deployment descriptor (web.xml) found in WEB-INF/web.xml. The entry looks like this:

<init-param>  <param-name>load-class</param-name>  <param-value>org.hsqldb.jdbcDriver</param-value> </init-param>

Supply the fully qualified name of your JDBC driver class as the init-param value. Here we’re showing you the name of the driver for the Hypersonic SQL database that’s included in the Cocoon distribution.

ESQL

Cocoon provides database access to XSPs via the built-in ESQL logicsheet. If you’re going to use ESQL, you need to perform one additional database configuration step. The Cocoon configuration file cocoon.xconf deals with databases in the <datasources> element. You need to add a datasource entry for the database you want to access via ESQL. A datasource entry is a child of the <datasources> element called <jdbc>. Here’s what a datasource entry might look like:

  1: <jdbc name="hsql">   2:  <pool-controller max="10" min="5"/>   3:  <dburl>jdbc:hsqldb:hsql://localhost:9002</dburl>   4:  <user>johndoe</user>   5:  <password>sekret</password>   6: </jdbc>

You need to supply a name attribute for the <jdbc> element. This is the name of the connection and is the name you should give to ESQL when it needs a connection. The <pool-controller> element uses the max and min elements to specify the maximum and minimum number of JDBC driver connections to maintain using Cocoon’s object pooling services. The <dburl> element gives the JDBC URL for accessing the machine, database, and database instance you’re interested in. (Consult the documentation for your JDBC driver to find the correct value to place here.) The last two elements are the username and password required to access the database. Once you’ve created this entry and restarted Cocoon, you should be ready to go. Of course, this assumes you’ve already created the database tables you need and that you’ve inserted any data necessary for your application.

To use the ESQL logicsheet in your XSPs, you need its namespace URI, which is http://apache.org /cocoon/SQL/v2. You should define this on the <xsp:page> element and use the prefix esql. By default, the ESQL logicsheet is defined as a built-in logicsheet, so you shouldn’t need an xml-stylesheet processing instruction to associate the logicsheet with your XSP.

To execute a SQL command in your XSP, insert an <esql:connection> element. This element contains all the information you need to execute a command. You can think of it as being divided into two sections: The first section describes how to get the database connection, and the second section executes the command.

Setting the Connection

There are two ways to specify the connection for an <esql:connection> element. If you’ve defined a datasource for your database in cocoon.xconf, you can use the <esql:pool> element and supply the name of the datasource as the content of the element. Otherwise, you can give all the information for the connection as the following sequence of elements:

<esql:driver>—The fully qualified classname of the JDBC driver for the connection.
<esql:dburl>—The JDBC URL that leads to the database you want to access.
<esql:username>—The username needed to log in to the database.
<esql:password>—The password needed to log in to the database.

With either method of describing the connection, you can control the commit behavior of the connection with an <esql:autocommit> element. If you set it to true (the default), the statement in the connection will be treated as a single transaction and committed. If you set it to false, the statement will be executed without the commit. This element is a direct child of <esql:connection>.

Specifying the SQL Statement

After you’ve specified the connection, you specify the statement you want executed. You do so with the <esql:execute-query> element. This element is a container for a sequence of elements. First, an <esql:query> element is used to specify the SQL statement. If the statement returns a result set, then you should provide an <esql:results> element. If the statement is an update, then you should provide an <esql:update-result> element instead of the <esql:results> element. If the statement is a stored procedure, then you should provide an <esql:call-results> element. You can provide an optional <esql:error-results> element to produce output if an error occurs. Let’s look at each of these elements in detail.

The <esql:query> element takes a SQL statement as its content. If you want to have dynamically generated queries (let’s say you want to generate the ORDER BY potion of a query), you can use the <esql:parameter> element to mark out a dynamic value. The content of the <esql:parameter> is then an <xsp:expr> element containing the code that computes the value for that portion of the query. When you use the <esql:parameter> element, the ESQL logicsheet uses JDBC PreparedStatements to execute the statement, which provides better protection against SQL injection attacks. If you need to dynamically generate portions of the query, then you can use <xsp:expr> to append a string to the content of <esql:query>; but be aware that this approach can post a security risk, especially if the data used in the <xsp:expr> element originated in the user agent.

Query Results

Once you’ve specified the statement to be executed, you need to describe what to do about the results. There are a number of choices. If the statement you executed is a query, then one possible result is a result set. The ESQL logicsheet provides you with the <esql:results> element as a way of formatting the result set. The child of <esql:results> is a mix of your own application-specific elements and elements taken from the <esql:namespace>. The ESQL elements let you mark the beginning and end of a row in the rowset as well as access the columns within a row. You enclose the elements that should make up a single row in the start and end tags of an <esql:row-results> element. Within this element are type-specific elements you use to retrieve the value of a column. Here’s a list of those elements—each takes an attribute named column that specifies the column being accessed:

<esql:get-string>—Get the column value as a string.
<esql:get-int>—Get the column value as an integer.
<esql:get-short>—Get the column value as a short.
<esql:get-long>—Get the column value as a long.
<esql:get-double>—Get the column value as a double.
<esql:get-float>—Get the column value as a float.
<esql:get-boolean>—Get the column value as a boolean.
<esql:get-date>—Get the column value as a date. If there is a format attribute, use its value as a java.text.SimpleDateFormat format string and format the value with it.
<esql:get-time>—Get the column value as a time. If there is a format attribute, use its value as a java.text.SimpleDateFormat format string and format the value with it.
<esql:get-timestamp>—Get the column value as a timestamp. If there is a format attribute, use its value as a java.text.SimpleDateFormat format string and format the value with it.
<esql-get-ascii>—Get the column value as a CLOB.
<esql:get-object>—Get the column value as an object.
<esql:get-array>—Get the column value as a java.sql.Array.
<esql:get-struct>—Get the column value as a java.sql.Struct.
<esql:get-xml>—Get the column value as XML.
<esql:is-null>—Return true if the column value is null.

Other elements you can use as children of the <esql:row-result> element are as follows:

<esql:get-columns>—Return a sequence of elements. Each element’s name is the name of a column in the table.
<esql:get-column-name>—Return the name of the column. The column attribute must be a number, not a name.
<esql:get-column-label>—Return the label of the column. The column attribute must be a number, not a name.
<esql:get-column-type-name>—Return the data type of the column. The column attribute must be a number, not a name.

You should also provide an <esql:no-results> element (at the same level as <esql:results>). The content of this element is output if the statement produces no results.

If your statement was an update statement, you need to provide <esql:update-results>. You provide your own content for this element. You can use the <esql:get-update-count> element as a child of <esql:update-results>. Doing so puts the number of updated rows into your XSP.

Errors

If an error occurs, the output is produced via an <esql:error-results> element. As usual, the child content of this element becomes the output for your XSP. You can use a few ESQL elements as children of <esql:error-result>:

<esql:get-message>—Get the message from the exception that was thrown.
<esql:to-string>—Convert the thrown exception to a String and insert that string in the output.
<esql:get-stacktrace>—Copy the stacktrace of the thrown exception into the output.

Stored Procedures

The ESQL logicsheet also lets you call stored procedures. Doing this changes the contents of the <esql:execute-query> element. Instead of an <esql:query> child, you’ll have an <esql:call> element. The content of this element is the stored procedure call. Some JDBC drivers use executeQuery rather than execute to invoke a stored procedure. If this is true of your JDBC driver, then you need to add a needs-query attribute to the <esql:call> element and set its value to "true".

You still use the <esql:parameter> element to supply the parameters for the stored procedure. Because stored procedure parameters have a direction (in, out, or inout), you can supply a direction attribute on an <esql:parameter> element. The value of this attribute is "in", "out", or "inout".

Stored procedures can return their results in a variety of ways. They can return a result set as the return parameter of the procedure invocation, or they can return several out parameter. If the procedure returns a result set via the return parameter, then you need to set the resultset-from-object attribute on <esql:call> to the value "1". You can then use the <esql:results> element to work with the results. You need to set the from-call attribute to the value "yes" on all the <esql:get-<type>/> elements. This tells them to get their values from the JDBC CallableStatement rather than the usual ResultSet.

If the stored procedure returns multiple out parameters, then you have two options for processing the results. If you only supply a single <esql:results> element, it will be reused for each out parameter, but this assumes that all the out parameters are result sets. If the output parameters are a sequence whose elements can be either result sets or update counts, then you can supply multiple <esql:results> and <esql:update-results> elements. You place them in the same order as the out parameters, so that an <esql:results> corresponds to a resultset out parameter and an <esql:update-results> corresponds to an update count out parameter. You can pair the <esql:results> or <esql:update-results> with an <esql:no-results> element. This functionality works only if you create a child element of <esql:connection> named <esql:allow-multiple-results> and set its content to "yes".

Example

Here’s an example of the ESQL logicsheet in action. Let’s assume you have a database of information on book publishers and you want to extract all the names and addresses:

  1: <?cocoon-process type="xsp"?>   2:    3: <xsp:page   4:   language="java"   5:   xmlns:xsp="http://apache.org/xsp"   6:   xmlns:esql="http://apache.org/cocoon/SQL/v2">   7:  <page>   8:   <esql:connection>   9:    <esql:pool>books</esql:pool>  10:    <esql:execute-query>  11:     <esql:query>SELECT name, address FROM publishers</esql:query>  12:     <esql:results>  13:      <table>  14:       <esql:row-results>  15:        <tr>  16:         <td><esql:get-string column="name"/></td>  17:         <td><esql:get-string column="address"/></td>  18:        </tr>  19:       </esql:row-results>  20:      </table>  21:     </esql:results>  22:     <esql:no-results>  23:      <table/>  24:     </esql:no-results>  25:    </esql:execute-query>  26:   </esql:connection>  27:  </page>  28: </xsp:page>

In line 9, you use <esql:pool> to set up the JDBC connection. The query you’re executing is simple and straightforward (line 11). The real meat is in lines 12-21, where the results are described, and lines 22-24, where the non-results are described. Notice how the content for <esql:results> has elements like <table>, <tr>, and <td> interspersed with elements like <esql:row-results> and <esql:get-string>. For the non-results in lines 22-24, you output an empty table element.

The XML produced by this XSP looks like this:

  1: <?xml version="1.0" encoding="UTF-8"?>   2: <page xmlns:xsp="http://apache.org/xsp"    3:       xmlns:xspdoc="http://apache.org/cocoon/XSPDoc/v1"    4:       xmlns:esql="http://apache.org/cocoon/SQL/v2">   5:      6:       7:       8:      <table>   9:         10:        <tr>  11:         <td>Addison-Wesley</td>  12:         <td>New York, New York</td>  13:        </tr>  14:         15:        <tr>  16:         <td>Addison-Wesley</td>  17:   18:         <td>Reading, Massachusetts</td>  19:        </tr>  20:         21:        <tr>  22:         <td>John Wiley and Sons</td>  23:         <td>New York, New York</td>  24:        </tr>  25:         26:      </table>  27:   28:       29:     30:  </page>

The only editing we performed on the file was to format the namespace declarations so they fit nicely onto lines.

Database Actions

Now let’s look at the other way of dealing with a SQL database from Cocoon: the modular database actions. The database actions in general work on a simple proposition. They look for parameters via an input module and use a descriptor table to map the parameter values onto a SQL table or tables. Each database action represents a single operation on a database table: an add (insert), delete, or update. So, once the parameter mapping is done, you already know what operation is to be performed, and you execute the appropriate SQL statement.

The modular database actions use Cocoon’s input and output modules to decouple the handling of input and output parameters from the processing of the mapped parameters and the SQL statement. Cocoon provides input modules that let you obtain data from an HttpServletRequest, which makes it easy to pick the parameter values out of a request and pass them to the action for execution. We’ll look at the mapping descriptor file, examine the supported actions, and work through a simple example.

The Descriptor File

The descriptor file for the database is an XML file that has three sections. The root element of the file is named <root> by convention, but you can name it anything you choose. The first section of the file is the single element <connection>, whose content is the name of a database source that has been defined in the cocoon.xconf file.

The second section of the file is a sequence of <table> elements. The table element has a name attribute and an optional alias attribute. The name attribute is the name of the database table to which this element corresponds. The alias attribute allows you to specify an additional name for this table, and you can use this additional name anywhere you can use the name. You can create a shorter name for the table or use the alias only for certain operations on the table.

The <table> element has two children: <keys> and <values>. The <keys> element contains a sequence of <key> elements, which are supposed to represent the key columns in the table. A <key> element has three attributes: name is the name of the column in the table, type is the type of the column in the table, and autoincrement is true if the column is an autoincrement column. You can use the following types as values of the type attribute:

string
big-decimal, byte, double, float, int, long, short
boolean
date, time, time-stamp
ascii, blob, binary, clob
array, object, row

Each <key> element can have zero or more child <mode> elements. A <mode> element has a name attribute that specifies the name of the input module to consult in order to handle this <key>. The <mode> element also takes a type attribute. There are two mode types: autoincr (autoincrement) and others (for all other operations). There is also a universal mode name, all, which matches any mode.

The <values> element contains a <value> element for each column of the table that isn’t a key column. A <value> element takes a name attribute that determines the column in the table and a type element that specifies the type of the column. It uses the same type names as the <key> element. <value> elements can also have nested <mode> elements.

The third and final section of the descriptor file is a sequence of <table-set> elements. A <table-set> element has a sequence of <table> elements as children. This list of tables specifies a group of tables to be operated on as a unit. Each <table> element has a name attribute whose value must be a table that was declared by a <table> element in the second section. A <table> element can also have an others-mode attribute, which can take the value of any <mode> type that was used in a <key> or <value> element for that table. It signifies that the table is using that mode, and that only those elements with child <mode> elements corresponding to that mode type will be affected.

Here’s a simple descriptor file:

  1: <?xml version="1.0"?>   2: <root>   3:  <connection>books</connection>   4:    5:  <table name="publishers" alias="publishers">   6:   <keys>   7:    <key name="id" type="int" autoincrement="true">   8:     <mode name="auto" type="autoincr"/>   9:    </key>  10:   </keys>  11:   <values>  12:    <value name="name" type="string"/>  13:    <value name="address" type="string"/>  14:   </values>  15:  </table>  16:   17:  <table-set name="publishers">  18:      <table name="publishers"/>  19:  </table-set>  20: </root>

You can see the <connection> element in line 3, the <table> element in lines 5-15, and the <table-set> element in lines 17-20. Notice that in line 8, you use a <mode> element to mark the id column as being an autoincrementing column.

The Modular Actions

The modular database actions are all in the package org.apache.cocoon.acting.modular. There are five actions:

DatabaseAddAction—This action inserts one or more new rows into one or more tables.
DatabaseDeleteAction—This action deletes one or more rows from one or more tables.
DatabaseQueryAction—This action executes an arbitrary query, including update queries. You associate the queries with tables by adding a <queries> child to the <table> element before the <keys> element. The children of <queries> are <query> elements. These elements take a mode attribute whose value is used to select the query when the action is executed. The query is the content of the <query> element. You can use the ? placeholder for a PreparedStatement in the query. The data that’s retrieved is available to the sitemap as parameters.
DatabaseSelectAction—This action selects one or more rows from one or more tables. The data that’s retrieved is available to the sitemap as parameters.
DatabaseUpdateAction—This action updates one or more row in one or more tables.

You need to declare the modular database actions in the <map:actions> section of your sitemap before you can use them. When you use one of the modular database actions, you need to supply a <map:parameter> child element that specifies the table-set the action should operate on. The actual input parameters for the action are delivered by a Cocoon input module. By default, this data comes from the request parameters.

Example

Here’s a simple example that combines the list-pubs.xsp you saw as the ESQL example with the use of the modular database actions to perform insertion on the database. It’s a common strategy to use an ESQL XSP to generate listings of data (using SELECT and so forth) and to use the modular database actions to process the modification operations such as insertion, deletion, and updates. The modular actions make it easy to take form parameters and use them to modify the contents of database tables.

Everything in Cocoon starts with the sitemap, so here’s the sitemap file:

  1: <?xml version="1.0" encoding="UTF-8"?>   2: <map:sitemap xmlns:map="http://apache.org/cocoon/sitemap/1.0">   3:  <map:components>   4:   <map:actions>   5:    <map:action name="mod-db-add"   6:     src="/books/2/639/1/html/2/org.apache.cocoon.acting.modular.DatabaseAddAction">   7:     <descriptor>database.xml</descriptor>   8:     <throw-exception>true</throw-exception>   9:    </map:action>  10:   11:    <map:action name="req-params"  12:     src="/books/2/639/1/html/2/org.apache.cocoon.acting.RequestParameterExistsAction"/>  13:   </map:actions>  14:  </map:components>  15:   16:  <map:pipelines>  17:   18:   <map:pipeline>  19:   20:     <map:match pattern="list-pubs.xsp">  21:      <map:generate type="serverpages" src="/books/2/639/1/html/2/list-pubs.xsp"/>  22:       <map:transform src="/books/2/639/1/html/2/form.xslt">  23:       <map:parameter name="contextPath"  24:                      value="{request:contextPath}"/>  25:      </map:transform>  26:      <map:serialize/>  27:     </map:match>  28:   29:     <map:match pattern="add-pub">  30:      <map:generate type="file" src="/books/2/639/1/html/2/add-pub.xml"/>  31:      <map:transform src="/books/2/639/1/html/2/form.xslt"/>  32:      <map:serialize/>  33:     </map:match>  34:   35:     <map:match pattern="form">  36:      <map:act type="req-params">  37:       <map:parameter name="parameters"   38:        value="add-publisher publishers.name publishers.address"/>  39:       <map:act type="mod-db-add">  40:        <map:parameter name="table-set" value="publishers"/>  41:       </map:act>  42:      </map:act>  43:      <map:redirect-to uri="list-pubs.xsp" />  44:     </map:match>  45:   46:   </map:pipeline>  47:   48:  </map:pipelines>  49:   50: </map:sitemap>

In the <map:actions> section (lines 4-13), you declare two actions. The first (lines 5-9) is the modular database action for adding rows to tables, org.apache.cocoon.acting.modular.DatabaseAction. This action needs a few parameters, which appear as child elements. The <descriptor> element tells the action where to find the database descriptor file used by all the modular actions. This example uses the descriptor file we showed you in the section on the descriptor file. To make debugging a little easier, you set the <throw-exception> parameter to true.

In lines 11-12, you also declare the org.apache.cocoon.acting.RequestParameterExistsAction. This action checks to make sure a particular set of request parameters exists. This is a concise stand-in for using the FormValidation action.

After the components are declared, all you have left is the pipelines. You’re declaring multiple pipelines within a single <map:pipeline> element using multiple <map:match> elements. Each <map:match> element controls a distinct pipeline. By looking at the pattern attributes of the <map:match> elements, you can see how the sitemap’s URI space is partitioned. This sitemap processes three URIs: list-pubs.xml, add-pub, and form.

The pipeline for list-pubs.xsp (lines 20-27) is straightforward. It uses the XSP serverpages generator to make list-pubs.xsp the generator for the pipeline. The XSLT stylesheet form.xslt is used to convert the output of list-pubs.xsp into HTML, which is then serialized by the default (HTML) serializer.

The URI add-pub takes the user’s browser to a form they can use to add a new publisher to the database (the pipeline is on lines 29-33). This form is described by an XML file, add-pub.xml, that lives in the same directory as the sitemap. You use the file generator to scoop up this file and send it into the pipeline. The XML file goes through form.xslt to become HTML, which again is output using the default serializer.

The last URI, form, is the URI for the input form’s action. When the user clicks the Add Publisher button in the HTML version of add-pub.xml, they are sent to this pipeline (lines 35-44). The first thing that happens in this pipeline is that the req-param action is executed. The <map:parameter> for this action says that the parameters add-publisher, publishers.name, and publishers.address must all be present in the request. If they are, then the mod-db-add action is executed. This is the modular database action for adding to the database. Its <map:parameter> says that it should use the publishers table-set, which is defined in the database.xml file. The request parameters publishers.name and publishers.address contain the values for the corresponding columns in that table-set and are used to perform an insert on the database. After the insert has been performed, the pipeline redirects the user’s browser to the list-pubs.xsp URI so they can see a display of the new publisher entry.

Let’s look at a few pieces of glue. Here’s the content of add-pub.xml. You can see that it looks like a subset of HTML. There’s not a lot of point in defining a completely new vocabulary when most of what you’re doing involves HTML—it’s easier to be able to insert HTML elements into the page’s XML form and let the stylesheet take care of doing the formatting of the HTML:

  1: <page>   2:  <p>   3:   Please enter the name and address of the new publisher:   4:  </p>   5:  <form action="form">   6: Name:  <input type="text" name="publishers.name"/>   7:   <br />   8: Publisher:  <input type="text" name="publishers.address"/>   9:   <br />  10:   <br />  11:   <input type="submit"   12:    name="add-publisher" value="Add publisher"/>  13:  </form>  14: </page>

The big thing going on in add-pub.xml is the <form>. Here’s where you see all the pieces of the sitemap tied together. The action attribute of the <form> element directs the user’s browser to the pipeline that does the work of adding the new publisher to the database. The text <input> elements are named to match the columns in the table to be inserted into, so that the modular database add action can pick up the values from the form submit request. The name attribute of the <submit> element provides the final form parameter the req-param action is looking for.

Here’s form.xslt, the simple stylesheeet we’re using:

  1: <?xml version="1.0"?>    2:  <xsl:stylesheet version="1.0"   3:    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">   4:    5:   <xsl:template match="page">   6:    <html>   7:     <head></head>   8:     <body>   9:      <xsl:apply-templates/>  10:     </body>  11:    </html>  12:   </xsl:template>  13:   14:   <xsl:template match="@*|node()" priority="-1">  15:    <xsl:copy>  16:     <xsl:apply-templates select="@*|node()"/>  17:    </xsl:copy>  18:   </xsl:template>  19:   20: </xsl:stylesheet>

A default template (lines 14-18) copies the contents and attributes of an element into the output document unless there’s a more specific template in the stylesheet. The stylesheet includes only one other template (lines 5-12), and that’s for handling the <page> element, which is the root element of the add-forms.xml file. This template rule creates an HTML template and then invokes <xsl:apply-templates/> to process the children of <page>, which are copied into the output HTML document verbatim thanks to the default template.

Simple Application

In this section we’ll look at using Cocoon to create a database-driven application. We won’t use every Cocoon concept we’ve talked about, but we’ll use quite a few. The application provides the following functionality:

Add a new book.
Add a new book.publisher.
List all the books. From the listing, you can edit a book or delete a book.
List all the book publishers. From the listing, you can edit a publisher or delete a publisher.

Database Table Definitions

This application is based on the books schema we’ve been using throughout this book. This time, you’ll translate it into a set of database tables and build a simple book list management program. The first thing you need, then, is a set of database tables. Let’s use the Hypersonic SQL database that comes built into the Cocoon distribution, for convenience. Because publishers publish many books, you’ll break out the publisher information from the book information and refer to it via a foreign key. That leaves you with the following two tables; both use Hypersonic SQL’s IDENTITY keyword to obtain autoincrementing behavior for row IDs:

CREATE TABLE publishers(  id IDENTITY,  name VARCHAR,  address VARCHAR ) CREATE TABLE books(  id IDENTITY,  author VARCHAR,  title VARCHAR,  isbn VARCHAR,  month VARCHAR,  year INTEGER,  pub_id INTEGER )

Sample Data Set

You also need some data, so you’ll execute the following set of SQL statements against the database. You can do this by obtaining a standalone Hypersonic SQL distribution (http://hsqldb.sourceforge.net) and using the DatabaseManager class to get a command-line interface to the database. Be sure to connect with the correct server parameters (you can get them from the JDBC entry, explained later):

INSERT INTO publishers  VALUES(1,'Addison-Wesley','New York, New York') INSERT INTO publishers  VALUES(2,'Addison-Wesley','Reading, Massachusetts') INSERT INTO publishers  VALUES(3,'Wrox','Indianapolis, Indiana') INSERT INTO books  VALUES(1,'Theodore W. Leung', Professional XML Development with  Apache Tools', '0-7645-4355-5','October',2003,3) INSERT INTO books  VALUES(2,'Joshua Bloch','Effective Java','0-201-31005- 8','August',2001,1) INSERT INTO books  VALUES(3,' Erich Gamma, Richard Helm, Ralph Johnson, John Vlissides  ','Design Patterns','0-201-63361-2','October',1994,2)

cocoon.xconf

Once you’ve created the tables, you’re ready to tell Cocoon about the database. To do this, you need to create a <jdbc> element that’s a child of the <datasources> element in WEB-INF/cocoon.xconf. Here’s the entry to add (assuming you haven’t altered your Cocoon configuration too much):

  1: <jdbc name="books">   2:  <pool-controller max="10" min="5"/>   3:  <dburl>jdbc:hsqldb:hsql://localhost:9002</dburl>   4:  <user>sa</user>   5:  <password/>   6: </jdbc>

database.xml

You’ll use a combination of ESQL logicsheets and the modular database actions to get the job done. The ESQL XSPs query information from the database and format it into HTML forms, while the modular database actions take care of all insert, update, and delete behaviors. To use the modular actions, you need a database descriptor file, database.xml. This file is an extension of the one you used in the example for the modular actions. The <table> and <table-set> elements for publishers are unchanged from that version, but the <table> and <table-set> elements for the books table are new. Nothing fancy is going on in either set of entries, though. The mode of the ID columns is set for autoincrement, but that’s about all:

  1: <?xml version="1.0"?>   2: <root>   3:  <connection>books</connection>   4:    5:  <table name="publishers" alias="publishers">   6:   <keys>   7:    <key name="id" type="int" autoincrement="true">   8:     <mode name="auto" type="autoincr"/>   9:    </key>  10:   </keys>  11:   <values>  12:    <value name="name" type="string"/>  13:    <value name="address" type="string"/>  14:   </values>  15:  </table>  16:   17:  <table name="books" alias="books">  18:   <keys>  19:    <key name="id" type="int" autoincrement="true">  20:      <mode name="auto" type="autoincr"/>  21:    </key>  22:   </keys>  23:   <values>  24:    <value name="author" type="string"/>  25:    <value name="title" type="string"/>  26:    <value name="isbn" type="string"/>  27:    <value name="month" type="string"/>  28:    <value name="year" type="int"/>  29:    <value name="pub_id" type="int"/>  30:   </values>  31:  </table>  32:   33:  <table-set name="publishers">  34:   <table name="publishers"/>  35:  </table-set>  36:   37:  <table-set name="books">  38:   <table name="books"/>  39:  </table-set>  40: </root>

sitemap.xmap

As always, understanding the sitemap is essential to understanding how the application works. In the <components> section of this sitemap, you define three of the modular database actions: add, delete, and update. You also use the RequestParameterExistsAction (lines 23-25) to verify that the necessary parameters are passed to the database actions. The remainder of the sitemap is the pipeline section, which is devoted to splitting out responsibility for the Web application’s URI space. Four match clauses are used to partition the URI space:

""—The empty URI, which corresponds to accessing the top of the space for this sitemap, redirects to the URI index.xml (lines 32-34).
*.xml—Any URI that ends with XML is processed by looking for a file whose name matches the *.xml part of the URI, processing that file with the XSLT stylesheet form.xslt, and serializing the results as HTML. There are such files: index.xml and add-pub.xml. The file index.xml creates a top-level menu that can be used to perform some of the application functions. The file add-pub.xml implements the form that’s used to add a new publisher.
*.xsp—Any URI that ends with .xsp is an XSP and is processed using the serverpages generator. The resulting XML is also transformed using form.xslt and rendered as HTML. The application includes five XSPs:
- add-book.xsp implements the form used to add a new book.
- list-books.xsp generates a listing of all the books in the database.
- list-publishers.xsp generates a listing of all the publishers in the database.
- edit-books.xsp implements the form used to edit a book.
- edit-publisher.xsp implements the form used to edit a publisher.
.*book.* | .*publisher.*—Any URI that contains the word book or publisher is directed into this pipeline (lines 51-104). The pipeline uses the regexp matcher instead of the wildcard matcher to accomplish this.

The pipeline consists of a guarded set of database action invocations. Each invocation is guarded by a call to the req-params action, which is used to ensure that the correct set of parameters for each action is present. This allows all the actions to go through the pipeline, but only the one that is relevant is executed. The key is to note that the first parameter in every request parameter check is the name of the operation being performed. If the guard is triggered (by having the right operation name and all the table parameters), then the modular database action executes. The actions are told which table set to use in order to perform the right operation. The pipeline ends by returning the user to the main menu via a redirect (line 103).

Here’s the code for the sitemap:

  1: <?xml version="1.0" encoding="UTF-8"?>   2: <map:sitemap xmlns:map="http://apache.org/cocoon/sitemap/1.0">   3:  <map:components>   4:   <map:actions>   5:    <map:action name="mod-db-add"    6:     src="/books/2/639/1/html/2/org.apache.cocoon.acting.modular.DatabaseAddAction">   7:     <descriptor>database.xml</descriptor>   8:     <throw-exception>true</throw-exception>   9:    </map:action>  10:   11:    <map:action name="mod-db-delete"  12:     src="/books/2/639/1/html/2/org.apache.cocoon.acting.modular.DatabaseDeleteAction">  13:     <descriptor>database.xml</descriptor>  14:     <throw-exception>false</throw-exception>  15:    </map:action>  16:   17:    <map:action name="mod-db-update"   18:     src="/books/2/639/1/html/2/org.apache.cocoon.acting.modular.DatabaseUpdateAction">  19:     <descriptor>database.xml</descriptor>  20:     <throw-exception>false</throw-exception>  21:    </map:action>  22:   23:    <map:action name="req-params"  24:     src="/books/2/639/1/html/2/org.apache.cocoon.acting.RequestParameterExistsAction"/>  25:   </map:actions>  26:  </map:components>  27:   28:  <map:pipelines>  29:   30:   <map:pipeline>  31:   32:     <map:match pattern="">  33:      <map:redirect-to uri="index.xml"/>  34:     </map:match>  35:   36:     <map:match pattern="*.xml">  37:      <map:generate type="file" src="/books/2/639/1/html/2/{1}.xml"/>  38:      <map:transform src="/books/2/639/1/html/2/form.xslt"/>  39:      <map:serialize/>  40:     </map:match>  41:   42:     <map:match pattern="*.xsp">  43:      <map:generate type="serverpages" src="/books/2/639/1/html/2/{1}.xsp"/>  44:       <map:transform src="/books/2/639/1/html/2/form.xslt">  45:       <map:parameter name="contextPath"  46:                      value="{request:contextPath}"/>  47:      </map:transform>  48:      <map:serialize/>  49:     </map:match>  50:   51:     <map:match type="regexp" pattern=".*book.*|.*publisher.*">  52:      <map:act type="req-params">  53:       <map:parameter name="parameters"   54:        value="add-publisher publishers.name publishers.address"/>  55:       <map:act type="mod-db-add">  56:        <map:parameter name="table-set" value="publishers"/>  57:       </map:act>  58:      </map:act>  59:   60:      <map:act type="req-params">  61:       <map:parameter name="parameters"   62:        value="add-book books.author books.title books.isbn  63:               books.month books.year books.pub_id"/>  64:       <map:act type="mod-db-add">  65:        <map:parameter name="table-set" value="books"/>  66:       </map:act>  67:      </map:act>  68:   69:      <map:act type="req-params">  70:       <map:parameter name="parameters"  71:        value="delete-publisher publishers.id"/>  72:       <map:act type="mod-db-delete">  73:        <map:parameter name="table-set" value="publishers"/>  74:       </map:act>  75:      </map:act>  76:   77:      <map:act type="req-params">  78:       <map:parameter name="parameters"  79:        value="delete-book books.id"/>  80:       <map:act type="mod-db-delete">  81:        <map:parameter name="table-set" value="books"/>  82:       </map:act>  83:      </map:act>  84:   85:      <map:act type="req-params">  86:       <map:parameter name="parameters"  87:        value="update-publisher publishers.name  88:               publishers.address"/>  89:       <map:act type="mod-db-update">  90:        <map:parameter name="table-set" value="publishers"/>  91:       </map:act>  92:      </map:act>  93:   94:      <map:act type="req-params">  95:       <map:parameter name="parameters"  96:        value="update-book books.author books.title books.isbn  97:               books.month books.year books.pub_id"/>  98:       <map:act type="mod-db-update">  99:        <map:parameter name="table-set" value="books"/> 100:       </map:act> 101:      </map:act> 102:  103:      <map:redirect-to uri="index.xml"/> 104:     </map:match> 105:  106:   </map:pipeline> 107:  108:  </map:pipelines> 109:  110: </map:sitemap>

index.xml

The form.xslt stylesheet assumes that most of the content of the input XML is close to HTML and that most of it will pass right through. In the case of index.xml, a message gives the user instructions, and some links lead to forms for performing operations. The <page> element is transformed into the HTML boilerplate:

  1: <page>   2:  <p>   3:   Please select the operation you want to perform:   4:  </p>   5:  <a href="add-book.xsp">Add a book</a> <br />   6:  <a href="add-pub.xml">Add a publisher</a> <br />   7:  <a href="list-books.xsp">Show books</a> <br />   8:  <a href="list-publishers.xsp">Show publishers</a> <br />   9: </page>

add-pub.xml

This is a simple form that allows the user to enter the name and address of a publisher. Note that the form action is add-publisher-action, which doesn’t refer to a file. It’s a URI that falls into the URI space handled by the action pipeline. The names of the text <input> elements correspond to columns in the publishers table, and the name of the submit <input> element provides the operation name for the action pipeline. When the user clicks Submit, the operation name and the table parameter are sent to the action pipeline, where they’re picked up by the correct action (the operation name check ensures this):

  1: <page>   2:  <p>   3:   Please enter the name and address of the new publisher:   4:  </p>   5:  <form action="add-publisher-action">   6: Name:  <input type="text" name="publishers.name"/>   7:   <br />   8: Publisher:  <input type="text" name="publishers.address"/>   9:   <br />  10:   <br />  11:   <input type="submit"   12:    name="add-publisher" value="Add publisher"/>  13:  </form>  14: </page>

list-publishers.xsp

List-publishers.xsp uses ESQL to return a result table that includes all the publisher entries in the database. It’s a straightforward application of the ESQL logicsheet. The one tricky thing going on is the use of an extra table column (lines 23-42) to handle the edit and delete functionality:

  1: <?cocoon-process type="xsp"?>   2:    3: <xsp:page   4:   language="java"   5:   xmlns:xsp="http://apache.org/xsp"   6:   xmlns:esql="http://apache.org/cocoon/SQL/v2">   7:  <page>   8:   <esql:connection>   9:    <esql:pool>books</esql:pool>  10:    <esql:execute-query>  11:     <esql:query>  12:      SELECT id, name, address FROM publishers  13:     </esql:query>  14:     <esql:results>  15:      <table border="1" cellspacing="0">  16:       <tr>  17:        <td>Name</td><td>Address</td>  18:       </tr>  19:       <esql:row-results>  20:        <tr>  21:         <td><esql:get-string column="name"/></td>  22:         <td><esql:get-string column="address"/></td>  23:         <td>  24:          <form action="edit-publisher.xsp">  25:           <input type="hidden" name="publishers.id">  26:            <xsp:attribute name="value">  27:             <esql:get-int column="id"/>  28:            </xsp:attribute>  29:           </input>  30:           <input type="submit"  31:            name="edit-publisher" value="edit"/>  32:          </form>  33:          <form action="delete-publisher-action">  34:           <input type="hidden" name="publishers.id">  35:            <xsp:attribute name="value">  36:             <esql:get-int column="id"/>  37:            </xsp:attribute>  38:           </input>  39:           <input type="submit"  40:            name="delete-publisher" value="delete"/>  41:          </form>  42:         </td>  43:        </tr>  44:       </esql:row-results>  45:      </table>  46:     </esql:results>  47:     <esql:no-results>  48:      <table/>  49:     </esql:no-results>  50:    </esql:execute-query>  51:   </esql:connection>  52:  </page>  53: </xsp:page>

The editing function is handled by embedding a form that has a hidden input field whose value is the ID of the publisher in that row of the result table. When the user submits the edit form, the publisher ID is sent to edit-publisher.xsp, which takes care of the user interface for changing the form data.

The deleting function is implemented much the same way, but the form action doesn’t go to another page the way it does for editing. Instead, the delete form action is another of the URIs, delete-publisher-action, that matches the action pipeline. The publisher’s ID is passed to the action along with the delete-publisher command name.

edit-book.xsp

The edit-book.xsp file is a combination of ESQL and modular actions. The page uses the ESQL logicsheet to bring up a form whose values are already filled in, so the user can edit them. To do this, it takes the publisher ID that was passed as a form parameter (line 20) and uses it in the WHERE clause of the SQL query (lines 18-20). The ESQL elements are mixed in with the form elements to produce the filled-in form; you use the <xsp:attribute> element to generate the value attribute on the <input> elements, which is how the filled-in values get into the form:

  1: <?cocoon-process type="xsp"?>   2:    3: <xsp:page   4:   language="java"   5:   xmlns:xsp="http://apache.org/xsp"   6:   xmlns:esql="http://apache.org/cocoon/SQL/v2"   7:   xmlns:xsp-request="http://apache.org/xsp/request/2.0">   8:    9:  <page>  10:   <p>  11:    Please change the name and address of the publisher:  12:   </p>  13:   <form action="update-publisher-action">  14:    <esql:connection>  15:     <esql:pool>books</esql:pool>  16:     <esql:execute-query>  17:      <esql:query>  18: SELECT name, address  19: FROM publishers  20: WHERE id = <xsp-request:get-parameter name="publishers.id"/>  21:      </esql:query>  22:      <esql:results>  23:       <esql:row-results>  24:        <input type="hidden" name="publishers.id">  25:         <xsp:attribute name="value">  26:          <xsp-request:get-parameter name="publishers.id"/>  27:         </xsp:attribute>  28:        </input>  29:   30: Name:   31:        <input type="text" name="publishers.name">  32:         <xsp:attribute name="value">  33:          <esql:get-string column="name"/>  34:         </xsp:attribute>  35:        </input>  36:        <br />  37: Publisher:    38:        <input type="text" name="publishers.address">  39:         <xsp:attribute name="value">  40:          <esql:get-string column="address"/>  41:         </xsp:attribute>  42:        </input>  43:        <br />  44:        <br />  45:        <input type="submit"   46:         name="update-publisher" value="Edit publisher"/>  47:       </esql:row-results>  48:      </esql:results>  49:     </esql:execute-query>  50:    </esql:connection>  51:   </form>  52:  </page>

  53: </xsp:page>

Note

This form has been somewhat pretty-printed for presentation in the book, which has introduced some spaces into the <xsp:attribute> elements. The real versions of these files don’t contain those spaces.

The form’s fields have the same names as the corresponding columns in the publishers table. The submit <input> element provides an operation name, and the form action (update-publisher-action) is set up to funnel that information to the action pipeline in the sitemap to perform the update.

The pages for the publisher side show the basic idea of how ESQL logicsheet elements, form actions, modular database actions, and the sitemap collaborate to make adding, listing, editing, and deleting data quite easy. The pages for the book side show how to achieve more complicated user interface effects using the same approach.

add-book.xsp

This form is similar to the add form for publishers. There are <input> fields for every column in the book table, except pub_id. The user interface for this form presents a drop-down list of all the publishers so the user can select one and have the interface figure out which ID to put in the pub_id column.

So, in addition to the regular form logic, an ESQL logicsheet query (lines 31-46) retrieves the ID and name for each publisher in the database and uses that information to build a drop-down menu of publisher names. The <option> element’s value attribute is set to the publisher’s ID (lines 38-40), and its content is the name of the publisher.

Note again the name of the form action (add-book-action) and the name of the submit <input> element (add-book), which are used to direct the form submission through the action pipeline to add the book:

  1: <?cocoon-process type="xsp"?>   2:    3: <xsp:page   4:   language="java"   5:   xmlns:xsp="http://apache.org/xsp"   6:   xmlns:esql="http://apache.org/cocoon/SQL/v2">   7:    8:  <page>   9:   <p>  10:   Please enter the information on the new book:  11:   </p>  12:   <form action="add-book-action">  13: Author:    14:    <input type="text" name="books.author"/>  15:    <br />  16: Title:    17:    <input type="text" name="books.title"/>  18:    <br />  19: ISBN:  20:    <input type="text" name="books.isbn"/>  21:    <br />  22: Month:  23:    <input type="text" name="books.month"/>  24:    <br />  25: Year:  26:    <input type="text" name="books.year"/>  27:    <br />  28:    <br />  29: Publisher:  30:    <select name="books.pub_id">  31:     <esql:connection>  32:      <esql:pool>books</esql:pool>  33:      <esql:execute-query>  34:       <esql:query>SELECT id, name FROM publishers</esql:query>  35:       <esql:results>  36:        <esql:row-results>  37:         <option>  38:          <xsp:attribute name="value">  39:           <esql:get-int column="id"/>  40:          </xsp:attribute>  41:          <esql:get-string column="name"/>  42:         </option>  43:        </esql:row-results>  44:       </esql:results>  45:      </esql:execute-query>  46:     </esql:connection>  47:    </select>  48:    <br />  49:    <input type="submit"   50:     name="add-book" value="Add book"/>  51:   </form>  52:  </page>  53: </xsp:page>

list-books.xsp

The file list-books.xsp is almost identical to list-publishers.xsp in concept. The only real difference is in the SQL query that’s used to get the books and the obvious changes to column names and so forth. The SQL query is a little more complicated because the name of the publisher is displayed in the listing:

  1: <?cocoon-process type="xsp"?>   2:    3: <xsp:page   4:   language="java"   5:   xmlns:xsp="http://apache.org/xsp"   6:   xmlns:esql="http://apache.org/cocoon/SQL/v2">   7:  <page>   8:   <esql:connection>   9:    <esql:pool>books</esql:pool>  10:    <esql:execute-query>  11:     <esql:query>  12: SELECT books.*, publishers.name   13: FROM books, publishers  14: WHERE books.pub_id = publishers.id</esql:query>  15:     <esql:results>  16:      <table border="1" cellspacing="0">  17:       <tr>  18:        <td>Author</td><td>Title</td><td>ISBN</td><td>Month</td>  19:        <td>Year</td><td>Publisher</td>  20:       </tr>  21:       <esql:row-results>  22:        <tr>  23:         <td><esql:get-string column="author"/></td>  24:         <td><esql:get-string column="title"/></td>  25:         <td><esql:get-string column="isbn"/></td>  26:         <td><esql:get-string column="month"/></td>  27:         <td><esql:get-string column="year"/></td>  28:         <td><esql:get-string column="name"/></td>  29:         <td>  30:          <form action="edit-book.xsp">  31:           <input type="hidden" name="books.id">  32:            <xsp:attribute name="value">  33:             <esql:get-int column="id"/>  34:            </xsp:attribute>   35:           </input>   36:           <input type="submit"  37:            name="edit-book" value="edit"/>  38:          </form>  39:          <form action="delete-book-action">  40:           <input type="hidden" name="books.id">  41:            <xsp:attribute name="value">  42:             <esql:get-int column="id"/>  43:            </xsp:attribute>   44:           </input>   45:           <input type="submit"  46:            name="delete-book" value="delete"/>  47:          </form>  48:         </td>  49:        </tr>  50:       </esql:row-results>  51:      </table>  52:     </esql:results>  53:     <esql:no-results>  54:      <table/>  55:     </esql:no-results>  56:    </esql:execute-query>  57:   </esql:connection>  58:  </page>  59: </xsp:page>

edit-book.xsp

The edit-book.xsp file is a bit more complicated than edit-publishers.xsp. The complexity arises from having to keep track of the publisher for a particular book. The publisher is shown in a drop-down menu, so when the edit page comes up, you want the drop-down menu to be set to the correct value for the book being displayed. This involves creating a selected attribute on the appropriate <option> element. A few <xsp:logic> sections (lines 10-12, lines 69-71, and lines 85-89) declare, initialize, and use the variable thePubId to accomplish this task. The menu selection is performed in the last <xsp:logic> section, lines 85-89:

  1: <?cocoon-process type="xsp"?>   2:    3: <xsp:page   4:   language="java"   5:   xmlns:xsp="http://apache.org/xsp"   6:   xmlns:esql="http://apache.org/cocoon/SQL/v2"   7:   xmlns:xsp-request="http://apache.org/xsp/request/2.0">   8:    9:  <page>  10: <xsp:logic>  11: int thePubId = -1;  12: </xsp:logic>  13:   <p>  14:   Please change the information on the book:   15:   </p>  16:   <form action="update-book-action">  17:    <esql:connection>  18:     <esql:pool>books</esql:pool>  19:     <esql:execute-query>  20:      <esql:query>  21: SELECT author, title, isbn, month, year, pub_id  22: FROM books  23: WHERE id = <xsp-request:get-parameter name="books.id"/>  24:      </esql:query>  25:        <esql:results>  26:       <esql:row-results>  27:        <input type="hidden" name="books.id">  28:         <xsp:attribute name="value">  29:          <xsp-request:get-parameter name="books.id"/>  30:         </xsp:attribute>  31:        </input>  32:   33: Author:    34:     <input type="text" name="books.author">  35:      <xsp:attribute name="value">  36:       <esql:get-string column="author"/>  37:      </xsp:attribute>  38:     </input>  39:     <br />  40: Title:    41:     <input type="text" name="books.title">  42:      <xsp:attribute name="value">  43:       <esql:get-string column="title"/>  44:      </xsp:attribute>  45:     </input>  46:     <br />  47: ISBN:  48:     <input type="text" name="books.isbn">  49:      <xsp:attribute name="value">  50:       <esql:get-string column="isbn"/>  51:      </xsp:attribute>  52:     </input>  53:     <br />  54: Month:  55:     <input type="text" name="books.month">  56:      <xsp:attribute name="value">  57:       <esql:get-string column="month"/>  58:      </xsp:attribute>  59:     </input>  60:     <br />  61: Year:  62:     <input type="text" name="books.year">  63:      <xsp:attribute name="value">  64:       <esql:get-string column="year"/>  65:      </xsp:attribute>  66:     </input>  67:     <br />  68:     <br />  69: <xsp:logic>  70: thePubId = <esql:get-int column="pub_id"/>;  71: </xsp:logic>  72:            </esql:row-results>  73:        </esql:results>  74:     </esql:execute-query>  75: Publisher:  76:     <select name="books.pub_id"> 77:      <esql:execute-query>  78:       <esql:query>SELECT id, name FROM publishers</esql:query>  79:       <esql:results>  80:        <esql:row-results>  81:         <option>  82:          <xsp:attribute name="value">  83:           <esql:get-int column="id"/>  84:          </xsp:attribute>  85: <xsp:logic>  86:  if (<esql:get-int column="id"/> == thePubId) {  87:   <xsp:attribute name="selected"/>;  88:  }  89: </xsp:logic>  90:          <esql:get-string column="name"/>  91:         </option>  92:        </esql:row-results>  93:       </esql:results>  94:      </esql:execute-query>  95:    </select>  96:    <br />  97:    <input type="submit"   98:     name="update-book" value="edit book"/>  99:    </esql:connection> 100:   </form> 101:  </page> 102: </xsp:page>

form.xslt

This is a slightly modified version of the form.xslt used in the modular database actions example. The only change is to add a navigational link back to the main menu (lines 10-11):

  1: <?xml version="1.0"?>    2:  <xsl:stylesheet version="1.0"   3:    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">   4:    5:   <xsl:template match="page">   6:    <html>   7:     <head></head>   8:     <body>   9:      <xsl:apply-templates/>  10:      <p />  11:      <a href="index">Back to main menu</a>  12:     </body>  13:    </html>  14:   </xsl:template>  15:   16:   <xsl:template match="@*|node()" priority="-1">  17:    <xsl:copy>  18:     <xsl:apply-templates select="@*|node()"/>  19:    </xsl:copy>  20:   </xsl:template>  21:   22: </xsl:stylesheet>

So there you have it—a simple example that shows how to query, insert, update, and delete data from a database.