apache > db
Apache DB Project
 
Font size:      

Using Apache Derby with iBATIS JPetStore 4 on the WebSphere Server

Introduction

JPetStore is a rewritten Pet Store application based on Sun's J2EE Pet Store. It was originally designed to compare the .NET and J2EE architectures. It is now the official example application for the iBATIS SQL Maps 2.0 and DAO 2.0 open source software products. See the 'JPetStore' and 'iBATIS' links in the related links section below to download JPetStore and for more information on the application history and architecture.

This document demonstrates setting up the JPetStore 4.0 application to run on a J2EE Application Server using an embedded Apache Derby database as the persistence layer. Apache Derby is a small footprint, open source, relational database engine written in Java. It is ideally suited for use with J2EE and Web applications where reliability, portability, ease of use and low maintenance is important. Derby is an Apache DB project.

One of the more difficult parts of installing JPetStore is meeting the initial assumption that you have some sort of relational database installed and working. Apache Derby, in it's embedded configuration, is a single 2 Mb jarfile that simply needs to be in the classpath of the application. The jarfile can be bundled with the application files and in many cases the user is not aware that a database is in use at all. Because it's java it does not matter what physical architecture the application will be deployed on. The same database files and database classes can be copied directly to any platform and used as-is. No local application or database build is required, only a functioning JVM is needed.

The following outlines the steps needed to install and run JPetStore 4 using Apache Derby on WebSphere Application Server version 6.0. Though WebSphere is used for this example, any J2EE Application Server could be used to run this demonstration. Only the cookbook details portion on installing the application in WebSphere will differ.

Required Software

  • Downloaded the JPetStore Demo - the download URL is also provided in the Links section below.
  • Downloaded the JPetStoreAPP4WebSphere.zip file or plan to download Derby separately and manually edit the application configuration as described in the CookBook Details section below. The downloads URLs are also provided in the Links section below.
  • WebShere Application Server version 6.0.1. You can obtain an evaluation copy of WebSphere and the 6.0.1 upgrade from IBM. The downloads URLs are also provided in the Links section below.

Setup Overview

Details on the following steps can be found in the CookBook Details section below. The following abbreviations will be used in the sections below:

  • WAS: WebSphere Application Server.
  • {machine-name}: the hostname of the machine on which the server and application are installed.
  • {SrvRoot}: the main directory of the WebSphere application server.
  • {Derby_Home}: the directory into which the Derby jar file(s) are placed. In a Server environment a good choice would be {SvrRoot}/Derby.
  • {AppRoot}: the directory into which the JPetStore WAR file was installed by WebSphere ( if the default names are used this will be: {SrvRoot}/profiles/default/installedApps/{machine-name}Node01Cell/jpetstore_war.ear )

Steps:

  • Setup {Derby_Home}: Unzip JPetStoreAPP4WebSphere.zip and move the Derby directory from the zip file into {Derby_Home}.
  • Create the datasource: Define a Server JDBC datasource for the JPetStore database with the JNDI name of jdbc/JPetStoreDB. Select 'Cloudscape' as the WAS database type and use the Derby embedded XA datasource.
  • Install JPetStore. Use the WAS Administration console to install the downloaded jpetstore.war file. Enter the context root as "/jpetstore". Accept all the defaults except:
    • Step 1: Check "Deploy Web Services".
    • Step 3: Map the application to the datasource created above ( jdbc/JPetStoreDB)
    • Update the JPetStore sql-map-config.xml file with the one provided in the JPetStoreAPP4WebSphere.zip file
    • Restart WebSphere
    • Stat the JPetStore application by entering the following URL into your favorite browser: 
         http://{machine-name}:9080/jpetstore

CookBook Details

Setup Derby

  • Unzip the Derby folder from the JPetStoreAPP4WebSphere.zip file. It is recommended this be placed in the {SrvRoot} directory. The folder contains the initialized JPetStore database (the directory tree JPetStoreDB) the derby.jar file and the new sql-map-config.xml file needed to define the JNDI datasource to the iBATIS system. Two sql files are also included so the database can be built from scratch if desired. See the Integration Notes section for information on how to use the scripts.
  • Starting the WebSphere 'First steps' program will provide an easy way to start and stop the server and the administrative console while performing the operations detailed below.

Create the WebSphere datasource

  • Start WAS and start the administrative console
  • Define a WAS environment variable for {Derby_Home} with the following steps:
    • Navigate the following links to the Node level variable definition screen: Environment > WebSphere Variables (check or set the NODE radio button active - click [APPLY] ) > NEW
    • Enter the following information: 
         Name: DERBY_HOME
   value: ${WAS_INSTALL_ROOT}/Derby
   Description:  JPetStore Derby Files
   Click: [SAVE]
  • Navigate the following links to the Server level JDBC Resource / datasource definition screens: Resources > JDBC providers (check or set the SERVER radio button active - click [APPLY]) > NEW
    • On the General Properties screen select from the drop down lists: 
          database type: Cloudscape
    provider type:  Cloudscape JDBC Provider
    Implementation:  XA data source  
    Click [NEXT]
    • On the JDBC Provider screen enter: 
         Name:Derby JDBC Provider (XA)
   Desc:Derby 10 embedded JDBC2-compliant Provider
   Path:  ${DERBY_HOME}/derby.jar
   Implementation class name :  org.apache.derby.jdbc.EmbeddedXADataSource
   Click [APPLY]
    • Under 'Additional Properties' click 'Data Sources'. On the Data Sources screen click 'New' and enter: 
         Name: JPetStoreDB
   JNDI Name:  jdbc/JPetStoreDB
   Uncheck the checkbox labeled: Use this Data Source in container managed persistence (CMP)
   Click the radio button labeled: Data store helper classes provided by WebSphere Application Server 
   Select the "Generic Datastore Helper"
   Click [APPLY]
    • Under 'Additional Properties' click 'Custom Properties' and enter: 
         NAME: databaseName
   Value:  ${DERBY_HOME}/JPetStoreDB
   Click [SAVE]
    • In the 'messages' box at the top of the page click the 'save' link. On the Application servers - Save screen click the button [SAVE] to save the new definitions

Install the JPetStore Application

  • Unzip the iBATIS JPetStore files in your favorite developement area and locate the jpetstore.war supplied. In the distribution I downloaded this was in: {favorite-development-area}/build/wars
  • In the WebSphere Application Console click on 'Install New Application' - this is in the left navigation pane under Applications. On the following screens accept the default values except as noted: 
     
      On the 'Preparing for the application installation - Specify..' screen:
            Click the radio button:  'Local Filesystem'
            Browse to the jpetstore.war file downloaded from iBATIS
            Enter the context-root as: /jpetstore
            Click [NEXT]
      On the 'Preparing for the application installation - Bindings.' screen:
		   Accept the defaults by clicking:  [NEXT]
		   Dismiss the security warnings by clicking [CONTINUE]
      On the Step 1 screen 
            Select the checkbox:  'Deploy Web services'
	            (note the application name defaults to: jpetstore_war )
            Click [NEXT]
      On the Step 2 screen 
            Accept the defaults by clicking [NEXT]
      On the Step 3 Map the JNDI datasource defined previously to the application:
		   At the bottom of the screen 
		         click the 'Select' checkbox next to the JPetStore Module
		   In the 'javax.sql.DataSource' box (near the top) 
		         select the JNDI datasource name from the dropdown list (e.g. jdbc/JPetStoreDB)
		   Click the [APPLY] button next to the JNDI datasource name
		   At the bottom check that the JNDI name appears in the JPetStore Module box.
            Click [NEXT]
      On the Step 4 screen accept the defaults by clicking [NEXT]
      On the Step 5 screen accept the defaults by clicking [NEXT]
      On the Step 6 screen review the SUMMARY, correct or click  [FINISH]
       After the 'Installing...' reports '..installed successfully.' click the 'Save to Master Configuration' link.
      The 'Changed documents' screen is displayed - click:  [SAVE]

Replace the sql-map-config.xml file in the application

  • In the WebSphere Application Console click on 'Enterprise Applications' in the left navigation pane under Applications
  • Select the check box next to the JPetStore application (jpetstore_war). The application will be stopped and have a red X next to it - this is not a problem. The application will automatically start when WebSphere is started.
  • Click the 'Update' button
  • On the 'Preparing for the application installation' screen: 
        Select the 'Single File' radio button
    Enter the path relative to {AppRoot} for the sql-map-config.xml file just installed (e.g. 
       jpetstore.war/WEB-INF/classes/com/ibatis/jpetstore/persistence/sqlmapdao/sql/sql-map-config.xml)
    Click the 'Local file system' radio button and browse to the directory where
       you unzipped the JPetStoreAPP4WebSphere.zip file.  
    Select the sql-map-config.xml file provided in the zipfile
    Click [NEXT]
    On the 'Confirmation' screen click [OK]
    On the 'Update' screen click the 'Save to Master Configuration' link.
    On the 'Changed documents' screen click:  [SAVE]
  • Restart the server and test the application

Optional instructions

The following steps show how to build the Derby database from scratch. Two files are supplied in the JPetStoreAPP4WebSphere.zip file to perform the initial build and data inserts. You will need to download the complete set of Derby jarfiles and place derbytools.jar in your classpath to perform the build as these instructions utilize the IJ tool to process the 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 files to the directory where you want to build the database: derby.jar, derbytools.jar,jpetstore-derby-schema.sql, jpetstore-derby-dataload.sql and issue the following command to create the database, tables and indexes: 
       java -cp ./derby.jar;./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.jar;./derbytools.jar -Dij.database=jdbc:derby:JPetStoreDB
	   org.apache.derby.tools.ij jpetstore-derby-dataload.sql

If you do not wish to use the sql-map-config.xml supplied in the JPetStoreAPP4WebSphere.zip file but prefer to modify the one supplied with the iBATIS JPetStore application do the following. Edit the sql-map-config.xml file and replace the existing TransactionManager definition section with this definition:

   <transactionManager type="JDBC" >
      <dataSource type="JNDI">
         <property name="DBJndiContext" value="jdbc/JPetStoreDB"/>
      </dataSource>
   </transactionManager>

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.

I recommend the following paper to people seriously interested in using Derby with WebSphere: Using IBM Cloudscape V10 with IBM WebSphere Application Server V6. It describes how to setup datasources to use the various drivers provided with Derby as well as how to create a helper class to assist with Derby usage rather than the Generic Datasource Helper used here.

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 'CookBook Details' section for how to use the IJ tools to build and populate the database.

Related Links

Related Aticles