This page describes the process of creating in memory database and maintaining the schema using Apache DDLUtils within your web application. Since we will be using a pure java database it allows developers to rapidly develop and unit test database driven applications in isolation.
There is no need to maintain an external mysql or Oracle database. The database is self contained within the application. Once you are finished with development you would just remove the HSQLDB dependency from the pom file and change the connect string to connect with a live database.
The primary motivation for writing this was so that I could use this as a base for writing CRUD type web applications.
This page is part of a series of Rapid Development and prototyping tutorials.
Background
HyperSQL DataBase (HSQLDB) is a Java based in-memory database that allows you to develop and unit test your web application in isolation. It supports stored procedures (callable statements) as well.
Schema maintenance is a common problem for in-memory or sandbox databases. Changes to the data model need to be applied to your test database using DDL statements often during application development.
This page describes the process of using the Apache DDLUtils framework to incrementally apply the changes kept in an XML file each time the application restarts. Since the changes are incremental (ALTER TABLE etc..) and every effort is made to preserve the existing data.
The application below will create 2 tables when the application initializes. The DDL will be generated and executed from an xml file located in the classpath. The application initialization and shut-down are handled by writing ContextLoaderListeners.
Requirements
- Maven
- Basic understanding of the Spring Framework
- Basic understanding of JDBC and SQL
Procedure
Start a new project
mvn archetype:generate -DarchetypeArtifactId=maven-archetype-webapp
groupId: com.test
artifactId: ddlutil
Answer the rest of the questions with defaults “Just hit the enter key”,
cd to the project base folder.
cd ddlutil
Since this is a web project maven2 does not create the java source folder.
Create missing folder
mkdir -p src/main/java/com/test
Modify the pom.xml file to look like this.
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.test</groupId>
<artifactId>ddlutil</artifactId>
<packaging>war</packaging>
<version>1.0-SNAPSHOT</version>
<name>ddlutil Maven Webapp</name>
<url>http://maven.apache.org</url>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.ddlutils</groupId>
<artifactId>ddlutils</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>1.8.0.10</version>
</dependency>
<dependency>
<groupId>org.apache.geronimo.specs</groupId>
<artifactId>geronimo-servlet_2.5_spec</artifactId>
<version>1.2</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring</artifactId>
<version>2.5.6</version>
</dependency>
</dependencies>
<build>
<finalName>ddlutil</finalName>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.0.2</version>
<configuration>
<source>1.5</source>
<target>1.5</target>
</configuration>
</plugin>
<plugin>
<groupId>org.mortbay.jetty</groupId>
<artifactId>jetty-maven-plugin</artifactId>
<version>7.0.0.v20091005</version>
<configuration>
<scanIntervalSeconds>2</scanIntervalSeconds>
</configuration>
</plugin>
</plugins>
</build>
</project>
Spring configuration
The following contains a single bean that represents a un-pooled data source.
src/main/resources/applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
<context:annotation-config/>
<context:component-scan base-package="com.test"/>
<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
<property name="url" value="jdbc:hsqldb:file:src/main/resources/db/testdb"/>
<property name="username" value="sa"/>
<property name="password" value=""/>
</bean>
</beans>
Servlet Context Listener
The following class listens to application life-cycle events like start-up and shut-down. It executes code to initialize and shut-down the database based on these events. Using listeners over Servlets is that the contextListeners will execute before any Servlets get a chance to run.
src/main/java/com/test/DBLifecycleContextListener.java
package com.test;
import java.io.InputStreamReader;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.io.DatabaseIO;
import org.apache.ddlutils.model.Database;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;
/**
* This listener initializes or gracefully shuts down the database based on
* events from the web application.
*/
public class DBLifecycleContextListener implements ServletContextListener {
private DataSource dataSource = null;
private WebApplicationContext springContext = null;
public void contextDestroyed(ServletContextEvent event) {
SimpleJdbcTemplate template = new SimpleJdbcTemplate(dataSource);
template.update("SHUTDOWN;");
System.out.println("HSQLDB was shutdown. ");
}
public void contextInitialized(ServletContextEvent event) {
springContext = WebApplicationContextUtils
.getWebApplicationContext(event.getServletContext());
dataSource = (DataSource) springContext.getBean("dataSource");
Platform platform = PlatformFactory
.createNewPlatformInstance(dataSource);
Database database = new DatabaseIO().read(new InputStreamReader(
getClass().getResourceAsStream("/ddl.xml")));
System.out.println(database);
platform.alterTables(database, false);
System.out.println("HSQLDB is ready: " + platform);
}
}
Database Schema (DDL)
The following file contains the schema that will be generated in the HSQLDB database. Any changes made to this file will result in “ALTER” table statements when the server restarts.
src/main/resources/ddl.xml
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="testdb">
<table name="author">
<column name="author_id"
type="INTEGER"
primaryKey="true"
required="true"/>
<column name="name"
type="VARCHAR"
size="50"
required="true"/>
<column name="organization"
type="VARCHAR"
size="50"
required="false"/>
</table>
<table name="book">
<column name="book_id"
type="INTEGER"
required="true"
primaryKey="true"
autoIncrement="true"/>
<column name="isbn"
type="VARCHAR"
size="15"
required="true"/>
<column name="author_id"
type="INTEGER"
required="true"/>
<column name="title"
type="VARCHAR"
size="255"
required="true"/>
<foreign-key foreignTable="author">
<reference local="author_id" foreign="author_id"/>
</foreign-key>
<index name="book_isbn">
<index-column name="isbn"/>
</index>
</table>
</database>
Web Application Configuration
The following loads both the spring and the DBLifecycle ContextLoaderListeners. The ordering is critical as you want the spring application context to initialize before anything else.
src/main/webapp/WEB-INF/web.xml
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd" > <web-app> <display-name>Archetype Created Web Application</display-name> <context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:applicationContext.xml</param-value> </context-param> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <listener> <listener-class>com.test.DBLifecycleContextListener</listener-class> </listener> </web-app>
Run the application
Return to the project main folder and run the following on the command line to start the jetty servlet engine.
mvn clean compile jetty:run
You should see the following at the console
Database [name=testdb; 2 tables] Mar 31, 2011 10:12:38 PM org.apache.ddlutils.alteration.ModelComparator compare INFO: Table author needs to be added Mar 31, 2011 10:12:38 PM org.apache.ddlutils.alteration.ModelComparator compare INFO: Table book needs to be added Mar 31, 2011 10:12:38 PM org.apache.ddlutils.platform.SqlBuilder getForeignKeyName WARNING: Encountered a foreign key in table book that has no name. DdlUtils will use the auto-generated and shortened name book_FK_author_id_author instead. Mar 31, 2011 10:12:38 PM org.apache.ddlutils.platform.PlatformImplBase evaluateBatch INFO: Executed 4 SQL command(s) with 0 error(s) HSQLDB is ready: org.apache.ddlutils.platform.hsqldb.HsqlDbPlatform@10e9c592 2011-03-31 22:12:38.392:INFO::Started SelectChannelConnector@0.0.0.0:8080 [INFO] Started Jetty Server [INFO] Starting scanner at interval of 2 seconds.
There is no need to open a browser to view the web application since all the work that was needed has already been done when the application initialized.
Shutdown the server by typing CTRL-C.
View the results
The server will initiate a graceful shut-down when you type CTRL-C at the console. The HSQLDB will write all data from the memory to a file located in the src/main/resources/db/ folder.
During runtime the data is kept in memory. Saving the data to a file-system allows the server to resume where it left of without loosing data when the server starts up again. We can use this to our advantage to peek into the file to see what is the state of the database.
After the first run the following file should look like this. Note the length of the AUTHOR.NAME field.
src/main/resources/db/testdb.script
CREATE SCHEMA PUBLIC AUTHORIZATION DBA CREATE MEMORY TABLE BOOK(BOOK_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,ISBN VARCHAR(15) NOT NULL,AUTHOR_ID INTEGER NOT NULL,TITLE VARCHAR(255) NOT NULL) CREATE INDEX BOOK_ISBN ON BOOK(ISBN) CREATE MEMORY TABLE AUTHOR(AUTHOR_ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(50) NOT NULL,ORGANISATION VARCHAR(50)) ALTER TABLE BOOK ADD CONSTRAINT BOOK_FK_AUTHOR_ID_AUTHOR FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID) ALTER TABLE BOOK ALTER COLUMN BOOK_ID RESTART WITH 0 CREATE USER SA PASSWORD "" GRANT DBA TO SA SET WRITE_DELAY 10
Altering the schema
Open up ddl.xml and increase the length of the name field of the “AUTHOR” table to VARCHAR(100) line 11 (highlighted).
1. Restart the jetty
2. shut it back down by typing CTRL-C.
Re-open the file below. You will notice that the auther.name field has been expanded to 100 characters.
src/main/resources/db/testdb.script
CREATE SCHEMA PUBLIC AUTHORIZATION DBA CREATE MEMORY TABLE BOOK(BOOK_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,ISBN VARCHAR(15) NOT NULL,AUTHOR_ID INTEGER NOT NULL,TITLE VARCHAR(255) NOT NULL) CREATE INDEX BOOK_ISBN ON BOOK(ISBN) CREATE MEMORY TABLE AUTHOR(AUTHOR_ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(100) NOT NULL,ORGANISATION VARCHAR(50)) ALTER TABLE BOOK ADD CONSTRAINT BOOK_FK_AUTHOR_ID_AUTHOR FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID) ALTER TABLE BOOK ALTER COLUMN BOOK_ID RESTART WITH 0 CREATE USER SA PASSWORD "" GRANT DBA TO SA SET WRITE_DELAY 10
That’s all for now!
