Embedding Apache Derby in Tomcat and creating an iBATIS JPetStore Demo

Most J2EE applications use a JDBC-compliant database to store data. Arguably, one of the more time consuming tasks when deploying such an web application is the database setup. These steps often involve compiling DBMS code (if the database system is not already installed) then creating and populating the physical database itself. Since Derby is implemented entirely in JAVA these steps can be avoided. Simply put, Derby is easy add to a Tomcat Server environment and reduces the complexity of deploying applications that use a database.

Installation of the Derby DBMS is as simple as copying a 2 Mb jarfile to a known location. Like any other general use component the Derby jarfile is added to the Tomcat common shared library directory. Setup of the physical database and application data can be as easy as copying the fully initialized physical database to a location on the Server machine then configuring datasource to access it. The following instructions demonstrate the ease with which this can be accomplished using Tomcat version 5.5.12.

Derby is an open source, java relational database freely available from the Apache Software Foundation (ASF). It is well suited for use with Java applications that require data persistence and is ideally suited for use in a Server environment like Tomcat. Derby can be used in one of two configurations, embedded in a java application or used with Network Server and run as a separate DBMS process. When embedded in an application like Tomcat that provides network communication services there is usually no need to run the Network Server.

Tomcat is a widely used, open source web application server freely available from ASF. Initially designed as a servlet/JSP container Tomcat now bundles many additional components that provide J2EE Server capabilities to the system. Tomcat development is managed as a subproject of the Apache Jakarta project.

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 Data Mapper framework. iBATIS greatly simplifies the coding of Java and .NET applications that access data from a relational database like Derby. iBATIS, like the other software used in this implementation is an open source product freely available from ASF.

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

{Tomcat_Home}

The installation directory for the Tomcat server (a.k.a. CATALINA_HOME).

{machine-name}

the hostname of the machine on which the server and application are installed.

{Derby_System_Home}

the directory containing the derby.log file. This is the default output directory for Derby and should be the same as {Tomcat_Home}. The JPetStoreDB database will be located in the {Derby_System_Home}\Databases subdirectory.

{Derby_Jars}

The location of the derby jarfiles. In this installation the location will be the Tomcat common shared library directory: {Tomcat_Home}/common/lib

• If necessary, install a functional J2SE Java Developers Kit (JDK). Tomcat 5.5.12 requires JDK 5.0 by default. If you will be using an JDK please see the Tomcat documentation for setup information. The JDK 'bin' directory should be included in your PATH.
• If necessary, Download and install Tomcat v 5.5.12.
• Download the DerbyJPetStore4Tomcat.zip file (4.8 Mb) associated with this article. Move the file to the directory {Tomcat_Home}/work.

The initial steps below walk you through moving the files contained in the DerbyJPetStore4Tomcat.zip file to the locations required to support the deployment instructions that follow. Note that the zipfile contains not only the application system but also the DBMS and the initialized database. No database activity is required other than placing the files in the proper directories. Assuming you have a functioning Tomcat 5.5.12 system already everything you need to create the demo is found in the zipfile associated with this article.

Subsequent steps show you how to define the JPetStoreDB datasource connection pool in the Global Naming Context. When defined in this way the datasource can be utilized by any application running on the server. Once the datasource required by the application is defined the JPetStore application is deployed and you have a functioning web application. The step-by-step instructions are:

• Stop Tomcat: Shutdown Tomcat if it is running.
• Extract the database zipfile and deployment files: Unzip the files from DerbyJPetStore4Tomcat.zip into the directory {Tomcat_Home}/work then "install" Derby as follows:
  • Install the Derby DBMS system: Move the derby.jar file to the Tomcat 'common' directory {Tomcat_Home}/common/lib.
  • Setup the JPetstore database: Unzip the database from the DerbyJPetStoreDB.zip file and move the Databases directory tree to {Tomcat_Home}.
