Start and setup in-memory DB using Spring

Question

I'm writing a small demo application in Java using Spring, that needs to have access to a database. It should run on different machines and it would be far too much effort to setup a real database. Therefore I want to use an embedded one.

The DB has a given schema (two tables) and some (very few) pre-defined entries. I'm looking for a simple way to start an in-memory database, create the tables and fill in the data. All of this should happen while initializing the Spring context.

My approach would be to use H2 as my database and then maybe Spring Batch to load the data from csv- or xml-files. However I was hoping there might be an easier way to achieve this. Are there any databases/frameworks/tools that can do this out-of-the-box?

It would only take a few SQL-commands to set-up everything I need. I'm looking for a way to do this in a Spring-environment as simple as possible.

Answer 1

With H2, you could initialize the database in the database URL itself. Example: you have a SQL script 'start.sql' that contains all the scripts to initialize. This can also include creating the tables from CSV file. Then use a database URL of the form jdbc:h2:~/temp/test;init=runscript from '~/temp/start.sql'. The start.sql could look like this (this is an example I'm working on anyway - it shows how to create tables from a CSV file):

create table if not exists location(id int primary key, country varchar, 
region varchar, city varchar, postalCode varchar, latitude float, longitude float, 
metroCode varchar, areaCode varchar) 
as select * from csvread('~/Downloads/GeoLiteCity/GeoLiteCity-Location.csv');

create table if not exists blocks(start long, end long primary key, location int) 
as select * from csvread('~/Downloads/GeoLiteCity/GeoLiteCity-Blocks.csv');

create alias if not exists ip2id deterministic as $$
long ip2id(String s) {
  String[] x = s.split("\\.");
  return (Long.parseLong(x[0]) << 24) + (Long.parseLong(x[1]) << 16) +
    (Long.parseLong(x[2]) << 8) + Long.parseLong(x[3]);
} $$;

create alias if not exists id2ip deterministic as $$
String id2ip(long x) {
  return (x >> 24) + "." + ((x >> 16) & 255) + "." + 
      ((x >> 8) & 255) + "." + (x & 255);
} $$;

Answer 2

Spring has some built-in embedded database support, see embedded database support in the documentation.

Answer 3

Spring 3 added more support for embedded databases starting from 3 with the help of jdbc:embedded-database element. Read this tutorial for more information.

I'd also recommend using Derby as it comes bundled with JDK 6.

Answer 4

HSQLDB is a good choice.