{scrollbar}

This article guides you through the JDBC features in the Apache Geronimo appplication server. To demonstrate the JDBC features, we use a simple Inventory application which has JSP, Servlets to handle web related features and inbuilt Derby as database.

Inventory Application will use the Service Provider Interface (SPI) method to access its database. In this method the application uses a JDBC DataSource interface to establish connections with the database. This is the preferred access method for a JEE application for several reasons:

• Program code will be totally database independent. Driver information, database location, and configuration parameters are stored in the JEE Server.
• It allows the use of connection pooling. The JEE Server connection manager effectively manages connections to greatly improve performance and scalability.
• It enables the database to be used by Enterprise JavaBeans (EJB) to implement business logic as part of the JEE Server. Implementing an EJB tier, although not required, lays the foundation for creating a highly scalable, distributed application architecture.

After reading this article you should be able get the best out of the JDBC features of Geronimo, such as defining database pools and using DataSources to access databases.

This article is organized in to following sections.

Overview of JDBC Features

JDBC implementation in application servers vary from application server to other. Following table gives a feature list of JDBC in Apache Geronimo.

Application Overview

The Inventory application in this article only supports three basic usecases of such applications.

1. Add Items to the Stock
2. Receive Items
3. Issue Items
   The application workflow starts with adding item information to the stock. Then it allows enter goods receiving and issuing information. All those updated information are stored in the inbuilt Derby database.

The Inventory Web Application has following list of pages

• Welcome
• Add Item
• Receive Goods
• Issue Goods

The following figure illustrates the application flow.
Image Removed

Welcome page of the application acting as a notice board which displays current stock of each item. Through the Welcome page users can access Add Item, Receive Goods or Issue Goods Pages. Upon successful completion of each activity, the page will be redirected back to the Welcome page with updated stock information. Add Item helps to define items in the stock, then 0 number of items will be added to the stock. Receive and Issue Goods pages represent Goods Receiving and Issuing activities of the application respectively.

Application contents

The Inventory application consist of following list of packages and classes.

• org.apache.geronimo.samples.inventory
  • Item - represents Item in the Inventory.
• org.apache.geronimo.samples.inventory.services
  • InventoryManager - represents list of services offered by the inventory.
• org.apache.geronimo.samples.inventory.dao
  • ItemDAO - contains all database access methods.
• org.apache.geronimo.samples.inventory.exception
  • DuplicateItemIdException - custom exception to handle duplication item id scenario.
  • NotSufficientQuantityException - Custom exception to handle not sufficient quantity situation.
• org.apache.geronimo.samples.inventory.util
  • DBManager - handle database related activities such as issuing database connections.
• org.apache.geronimo.samples.inventory.web
  • AddItemServlet - dispatch add item information to service layer.
  • IssueingServlet - dispatch issuing items information to service layer.
  • RecievingServlet - dispatch receiving items information to service layer.

The list of web application files in the application is depicted in the following.

|- jsp
    +- add.jsp
    +- error.jsp
    +- issue.jsp
    +- recv.jsp
|- WEB-INF
    +- geronimo-web.xml
    +- web.xml
|- welcome.jsp

This application defines a datasource with the help of geronimo-web.xml and web.xml deployment plans. geronimo-web.xml adds a link to the database pool that is packaged with the EAR file.

sample illustrates direct use of JDBC connections from a servlet. For details on outbound connection support see Connectors and Transaction Management (JDBC, JMS, J2CA, DataSource, Connection Pool, EIS). Generally direct use of JDBC is not advised unless you have performance constraints or need for dynamic jdbc (such as in a database browser) that make the use of JPA impractical.

This article is organized in to following sections.

Application Overview

The Inventory application in this article only supports three basic usecases of such applications.

1. Add Items to the Stock
2. Receive Items
3. Issue Items
   The application workflow starts with adding item information to the stock. Then it allows enter goods receiving and issuing information. All those updated information are stored in the sample database, by default Derby.

The Inventory Web Application has following list of pages

• Welcome
• Add Item
• Receive Goods
• Issue Goods

The following figure illustrates the application flow.
Image Added

Welcome page of the application acting as a notice board which displays current stock of each item. Through the Welcome page users can access Add Item, Receive Goods or Issue Goods Pages. Upon successful completion of each activity, the page will be redirected back to the Welcome page with updated stock information. Add Item helps to define items in the stock, then 0 number of items will be added to the stock. Receive and Issue Goods pages represent Goods Receiving and Issuing activities of the application respectively.

