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

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.
  • ReceivingServlet - 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">
  <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>
      <dep:version>2.2-SNAPSHOT</dep:version>
      <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>ReceivingServlet</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>

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.

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

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.

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