Date: 2004-11-12T09:04:15
Editor: PavelVrecion <[EMAIL PROTECTED]>
Wiki: Cocoon Wiki
Page: HowToCreateHiearchicalXmlUsingESQL
URL: http://wiki.apache.org/cocoon/HowToCreateHiearchicalXmlUsingESQL
new how to document
New Page:
= How to create hiearchical xml using ESQL =
This How-To describes the steps to write xsp that produces XML hierarchical
structure using ESQL nested queries.[[BR]]
Autor: '''Pavel Vrecion'''
= Purpose =
There is quite often need to create master-detail web pages based on database
queries.[[BR]]
Typical example is company that has more departments with employees.[[BR]]
In this case we would like to create XML structure starting with company, with
more child elements describing department and on next level also child elements
for each employee. Note that for such tasks Cocoon has already partial support
in ESQL called group queries, but this is limited to two level queries. We will
examine the other way, how to achieve this.
It will enable us to create richer XML structures.[[BR]]
During the process we will also learn how to interweave Java and SQL.
----
= Prerequisites =
We will need Cocoon (of course) and database with at least two tables linked by
�one to many� reference.[[BR]]
In the example we will use Firebird database engine and we will utilize example
databases employee.fdb, which is included in Firebird installation.
But other databases with JDBC support will work, too.[[BR]]
Consult Cocoon and/or JDBC and your database documentations for details
regarding database installation, running, parameters and JDBC connections.
= Steps briefly =
We will:[[BR]]
1. create at least two tables (or you can skip this step and use already
existing database)[[BR]]
2. define database connection pool in cocoon.xconf (Cocoon main configuration
file)[[BR]]
3. create application with:[[BR]]
- simple sitemap[[BR]]
- and simple xsp (XML Server Page)[[BR]]
4. create simple stylesheet to transform XML into HTML
= Steps in detail =
== Database tables ==
We will need two tables, one for department and second for employee.[[BR]]
Two tables will be linked through �one to many� relationship.[[BR]]
Employee table will have department foreign key � department id, which will
link employee to department.[[BR]]
[[BR]]
You can create tables with following SQL DDL statements:[[BR]]
{{{
CREATE TABLE DEPARTMENT (
DEPT_NO CHAR(3) NOT NULL,
DEPARTMENT VARCHAR(25) NOT NULL,
HEAD_DEPT CHAR(3),
LOCATION VARCHAR(15),
PHONE_NO VARCHAR(30)
}}}
{{{
CREATE TABLE EMPLOYEE (
EMP_NO INTEGER,
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(40),
PHONE_EXT VARCHAR(4),
DEPT_NO CHAR(3),
JOB_CODE VARCHAR(4),
JOB_COUNTRY VARCHAR(15)
}}}
Well, if you like, you can also create some integrity constraints and other
fancy database stuff like sequences, triggers,
but for our purpose SQL, we have mentioned above, is fine.[[BR]]
Note that column {{{DEPT_NO}}} is primary key in {{{DEPARTMENT}}} table (and so
should have mandatory and unique value).
Same column and its value is used in {{{EMPLOYEE}}} table to link specific
employee to specific department.
HEAD_DEPT is �self reference� and can be used to create rich company structure
that consists from divisions, departments, sub-departments etc.[[BR]]
To verify that everything works you should test database using some SQL tool.
It is good idea to add some test, but meaningful, data during the process,
too.[[BR]]
When we have tables in the database we must tell Cocoon, where to find the data
and how to connect to database.
That is why we will add new datasource to Cocoon configuration file.
== Adding new datasource to cocoon.xconf ==
Find {{{cocoon.xconf}}} (usually located in Cocoon directory
{{{build/webapp/WEB-INF}}}), open it with some text editor,
find datasources section and add new connection parameters.
After editing datasource part of {{{cocoon.xconf}} it will look somehow like
this:
{{{<datasources>
....
<!-- Test company database -->
<jdbc name="dbCompany">
<pool-controller max="5" min="1"/>
<auto-commit>true</auto-commit>
<dburl>jdbc:firebirdsql:localhost/3050:c:\PathToDB\EMPLOYEE.FDB</dburl>
<user>MyName</user>
<password>MyPassword</password>
</jdbc>
.....
}}}
We suppose that you have already downloaded and installed database engine and
libraries,
and you have added JDBC driver name to Cocoon {{{web.xml}}} configuration file
(located in the same directory as {{{cocoon.xconf}}}).[[BR]]
Your {{{web.xml}}} should have param-value element under servlet/init-param
elements similar to this:
{{{<init-param>
<param-name>load-class</param-name>
<param-value>
<!-- For parent ComponentManager sample:
org.apache.cocoon.samples.parentcm.Configurator -->
<!-- For IBM WebSphere:
com.ibm.servlet.classloader.Handler -->
<!-- For Database Driver: -->
org.hsqldb.jdbcDriver
<!-- For Firebird: -->
org.firebirdsql.jdbc.FBDriver
<!-- For MySQL Driver: -->
com.mysql.jdbc.Driver
</param-value>
</init-param>
}}}
Note that in this example there are specified drivers for several database
engines.
If you use some other database, please consult corresponding
documentation.[[BR]]
After these steps we should have database and Cocoon up and running, Cocoon
also should already �know� how to connect to database.[[BR]]
We are ready to start writing xsp and ultimately to show some data to crowd of
cheering users.
== Writing XSP ==
As you remember, main purpose of our xsp is to present data from database in
rich xml structure.
We will do it by executing SQL SELECT command that will return rowset of
departments. [[BR]]
We will save department id (identification, or primary key) into Java variable
and use it in nested query. [[BR]]
Nested query will then produce list of employees for each department.[[BR]]
Clear? No? Don�t worry, and let�s go to examples. [[BR]]
Create new directory called test (for example) in Cocoon {{{build/webapp}}}
directory (other applications are located there, too) and create new text file
named empxml.xsp (you can name it differently, of course). Add following code
to it and save it.[[BR]]
{{{<?xml version="1.0"?>
<xsp:page language="java"
xmlns:xsp="http://apache.org/xsp";
xmlns:esql="http://apache.org/cocoon/SQL/v2";
xmlns:xsp-request="http://apache.org/xsp/request/2.0";
xmlns:xsp-session="http://apache.org/xsp/session/2.0";
xmlns:cinclude="http://apache.org/cocoon/include/1.0";
xmlns:source="http://apache.org/cocoon/source/1.0";
xmlns:util="http://apache.org/xsp/util/2.0";
>
<!-- declare integer variable called deptno -->
<xsp:logic>int deptno=-1;</xsp:logic>
<!-- create root xml element -->
<Company>
<!-- start esql section using database connection declared in cocoon.xconf -->
<esql:connection>
<esql:pool>dbCompany</esql:pool>
<!-- query departments -->
<esql:execute-query>
<esql:query>select DEPT_NO, DEPARTMENT, LOCATION, PHONE_NO from
DEPARTMENT</esql:query>
<esql:results>
<esql:row-results>
<!-- get department id from database and store it in deptno variable -->
<xsp:logic>deptno=<esql:get-int
column="DEPT_NO"/>;</xsp:logic>
<!-- create XML element for department and add some interesting values from
database -->
<Department>
<DepartmentName><esql:get-string
column="DEPARTMENT"/></DepartmentName>
<DepartmentLocation><esql:get-string
column="LOCATION"/></DepartmentLocation>
<DepartmentPhone><esql:get-string
column="PHONE_NO"/></DepartmentPhone>
<!-- create XML element for emloyees and add some data -->
<Employees>
<!-- nested query for employees
For each record in DATABASE table do the query for all employees in given
department.
In select statement saved value of department number (primary key) is used
-->
<esql:execute-query>
<esql:query>
select FIRST_NAME, LAST_NAME,
PHONE_EXT, JOB_CODE, JOB_COUNTRY
from EMPLOYEE where DEPT_NO =
<xsp:expr>deptno</xsp:expr>
order by LAST_NAME, FIRST_NAME
</esql:query>
<esql:results>
<esql:row-results>
<!-- create XML element for employee and add some child elements with values -->
<Employee>
<Lastname><esql:get-string column="LAST_NAME"/></Lastname>
<Firstname><esql:get-string column="FIRST_NAME"/></Firstname>
<JobCode><esql:get-string column="JOB_CODE"/></JobCode>
<JobCountry><esql:get-string column="JOB_COUNTRY"/></JobCountry>
<PhoneExt><esql:get-string column="PHONE_EXT"/></PhoneExt>
</Employee>
</esql:row-results>
</esql:results>
</esql:execute-query>
</Employees>
</Department>
</esql:row-results>
</esql:results>
</esql:execute-query>
</esql:connection>
</Company>
</xsp:page>
}}}
OK we have xsp page. Note that we have filled Java variable '''with''' ESQL
command[[BR]]
{{{<xsp:logic>deptno=<esql:get-int column="DEPT_NO"/>;</xsp:logic>
}}}
And then used Java variable '''in''' ESQL command
{{{<esql:query>
select FIRST_NAME, LAST_NAME, PHONE_EXT, JOB_CODE, JOB_COUNTRY
from EMPLOYEE where DEPT_NO = <xsp:expr>deptno</xsp:expr>
order by LAST_NAME, FIRST_NAME
</esql:query>
}}}
That is the main trick of nested queries and hierarchical structures.[[BR]]
As a next step we will create simple sitemap in our application directory.
== Simple sitemap ==
Sitemap will be really simple.
Its only purpose is to show one page, we have just created.
Create new text file called sitemap.xmap in application directory and fill it
with following text:
{{{<?xml version="1.0"?>
<map:sitemap xmlns:map="http://apache.org/cocoon/sitemap/1.0";>
<map:pipelines>
<map:pipeline>
<map:match pattern="employees.xml">
<map:generate type="serverpages"
src="empxml.xsp" />
<map:serialize type="xml"/>
<map:serialize/>
</map:match>
</map:pipeline>
</map:pipelines>
</map:sitemap>
}}}
At this stage we should have 2 files in our example application directory:
* {{{empxml.xsp}}}
* {{{sitemap.xmap}}}[[BR]]
We have created xsp that produces xml structure. We have also sitemap with
pipeline that calls this xsp when Cocoon gets employee.xml request. [[BR]]
So let�s try it. In your favorite browser type url (when you run Cocoon
locally, your url will probably be
{{{http://localhost:8888/test/employees.xml}}}).[[BR]]
You should see xsp result in the structured XML form, something like this:
{{{<Company>
<Department>
<DepartmentName>Corporate Headquarters</DepartmentName>
<DepartmentLocation>Monterey</DepartmentLocation>
<DepartmentPhone>(408) 555-1234</DepartmentPhone>
<Employees>
</Employees>
</Department>
<Department>
<DepartmentName>Sales and Marketing</DepartmentName>
<DepartmentLocation>San Francisco</DepartmentLocation>
<DepartmentPhone>(415) 555-1234</DepartmentPhone>
<Employees>
<Employee>
<Lastname>MacDonald</Lastname>
<Firstname>Mary S.</Firstname>
<JobCode>VP</JobCode>
<JobCountry>USA</JobCountry>
<PhoneExt>477</PhoneExt>
</Employee>
<Employee>
<Lastname>Yanowski</Lastname>
<Firstname>Michael</Firstname>
<JobCode>SRep</JobCode>
<JobCountry>USA</JobCountry>
<PhoneExt>492</PhoneExt>
</Employee>
</Employees>
</Department>
<Department>
<DepartmentName>Engineering</DepartmentName>
......
}}}
Note that department can be linked to parent department through HEAD_DEPT
column. So, as homework, you can enhance example application by modifying xsp
so that it will contain nested queries:
{{{
select * from DEPARTMENT where HEAD_DEPT is null -- (get company divisions)
select * from EMPLOYEE where DEPT_NO = <xsp:expr>deptno</xsp:expr> -- (get
division employees)
select * from DEPARTMENT where HEAD_DEPT = <xsp:expr>deptno</xsp:expr> -- (get
divisions departments i.e. second level departments)
select * from EMPLOYEE where DEPT_NO = <xsp:expr>deptno</xsp:expr> -- (get
department employees)
}}}
� and so on for other levels. In this case resulting XML will have more levels
because DEPARTMENT elements will have child DEPARTMENT elements (you can also
use DIVISION - DEPARTMENT, or other tag names according to your needs or
taste).[[BR]]
You should see xml, maybe even in the structure, you have expected. You are
happy, but your users expect some fancy graphics with company logo, and they
strictly demand yellow text on pink background.[[BR]]
Well, make them happy, too. We will write xsl and postpone our celebration, but
just a little.[[BR]]
XSL has one hidden virtue, it divides data and presentation. And we just know,
that our users will strictly demand something else next week, and quite often
they will act in separated groups divided by languages, browsers etc. This
approach opens the way how to serve them all, and how to make later changes
fast.[[BR]]
== Simple XSL ==
To keep things simple and clear, we will start with several HTML lines for each
department and one table for department employees.[[BR]]
Create new file {{{employees.xsl}}} in application directory and fill it with
following content:
{{{<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
>
<xsl:template match="Company">
<html>
<head><title>My company departments</title></head>
</html>
<body>
<xsl:for-each select = "Department">
<h1><xsl:value-of select="DepartmentName"/></h1>
Location: <xsl:value-of select="DepartmentLocation"/><br/>
Phone: <xsl:value-of select="DepartmentPhone"/><br/>
<xsl:for-each select = "Employees"><br/>
<table border="1">
<caption>Department employees</caption>
<thead>
<th>Employee</th>
<th>Job code</th>
<th>Job country</th>
<th>Phone extension</th>
</thead>
<tbody>
<xsl:for-each select = "Employee">
<tr>
<td>
<xsl:value-of
select="Lastname"/>
<xsl:text> </xsl:text>
<xsl:value-of
select="Firstname"/>
</td>
<td><xsl:value-of
select="JobCode"/></td>
<td><xsl:value-of
select="JobCountry"/></td>
<td><xsl:value-of
select="PhoneExt"/></td>
</tr>
</xsl:for-each>
</tbody>
</table>
</xsl:for-each>
<hr/>
</xsl:for-each>
</body>
</xsl:template>
</xsl:stylesheet>
}}}
Now we will use this xslt code to transform xml data into html.
To do it, we must enhance Cocoon application sitemap a little.
Add several lines to {{{sitemap.xmap}}}, so the new sitemap will look like
this:[[BR]]
{{{
<?xml version="1.0"?>
<map:sitemap xmlns:map="http://apache.org/cocoon/sitemap/1.0";>
<map:pipelines>
<map:pipeline>
<map:match pattern="employees.xml">
<map:generate type="serverpages"
src="empxml.xsp" />
<map:serialize type="xml"/>
<map:serialize/>
</map:match>
<map:match pattern="employees.html">
<map:generate type="serverpages"
src="empxml.xsp" />
<map:transform src="employees.xsl"/>
<map:serialize type="html"/>
<map:serialize/>
</map:match>
</map:pipeline>
</map:pipelines>
</map:sitemap>
}}}
At this stage we should have 3 files in our example application directory:
* {{{empxml.xsp}}} (producing xml from database)
* {{{sitemap.xmap}}} (describing our application components and basic flow of
pages)
* {{{employees.xsl}}} (transforming xml into html)
After calling {{{employees.html}}} (for example
{{{http://localhost:8888/test/employees.html}}} we should see html formatted
text:
[[BR]][[BR]]
'''Corporate Headquarters'''[[BR]]
Location: Monterey[[BR]]
Phone: (408) 555-1234
||||||||<tablewidth="80%">Department employees||
||Employee||Job code||Job country||Phone extension||
[[BR]]
'''Sales and Marketing'''[[BR]]
Location: San Francisco[[BR]]
Phone: (415) 555-1234
||||||||<tablewidth="80%">Department employees||
||Employee||Job code||Job country||Phone extension||
||Warvick Mary S.||VP||USA||477||
||Yanowski Michael||SRep||USA||492||
[[BR]]
'''Engineering'''[[BR]]
Location: Monterey[[BR]]
Phone: (408) 555-1234
||||||||<tablewidth="80%">Department employees||
||Employee||Job code||Job country||Phone extension||
||Brown Kelly||Admin||USA||202||
||Nelson Robert||VP||USA||250||
[[BR]]....[[BR]]
In praxis you will probably spend some more time playing with HTML layout,
fonts, colors and graphics, but believe me,
better is to find somebody who really has the taste, and who will do HTML
coding in xsl much better then we, programmers, usually do.
= Achievements =
We have learned how to use xsp and how to mix Java and SQL codes to create
hierarchical structures.[[BR]]
Such approach has several advantages. Code is short and more readable.[[BR]]
Hierarchical XML structure simplifies transformations like using XSLT for HTML
pages, as well as any other automated processing.
And, as a bonus, data, logic and presentation layers are kept apart.
= Comments =
Care to comment on this How-To? [[BR]]
Got another tip? [[BR]]
Help keep this How-To relevant by passing along any useful feedback to the
author, [mailto:[EMAIL PROTECTED] Pavel Vrecion.]
