How to initialize an embedded Java DB database using JPA/TopLink Essentials

The question of how to initialize an embedded Java DB database when you are using JPA/TopLink Essentials came up recently on the NetBeans users list.

I answered the question there and thought I'd answer it here as well... These steps are applicable even when you're not using TopLink, with the exception of how you configure your persistence unit to auto-create tables for you.

There are three parts to initializating an embedded database. Note that this is for a fresh install of the application. If you are upgrading an application, that is a whole other kettle of fish that I will answer another time...

• Create the database
• Create tables
• Load initial data set

I'll cover each of these separately. See also the last section, where I talk about using a model database, which handles all of these steps in one fell swoop.

Create the Database

If you change your persistence.xml so that the URL includes the parameter ";create=true" then Java DB will automatically create the database for you

Create the Tables

You can have TopLink to this for you by setting a property for the
persistence unit in your persistence.xml file:

<property name="toplink.ddl-generation" value="create-tables">
</property>


See the Oracle TopLink extensions documentation for more information

Load the Initial Data Set

There are a number of ways you can do this:

• You can write some code that creates new JPA objects, initializes them, and commits them using JPA
• You can write some code that uses JDBC to load your data set
• Use a SQL script and the Derby tool 'ij' to load the SQL. This requires including derbytools.jar in your application. See this blog entry for a reference to one user who was successful doing it this way.

Three in One Blow: Using a Model Database

Another way to create the database, tables and data is to use a model database. You create your database as you want it, and then include the database directory in your application jar file. Derby databases are completely portable across any platform, so you don't need to worry about having different versions for, say, different byte-ordering architectures or for 32-bit/64-bit architectures.

Then at startup if the database directory is not there, you can extract it from your application jar and you're good to go.

Note this is what NetBeans does. When you register Java DB, or if Java DB is detected when you register Glassfish or you use JDK 6, we automatically extract a zip of a sample database that is included in NetBeans and create a connection to it in the Services tab. It works quite nicely.