• Define the Global Datasource: A Global datasource is defined in the server XML configuration file {Tomcat_Home}/conf/server.xml. Save a copy of the current server configuration file then add the following definition to {Tomcat_Home}/conf/server.xml. The location of the definition must be in the Global Naming Resources section of the file. For simplicity locate the end of section marker </GlobalNamingResources> and add the following lines just above it:

           <!-- Global Datasource for Derby JPetStoreDB database -->
           <Resource name="jdbc/JPetStoreDB"
                type="javax.sql.DataSource"  auth="Container"
                description="Derby database for JPetStoreApp"
                maxActive="100" maxIdle="30" maxWait="10000"
                username="" password="" 
                driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
                url="jdbc:derby:Databases/JPetStoreDB"/>
  			

• Start Tomcat and the manager console: Start Tomcat and open the Manager application in a browser (URL for default installation: http://{machine-name}:8080/manager/html). Supply a valid username and password to access the Tomcat Web Application Manager screen.
• Deploy the JPetStore application: Scroll past the application list to the section labeled: Deploy directory or WAR file located on server (see Figure 1). Enter the following information:

           Context Path (optional):   /JPetStoreApp
        XML Configuration file URL:   work/JPetStoreAppGbl.xml
              WAR or Directory URL:   work/JPetStoreApp.war
        

  Figure 1: Tomcat Manager Deployment Screen

  
  Click the Deploy button to deploy and start the application. Check in the message section for the confirmation of a successful deployment: OK - Deployed application at context path /JPetStoreApp.
• Test the application: In the Applications list section, click on the application name in the Path column or open the application from a new window with the URL: http://{machine-name}:8080/JPetStoreApp

The datasource connection pool used by the JPetStore application can be defined in the Global context as show above or in the Application context outlined here. Either context will support connections between iBATIS and Derby. The difference between the two setups is whether or not another application deployed to this Tomcat server will be able to access the datasource. When the datasource is defined in the Global context other applications can also connect to the pool by linking to the Global datasource using a copy of the Resource-link definition found in the JPetStoreAppGbl.xml file used in the deployment above. This is the configuration that should be used if, say, a Petstore accounting or reporting application will be deployed in a separate secured context that is available to a limited number of users.

When setting up a connection pool in the application context all the information about the datasource is provided in the application XML configuration file. Compare this to the Global datasource setup where the definition is in the server XML configuration file server.xml rather than the application XML configuration file. The contents of these two files are all that differs in the following set of steps for recreating the demo using an application context datasource.

• Stop Tomcat: Shutdown Tomcat if it is running.
• Replace the server.xml file: Restore the copy of the original server.xml saved previously or delete the Datasource definitions lines added to the GlobalNamingResources section. No changes to the server.xml file are needed in this configuration.
• Extract the database zipfile and deployment files: Already done
  • Install the Derby DBMS system: Already done
  • Setup the JPetstore database: Already done
• Start Tomcat and the manager console: Start Tomcat and open the Manager application in a browser (URL for default installation: http://{machine-name}:8080/manager/html). Supply a valid username and password to access the Tomcat Web Application Manager screen.
• Undeploy the JPetStore application: The application deployed above is not functional and recorded errors to the logfile when it attempted to start. Click the Undeploy for JPetStoreApp in the application list
• Deploy the JPetStoreA application: Scroll past the application list to the section labeled: Deploy directory or WAR file located on server (refer to Figure 1). Enter the following information:

           Context Path (optional):   /JPetStoreAppA
        XML Configuration file URL:   work/JPetStoreAppCntxt.xml
              WAR or Directory URL:   work/JPetStoreApp.war
        

  Click the Deploy button to deploy and start the application. Check in the message section for the confirmation of a successful deployment: OK - Deployed application at context path /JPetStoreAppA.
• Test the application: In the Applications list section, click on the application name in the Path column or open the application from a new window with the URL: http://{machine-name}:8080/JPetStoreAppA

The following steps show how to build the Derby database from scratch. This is similar to the build procedure required when deploying applications using most other DBMS system except a separate database creation step is not required. Two files (*.sql) are supplied in the zipfile to perform the schema build and data inserts. To use this procedure you will need to download a complete set of Derby jarfiles in order to obtain the IJ scripting tool used in the following commands. The IJ tool is used to process the SQL commands in the provided scripts. The IJ tool is contained in the derbytools.jar file. Place derbytools.jar in the {Derby_Jars} directory. 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 \
     -Dderby.system.home={Derby_System_Home}\Databases  \
     -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:
  Note

  If you are performing the build on a Windows based OS change the classpath separator between the jarfile names for a colon (:) to a semicolon (;) in the following commands: e.g. java -cp {Derby_Jars}/derby.jar;{Derby_Jars}/derbytools.jar...

  	java -cp {Derby_Jars}/derby.jar:{Derby_Jars}/derbytools.jar   \
  	 -Dderby.system.home={Derby_System_Home}\Databases  \
  	 -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.

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 defined datasource to avoid problems that can occur when Derby is used in an environment that has 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.

Tomcat is designed so that new java components (a.k.a. shared libraries) can be easily added to the system. This is accomplished by adding the jarfiles containing the components to the $CATALINA_HOME/common/lib directory. Derby is designed to be used in just this manner. The Derby manuals refer to this as embedding. Embedding makes Derby just another component available to the applications deployed on Tomcat. Adding the derby.jar file to the common shared libraries directory makes the Derby engine, not just the JDBC driver, available. No separate steps are required to install, configure or start the Derby engine.

The default Session Manager (org.apache.catalina.session.StandardManager) created for each application context (a.k.a. web application) enables Restart persistence. This feature is disabled in the JPetStore XML configuration files to avoid the problem where the JNDI context java:comp is not accessible when the iBATIS Dao Transaction Pool is restarted. This problem is typified by startup messages in the catalina.log file containing the following error text:

org.apache.catalina.session.StandardManager start
SEVERE: Exception loading sessions from persistent storage
java.lang.ExceptionInInitializerError
  at com.ibatis.jpetstore.service.CatalogService.<nit>(CatalogService.java:23)
       --- rest of trace removed --
  Caused by: java.lang.RuntimeException: 
      Could not initialize DaoConfig.  
  Cause: com.ibatis.dao.client.DaoException: 
      Error while configuring DaoManager. 
  Cause: com.ibatis.sqlmap.client.SqlMapException: 
      There was an error while building the SqlMap instance. 
         --  messages removed --
  --- Cause: com.ibatis.sqlmap.client.SqlMapException: 
      There was an error configuring JndiDataSourceDaoTransactionPool. 
  Cause: javax.naming.NameNotFoundException: 
      Name java:comp is not bound in this Context 

If you wish to build your own iBATIS JPetstore war file like the one supplied in the zipfile 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"/>
        </dataSource>
     </transactionManager>
     

• Edit the web.xml file and replace the existing resource-ref definition with the following:

        <resource-ref> 
     <description>JPetStore DataSource</description>
  	<res-ref-name>jdbc/JPetStoreDB</res-ref-name>
  	<res-type>javax.sql.DataSource</res-type>
  	<res-auth>Container</res-auth>
  	<res-sharing-scope>Shareable</res-sharing-scope>
     </resource-ref>
     

Required Downloads to perform this deployment:

• DerbyJPetStore4Tomcat.zip
• Tomcat v 5.5.12

Download links for software used in creating this deployment:

• Apache Derby version 10.0.2.1 [Note: the *-bin archives contain the documentation as well as Derby].
• iBATIS JPetStore version 4.0.5

Learn more:

• The Apache Derby Project homepage.
• The Apache iBATIS Project homepage.
• The Apache Tomcat Project homepage
• The developerWorks Derby / IBM Cloudscape Technical Library
• Apache Derby White Papers

Related articles:

• The Derby difference: Using Derby with J2EE Servers
• Derby WebSphere deployment of JPetStore Using Derby with WebSphere to implement iBATIS JPetStore
• Derby Geronimo deployment of JPetStore Using Derby with Geronimo to implement iBATIS JPetStore

Stan Bradbury wrote these instructions. Please post any questions about them to derby-user@db.apache.org.