MemeStorm

We want to introduce a database into our sample application, in an
unobtrusive way requiring little in the way of configuration. We’ll do this with an all-Java in-memory database, and along the way learn a little about Aware interfaces, and running methods on context startup or shutdown. The result is a deployed WAR that has an in-built database.

Background

I was thinking about how to introduce databases without requiring you lot to do a lot of downloads, installation and configuration. Then it came to me - an in-memory database would be great. The full Spring distribution comes with HSQLDB, which is what we’ve used below. We’ve also got this working with Apache Derby.

Because we’ve opted for in-memory, and non-persistence, we have to essentially create the database and the tables every time we start up our application. This takes no time at all, but does force us to look a little at how a configuration context starts up and shuts down.

How to do it: Configuration

We’ll walk through the configuration by looking at the changes made to skeleton1, introduced in Basic Spring Web MVC Example.

First, let’s look at changes to our build.xml. The only thing you’ll notice is the addition of several libraries:

<include name="hsqldb.jar" />
<include name="commons-dbcp.jar" />
<include name="commons-pool.jar" />
<include name="commons-io.jar" />

The first is the database engine itself, the next two are for interfacing to the database, and the last is simply a utility library that we use later.

Now the way you typically integrate a database with a Spring application, is to introduce a data source bean. Data sources are the bridge between your application and the database. Even though you may use different database implementations, the data source will hide the details. Here’s our implementation:

<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource“>
<property name=”driverClassName”>
<value>${jdbc.driverClassName}</value>
</property>
<property name=”url”>
<value>${jdbc.url}</value>
</property>
</bean>

You’ll note we’ve externalized (see Parameterizing Spring’s Configuration Files, and BeanFactoryPostProcessors) the name of the database driver and URL used for connecting to the database. You’ll find them in jdbc.properties:

jdbc.driverClassName=org.hsqldb.jdbcDriver
jdbc.url=jdbc:hsqldb:mem:exampledb

The bean dataSource is an instance of BasicDataSource supplied by the DBCP library, which in turn implements the necessary interface, javax.sql.DataSource. Any interface with a database generally needs a bean that implements this interface. The DBCP version, which we’ve used, creates a data source by wrapping the necessary functionality around a database driver, which you can see we’ve supplied as a parameter. DBCP can add additional functionality such as connection pooling, which we’ll see in a later story.

This is pretty much standard - the details of what kind of data source to create, and which database driver to use, will vary - but the intent will remain no matter what db you connect to.

The database we’re using has a special driver class - normally a driver class allows you to connect to a running database. This driver class allows the same, but it also starts the database! So this database runs in the same VM as your code.

How to do it: Using the database

At this point (ignoring creating tables), we’re ready to use our database. Typically you would do this by making sure some data access objects have access to the data source. We’re not there yet. Instead, we’re going to make sure our controller has access to the data source. In that way we can add another handler method, which will in turn query the database:

<bean id="dispatchController"
class="com.memestorm.web.DispatchController">
<property name="dataSource"><ref bean="dataSource“/></property>
</bean>

As you can see, we simply pass in the data source bean defined above. We’ve added an additional method to our dispatch controller too:

public ModelAndView listFromDB(HttpServletRequest request,
HttpServletResponse response) throws Exception {
JdbcTemplate jt;
jt = new JdbcTemplate(this.dataSource);
jt.update(”insert into myTable values (’jon’,31)”);
List rows = jt.queryForList(”select * from myTable”);
ModelAndView mav = new ModelAndView(”listview”);
mav.addObject(”rows”, rows);
return mav;
}

Spring provides a really nice templating approach to interfacing with a database, which we’ll describe in a later story. You should be able to figure it out though. You use the data source to construct a JdbcTemplate object, and then use this to execute SQL. In this case, we execute an SQL update - inserting a row into the database. We then perform a query (an SQL select), and pass the results to our view (listview). What does the view look like?

<body>
Here are the results from the database:<br />
<%= request.getAttribute("rows") %>
</body>

It simply prints out the result! So, if we invoke our handler method:

http://localhost:8080/skeleton2/send/listFromDB

twice, then we’ll see this as output:

Here are the results from the database:
[{NAME=jon,AGE=31},{NAME=jon,AGE=31}]

Neat.

How to do it: Setting up the database

As mentioned, we start up with a clean database every time the web application is deployed. We also want to shut the database down nicely when we shut the application down. You’ll be astounded how easy this is. We’ll simply define one more bean:

<!--  can be used to initialize and shutdown the database instance -->
<bean id="databaseUtils" class="com.memestorm.utils.db.HSQLDB">
<property name="dataSource">
<ref local="dataSource" />
</property>
<property name="location">
<value>${db.sql.init}</value>
</property>
</bean>

That’s it! That’s pretty unobtrusive. The magic happens in HSQLDB (note how we pass it the data source, and a file location). Let’s explore this class:

public class HSQLDB  implements ApplicationListener … {
…
}

The first thing to note is that it implements ApplicationListener. Any bean that you register in the configuration context which implements this interface will automatically get sent important lifecycle events. All you have to do is then implement an onApplicationEvent() method. Here is our implementation:

public void onApplicationEvent(ApplicationEvent event) {
if (event instanceof ContextClosedEvent) {
shutdownDB();
}
}

We wait for a particular event, and when we get it, invoke shutdown. Simple! So in contrast, how do we do something when the context starts up? Well, we implement another interface:

public class HSQLDB  implements InitializingBean, ApplicationListener … {
…
}

What happens then is that when the context is initialized, and all properties are set, the context will invoke the afterPropertiesSet() method on all beans that implement the interface. How simple is that? Here is what we do:

public void afterPropertiesSet() throws Exception {
Resource sql = new ServletContextResource(this.servletContext, getLocation());
JdbcTemplate jt = new JdbcTemplate(this.dataSource);
jt.execute(IOUtils.toString(sql.getInputStream()));
}

As you can see, we simply grab the file given by the location variable, grab its contents, and execute it as SQL. Our file contains the following:

create table myTable (name varchar(40), age int);

You’ll notice that to grab the file at the location (which is relative to the root of the web application), we actually need the servlet context. So how can any bean get access to the servlet context in which it is running? Well, again, you just implement an interface!

public class HSQLDB  implements InitializingBean, ApplicationListener
ServletContextAware {
…
}

If you do this, Spring will again automatically invoke the setServletContext(ServletContext servletContext) on your bean, passing you the context which you can store. Neat hey? That’s it!

Recap

We’ve covered quite a bit here, so let’s recap:

• Implementing ServletContextAware, one of the “Aware” interfaces, will result in the context telling your bean what the servlet context is. This is a service provided by any web application context
• Implementing ApplicationListener allows you to listen to context life cycle events. Another interesting event is ContextRefreshedEvent and RequestHandledEvent
• Implementing InitializingBean allows our beans to do somem work after all their properties have been set by the Bean Factory.

Additional Material

Our sample does just a little more than this. Instead of mixing in our database configuration with our dispatch configuration (dispatch-servlet.xml), we’ve put them in a separate file, jdbcContext.xml. To load this, we’ve introduced a new configuration to our dispatcher servlet (in web.xml):

<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>
/WEB-INF/applicationContext.xml  /WEB-INF/jdbcContext.xml
</param-value>
</context-param>

This does what it says on the tin - it loads a number of configurations from a “space separated” list of files. In this way you can keep a number of configuration files, keeping them all neat and tidy.

Download

It’s been a long journey, but hopefully a rewarding one. You’re probably keen to download the code and build your own WAR. Here it is:

skeleton2.zip