apache > db
Apache DB Project
Font size:      

Using Apache Derby with iBATIS JPetStore 4 running on the Geronimo J2EE server

The first tasks performed when installing an application that uses a database are generally related to database setup. These steps often involve compiling DBMS code (if the database system is not already installed) and creating and populating the physical database used by the application. Such steps are required to create executables and files that can be used in the operating environment of the host computer. By using a database like Derby that is implemented entirely in JAVA these build steps are not required. Installation of the Derby DBMS can be as simple as copying a 2 Mb jarfile to a known location. The fully initialized physical database can be bundled with the application thus reducing the database setup steps to a simple copy operation. The following instructions demonstrate this by deploying the iBATIS JPetStore application along with a ready-to-go Derby database on a Geronimo server. These instructions can be used on any machine with a functioning J2SE JVM using the ASCII codeset. Conversion or recreation of the supporting files (but not the database) will be required for deployment in an EBCDIC environment.


This article demonstrates the ease with which a J2EE application that uses a Derby database (the iBATIS JPetStore 4.0 web-based sales application) can be setup and deployed on the Geronimo J2EE server. iBATIS JPetStore is based on Sun's J2EE Pet Store and can be setup to use a variety of databases and executed on a variety of J2EE or JSP Servers. JPetStore was written by Clinton Begin as a comparison of the .NET and J2EE architectures. JPetStore uses Struts, the iBATIS data mapper framework and JAVA to implement the application logic and user interface. The three components used to implement this deployment of JPetStore, iBATIS, Geronimo and Derby, are all being developed by the Apache community and are freely available thru the Apache Foundation Website.

Typographic Conventions Used

The following codes in curly braces will be used to represent installation dependant file locations as described below:

  • {GERONIMO_HOME}: The installation directory for the Geronimo Server. Note that this installation path should not contain any spaces.
  • {machine-name}: the hostname of the machine on which the server and application are installed.
  • {Derby_Jars}: the directory containing the Derby jar file(s). The deployment files provided with this article use the derby jars provided with Geronimo. Verify that the file derby- exists in the directory {GERONIMO_HOME}\repository\incubator-derby\jars.
  • {Derby_System_Home}: the directory containing the derby.log file, Geronimo SystemDatabase and the application Databases directory tree. To use the files provided with this article this location should be {GERONIMO_HOME}/var/derby. Note that Geronimo creates this directory the first time it starts and sets the derby.system.home variable to this location. The JPetStoreDB database will be located in the {Derby_System_Home}\Databases subdirectory.

Required Software / Downloads

  • If necessary, install a functional J2SE Java Developers Kit (JDK). A JDK of version 1.4.1 or higher is recommended. The JDK 'bin' directory should be included in your PATH.
  • Download the Geronimo 1.0 Milestone 4 release. Extract the Geromino installation files to {GERONIMO_HOME}.
  • Download the JPetStoreAPP4Geronimo.zip file associated with this article. Extract the files to a convenient working directory.

Setup Steps:

  • Extract the database zipfile and deployment files: Unzip the files from JPetStoreAPP4Geronimo.zip into a working directory.
  • Move deployment files to {GERONIMO_HOME}:Move the following three files to the {GERONIMO_HOME} directory:
    • jpetstoreAPP.war - The JPetstore application web archive file.
    • jpetstoreAPP-geronimo-jetty.xml - The Geronimo web application deployment descriptor.
    • DbPoolDeployPlan.xml - The Derby datasource deployment descriptor.
    Using the file setupM4.bat as a template, create a script file for your platform that defines the locations on your machine of the Geromino installation, the Geronimo J2EE jarfile and the Java JRE or JDK.
  • Start Geronimo: In a command window/shell, execute the customized setupM4 script file then start the Geronimo server from {GERONIMO_HOME} using the command: java -jar bin\server.jar
  • Setup the JPetStoreDB database: Unzip the files from JPetStoreAPP4Geronimo.zip into the {Derby_System_Home} directory ({GERONIMO_HOME}/var/derby).
  • Deploy and start the datasource: In a new command shell/window execute the customized setupM4 script file then issue the following commands to deploy and start the JPetstore datasource:
       > java -jar bin/deployer.jar --user system --password manager distribute DbPoolDeployPlan.xml repository/tranql/rars/tranql-connector-1.0-20050716.rar
          Distributed JPetStoreDB
       > java -jar bin/deployer.jar --user system --password manager start JPetStoreDB
          Started JPetStoreDB
  • Deploy and start the application: Issue the following commands to deploy and start the iBATIS JPetStore application:
        >  java -jar bin\deployer.jar --user system --password manager distribute jpetstoreAPP.war jpetstoreAPP-geronimo-jetty.xml
             Distributed JPetStoreAPP
        > java -jar bin/deployer.jar --user system --password manager start JPetStoreAPP
              Started JPetStoreAPP
  • Test the JPetStoreAPP: Open your browser and enter the following URL:

