Generating PDFs from ADF Business Components View Object datasets using XSQL

In the projects we do, we very often
encounter the requirement for generating PDF documents containing
data or reports from a database. We usually meet this requirement by
creating a custom class that reads an XSL style sheet which
transforms our query results to XSL-FO that is then processed by a
Apache FOP driver. The custom class then is initialized and called
upon from a series of method calls that start in a custom Struts
action class.

This article also uses Apache FOP to
render the PDF, but uses a slightly different approach for retrieving
the data from the database. We will use XSQL here. Furthermore, we
will not directly query our databse but use an ADF Business
Components View Object to do the querying for us.

 

XSQL consists of two parts. First of
all, there is a taglib that provides several tags in the xsql
namespace. Second there is an XSQL page processor engine that is
shared by an XSQL Servlet, an XSQL command line utility and a
programmic API that allows you to process the XSQL pages.

Getting started with XSQL

In order to follow the steps as lined
out in this article, start up JDeveloper 10.1.3 and create a new
Application, called e.g. XSQLApp. For now, choose “No Template”
so no Projects will be created. when prompted for a Project name,
click the cancel button or hit the esc key.

In the Application Navigator pane,
right click the XSQLApp application and choose New Project. In the
Categories pane (left side) expand General and select Projects. Then,
in the Items pane (right side) select Web Project. In the New Project
Wizard name the project XSQLWeb and accept all remaining default
values.

The next thing to do is to create an
XSQL file. To do so, right click the XSQLWeb project and select New.
Make sure the Filter By drop down is set to “All Technologies”.
Then expand General and select XML in the left pane. In the right
pane select XSQL File. Name the xsql file “depstats.xsql”.

Before we can use our “depstats.xsql”
page, we need to setup a database connection. Open the Connections
Navigator, right click Database Connection and select “New
Database Connection…”. Follow the steps in the wizard to setup
the database connection and make sure it connects to the HR scheme. I
have named this connection “HR”.

Next our “depstats.xsql” page
needs to be modified so it will perform a query on the HR scheme. To
do so, open the Component Palette, select XSQL from the drop down to
the top and click Query.

 

In the wizard that follows, leave all
defaults as they are except for two. First of all, make sure to
select the HR database connection. Next, enter this SQL query:

select
dep.department_id,
dep.department_name,
emp.last_name
from
departments
dep,
employees
emp
where
dep.manager_id
=
emp.employee_id

After finishing the wizard, run the
application to see the XML that is returned by the XSQL page.

A simple style sheet to format the XML
can be found here. Download the stylesheet and put it in the
public_html folder within your project. Next, modify the
depstats.xsql file and uncomment the <xml-stylesheet directive.
Make sure the href option is set to “html.xsql”. Next,
restart OC4J and see the stylesheet in action.

Connecting to a View Object from the
XSQL page

Personally, I am
not a big fan of having an SQL query in my web pages. I think most
people will agree with me that keeping data management and data
presentation separate is a very good idea. In order to take the query
out of our XSQL page we need to setup an ADF Business Components
layer and make our XSQL page fetch the data from that layer.

In order to make
use of ADF Business Components we need to create another project
within our XSQLApp application. In the Applications Navigator right
click the XSQLApp application and select New Project. In the
Categories pane to the left expand Business Tier and select ADF
Business Components. In the Items pane to the right select Business
Components Project.

In the wizard that
starts name the new project XSQLModel, provide a default package for
the Java classes that will be generated (this option isn’t very
important for this article) and click Finish. Next another wizard
starts but we won’t use it here. Make sure the HR scheme is selected
and click Next. In Step 1 of 5 enter a default package if you like
and click Finish. Make sure the Application Sources folder in the
XSQLModel project is empty.

Right click the
XSQLModel project and select New. Expand the Business Tier node in
the left panel and select ADF Business Components. Select View Object
in the right panel and click OK. Name the new View Object
“DepStatsView” and keep clicking Next utill you can click
Finish, which you should do. A popup is shown that the ViewObject has
been modified to be in expert mode, which is fine by us.

Next expand all
nodes in the XSQLModel project and double click the DepStatsView View
Object. Select the SQL Statement node in the left pane and past in
the SQL query that you put into the xsql page.

 

