Wiki Markup
{scrollbar}

AnchortoptopThis 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 it's 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 J2EE JEE application for several reasons:

Program code will be totally database independent. Driver information, database location, and configuration parameters are stored in the J2EE JEE Server.
It allows the use of connection pooling. The J2EE 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 J2EE JEE Server. Implementing an EJB tier, though although not required, lays the foundation for creating a highly scalable, distributed application architecture.

...

This article is organized in to following sections.

...

Table of Contents

...

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.
Feature
Description
JDBC access
Geronimo does not have any direct integration with JDBC but supports access through the generic J2CA framework. The TranQL project has J2CA adapters for various databases.
JCA implementation
Geronimo supports the JCA 1.5 specification and is backward compatible to the JCA 1.0 specification.
Data sources supported
TranQL has generic wrappers for each data sources.
Data source failover
TranQL has specialized drivers for certain databases (including Apache Derby, Oracle and DB2) that provide a tighter integration with the advanced features of the driver.
It is at this level that features such as load-balancing and failover would be provided. You can also use a C-JDBC wrapper for providing database clustering and failover.
XA support
Supports XA transactions, Local Transactions, and No transaction.
Connection Manager Configurability
The J2CA framework is interceptor based which allows different parts of the connection framework to be plugged in.
JTA implementation
Transaction support is provided through Geronimo Specific Transaction Managing Framework and HOWL.
Connection pooling and management
Custom Geronimo Code and TranQL used for connection pooling and management.
Legacy driver support
Geronimo provides this through the TranQL- connector JDBC to JCA wrapper in Geronimo. Supports JDBC 3.0 and 2.1.

Application Overview

...

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

...

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

No Format

borderColor	#FFFFFF
bgColor	#FFFFFF
borderStyle	solid

Code Block

java

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

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

Code Block

	xml
	xml
borderStyle	solid
title	geronimo-web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app
	xmlns="http://geronimo.apache.org/xml/ns/j2ee/web-12.0.1">
	
	<environment>
		<moduleId>
			<groupId>${pom.groupId}<<groupId>org.apache.geronimo.samples</groupId>
			<artifactId>${pom.artifactId}<<artifactId>inventory-war</artifactId>
			<version>${version}<<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>
        <resource-link>InventoryPool</resource-link>
    </resource-ref>    
</web-app>

...

Code Block

	xml
	xml
borderStyle	solid
title	web.xml

<?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.

Code Block

	xml
	xml
2	geronimo-application.xml
borderStyle	solid

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

    <dep:environment xmlns:dep="http://geronimo.apache.org/xml/ns/deployment-1.12">
        <dep:moduleId>
            <dep:groupId>${pom.groupId}<groupId>org.apache.geronimo.samples</dep:groupId>
            <dep:artifactId>${pom.artifactId}<artifactId>inventory-ear</dep:artifactId>
            <dep:version>${version}<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>

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

Code Block

	java
	java
borderStyle	solid
title	DBManager.java

