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.