Apache Jakarta and Beyond: A Java Programmeramp;#039;s Introduction [Electronic resources] نسخه متنی

20.3. Accessing Databases

Cocoon offers a few different mechanisms for database access; the simplest of these uses another transformer. The general idea is that this transformer replaces SQL commands within special tags by XML representing the rows and columns of the results. The entry in sitemap.xmap will specify the transformer and provide the name of the connection to use. The name of this connection should match one provided in cocoon.xconf. The next example will generate a page of artist names pulled from the CD database discussed in Chapter 10 and elsewhere. The first step is once again to add the entry in sitemap.xmap:

<map:match pattern="artist201">
<map:generate src="artists.xml"/>
<map:transform type="sql">
<map:parameter name="use-connection"   value="toolbook"/>
</map:transform>
<map:transform src="sq194.xsl"/>
<map:serialize type="xhtml"/>
</map:match>

The artist.xml page is shown in Listing 20.5.

Listing 20.5. A Cocoon page with embedded SQL

<?xml version="1.0"?>
<page xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<title>Artists</title>
<content>
<sql:execute-query>
<sql:query name="artists">
select * from artist
</sql:query>
</sql:execute-query>
</content>
</page>

Note the use of the sql namespace and the select statement enclosed within the sql:query tags.

After passing through the sql TRansformer, the SQL tags in this page will be replaced with XML resembling the following:

<rowset name="artists"
xmlns="http://apache.org/cocoon/SQL/2.0">
<row>
<artist_id>1</artist_id>
<name>The Cr&#252;xshadows</name>
</row>

<row>
<artist_id>2</artist_id>
<name>VNV Nation</name>
</rowset>

All that remains is to transform this int197. This is accomplished by sq194.xsl, which is shown in Listing 20.6.

Listing 20.6. XSLT to convert rowsets t197

<?xml version="1.0"?>
<xsl:stylesheet
version="1.0"  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<xsl:import href="simple-pag187.xsl"/>
<xsl:template match="sql:rowset">
<xsl:choose>
<xsl:when test="ancestor::sql:rowset">
<tr>
<td>
<table border="1">
<xsl:apply-templates/>
</table>
</td>
</tr>
</xsl:when>
<xsl:otherwise>
<table border="1">
<xsl:apply-templates/>
</table>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
<xsl:template match="sql:row">
<tr>
<td><xsl:value-of select="sql:artist_id"/></td>
<td><a href="album201?artist_id={./sql:artist_id}">
<xsl:value-of select="sql:name"/></a></td>
</tr>
</xsl:template>
</xsl:stylesheet>

The included simple-pag187.xsl file is included with Cocoon and handles tags such as page and content. The remaining transformation rules build a196 table with two columns: one showing the ids and the other showing the names. The names are linked to the album201 page, passing along the artist id.

The next task is to build the album201 page that will show a list of albums for the selected artist. It is clear that this page will need to use the sql transformer in order to obtain the data. What is not yet clear is how the page will use the provided value for artist_id.

Request values from hrefs and forms is available through another transformer called session. There are tags that may refer to values in session and request objects, and the session TRansformer will replace these tags with their values. Listing 20.7 shows the albums.xml page that uses this session capability.

Listing 20.7. The albums page

<?xml version="1.0"?>
<page xmlns:session="http://apache.org/cocoon/session/1.0"
xmlns:sql="http://apache.org/cocoon/SQL/2.0">
<title>Albums</title>
<content>
<sql:execute-query>
<sql:query name="albums">
select * from album
where artist_id=<session:getxml
context="request"
path="/parameter/artist_id"/>
</sql:query>
</sql:execute-query>
</content>
</page>

Note the new session namespace and the getxml tag. The request context refers to values associated with the current request, much like the request object in JSPs. The path attribute references a value in a hierarchy associated with each context; in this case, it is used to obtain the value of the parameter.

Here is the entry in sitemap.xmap that drives this page:

<map:match pattern="album201">
<map:generate src="albums.xml"/>
<map:transform type="session"/>
<map:transform type="sql">
<map:parameter name="use-connection"   value="toolbook"/>
</map:transform>
<map:transform src="sq194.xsl"/>
<map:serialize type="xhtml"/>
</map:match>

Requests for album201 are handled by first using a generator to load albums.xml. The resulting XML is then fed through the session TRansformer, which will replace the getxml tag with the value of the artist_id parameter. After this transformer is finished, the text within the sql:query tag will resemble

select * from album where artist_id=1

The transformed page with this complete SQL statement is then passed to the sql transformer, which will process the query and replace it with rowset data, just as in the artist example. The XML is then processed by another XSLT transformation and sent to the user as XHTML.