Connecting your database

by Michal Durdina

How to get your data

Most web applications provide access to online information. Although many new services for accessing information emerged, storing information in relational database is still the most used option. Cocoon2 knows this and provides tools for simple connection and querying to most common databases.

To access the database in Java, developers use JDBC API for this purpose. JDBC is integral part of JDK, thus every application written in Java can easily access the database using this API. Cocoon2 is built on Java technology and Java is used also for writing logic to control dynamic behavior. Thus, it is natural to use JDBC in your web applications.

Cocoon2 does it better

Using the JDBC in the logic in your web application means to add a lot of Java code to your XSP pages. Moreover, there is a problem how to share the JDBC connection between pages. The straightforward solution to this problem is to use ESQL taglib and the connection pool provided by Cocoon2.

Connection pool

You can get the connection to your database from connection pool provided by Cocoon2. The connection pool is the central repository for keeping JDBC connections for all applications served by Cocoon. After settings JDBC parameters in Cocoon configuration (in cocoon.xconf) you can access your data by specifying only the pool name. Then you can get a Datasource object and Connection object from Cocoon2 component manager. This is working technique, however, it is kind of low level approach. It is much easier to use ESQL taglib (defined in ESQL logicsheet) in your XSP page which allows you to encapsulate all Java code to XML tags.

Before using the connection pool you need to set some parameters in Cocoon and Tomcat (or your servlet container) configuration for them to know how to create a Datasource object for you.

cocoon configuration^

Cocoon2 uses an XML file cocoon.xconf for storing runtime parameters for Cocoon servlet. This file is located in the root directory of your Cocoon web app. It contains parameters for internal components as caching, logicsheets, sitemap and ... datasources.

To set up your datasource for using it in connection pool, you need to add JDBC parameters of your database to <datasources> element in cocoon.xconf. One may look like this: 

    				
   <jdbc name="hb_pool">      
     <pool-controller min="5" max="10"/>
     <dburl>jdbc:interbase://hbser/d:\ibbase\hbsbase.gdb</dburl>  
     <user>webapp</user>
     <password>heslo</password>
   </jdbc>

In this example we have a connection pool named hb_pool with JDBC connection to Interbase server on computer hbser connecting the database d:\ibbase\hbsbase.gdb. The username and password usually have to be specified, too. If you are not sure whether you specified the JDBC string with no mistakes you can test it either in some software for exploring databases such as JDBC Explorer (part of JBuilder) or you can write short code in Java to establish JDBC connection using particular JDBC string.

Note:
JDBC naming conventions for selecting the database server and database name are not specified and are subject to particular JDBC driver reflecting characteristics of particular database system.
loading JDBC driver^

It probably looked quite simple, but to make things working there is lot of additional work to do. First of all you need to ensure that JDBC driver you are using is on Java CLASSPATH. JDBC drivers other than jdbc:odbc: are usually provided from companies developing particular database systems. . For example Interbase Corp. distributes JDBC driver for Interbase database as a interclient.jar. This package contains JDBC driver (interbase.interclient.Driver) you need to explicitly load to Java environment.

The driver must be loaded in memory before JDBC can use it. In Java program you can do it by calling Class.forName("interbase.interclient.Driver"); before getting java.sql.Connection object from java.sql.DriverManager. Of course, it assumes that you have the package (e.g. interclient.jar) on your CLASSPATH.

In Cocoon framework the same is valid except the way how to load the JDBC driver to memory. In servlet environment the servlet container is responsible for setting up the workspace for Cocoon servlet. Thus, you need to configure your servlet container to load the JDBC driver to memory.

Tomcat (servlet container) configuration^

Tomcat uses deployment descriptor web.xml located in WEB-INF directory of your Cocoon web app. All you need to do is to modify the initialization parameter load-class <init-param>. 

    				
   <init-param>
     <param-name>load-class</param-name>
     <param-value>
       <!-- For IBM WebSphere: --> 
       com.ibm.servlet.classloader.Handler 

       <!-- For JDBC-ODBC Bridge: -->
       sun.jdbc.odbc.JdbcOdbcDriver
       
       <!-- For Interbase DBMS: -->
       interbase.interclient.Driver
      </param-value>
    </init-param>

Now, the JDBC driver for your database is being loaded everytime you start Tomcat. Do not forget, however, to set CLASSPATH to contain the path to all JDBC drivers your web app is using.

Conclusion^

To get your database connection working make sure that:

  • JDBC driver (jar archive with full path) is on Java CLASSPATH
  • JDBC driver is loaded in memory
  • your connection string works with your JDBC driver

ESQL Taglib

