Using Embedded Derby in Tomcat

Published in Java on 10-Jan-2012

I have been using Apache Derby for several years. It is an open source, fully featured, ANSI compliant RDBMS, written entirely in Java. One of the great features that sets Derby apart from many other databases is that it can be run in embedded mode. Embedded mode means that it runs in the same Java Virtual Machine as the application that uses it and the life-cycle of the database is managed by the application accessing it.

This makes Derby particularly well suited for use when you don't want a user to have to experience the complications of installing and starting up a database server like Oracle, PostgreSQL or MySQL, in order to use the application. Two particular types of applications come to mind that fit this description:

Desktop GUI applications where you wish to use SQL compliant data storage but also want the application to be easy to distribute
Web applications requiring small amounts of persistent storage (say for a user database) but also requiring easy distribution

When used as part of a desktop GUI based application, the author of the application has complete control over the application life-cycle, and as such, it is easy to also control when the Apache Derby instance will be started and terminated. Things get a little more complicated when we are dealing with the second scenario described above: A web based application requiring persistent storage where the author does not wish the users to have to trouble themselves with installing and managing a database server. The focus of this article will be the complications that this second scenario presents.

Apache Derby Embedded Basics

Generally, when you wish to startup an Apache Derby instance in embedded mode, only a few basic steps need to be followed:

1) Ensure the Apache Derby Embedded driver is in the application classpath and then execute the following to load the driver:

Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

2) Create a connection to the database:

Connection con = DriverManager.getConnection("jdbc:derby:databaseName;create=true");

3) Use the connection to execute your SQL statements.

Note that in step 2 above, the database connection string contains two interesting items:

The databaseName property: The databaseName is the name of the directory where the database files can be found. This name can potentially contain a path, but if it does not, the database directory will be relative to the current execution directory. Later when we begin discussing using Derby embedded inside of Tomcat, if we do not specify a path the database directory will be in the Tomcat /bin directory.
The create=true property: This will create the database directory and files if they do not already exist. If the database directory already exists, this property will have no effect. If the database does not exist and the create=true property is not specified, a SQLException will be thrown.

4) Shutdown the Embedded Derby Instance.

DriverManager.getConnection("jdbc:derby:databaseName;shutdown=true");

One thing to notice is that the .getConnection(...) method of the DriverManager is also used for shutting down the embedded Derby instance by passing the shutdown=true parameter in the connection string. A clean shutdown is important for ensuring database file integrity.

Somewhat counter-intuitively, when an embedded Derby instance successfully shuts down, the .getConnection(...) method will throw a SQLNonTrasientConnectionException wrapping a SQLException, with the message Database 'databaseName' shutdown..

Admittedly, I was a bit puzzled by this behavior for a while, given the general understanding that exceptions should be thrown in exceptional circumstances, and shutting down a database hardly seemed to qualify as exceptional (in fact, it seems rather rudimentary). However, I have since realized that it makes perfect sense given the method being used. When we call .getConnection(...) we expect it to return a usable database connection. As we are shutting down the embedded database instance, we are not going to receive a usable connection back from the execution of this method. That seems to qualify as an exceptional circumstance and throwing the exception, as such, seems perfectly reasonable.

Derby in Tomcat

Now that we have seen the basics of using Apache Derby in embedded mode, we shall see how we can apply this to usage in a web application.

Initializing Derby in Embedded Mode

The first consideration is how we will startup the Derby instance in our web application, including the creation of the database and the initialization of its schema and seed data. It is important to note that is is necessary for the derby.jar file to be available to the application in order to access the embedded derby database engine and drivers. Files like this are usually deployed inside the /lib directory of Tomcat.

There are two primary ways we can initialize our Derby instance. We can define a Tomcat datasource in our application deployment WAR or we can initialize the embedded Derby instance on application context startup in a ServletContextListener.

Defining a Tomcat Datasource

We can define a Tomcat Datasource as part of our application deployment WAR file. When the application requests a connection to the Datasource a connection will be established using the connection string we specify in the Datasource descriptor. Our Datasource definition will exist in a Resource element contained in a file called context.xml which will be contained inside the META-INF directory inside the application WAR file. Our datasource entry will resemble the following:

<Context>
    <Resource
        name="jdbc/datasource" type="javax.sql.DataSource"
        maxActive="20" maxIdle="20" maxWait="5000"
        validationQuery="values(1)"
        driverClassName="org.apache.derby.jdbc.EmbeddedDriver"
        url="jdbc:derby:databaseName;create=true" />
</Context>

If you are using Apache Maven to package your WAR file, simply including this file in your src/main/webapp/META-INF/ folder will ensure it is included in the correct place in your distributable WAR.

Using a ServletContextListener

