Using H2 as embedded in-memory database
Lately (as in this week) I have had to do some work with relational
databases (argh!) -- nothing too bad, pretty basic stuff, something
where BDB would work mostly fine, but where accessibility dictates more
shareable alternative.
But for testing purposes, I much rather have
something fully controllable from within JVM instead of having to deploy
a repliace of DB that will be used for production.
Fortunately there are multiple viable choices; and just based on
personal preferences I decided to try out H2.
1. Detour: why H2?
Part of my reasoning is due to seemingly healthy activity on mailing lists; and another part is the feature set (which is extensive). But ultimately it mostly comes down to the fact that project and its author seem very much like archetypical examples of Good Open Source Project (and its leader); specifically project that has a single author that
- has strong vision regarding what the product should be about, and
- has actually "done it before"; either implemented a similar product before (in this case, hypersonicSQL, and even more) or worked in domain, and
- is a competent developer all-around, outside his/her specific domain
But I digress. I just thought I'll mention how much good leadership means for Open Source projects, and why it is perhaps the best indicator for quality of project output (this is why Saxon is so good; why Jersey kicks ass; and so forth); much more so than branding (yes, being a "Google project" gives you plenty of insta-karma, but means surprisingly little with regards to quality).
2. Ok, so How Do I Start the Thing?
Anyway: back to what I was doing... a t first I spend quite a bit of
time trying to read through documentation (which does exist; not in
abundance, but in large enough quantities) trying to figure out how to
start up database in embedded, in-memory mode. From feature set, it is
clear that one can run H2 in embedded mode; and further, fully in-memory
(as an alternative to disk-based persistence). But somehow I failed to
initially realize how simple this is, and tried to figure out with
Server/Service (TCP, HTTP or PG == Postgres) is needed.
Turns out the right answer is "none of above". In fact, you don't need
to start anything. Rather: "if you call it, it will be there".
Literally:
database will spring to existence if you try connecting to it via JDBC,
using special URL.
... d'oh! But of course. :-)
3. Really? Are you kidding me?
Nope.
Once you wrap your brains around this Zen-y idea, it starts to make more
sense (and also suggest at one issue to overcome -- database will also,
by default, be gone when the last connection closes -- this can be
easily resolved, more on this later)
So, just to help anyone else who might just be googling for the answer; it is JDBC connection String that matters. I use something like:
jdbc:h2:mem:test;DB_CLOSE_DELAY=-1
(where 'test' is logical name of the database, needed to allow multiple connections)
so, for example:
DataSource ds = JdbcConnectionPool.create("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1", "user", "password");
Connection conn = ds.getConnection();
conn.createStatement().executeUpdate("CREATE TABLE data ("
+" key VARCHAR(255) PRIMARY KEY,
+" value VARCHAR(1023) )");
// ... populate with data, test etc
conn.close();
and that's all. No need to start anything else up unless you actually
want DB to serve external connections. Oh, and if you do: yes, you can
actually expose embedded and/or in-memory databases (these are
orthogonal choices!) to external clients too.
Connecting is done
either using straight JDBC, or using simple connection pool H2 comes
equipped with, which actually seems functional enough for most needs. I
think I like this little (?) database already; it makes writing unit
tests a breeze, obviously. But I suspect it could also act as somewhat
production ready light-weight - yet - non-toy SQL database. We shall see.
Oh, and for more quick-n-dirty tips, just check out H2 Cheat Sheet. It's some condensed good stuff.