In order to be
able to use this View Object we need to create an Application Module
and expose the View Object in it. Right click the XSQLModel project
once more and select New. Make sure the ADF Business Components node
under Business Tier is selected in the left pane and select
Application Module in the right pane. Name the application module
XSQLAppModule and click Next and Finish. In the Application
Navigator, double click the XSQLAppModule application module and make
sure the DepStatsView View Object is published in the data model to
the right. In order to do so, click the View Object and then the blue
arrow button in the middle of the Application Module Editor window.

Now we will expose
the View Object to our xsql page. Open the page if it’s not already
open and remove the xsql:query opening and closing tag with
everything in between. Also make sure the connection property of the
page tag has been removed. In short, the code should look like in the
image below.

Next, click
ViewObject Show from the Component Palette. This will start a wizard
that allows you to select the View Object that you would like to
fetch data from.

Make sure to
select the DepStatsView1 View Object. In the end, your xsql page
should look like this.

 

The fact that we
changed our query to make use of the View Object means that we need
to adjust our XSLT stylesheet. In short, the rowset no longer is
called “rowset” and the rows are no longer called “row”.
They now are called DepStatsView1 and DepStatsViewRow. Moreover, the
columns in the View Object have different names.

The adjusted XSQL
file can be found here. Download the stylesheet and put it in the
public_html folder within your project. Make sure to modify the
xml-stylesheet tag and make the href property point to
“htmlview.xsql”. Next, restart OC4J and verify everything
is working ok.

Generating PDF

As explained at
the start of this article, the XSQL tags are processed by the XSQL
page processor engine though the XSQL Servlet. This servlet is
configured by the XSQLConfig.xml file which can be found via the
Application Navigator pane in XSQLWeb under Application Sources. The
default behavior of the servlet is to output all the data of the
query after running it through the XSLT stylesheet. But the servlet
can be configured to act differently.

If you open the
XSQLConfig.xml file and scroll all the way to the bottom of the file,
you’ll see that several serializers are defined. One of the
serializers is the XSQLFOPSerializer. This serializer can be used,
along with the Apache FOP jars, to output PDF files.

In order to make
use of the XSQLFOPSerializer serializer, you’ll need to download the
FOP jars from

http://xmlgraphics.apache.org/fop/

In order to have
OC4J load the FOP jar files, stop the OC4J server and select Tools ->
Embedded OC4J Server Preferences. Next select the Libraries option,
either from Global or from Current Workspace and add the six jars
contained in the FOP zip file. These six jars are fop.jar,
avalon-framework-cvs-20020806.jar, batik.jar, xalan-2.4.1.jar,
xercesImpl-2.2.1.jar and xml-apis.jar.

Besides that,
you’ll need an XSLT stylesheet to generate XSL-FO data that can be
processed by FOP to generate PDF. The stylesheet can be found here.
Download the stylesheet and put it in the public_html folder within
your project. Make sure to modify the xml-stylesheet tag and make
the href property point to “pdf.xsql”.

I wish that was
all there’s to it, but unfortunately it not. The XSQLFOPSerializer
expects an old version of FOP and it won’t generate PDFs at all. To
solve this issue I used the class provided by Richard Gossow from
this Oracle Forums post
http://forums.oracle.com/forums/thread.jspa?threadID=213304&tstart=60.
The class can be found here. You need to put it into the XSQLModel
project. In order to use it, make sure that

• the package
  statement at the top of the source code matches the package you put
  the class into

• the FOP
  libraries are added to the project (right click the XSQLModel
  project and choose Libraries)

• the XSQL
  Runtime libraries are added to the project (right click the
  XSQLModel project and choose Libraries)

Next, replace the
oracle.xml.xsql.serializers.XSQLFOPSerializer by your own class in
the XSQLConfig.xml file. Then there is one more issue…

Putting the
xsql:action tag below the page tag will make FOP not being able to
recognize the rowset. In order to solve this, we need to move the
namespace definition from the page tag to the xsql:action tag and
remove the page start and end tags. JDeveloper will complain that
certain properties are not defined for the xsql:action tag, but the
page compiles and runs just fine.

Please note the
pdf.xslt stylesheet already expects this  Now, start up OC4J and
enjoy the PDF.

Conclusion

Combining XSQL and
FOP may not be as straight forward as expected given the seemingly
good support of FOP by the XSQL Servlet. However, with some small
modifications the PDF generation works without any problems. In an
already setup ADF BC enabled application generating PDFs this way may
be easier than doing it through a struts action.