ESQL taglib is a nice substitution of Java code when querying database in your XSP pages. It uses JDBC API so all previous assumptions about JDBC settings in Cocoon are valid if using ESQL. The best way to show how to use ESQL is to provide an example. Following example queries transaction 3 to 7 in unknown bank institution with specific account number and the resultset is ordered by date. There is no static content on this page for higher simplicity, but you can certainly imagine other tags included for maintaining other information to resulting page. 


   <?xml version="1.0" encoding="ISO-8859-1"?>
   
   <xsp:page language="java"
     xmlns:xsp="http://apache.org/xsp"
     xmlns:esql="http://apache.org/cocoon/SQL/v2"
   >

   <page>

     <esql:connection>
       <esql:pool>hb_pool</esql:pool>

       <transactions>

         <esql:execute-query>
           <esql:skip-rows>2</esql:skip-rows>
           <esql:max-rows>5</esql:max-rows>

           <esql:query>
             select * from srv_trans
               where acc_num = 0180698943
               order by trans_date
           </esql:query>

           <esql:results>
             <esql:row-results>
               <trans>
                 <xsp:attribute name="no">
                   <xsp:expr>
                     <esql:get-row-position/>+1</xsp:expr>
                 </xsp:attribute>

                 <id>
                   <esql:get-int column="trans_id1"/>
                 </id>
                 <date>
                   <esql:get-date column="trans_date"
                      format="dd. MMM. yyyy"/>
                 </date>
                 <account>
                   <esql:get-string column="acc_num"/>
                 </account>
                 <amount>
                   <esql:get-double column="amount"/>
                 </amount>
                 <currency>
                   <esql:get-string column="curr"/>
                 </currency>
               </trans>
             </esql:row-results>
           </esql:results>
       
           <esql:no-results>
             No records found...
           </esql:no-results>

           <esql:error-results>
             SQL Exception: <esql:get-message/>
           </esql:error-results>

         </esql:execute-query>

       </transactions>
     </esql:connection>

   </page>

   </xsp:page>
Note:
As this is a working example, you can copy/paste it, but you need to set the database connection settings described before to get started.

This XSP page is regular XML document so it starts with XML declaration <?xml...>. The ESQL taglib must be specified as xmnls namespace in <xsp:page> tag - this instruct Cocoon to to interpret the esql tags (to use ESQL logicsheet). Then the connection settings are specified in <esql:connection> tag. In this case a pooled connection "hb_pool" is used, however, you can specify your own database settings (dburl, username, password) instead. <esql:query> element contains SQL SELECT query string which is very simple now, but can be easily parameterized.

To execute the query the <esql:results> with <esql:row-results> elements must be included in esql:execute-query. The tag esql:results serves multiply ResultSets (rare), esql:row-results encapsulates the loop through all rows in one ResultSet. Within esql:row-results we build transaction elements with id, date, account, amount and currency child elements. Values from rows of query result are retrieved using esql:get-int, esql:get-date, esql:get-string or esql:get-double respectively.

Finally we need to serve exceptions, <esql:no-result> body is used in case of empty ResultSet and <esql:error-results> body is used in case of JDBC errors.

The result is dynamically generated XML with no JAXP or JDBC coding - all in clear and concise fashion. And the best on this approach is the possibility to mix and nest various taglibs to auxiliary depth and then easily use the result of one as a parameter to another.

Depending on your data the final XML produced by the example may look like this: 

   <?xml version="1.0" encoding="UTF-8"?>
   <page xmlns:xspdoc="http://apache.org/cocoon/XSPDoc/v1"
     xmlns:esql="http://apache.org/cocoon/SQL/v2"
     xmlns:xsp="http://apache.org/xsp">
     <transactions>          
       <transaction no="1">
         <id>444</id>
         <date>31. mar. 2001</date>
         <account>0180698943</account>
         <amount>-10.0</amount>
         <currency>SKK</currency>
       </transaction>          
       <transaction no="2">
         <id>344</id>
         <date>30. apr. 2001</date>
         <account>0180698943</account>
         <amount>-10.0</amount>
         <currency>SKK</currency>
       </transaction>
       <transaction no="3">
         <id>141</id>
         <date>31. maj. 2001</date>
         <account>0180698943</account>
         <amount>-10.0</amount>
         <currency>SKK</currency>
       </transaction>   
       <transaction no="4">
         <id>849</id>
         <date>30. jun. 2001</date>
         <account>0180698943</account>
         <amount>-10.0</amount>
         <currency>SKK</currency>
       </transaction>
       <transaction no="5">
         <id>4329</id>
         <date>31. jul. 2001</date>
         <account>0180698943</account>
         <amount>-10.0</amount>
         <currency>SKK</currency>
       </transaction>
     </transactions>
   </page>