Mailing-List: contact docs-help@cocoon.apache.org; run by ezmlm
Precedence: bulk
Reply-To: docs@cocoon.apache.org
Content-Type: text/plain; charset="iso-8859-1"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
From: docs@cocoon.apache.org
To: docs@cocoon.apache.org
Subject: 
 =?iso-8859-1?q?=5BCocoon_Wiki=5D_New=3A__HowToCreateHiearchicalXmlUsingES?=
 =?iso-8859-1?q?QL?=
Date: Fri, 12 Nov 2004 17:04:16 -0000
Message-ID: <20041112170416.32831.82814@minotaur.apache.org>

   Date: 2004-11-12T09:04:15
   Editor: PavelVrecion <pavel.vrecion@plarmy.org>
   Wiki: Cocoon Wiki
   Page: HowToCreateHiearchicalXmlUsingESQL
   URL: http://wiki.apache.org/cocoon/HowToCreateHiearchicalXmlUsingESQL

   new how to document

New Page:

=3D How to create hiearchical xml using ESQL =3D
This How-To describes the steps to write xsp that produces XML hierarchical=
 structure using ESQL nested queries.[[BR]]
Autor: '''Pavel Vrecion'''
=3D Purpose =3D
There is quite often need to create master-detail web pages based on databa=
se 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, w=
ith more child elements describing department and on next level also child =
elements for each employee. Note that for such tasks Cocoon has already par=
tial 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.
----

=3D Prerequisites =3D
We will need Cocoon (of course) and database with at least two tables linke=
d by =93one to many=94 reference.[[BR]]
In the example we will use Firebird database engine and we will utilize exa=
mple 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 reg=
arding database installation, running, parameters and JDBC connections.

