HomeDocumentation > Apache Geronimo v1.1 - User's Guide > Sample applications > DB Pool Testing sample application


Accessing application server specific features and using them in your J2EE application would make it more powerful than accessing only the J2EE features from them. It gives you the ability to write extensions to your application server.

This sample application lists all the database connection pools defined in Geronimo. You can select any of these connection pools and test the connectivity against the database as well as listing existing schemas and tables. In addition, the user can view the records of the each of the listed tables.

After reading this article you should be able to access Geronimo specific resources from your applications and use them in an effective manner.

This article is organized into the following sections :-

Application Overview


The sample application covered in this article will help you to test the Database connection pools deployed in your Geronimo server. One can consider this as an extension to the Geronimo console because the current version does not contain the ability to test connections to database pools after they have been deployed.

The following figure illustrates the application flow.

Welcome page of the application acts as a notice board which displays list of Database connection pools deployed in the Geronimo application server. Users can directly test those connection pools from the first page. If that particular connection pool requires a username and a password to get a connection, enter those details in the pop up window that appears. The list of database schemas and the tables associated with the connection pool will be displayed in the Schemas and Tables page. The contents of each table can be accessed from there on.

Application contents

The Inventory application consists of following list of packages.

  • org.apache.geronimo.samples.dbtester.beans
    • DBManagerBean - Heart of the application which handles most of the application logic (including access of Geronimo Kernel).
  • org.apache.geronimo.samples.dbtester.web
    • ContentTableServlet - Gets the content of a Database table and passes them to the presentation
      layer.
    • ListTablesServlet - Gets the list of schemas and tables accociated with a database pool.

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

|- jsp
    |- common_error.jsp
    |- popup.jsp
    |- table_content.jsp
    |- table_list.jsp
|- WEB-INF
    |- geronimo-web.xml
    |- web.xml
|- index.jsp

geronimo-web.xml defines the list of dependencies that have to be loaded into the web application class loader.

geronimo-web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://geronimo.apache.org/xml/ns/j2ee/web-1.1">
        
    <environment>
        <moduleId>
            <groupId>org.apache.geronimo.samples.dbtester</groupId>                 
            <artifactId>dbtester</artifactId>
            <version>1.0</version>
        </moduleId>
        <dependencies>
            <dependency>
                <groupId>geronimo</groupId>
                <artifactId>geronimo-kernel</artifactId>
            </dependency>
            <dependency>
                <groupId>geronimo</groupId>
                <artifactId>geronimo-connector</artifactId>
            </dependency>
            <dependency>
                <groupId>geronimo</groupId>
                <artifactId>geronimo-management</artifactId>
            </dependency>
            <dependency>
                <groupId>mx4j</groupId>
                <artifactId>mx4j</artifactId>
            </dependency>
        </dependencies> 
    </environment>
                
    <context-root>/dbtester</context-root>

</web-app>

web.xml defines two servlets that will act as the control layer between presentation and service layers.

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.jsp</welcome-file>
  	</welcome-file-list>
    	
    <servlet>
	    <display-name>ContentTableServlet</display-name>
	    <servlet-name>ContentTableServlet</servlet-name>
		<servlet-class>
			org.apache.geronimo.samples.dbtester.web.ContentTableServlet
		</servlet-class>
  	</servlet>
	
	<servlet>
	    <display-name>ListTablesServlet</display-name>
	    <servlet-name>ListTablesServlet</servlet-name>
		<servlet-class>
			org.apache.geronimo.samples.dbtester.web.ListTablesServlet
		</servlet-class>
  	</servlet>
  	
    <servlet-mapping>
	    <servlet-name>ContentTableServlet</servlet-name>
	    <url-pattern>/listContent</url-pattern>
    </servlet-mapping>
	
	<servlet-mapping>
	    <servlet-name>ListTablesServlet</servlet-name>
	    <url-pattern>/listTables</url-pattern>
    </servlet-mapping>
        
</web-app>

The most important part of this application is how to access Geronimo kernel and retrieve the list of database pools deployed there. This task is handled by the DBManagerBean class.

DBManagerBean.java
	private void init(){
		Kernel kernel = KernelRegistry.getSingleKernel();
		Set cfList = kernel.listGBeans(new AbstractNameQuery(ConnectionFactorySource.class.getName()));
		
		for(Iterator iterator = cfList.iterator();iterator.hasNext();){

			AbstractName name = (AbstractName)iterator.next();
			try {
				Object rs = kernel.invoke(name, "$getResource", new Object[]{}, new String[]{});
				
				if(rs instanceof javax.sql.DataSource){
					DataSource ds = (DataSource)rs;	
					poolMap.put(name.getArtifact().getArtifactId(), ds);
				}
			} catch (GBeanNotFoundException e) {
				e.printStackTrace();
			} catch (NoSuchOperationException e) {
				e.printStackTrace();
			} catch (InternalKernelException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

To retrieve the list of schemas and their tables, the application uses database metadata provided in a JDBC driver. ResultSet meta data has been used to get record related data and to display database contents. The following code snippet depicts how the application retrieves the schemas and their tables in a DataSource.

DBManagerBean.java
	public Map getTableList(String poolName) throws SQLException {
		
		tableMap = new HashMap();
		
		if(poolMap.containsKey(poolName)){
			DataSource ds = (DataSource)poolMap.get(poolName);
			Connection con = null;
			try {
				
				con = ds.getConnection();
				
				DatabaseMetaData metaData = con.getMetaData();
				String[] tableTypes = {"TABLE"};
				ResultSet rs = metaData.getTables(null, null, null, tableTypes);
				
				while(rs.next()){
					String schemaName = rs.getString("TABLE_SCHEM");
					String tableName = rs.getString("TABLE_NAME"); 
					ArrayList tableList = null;
					
					if(tableMap.containsKey(schemaName)){
						tableList = (ArrayList)tableMap.get(schemaName);
						tableList.add(tableName);
					}else {
						tableList = new ArrayList();
						tableList.add(tableName);
					}
					tableMap.put(schemaName, tableList);
				}
			} catch (SQLException e) {
				throw e;
			}finally {
				if(con != null){
					try {
						con.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
		
		return tableMap;
	}

Tools used

The tools used for developing and building the DB List sample application are:

Eclipse

The Eclipse IDE was used for development of the this sample application. This is a very powerful and popular open source development tool. Integration plug-ins are available for the Geronimo application server too. Eclipse can be downloaded from the following URL:
http://www.eclipse.org

Apache Maven 2

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

Building and Deploying the Sample Application

Download the dbtester application from the following link:
dbtester

After extracting the zip file, the dbtester directory will be created.

Building

dbtester application comes with a Maven 2 script to help users to build from source code. Open a command prompt window and navigate to the dbtester directory and issue the mvn clean install command. This will create a dbtester-1.0.war file under the target folder within dbtester. Now,dbtester web application is ready to be deployed on the Geronimo Application server.

Deploying

Deploying sample application is pretty straight forward, since we will be using Geronimo Console.

  1. Navigate to Deploy New link from the Console Navigation panel.
  2. Load dbtester-1.0.war file from dbtester/target folder onto the Archive input box.
  3. 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/dbtester. This will load the index page of dbtester application which acts as a notice board, with the list of database pools deployed in Geronimo.

The user can directly test the listed database pools with this sample application. Furthermore, the application can be used to list the contents of the databases.

Summary

This article has shown you how to access Geronimo related features from a J2EE application. You followed step-by-step instructions to build, deploy and test a sample application to elaborate these features. This sample application can be used as tester for database connection pools deployed in Geronimo.