Application contents

The Inventory application consist of following list of packages and classes.

• org.apache.geronimo.samples.inventory
  • Item - represents Item in the Inventory.
• org.apache.geronimo.samples.inventory.services
  • InventoryManager - represents list of services offered by the inventory.
• org.apache.geronimo.samples.inventory.dao
  • ItemDAO - contains all database access methods.
• org.apache.geronimo.samples.inventory.exception
  • DuplicateItemIdException - custom exception to handle duplication item id scenario.
  • ItemException - wraps data access exceptions (NamingException, SQLException).
  • NotSufficientQuantityException - Custom exception to handle not sufficient quantity situation.
• org.apache.geronimo.samples.inventory.web
  • AddItemServlet - dispatch add item information to service layer.
  • IssueingServlet - dispatch issuing items information to service layer.
  • RecievingServlet - dispatch receiving items information to service layer.

The list of web application files in the application is depicted in the following.

|- jsp
    +- add.jsp
    +- error.jsp
    +- issue.jsp
    +- recv.jsp
|- WEB-INF
    +- geronimo-web.xml
    +- web.xml
|- welcome.jsp

This application defines a datasource with the help of the geronimo plan and web.xml deployment plans. The Geronimo plan from inventory-jetty or inventory-tomcat links the internal JNDI name "java:comp/env/jdbc/InventoryDS" to the sample database.

<?

<?xml version="1.0" encoding="UTF-8"?>
<web-app
	 xmlns="http://geronimo.apache.org/xml/ns/j2ee/web-2.0.1">
	
	<environment>
		<moduleId>
			<groupId>org.apache.geronimo.samples</groupId>
			<artifactId>inventory-war</artifactId>
			<version>2.1</version>
                        <type>war</type>
		</moduleId>
		<dependencies></dependencies>		
	</environment>
		
	<context-root>/inventory</context-root>
	
	<!-- define a reference name to the db pool-->
	<resource-ref>
        <ref-name>jdbc/InventoryDS</ref-name>.org/xml/ns/j2ee/web-2.0.1">
  <dep:environment xmlns:dep="http://geronimo.apache.org/xml/ns/deployment-1.2">
    <dep:moduleId>
      <dep:groupId>org.apache.geronimo.samples</dep:groupId>
      <dep:artifactId>inventory-jetty</dep:artifactId>
        <resource-link>InventoryPool</resource-link><dep:version>2.2-SNAPSHOT</dep:version>
    </resource-ref>    
</web-app>

Following is the web.xml of the Inventory application. It uses same name as in the geronimo-web.xml, which is used to create the datasource.

...

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
	 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	 xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
	 http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
	 version="2.4">	
	 
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
  	</welcome-file-list>  
  	
  	<servlet>
	    <display-name>AddItemServlet</display-name>
	    <servlet-name>AddItemServlet</servlet-name>
	    <servlet-class>org.apache.geronimo.samples.inventory.web.AddItemServlet</servlet-class>
  	</servlet>
  	<servlet>
	    <display-name>IssueingServlet</display-name>
	    <servlet-name>IssueingServlet</servlet-name>
	    <servlet-class>org.apache.geronimo.samples.inventory.web.IssueingServlet</servlet-class>
	</servlet>
	<servlet>
	    <display-name>RecievingServlet</display-name>
	    <servlet-name>RecievingServlet</servlet-name>
	    <servlet-class>org.apache.geronimo.samples.inventory.web.RecievingServlet</servlet-class>
	</servlet>
  	
  	<servlet-mapping>
	    <servlet-name>AddItemServlet</servlet-name>
	    <url-pattern>/add_item</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
	    <servlet-name>IssueingServlet</servlet-name>
	    <url-pattern>/issue</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
	    <servlet-name>RecievingServlet</servlet-name>
	    <url-pattern>/recv</url-pattern>
    </servlet-mapping>
    
    <!-- reference name exposed as a datasource -->
    <resource-ref>
    	<res-ref-name>jdbc/InventoryDS</res-ref-name>
    	<res-type>javax.sql.DataSource</res-type>
    	<res-auth>Container</res-auth>
    	<res-sharing-scope>Shareable</res-sharing-scope>
  	</resource-ref>
    
</web-app>

geronimo-application.xml tells the application that there is a database pool that needs to be deployed as well. The db pool is defined in InventoryPool.xml and the driver that is needs in order to be deployed is the tranql-connector-ra-1.3.rar file--these two files will reside on the top level layer of the resultant EAR file.