=3D Steps briefly =3D
We will:[[BR]]
1. create at least two tables (or you can skip this step and use already ex=
isting database)[[BR]]
2. define database connection pool in cocoon.xconf (Cocoon main configurati=
on 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
=3D Steps in detail =3D
=3D=3D Database tables =3D=3D
We will need two tables, one for department and second for employee.[[BR]]
Two tables will be linked through =93one to many=94 relationship.[[BR]]
Employee table will have department foreign key =96 department id, which wi=
ll 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 (an=
d 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 =93self reference=94 and can be used to create rich company st=
ructure that consists from divisions, departments, sub-departments etc.[[BR=
]]
To verify that everything works you should test database using some SQL too=
l. =

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.

=3D=3D Adding new datasource to cocoon.xconf =3D=3D
Find {{{cocoon.xconf}}} (usually located in Cocoon directory {{{build/webap=
p/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 lik=
e this:
{{{<datasources>
....
	<!-- Test company database -->
	<jdbc name=3D"dbCompany">      =

	 <pool-controller max=3D"5" min=3D"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 a=
nd libraries, =

and you have added JDBC driver name to Cocoon {{{web.xml}}} configuration f=
ile (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 =93know=94 how to connect to database.[[BR]]
We are ready to start writing xsp and ultimately to show some data to crowd=
 of cheering users.

=3D=3D Writing XSP =3D=3D
As you remember, main purpose of our xsp is to present data from database i=
n rich xml structure. =

We will do it by executing SQL SELECT command that will return rowset of de=
partments. [[BR]]
We will save department id (identification, or primary key) into Java varia=
ble and use it in nested query. [[BR]]
Nested query will then produce list of employees for each department.[[BR]]
Clear? No? Don=92t worry, and let=92s 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 followi=
ng code to it and save it.[[BR]]
{{{<?xml version=3D"1.0"?>
<xsp:page language=3D"java" =

    xmlns:xsp=3D"http://apache.org/xsp" =

    xmlns:esql=3D"http://apache.org/cocoon/SQL/v2" =

    xmlns:xsp-request=3D"http://apache.org/xsp/request/2.0"
    xmlns:xsp-session=3D"http://apache.org/xsp/session/2.0"
    xmlns:cinclude=3D"http://apache.org/cocoon/include/1.0"
    xmlns:source=3D"http://apache.org/cocoon/source/1.0"
    xmlns:util=3D"http://apache.org/xsp/util/2.0"
    >

<!-- declare integer variable called deptno -->
<xsp:logic>int deptno=3D-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 DEPARTME=
NT</esql:query>
   		<esql:results>
     	<esql:row-results>
<!-- get department id from database and store it in deptno variable -->
			<xsp:logic>deptno=3D<esql:get-int column=3D"DEPT_NO"/>;</xsp:logic>
<!-- create XML element for department and add some interesting values from=
 database -->
			<Department>
				<DepartmentName><esql:get-string column=3D"DEPARTMENT"/></DepartmentNam=
e>
				<DepartmentLocation><esql:get-string column=3D"LOCATION"/></DepartmentL=
ocation>
				<DepartmentPhone><esql:get-string column=3D"PHONE_NO"/></DepartmentPhon=
e>
<!-- 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 =3D <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 value=
s -->
						<Employee>
							<Lastname><esql:get-string column=3D"LAST_NAME"/></Lastname>
							<Firstname><esql:get-string column=3D"FIRST_NAME"/></Firstname>
							<JobCode><esql:get-string column=3D"JOB_CODE"/></JobCode>
							<JobCountry><esql:get-string column=3D"JOB_COUNTRY"/></JobCountry>
							<PhoneExt><esql:get-string column=3D"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=3D<esql:get-int column=3D"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 =3D <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.

=3D=3D Simple sitemap =3D=3D
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=3D"1.0"?>
<map:sitemap xmlns:map=3D"http://apache.org/cocoon/sitemap/1.0">

	<map:pipelines>
		<map:pipeline>

			<map:match pattern=3D"employees.xml">
				<map:generate type=3D"serverpages" src=3D"empxml.xsp" />
				<map:serialize type=3D"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=92s try it. In your favorite browser type url (when you run Cocoon l=
ocally, your url will probably be {{{http://localhost:8888/test/employees.x=
ml}}}).[[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 c=
olumn. So, as homework, you can enhance example application by modifying xs=
p so that it will contain nested queries:
{{{
select * from DEPARTMENT where HEAD_DEPT is null -- (get company divisions)
select * from EMPLOYEE where DEPT_NO =3D <xsp:expr>deptno</xsp:expr> -- (ge=
t division employees)
select * from DEPARTMENT where HEAD_DEPT =3D <xsp:expr>deptno</xsp:expr> --=
 (get divisions departments i.e. second level departments)
select * from EMPLOYEE where DEPT_NO =3D <xsp:expr>deptno</xsp:expr> -- (ge=
t department employees)
}}}
=85 and so on for other levels. In this case resulting XML will have more l=
evels because DEPARTMENT elements will have child DEPARTMENT elements (you =
can also use DIVISION -  DEPARTMENT, or other tag names according to your n=
eeds 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 th=
ey 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 kn=
ow, 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]]

=3D=3D Simple XSL =3D=3D
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 wi=
th following content:
{{{<?xml version=3D"1.0" encoding=3D"UTF-8"?>
<xsl:stylesheet version=3D"1.0" =

                xmlns:xsl=3D"http://www.w3.org/1999/XSL/Transform"
                >
                =

<xsl:template match=3D"Company">
	<html>
		<head><title>My company departments</title></head>
	</html>
	<body>

	<xsl:for-each select =3D "Department">
		<h1><xsl:value-of select=3D"DepartmentName"/></h1>
		Location: <xsl:value-of select=3D"DepartmentLocation"/><br/>
		Phone: <xsl:value-of select=3D"DepartmentPhone"/><br/>
		<xsl:for-each select =3D "Employees"><br/>
			<table border=3D"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 =3D "Employee">
					<tr>
						<td>
							<xsl:value-of select=3D"Lastname"/>
							<xsl:text> </xsl:text>
							<xsl:value-of select=3D"Firstname"/>
						</td>
						<td><xsl:value-of select=3D"JobCode"/></td>
						<td><xsl:value-of select=3D"JobCountry"/></td>
						<td><xsl:value-of select=3D"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=3D"1.0"?>
<map:sitemap xmlns:map=3D"http://apache.org/cocoon/sitemap/1.0">

	<map:pipelines>
		<map:pipeline>

			<map:match pattern=3D"employees.xml">
				<map:generate type=3D"serverpages" src=3D"empxml.xsp" />
				<map:serialize type=3D"xml"/>
				<map:serialize/>
			</map:match>

			<map:match pattern=3D"employees.html">
				<map:generate type=3D"serverpages" src=3D"empxml.xsp" />
				<map:transform src=3D"employees.xsl"/>
				<map:serialize type=3D"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/te=
st/employees.html}}} we should see html formatted text:
[[BR]][[BR]]
'''Corporate Headquarters'''[[BR]]
Location: Monterey[[BR]]
Phone: (408) 555-1234
||||||||<tablewidth=3D"80%">Department employees||
||Employee||Job code||Job country||Phone extension||

[[BR]]

'''Sales and Marketing'''[[BR]]
Location: San Francisco[[BR]]
Phone: (415) 555-1234
||||||||<tablewidth=3D"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=3D"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 c=
oding in xsl much better then we, programmers, usually do.

=3D Achievements =3D
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 H=
TML pages, as well as any other automated processing. =

And, as a bonus, data, logic and presentation layers are kept apart.

=3D Comments =3D
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:pavel.vrecion@plarmy.org Pavel Vrecion.]