Monday, November 26, 2007

Auto-creating your embedded Java DB database - some working code

A lot of folks are surprised when an embedded database doesn't work the same as a client/server database. In particular, they don't initially realize that it is the application's responsibility to create the necessary tables and load them with any initial data the first time the application is run in a new location. Many of use are used to the database just being out there on a server somewhere and aren't used to having this responsibility.

I asked for a blog or tutorial on how to do this on the Derby user mailing list, and one user, Alan Burlison, was kind enough to point me to his work. And I quote:

No tutorial, but some working code

This runs inside Tomcat and creates a new database the first time it is run, subsequent runs use the existing database. The database runs in embedded server mode, so whilst Tomcat is running you can access it externally as well.

The database is initialised via SQL script files that are contained in the application WAR file, using the ij utility (derbytools.jar). ij can be run from inside an application as well as from the command-line, and ij + SQL scripts is the easiest way I've found so far of setting up a database, because the SQL scripts can be debugged from the command-line before being embedded in the app.

9 comments:

Emilian Bold said...

Hy,

The thing I didn't like about Derby compared to HSQL I used long ago is that all the databases started from JAR for example are read-only.

Normally with unit-tests I need a "pristine" copy as a resource (which will be in a JAR). Then the unit-test changes some stuff (so read-only is useless here) and the I assert my changes and then I can just discard everything.

Basically Derby doesn't seem to have an in-memory DB which can be started as an image of a resource (from a JAR/URL, whatever).

Probably a workaround is to manually copy the resource files somewhere (/tmp?) and then set derby home, etc. which is cumbersome and I haven't gotten to that yet.

PS: There's a typo in the link you gave, it also includes some html.

Unknown said...

I think your point about database in jar file being read-only is a good point, one worth passing on to the Derby team.

I fixed the link, it should work now.

Anonymous said...

Unfortunately the link you provided is broken. Could you please correct it?

Unknown said...

Bummer, it looks like the link has been removed from the OpenSolaris pages! If anyone saved the code, can you post it for our dear readers?

Unknown said...

I talked to Alan, and I fixed the link.

Anonymous said...

The link is broken again. Can you please fix it? Thank you.

Unknown said...

Thanks, Anonymous. I sent an email to Alan. Maybe once I get a new link I'll just embed the code in the blog.

Unknown said...

OK, I fixed the link, thanks for notifying me of the change.

Anonymous said...

Thanks David for this post. For those who complained about broken link - Go to http://src.opensolaris.org/source/

then search for DbManager.java

and then download to save a copy.