...

<?xml version="1.0" encoding="UTF-8"?>
<application xmlns="http://geronimo.apache.org/xml/ns/j2ee/application-2.0">

    <dep:environment xmlns:dep="http://geronimo.apache.org/xml/ns/deployment-1.2">
        <dep:moduleId>
            <dep:groupId>org.apache.geronimo.samples</dep:groupId>
            <dep:artifactId>inventory-ear</dep:artifactId>
            <dep:version>2.1</dep:version>
            <dep:type>ear</dep:type>
        </dep:moduleId>
    </dep:environment>
	<module>
		<connector>tranql-connector-ra-1.3.rar</connector>
		<alt-dd>InventoryPool.xml</alt-dd>
	</module>
</application>

  <dep:type>car</dep:type>
    </dep:moduleId>
    <dep:dependencies>
      <dep:dependency>
        <dep:groupId>org.apache.geronimo.configs</dep:groupId>
        <dep:artifactId>jasper</dep:artifactId>
        <dep:version>2.2-SNAPSHOT</dep:version>
        <dep:type>car</dep:type>
      </dep:dependency>
      <dep:dependency>
        <dep:groupId>org.apache.geronimo.configs</dep:groupId>
        <dep:artifactId>jetty6</dep:artifactId>
        <dep:version>2.2-SNAPSHOT</dep:version>
        <dep:type>car</dep:type>
      </dep:dependency>
      <dep:dependency>
        <dep:groupId>org.apache.geronimo.samples</dep:groupId>
        <dep:artifactId>sample-datasource</dep:artifactId>
        <dep:version>2.2-SNAPSHOT</dep:version>
        <dep:type>car</dep:type>
      </dep:dependency>
    </dep:dependencies>
    <dep:hidden-classes/>
    <dep:non-overridable-classes/>
  </dep:environment>
  <context-root>/inventory</context-root>
  <!--define a reference name to the db pool-->
  <resource-ref>
    <ref-name>jdbc/InventoryDS</ref-name>
    <resource-link>SampleTxDatasource</resource-link>
  </resource-ref>
</web-app>

Following is the web.xml of the Inventory application. It declares the name "java:comp/env/jdbc/InventoryDS" used in ItemDAO to lookup the datasource.

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
	 http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
         version="2.4">

    <welcome-file-list>
        <welcome-file>index.html</welcome-file>
    </welcome-file-list>

    <servlet>
        <display-name>AddItemServlet</display-name>
        <servlet-name>AddItemServlet</servlet-name>
        <servlet-class>org.apache.geronimo.samples.inventory.web.AddItemServlet</servlet-class>
    </servlet>
    <servlet>
        <display-name>IssueingServlet</display-name>
        <servlet-name>IssueingServlet</servlet-name>
        <servlet-class>org.apache.geronimo.samples.inventory.web.IssueingServlet</servlet-class>
    </servlet>
    <servlet>
        <display-name>RecievingServlet</display-name>
        <servlet-name>ReceivingServlet</servlet-name>
        <servlet-class>org.apache.geronimo.samples.inventory.web.ReceivingServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>AddItemServlet</servlet-name>
        <url-pattern>/add_item</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>IssueingServlet</servlet-name>
        <url-pattern>/issue</url-pattern>
    </servlet-mapping>
    <servlet-mapping>
        <servlet-name>ReceivingServlet</servlet-name>
        <url-pattern>/recv</url-pattern>
    </servlet-mapping>

    <!-- reference name exposed as a datasource -->
    <resource-ref>
        <res-ref-name>jdbc/InventoryDS</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
        <res-sharing-scope>Shareable</res-sharing-scope>
    </resource-ref>

</web-app>

Next important phase of the application is accessing defined datasource from the source code. This part is handled by the DBManager class.

package org.apache.geronimo.samples.inventory.util;

import java.sql.Connection;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;


public class DBManager {
	
	public static Connection getConnection(){
		Connection con = null;

		try {
			Context context = new InitialContext();
			DataSource ds = (DataSource)context.lookup("java:comp/env/jdbc/InventoryDS");
			con = ds.getConnection();
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}

}

Sample Database

The sample database that is being used to demonstrate this application is in-built Derby database. The name of the sample database is InventoryDB and it consists of two tables, namely ITEM and ITEM_MASTER. The fields for each of these tables are described below.

...