H2 database on Tomcat

I’ve been looking at embedded in-memory databases for application servers. Although there’s decent documentation out for setting up datasources in Tomcat, it takes a bit of experimentation to get things just right.  Here’s my recipe for configuring H2 as a database embedded in Tomcat 6.  The steps should be similar for other databases.

In this case, I’m creating an in-memory database called “employee” with a connection pool named “jdbc_empDS” with a JNDI address of “jdbc/empDS”.

 

Install the driver

With H2 you get a single JAR file for the whole distribution. Most databases have separate JARs for the drivers.   The H2 JAR contains both the drivers and the whole database.  For just a second this seems excessive, but it makes sense because the database can be run embedded in your app server.   When your code loads the driver, it can start the whole database in local memory.

Install Tomcat and change directories to base directory.   Copy your h2.*.jar file into Tomcat’s lib directory.

 

Create a connection pool

Now go to the conf directory and edit the server.xml file.  You’ll be defining a new connection pool as a Resource within the GlobalNamingResource section.

You can also trim out a lot of unnecessary text in this file. Here’s a minimal server.xml file sufficient to run Tomcat:

<?xml version='1.0' encoding='utf-8'?>

<Server port="8005" shutdown="SHUTDOWN">

  <Listener className="org.apache.catalina.core.AprLifecycleListener"
      SSLEngine="on" />
  <Listener className="org.apache.catalina.core.JasperListener" />
  <Listener className="org.apache.catalina.mbeans.ServerLifecycleListener" />
  <Listener className="org.apache.catalina.mbeans.GlobalResourcesLifecycleListener" />

  <GlobalNamingResources>
    <Resource name="jdbc_empDS" auth="Container"
        type="javax.sql.DataSource"
        driverClassName="org.h2.Driver"
        url="jdbc:h2:mem:employee"
        username="sa" password=""
        maxActive="20" maxIdle="10" maxWait="-1"
        description="Datasource to employee database"  />
    <Resource name="UserDatabase" auth="Container"
        type="org.apache.catalina.UserDatabase"
        description="User database"
        factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
        pathname="conf/tomcat-users.xml" />
  </GlobalNamingResources>

  <Service name="Catalina">
    <Connector port="8080" protocol="HTTP/1.1"
        connectionTimeout="20000"
        redirectPort="8443" />
    <Engine name="Catalina" defaultHost="localhost">
      <Realm resourceName="UserDatabase"
          className="org.apache.catalina.realm.UserDatabaseRealm" />
      <Host name="localhost"  appBase="webapps"
           unpackWARs="true" autoDeploy="true"
           xmlValidation="false" xmlNamespaceAware="false">
      </Host>
    </Engine>
  </Service>

</Server>

Note that the JDBC URL here is in the format for an embedded/named instance of the H2 database. You can specify any other type of JDBC URL, such as an embedded/file database or an external server.

 

Set up a JNDI datasource

Now you’ll need to define a Java datasource that uses your new connection pool, and then you’ll declare it is a resource that Tomcat will serve up through JNDI.  You can specify this in the central conf/context.xml file.  You can also define this within your web application by creating a META-INF/context.xml file.  I usually do the central definition, since it makes the datasource available to all webapps.
Add the following to the context.xml file:

    	<ResourceLink name="jdbc/empDS"
			global="jdbc_empDS"
			type="javax.sql.DataSource"/>

 

Use your new datasource

Now you can start up Tomcat and start using the database:

Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env/jdbc/empDS");

 ...

 Connection conn = null;
 try {
     conn = ds.getConnection();
     ...
 } finally {
     if (conn!=null) { conn.close(); }
 }

If the Tomcat manager app is enabled, you can verify that your datasource is available by listing all JNDI resources at http://localhost:8080/manager/resources
You can get a peek into Tomcat’s internals if you run JConsole. This will let you drill into the MBeans for Catalina and see the DataSource and Resource definitions. You’ll need to add the following system property to your CATALINA_OPTS variable:

-Dcom.sun.management.jmxremote

Advertisements

No comments yet

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: