Monday, October 24, 2011

SQLFire and Spring Roo

SQLFire is an exciting new member of the VMware vFabric family, joining the likes of tc Server, GemFire, Hyperic, and RabbitMQ. SQLFire provides an in-memory high-performance data fabric with a familiar SQL interface.

Chris Harris wrote a series of blog entries about getting starting with SQLFire, using Grails to quickly generate a test application. In the same spirit as Chris' articles, this posting will show the steps for creating a simple Spring Roo application that uses SQLFire for data persistence.

The Roo reference docs contain a great tutorial for building a "pizza shop" application with Roo. This article will use the pizza shop tutorial application as a starting point. If you want to get familiar with Roo you can go through the full tutorial first, and then come back to this article. If you just want to jump into SQLFire, everything you need to get the application running is contained here. This article assumes that you already have Roo and all of its dependencies installed and working, and that you have some understanding of how to use the Roo shell.

Step 1: Create the Pizza Shop application

If you already have the Pizza Shop Roo application running, you can skip this step and go on to Step 2. If you haven't followed the Roo tutorial to create the Pizza Shop application, you can quickly create the app now.

First, copy the text in the box below and paste it into a file named "pizza.roo". This is the same set of Roo commands used in the tutorial, which will be run as a script.

project --topLevelPackage com.springsource.roo.pizzashop
persistence setup --provider HIBERNATE --database HYPERSONIC_IN_MEMORY
entity --class ~.domain.Topping
field string --fieldName name --notNull --sizeMin 2
entity --class ~.domain.Base --testAutomatically 
field string --fieldName name --notNull --sizeMin 2
entity --class ~.domain.Pizza --testAutomatically 
field string --fieldName name --notNull --sizeMin 2
field number --fieldName price --type java.lang.Float
field set --fieldName toppings --type ~.domain.Topping
field reference --fieldName base --type ~.domain.Base
entity --class ~.domain.PizzaOrder --testAutomatically 
field string --fieldName name --notNull --sizeMin 2
field string --fieldName address --sizeMax 30
field number --fieldName total --type java.lang.Float 
field date --fieldName deliveryDate --type java.util.Date
field set --fieldName pizzas --type ~.domain.Pizza
controller all --package ~.web
Next, execute the following list of operating system and Roo commands to create a project root directory, make the project root the current directory, start the Roo shell, run the script to create the tutorial application, and run the application.
~/projects> mkdir roo-pizza
~/projects> cd roo-pizza
~/projects/roo-pizza> roo
roo> script /path/to/pizza.roo
Created ... (long list of Roo messages omitted)
Script required 8 second(s) to execute
~.web roo> quit 
~/projects/roo-pizza> mvn tomcat:run
The last command uses Maven to build the project and run the application in an embedded Tomcat server. The first time you run Maven, it may take a while to download all the dependencies for Roo and the application.

Use your favorite browser and navigate to the address "http://localhost:8080/pizzashop/". You should see the Roo application home screen. You can test the app by creating and listing bases, toppings, pizzas, pizza orders, etc.

Step 2: Introducing SQLFire

If you haven't already, download SQLFire from the download site. Installation is very simple. Assuming the installer package is in the root of the user home directory, run the following command.
~> java -jar SQLFire10Beta_Installer.jar
This will create a "SQLFire10Beta" directory under the directory the installer was run from. The "bin" directory under the install directory contains a script that is used to stop, start, and control SQLFire. Run the following commands to start a new SQLFire instance.
~/SQLFire10Beta> mkdir server1
~/SQLFire10Beta> bin/sqlf server start -dir=server1 &
The "server start" command will start a SQLFire server node with default options, using the specified directory to store state. SQLFire will be listening for JDBC connections from "localhost" only on port 1527.

You can use the "sqlf" command to make sure SQLFire is up and running.
~/SQLFire10Beta> bin/sqlf 
sqlf> connect client 'localhost:1527';
sqlf> show tables in sys;
sqlf> quit;
SQLFire should display a list of system tables that are created in each new database.

Step 3: Modifying the application for SQLFire

Now that the Pizza Shop application is created and SQLFire is running, we can modify the app to use SQLFire as the database instead of the Hypersonic database suggested by the Roo tutorial. This requires editing two configuration files and making the SQLFire JDBC driver available to the application via Maven.

First, edit the file "persistence.xml", which is in the directory "/src/main/resources/META-INF" under the project root directory. Change the line containing the "hibernate.dialect" property to use the Apache Derby dialect instead of the Hypersonic (HSQL) dialect. In the example below, line 6 has been changed to use the Derby dialect. This is the only line in the file that needs to be changed (comments are omitted from the Roo-generated file for clarity).

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="" xmlns:xsi="" version="2.0" xsi:schemaLocation="">
  <persistence-unit name="persistenceUnit" transaction-type="RESOURCE_LOCAL">
      <property name="hibernate.dialect" value="org.hibernate.dialect.DerbyDialect"/>
      <property name="" value="create"/>
      <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
      <property name="hibernate.connection.charSet" value="UTF-8"/>
Next, change the "" file in the directory "/src/main/resources/META-INF/spring". All four properties in this file should be changed to match the example below.

The jar file containing the SQLFire thin-client JDBC driver must be made available to the application at run-time. Since Roo uses Maven to build and package the application, we can add the SQLFire jar files as dependencies to the Pizza Shop project. To do this, edit the "pom.xml" file in the project root directory and add the following to the "<dependencies>" section of the file. At the time of this posting, SQLFire is still in beta so we will use "1.0-beta" as the version.

The beta release of the SQLFire client jar file is not available in any public Maven repositories. The jar file is included in the SQLFire distribution, and can be installed to your local Maven repository. From the "lib" directory under the SQLFire installation directory, run the following Maven command.
~/SQLFire10Beta/lib> mvn install:install-file -DgroupId=com.vmware.sqlfire /
-DartifactId=sqlfireclient -Dversion=1.0-beta /
-Dpackaging=jar -Dfile=sqlfireclient.jar
Finally, re-run the Maven command to start the application in an embedded Tomcat server.
~/projects/roo-pizza> mvn tomcat:run
Once again, point your browser at http://localhost:8080/pizzashop and verify that the application is running. Try creating a few entities (bases, toppings, pizzas) and use the "sqlf" client to verify that the data is being stored in SQLFire.
~/SQLFire10Beta> bin/sqlf 
sqlf> connect client 'localhost:1527';
sqlf> show tables in pizza;
sqlf> set schema pizza;
sqlf> select * from base;
Next Steps

From here, you can set up a more complex SQLFire data fabric with multiple nodes, using a peer-to-peer or locator topology, as described in the SQLFire documentation and in Chris Harris' Grails + SQLFire series.

You can also use Maven to package the application into a war file and run it in Tomcat, tc Server, or another web server.
~/projects/roo-pizza> mvn package


  1. Nice post, Scott! Always good to see people using Spring Roo. Don't forget there are demo scripts built into Roo such as clinic.roo and vote.roo; this might be easier for people than getting to copy and paste into a new file (they can just run "script clinic.roo" in the Roo shell).

    Also, are the "property" elements in persistence.xml meant to be nested like that?

  2. Thanks Andrew. Actually, I didn't know about the two bundled demo scripts in Roo. That's good to know.

    I hadn't noticed the problem in persistence.xml. The XML snippet is correct in the source of the page, but the JavaScript I'm using for code formatting is adding all the extra closing tags. I changed the XML so it renders correctly but it doesn't look as nice as it should. I'm going to start using GitHub gists for sample code like this, the JavaScript formatting causes too many problems.