Optional Database Setup Instructions

The following steps show how to build the Derby database from scratch. Two files (*.sql) are supplied in the JPetStoreAPP4Geronimo.zip. file to perform the initial build and data inserts. You will need to download the complete set of Derby jarfiles and use the IJ tool found in derbytools.jar to perform the build. Place derbytools.jar in the {Derby_Jars} directory. The IJ tool is used to process the SQL commands in the provided scripts. For simplicity the examples below place all necessary files in the same directory and specify as much a possible (including the command to create the database) on the command line.

  • Copy the following SQL script files to the {Derby_System_Home}\Databases subdirectory where you will build the database: ,jpetstore-derby-schema.sql, jpetstore-derby-dataload.sql. Issue the following command to create the database, tables and indexes:
       java -cp {Derby_Jars}/derby.jar;{Derby_Jars}/derbytools.jar -Dij.database=jdbc:derby:JPetStoreDB;create=true 
    	   org.apache.derby.tools.ij jpetstore-derby-schema.sql
  • Now load the data into the tables using the following command:
    	java -cp {Derby_Jars}/derby.jar;{Derby_Jars}/derbytools.jar -Dij.database=jdbc:derby:JPetStoreDB
    	   org.apache.derby.tools.ij jpetstore-derby-dataload.sql

You now have a fully populated database to use with the JPetStore application.

Integration Notes

J2EE Servers use multiple classloaders (aka hierarchies) to provide the isolation necessary to run many applications at once. This Derby-JPetStore integration uses a server datasource to avoid problems that can occur when Derby is used in an environment using multiple classloaders. Derby is written in Java and all its classes must be loaded by the same classloader. When using a J2EE Server this can be assured by defining Derby datasources at the server level. This is particularly important when using Derby in it's embedded configuration. When using a database or other datasource in a J2EE environment it is also a good design practice to access them via a J2EE resource managed by the server.

The zipfile provided contains a fully initialized database to demonstrate that a Derby database built and populated on one platform (in this case Windows) can be transferred to different platforms and work fine. Try copying the database and derby jarfiles to different platforms and you will see that the system works without modification. The text files included in the zipfile will not do well in an EBCDIC architecture but the Derby engine and supplied database will work fine. If you want to build the database from scratch you can use the SQL files supplied in the archive. See the 'Optional Database Setup Instructions' section for how to use the IJ tools to build and populate the database.

If you wish to build your own iBATIS JPetstore war file like the one supplied in the JPetStoreAPP4Geronimo.zip file you will need to download the JPetStore application source code (see the 'Related Links' section) and make the following modifications to the source files before performing a build as described in the JPetStore build instructions:

  • Edit the sql-map-config.xml file and replace the existing TransactionManager definition section with the this section that specifies a JNDI lookup should be performed to obtain the datasource to use:
       <transactionManager type="JDBC" >
          <dataSource type="JNDI">
             <property name="DBJndiContext" value="jdbc/JPetStoreDB"/>
  • Edit the web.xml file and replace the existing resource-ref definition with the following:
       <description>JPetStore DataSource</description>

If you are interested in using Geronimo you may also want to check out the Gluecode SE application server. The Gluecode SE application server bundles Geronimo and provides a GUI management console that simplifies and automates the operations outlined above. Rather than creating the XML descriptor files using a text editor the datasource and application deployments are performed from console screens. Starting the datasource(s) and application(s) can also be done from the console or defined to start automatically when the Gluecode server starts. For more information see the Geronimo link in the 'Related Links' section of this document.

Related Links

Required Downloads to perform this deployment:

Download links for software used in creating this deployment:

Main websites references:

Related products and artilces: