cocoon-docs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From d...@cocoon.apache.org
Subject [Cocoon Wiki] New: HowToCreateHiearchicalXmlUsingESQL
Date Fri, 12 Nov 2004 17:04:16 GMT
   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:

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

Mime
View raw message