If we do not want to create a Tomcat Datasource to access our Derby instance through, we can simply initialize the database through the contextInitialized(...) method of a registered ServletContextListener. Please note that if you are using this method to initialize your database, it is not necessary for the derby.jar file to be placed in the Tomcat /lib directory, as long as the derby.jar file is included as a dependency inside your WAR file.

A class which implements the ServletContextListener interface must contain two methods:

void contextInitialized(ServletContextEvent event);

void contextDestroyed(ServletContextEvent event);

These methods will be automatically executed by the servlet container when the context for the application is started (contextInitialized) or terminated (contextDestroyed). In order to be executed, an implementation of the ServletContextListener interface must be declared in the application's web.xml file as follows:

<?xml version="1.0" encoding="UTF-8"?>
<web-app
    xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
        http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
    version="2.5">


    <listener>
        <listener-class>ca.quadrilateral.blog.SomeServletContextListene</listener-class>
    </listener>

    ...

<web-app>

Thus, we can create a ServletContextListener which, on context startup, will ensure that the Derby jar file is present in our classpath, and that our Embedded database is created and ready for use:

public class DerbyDBInitializingListener implements ServletContextListener {
    private static final Logger log = Logger
            .getLogger(DerbyDBInitializingListener.class.getName());

    @Override
    public void contextInitialized(ServletContextEvent event) {
        log.info("Servlet Content Initialized");

        try {
            log.info("Loading Derby DB Driver...");
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            initializeDatabase();
        } catch (ClassNotFoundException e) {
            log.log(Level.SEVERE, "Could not load Derby Embedded Driver!", e);
        } catch (SQLException sqle) {
            log.log(Level.SEVERE, "Fatal Database Error!", sqle);
        }
    }

    private void initializeDatabase() throws SQLException {
        Connection connection = null;
        try {
            log.info("Starting up Derby DB...");
            connection = DriverManager
                    .getConnection("jdbc:derby:databaseName;create=true");
        } catch (SQLException sqle) {
            log.log(Level.SEVERE, "Could not connect to Derby Embedded DB!", sqle);
            throw sqle;
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }

    @Override
    public void contextDestroyed(ServletContextEvent event) { }
}

Seeding the Database

Which ever method you choose to create your Derby embedded database, a ServletContextListener can still come in handy for ensuring that the schema of your newly created database is created and any seed data you require is populated.

Once the Derby database has been created and you have a connection to it, regardless of whether it is retrieved by a DriverManager.getConnection(...) call or by a JNDI lookup, your contextInitialized(...) method can execute SQL to determine if the Schema already exists, and take certain actions if it does not - like, for example, executing a script that will create the necessary tables and seed data. We could modify the above code as follows (see the changes in bold):

public class DerbyDBInitializingListener implements ServletContextListener {
    private static final Logger log = Logger
            .getLogger(DerbyDBInitializingListener.class.getName());

    @Override
    public void contextInitialized(ServletContextEvent event) {
        log.info("Servlet Content Initialized");

        try {
            log.info("Loading Derby DB Driver...");
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            initializeDatabase();
        } catch (ClassNotFoundException e) {
            log.log(Level.SEVERE, "Could not load Derby Embedded Driver!", e);
        } catch (SQLException sqle) {
            log.log(Level.SEVERE, "Fatal Database Error!", sqle);
        }
    }

    private void initializeDatabase() throws SQLException {
        Connection connection = null;
        try {
            log.info("Starting up Derby DB...");
            connection = DriverManager
                    .getConnection("jdbc:derby:databaseName;create=true");
            if (!schemaHasBeenInitialized(connection)) {
                initializeSchema(connection);
            }
        } catch (SQLException sqle) {
            log.log(Level.SEVERE, "Could not connect to Derby Embedded DB!", sqle);
            throw sqle;
        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    }


    private boolean schemaHasBeenInitialized(Connection con) throws SQLException {
        final DatabaseMetaData metaData = con.getMetaData();
        final ResultSet tablesResultSet =
                metaData.getTables(
                        null, null, null,
                        new String[] { "TABLE" });

        try {
            while (tablesResultSet.next()) {
                final String tableName = tablesResultSet.getString("TABLE_NAME");
                if (tableName != null
                        && "MY_TABLE_NAME".equalsIgnoreCase(tableName)) {
                    return true;
                }
            }
        } finally {
            if (tablesResultSet != null) {
                tablesResultSet.close();
            }
        }
        return false;
    }

    private void initializeSchema(Connection con) {
        // Execute whatever SQL is necessary to
        // create the schema tables and seed data
    }


    @Override
    public void contextDestroyed(ServletContextEvent event) { }
}

In the modified code above, we use the getMetaData() method on the database connection in order to get a ResultSet containing all the table names in the database. We compare each of these table names against a table name that is part of our schema that we expect to be present if the database has been previously initialized, in this case MY_TABLE_NAME. If our table is present, we assume that the database has already been initialized and allow the application to proceed. If our table is not present, we call an initializeSchema(...) method which is responsible for executing whatever SQL is necessary to prepare the database for use in our application.

Now, when our application requires a database connection in any of our business service classes, we only need to conduct a JNDI lookup (if we are using a Tomcat datasource), or execute the simple command (noting the absence of the create=true parameter):

Connection con = DriverManager.getConnection("jdbc:derby:databaseName");

And we are ready to access our database!

Shutting Down Derby

As we have previously mentioned, it is necessary to Derby to be cleanly shutdown using the shutdown=true parameter attached to the connection string used by the DriverManager.getConnection() method. To accomplish this in a Tomcat web application, we need to shutdown the database when the application context is being shutdown. As I hope would be obvious from the above discussion, the ServletContextListener interface provides us an excellent opportunity to accomplish this.

When the application server un-deploys a web application (either at the direction of the user, as part of a hot deploy, or because the application server is preparing to be shutdown) it will execute the contextDestroyed(...) method of any registered ServletContextListeners. As such, if we want to cleanly shutdown our embedded instance of Derby, the contextDestroyed(...) method seems to be an excellent place to do this.

Continuing with our above example, but for brevity, omitting the methods related to the initialization of the context and the database schema, we will add the following code to our contextDestroyed(...) method:

public class DerbyDBInitializingListener implements ServletContextListener {

    ...

    @Override
    public void contextDestroyed(ServletContextEvent event) {
        log.info("Servlet Context Destroyed");
        try {
            log.info("Shutting down Derby DB...");
            DriverManager.getConnection("jdbc:derby:databaseName;shutdown=true");
        } catch (SQLException sqle) {
            if (sqle.getMessage().equals("Database 'databaseName' shutdown.")) {
                log.info("Derby DB Shutdown successfully!");
            } else {
                throw new RuntimeException(
                    "An error occurred shutting down the Derby instance!"
                    , sqle);
            }
        }
    }
}

Note that in the above code example, we are adding the shutdown=true parameter to the connection string, telling the Derby engine that we wish it to cleanly and safely shut itself down. Also, note that, as we mentioned above, due to the slightly extraordinary way that the getConnection(...) method is being used as a trigger to the Database shutdown procedure, it throws a SQLException on success. We test for the expected SQLException and re-throw the exception wrapped in a Runtime exception if it is not the exception that indicates the shutdown was a success.

So, putting all these pieces together, we are able to easily, with only the addition of a single ServletContextListener implementation, use an embedded Derby database in our web application deployed under Tomcat. The user does not need to install or manage a traditional database server and your application is still able to include all the benefits of a robust relational database.

Considerations for other Application Servers

The functionality described above should work successfully for any servlet container, whether it is Apache Tomcat, Resin, JBoss, etc - as long as it is compliant with the Java Servlet API. There are some other considerations however that may come into play.

If, for example, you wanted to use an embedded Derby database across several web applications deployed on a single application server. Starting and stopping the embedded instance of Derby with each application would be insufficient, as you would only want to start the database instance on server start, not context start, and the same is true of the shutdown of the application server.

The best way to handle this is going to be application server specific. Each container should have a means by which you can execute the necessary code during various life-cycle stages of the application server.

If you were using JBoss, for example, and wished to have a single embedded instance of Derby to be shared by multiple deployed applications, a way to accomplish this might be through the use of JMX MBeans. MBeans are deployed in JBoss in SAR archives, are automatically started and stopped by the container during the startup and shutdown of the application server, and contain life cycle methods that can be easily tapped in to. The details of this are beyond the scope of this article, but hopefully this gives you an idea of the mechanisms to look for in your application server of choice if you require the functionality of an embedded Database across several deployed applications.

Final Words

I am sure that there are several approaches to the problem of controlling the life cycle of an embedded instance of an Apache Derby database in an Apache Tomcat web application, and I am by no means sure that the way I have described above is necessarily the best way to accomplish this. I believe the solution I have outlined is fairly robust and should work well, but I would certainly welcome any criticism or suggestions of alternative or superior approaches to this issue.

Complete Source Code

The source code for a complete, bare-bones sample application is available in zipped format. It can be built using Maven 3.0 and was tested on Apache Tomcat 6.

embeddedDerbyInTomcatSource.zip

References

Apache Derby 10.8 Reference Manual

Java Servlet 2.4 Specification

Apache Tomcat 6.0 Documentation

About the Author

Daniel Morton is a Software Developer with Shopify Plus in Waterloo, Ontario and the co-owner of Switch Case Technologies, a software development and consulting company. Daniel specializes in Enterprise Java Development and has worked and consulted in a variety of fields including WAN Optimization, Healthcare, Telematics, Media Publishing, and the Payment Card Industry.