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.
- Add Items to the Stock
- Receive Items
- 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.
- 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"> <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>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>
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_MASTER |
ITEM_ID (PRIMARY KEY) |
The ITEM table stores the data related to the items while ITEM_MASTER stores the quantity in hand of each item.
Tools used
The tools used for developing and building the Inventory sample application are:
Apache Derby
Apache Derby, an Apache DB subproject, is a relational database implemented in Java. Its footprint is so small you can easily embed it in any Java-based solution. In addition to its embedded framework, Derby supports a more familiar client/server framework with the Derby Network Server.
http://db.apache.org/derby/index.html
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
Configuring, building and deploying
Currently this sample application is available from our subversion repository, use the following svn command to retrieve the content:
svn co http://svn.apache.org/repos/asf/geronimo/samples/branches/2.1/samples/inventory/ 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.
Creating and Populating Database
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.
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);
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.1-SNAPSHOT.ear file under the <sample_home> directory. Now, you are ready to deploy Inventory web application in to the Geronimo Application server.
Deploying
Deploying sample application is pretty straight forward as we are going to use the Geronimo Console.
- Click the Deploy New link on the Console Navigation panel.
- Load inventory-ear-2.1-SNAPSHOT.ear file from <sample_home> directory into the Archive input box.
- Press Install button to deploy the application in the server.
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.
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.