Apologies in advance to those of you expecting a weightier post. Unfortunately, I've been busy lately, I haven't had time to come up with working examples of applications that handle things like many-to-many relationships or which display information from multiple tables in one cell. But I do have time to squeeze out entries on other simple-but-cool things that have been on my mind a long time. Today I tackle embedding a database within a Java desktop application.

Most of the tutorials I've run across talk about creating applications that connect with a database that is managed from a server. This is appropriate for most business applications. However, sometimes you might want to create a more portable application that carries its own data with it, such as an application in which a user manages personal data.

In this application, we will use NetBeans to create a simple desktop application to store info on your personal music collection. We will use Java DB, which has an embedded mode so that it can be packaged within the application. The application will also make use of the Beans Binding library and the Java Persistence API.

Setting Up the Database

First we will create a "connection". This isn't a connection to a real database but it gives us a place to create a database structure, which we can then use to generate application code.

1. In NetBeans, open the Services window and expand the Drivers node.
2. Right-click Java DB (Embedded) and choose Connect Using.

3. For Database URL, enter jdbc:derby:Recordings;create=true.
4. For User Name, enter APP.
5. Enter whatever you wish the password and click OK.

Now we need to generate the database structure. We will do so by executing an SQL script that defines a single table and its columns.

To generate the database structure:

1. In the Services window, scroll down to the jdbc:derby:Recordings;create=true node, right-click, and choose Execute Command.
2. Paste the following code into the editor.

   create table "APP".RECORD
   (
   	ARTIST VARCHAR(30) NOT NULL,
   	TITLE VARCHAR(30) NOT NULL PRIMARY KEY,
   	FORMAT VARCHAR(30) NOT NULL,
   	RATING INTEGER,
   	CONDITION VARCHAR(10),
   	COMMENTS VARCHAR(30)
   )
   
   
   

3. Click the Run SQL button to execute the command.

4. Right-click the jdbc:derby:Recordings;create=true node and choose Refresh.
5. Expand the node and then expand the Tables node.

Creating the Application

With the database structure set up, we can now use the Java Desktop Application project template to create a basic CRUD application based on that structure.

1. Choose File | New Project.
2. In the wizard select the Java | Java Desktop Application template.
3. In the Name and Location page of the wizard, select the Database Application skeleton.
4. In the Master Table page of the wizard, select the connection for the Recordings database.
5. In the Detail Options page, click Finish.

Once you complete the wizard, you have a basic CRUD application that should be ready to build and run. Here is how the application looks in the Design view of the GUI Builder:

Building, Testing, and Distributing the Application

Before building and running, make sure that you have all of the necessary libraries by expanding the project's Libraries node.

You should see libraries for the Swing Application Framework, Beans Binding, TopLink (which contains classes from the Java Persistence API), and Derby (Java DB). Depending on your setup, it might happen that TopLink and Derby are not added. If those libraries are not listed, you need to add the libraries manually. The TopLink library is available within the IDE's list of libraries. You can get the Derby JAR file from an installation of JDK 6, Glassfish, or from a Java DB or Derby standalone installation.

To add the TopLink library:

1. Right-click the Libraries node and choose Add Library.
2. From the Available Libraries list, add TopLink Essentials.

To add derby.jar:

1. Right-click the Libraries node and choose Add JAR/Folder.
2. Navigate to your Derby/Java DB installation and select derby.jar. (I used the copy I found in C:\Program Files\glassfish-v2ur2\javadb\lib, but you might have it as part of your JDK.)

To build and test run the project:

1. Press F11 to build the project. (If this project is not your main project, right-click the project's node in the Projects window and choose Build.)
2. Press F6 to run the project in the IDE. (If this project is not your main project, right-click the project's node in the Projects window and choose Run.)
3. In the running application, add a few records and save them.

The database is created and saved in your project directory. You can glimpse the database files that were created in the test run by opening the Files window and expanding the node for your project.

You'll notice that there is a sub-folder called Recordings (based on the database name) which contains the database files.

Note: If you run the application directly from dist/Recordings.jar, the database once again will be empty. When you add records, the database files will be created in a location that depends conventions of your operating system. I run on Vista, and so my database files are created in the VirtualStore folder of my Windows user directory.

You can distribute the application by zipping up the project's dist folder and giving it to the user. The dist folder contains the application's main JAR file, Recordings.jar, and the lib folder. The lib folder contains various libraries essential for the project, including derby.jar, which contains pretty much all of Java DB (which is just 2.2 MB).

Once they unzip the file, they can run the Recordings.jar file, either by double-clicking it (if they have the .jar file extension associated with Java on their system) or by running it from the command line with the command java -jar Recordings.jar.

So there you have it - a portable database application with no hand coding.

Bonus Note on the Database Structure

For purposes of quickly showing how to use Java DB as an embedded db, I used and over-simplified database structure, especially regarding the primary key. So that you can you can have multiple entries for the same artist, you might want to create an auto-generated identity field and make that the primary key instead. For example:

create table "APP".RECORD
(
    ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    ARTIST VARCHAR(30) NOT NULL,
    TITLE VARCHAR(30) NOT NULL PRIMARY KEY,
    FORMAT VARCHAR(30) NOT NULL,
    RATING INTEGER,
    CONDITION VARCHAR(10),
    COMMENTS VARCHAR(30)
);

ALTER TABLE "APP".RECORD
    ADD CONSTRAINT CONTACTS2008_PK Primary Key (ID);

Then after creating the project, you would need to modify the Recordings entity class by inserting the line @GeneratedValue(strategy=GenerationType.IDENTITY)@Id.