Start and setup in-memory DB using Spring

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开发者_StackOverflow社区 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.

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

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);
} $$;

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.

HSQLDB is a good choice.