public static Connection getConnection(){
	Connection con = null;
	try {
		Context context = new InitialContext();
		DataSource ds = (DataSourcepackage 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.

Table Name	Fields
ITEM	ITEM_ID (PRIMARY KEY) ITEM_NAME DESCRIPTION
ITEM_MASTER	ITEM_ID (PRIMARY KEY) QUANTITY

The ITEM table stores the data related to the items while ITEM_MASTER stores the quantity in hand of each item.

...

Maven is a popular open source build tool for enterprise Java projects, designed to take much of the hard work out of the build process. Maven uses a declarative approach, where the project structure and contents are described, rather than the task-based approach used in Ant or in traditional make files, for example. This helps enforce company-wide development standards and reduces the time needed to write and maintain build scripts. The declarative, lifecycle-based approach used by Maven 1 is, for many, a radical departure from more traditional build techniques, and Maven 2 goes even further in this regard. Maven 2 can be download from the following URL:
http://maven.apache.org
Back to Top

Configuring,

...

building and deploying

Currently this sample application is available from our subversion repository, use the following svn command to retrieve the content:

...

Download the Inventory application from the following link:
Inventory

After decompressing the given file, the inventory directory is created.

Source Code

You can checkout the source code of this sample from SVN:

svn checkout svn co http://svn.apache.org/repos/asf/geronimo/samples/trunkbranches/2.1/samples/inventory/^{Image Modified} inventory_home

A directory named inventory_home will be created when you check out from the source, you can choose a directory name and location convenient to you. We will generically refer to this directory as <sample_home>.

Configuring

Configuration of the application consists of creating the database and defining the connection pool to access it.

...

After starting Apache Geronimo log into the console and follow the given steps to create the InventoryDB database.

Select DB Manager link from the Console Navigation panel on the left.
Give the database name as InventoryDB and click Create button.
Select InventoryDB to the Use DB field.
Open InventoryDB.sql in the <sample_home>/inventory-ear/src/main/resources directory from a text editor.
Paste the content InventoryDB.sql to the SQL Commands text area and press Run SQL button.

No Format

borderStyle	solid
title	InventoryDB.sql

No Format

borderStyle	solid
title	InventoryDB.sql

CREATE TABLE item(
	item_id VARCHAR(10) PRIMARY KEY,
	item_name VARCHAR(25),
	description VARCHAR(100)	
);

CREATE TABLE item_master(
	item_id VARCHAR(10) PRIMARY KEY,
	quantity INTEGER
);

INSERT INTO item VALUES('001', 'Item 1', 'Test Item 1');
INSERT INTO item VALUES('002', 'Item 2', 'Test Item 2');
INSERT INTO item VALUES('003', 'Item 3', 'Test Item 3');
INSERT INTO item VALUES('004', 'Item 4', 'Test Item 4');


INSERT INTO item_master VALUES('001', 12);
INSERT INTO item_master VALUES('002', 8);
INSERT INTO item_master VALUES('003', 49);
INSERT INTO item_master VALUES('004', 34);

...

, 34);

Building

Inventory application comes with an pom.xml script to help users to build from source code. Use the command prompt to navigate into the inventory directory and just give mvn install command to build. It will create the inventory-ear-2.01-SNAPSHOT.ear file under the inventory folder <sample_home> directory. Now, you are ready to deploy Inventory web application in to the Geronimo Application server.

...

Click the Deploy New link on the Console Navigation panel.
Load inventory-ear-2.01-SNAPSHOT.ear file from inventory folder <sample_home> directory into the Archive input box.
Press Install button to deploy the application in the server.

Back to Top

Testing of the Sample Application

...

To test the sample application, open a browser and type http://localhost:8080/inventory. The Welcome page of Inventory application which is acts as a notice board will be loaded.

Wiki Markup
!inventory.pgn\|align=center!

Image Removed

The user can directly access Add Items, Receive Goods and Issue Goods functionalities from the Welcome page.

Summary

...

This article has shown you how to use JDBC features inside the Geronimo Application Server. You followed step-by-step instructions to build, deploy and test a sample application to elaborate these features.

The highlights of this article are: -

JDBC features in Apache Geronimo.
Create a database and populate the data in Geronimo with in built Derby Database.
Deploy a database pool plan to access a database.
Deploy web archives to access database via the pool defined in Geronimo.

Child pages

Versions Compared

Old Version 1

New Version 2

Key

Overview

of JDBC Features

Application Overview

Sample Database

Configuring,

building and deploying

Source Code

Configuring

Building

Testing of the Sample Application

Summary

Feature	Description
JDBC access	Geronimo does not have any direct integration with JDBC but supports access through the generic J2CA framework. The TranQL project has J2CA adapters for various databases.
JCA implementation	Geronimo supports the JCA 1.5 specification and is backward compatible to the JCA 1.0 specification.
Data sources supported	TranQL has generic wrappers for each data sources.
Data source failover	TranQL has specialized drivers for certain databases (including Apache Derby, Oracle and DB2) that provide a tighter integration with the advanced features of the driver. It is at this level that features such as load-balancing and failover would be provided. You can also use a C-JDBC wrapper for providing database clustering and failover.
XA support	Supports XA transactions, Local Transactions, and No transaction.
Connection Manager Configurability	The J2CA framework is interceptor based which allows different parts of the connection framework to be plugged in.
JTA implementation	Transaction support is provided through Geronimo Specific Transaction Managing Framework and HOWL.
Connection pooling and management	Custom Geronimo Code and TranQL used for connection pooling and management.
Legacy driver support	Geronimo provides this through the TranQL- connector JDBC to JCA wrapper in Geronimo. Supports JDBC 3.0 and 2.1.

Child pages

Page History

Versions Compared

Old Version 1

New Version 2

Key

Overview

of JDBC Features

Application Overview

Sample Database

Configuring,

building and deploying

Source Code

Configuring

Building

Testing of the Sample Application

Summary