Patrick Keegan's Blog

A Personal Data Storage Application With Embedded Java DB

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.

Comments

• Nice post! I've copied the link to our Mobile & Embedded Wiki in the Databases section: http://wiki.java.net/bin/view/Mobileandembedded/DbResources

  -- Terrence

  Posted by: terrencebarr on September 01, 2008 at 07:08 AM

• Excellent! I would like to port a HTML/Javascript/Gears application to Java. I need to do it in stages and would like to initially swap Gears for JavaDB. Anyway to run an embedded JavaDB on a client and access it from a Web application using javascript?

  Thanks for teh great tutorial.

  Scott

  Posted by: sderrick on September 02, 2008 at 08:12 AM

• Scott,

  Yes you can access Java DB embedded in a web client and access it via Javascript. You can do this via Sun's Java plug-in which gets installed by default with Sun's JRE or/and JDK. Check the DerbyTax demo application at the Java DB portal which just does this...

  http://developers.sun.com/javadb/overview/product_tour/index.jsp
  

  Posted by: forsini on September 05, 2008 at 11:03 AM

• This is a great blog entry. I hope there will be more like it!

  Posted by: mortazavi on September 29, 2008 at 03:22 PM

• I have used this as a model for my own program and it works great except for 1 issue. I can't see the data from the database explorer in Netbeans, and when I use the iReport add-in it can't see the data also ie the IDE can't see the actual database, but the running program in the IDE can. I got around this by using SquirrelSQL and the standalone version of iReport (which really shouldn't be necessary). Problem now is when I add my report (which works fine in the standalone copy of iReport) to a button in my application it now no longer finds the data. Is there any way to make the IDE (ie both the database explorer and iReport add-in) see the database created by running the application within the IDE and use it in a way consistent with the production application?

  Posted by: roggy on September 29, 2008 at 04:22 PM

• Hey patrick ...
  I have a problem to implement persistence in a LAN connection with a mysql

  property name="toplink.jdbc.url" value="jdbc:mysql://192.168.0.129:3306/area4ybd"

  
  [TopLink Info]: 2008.10.07 11:04:54.718--ServerSession(3045408)--TopLink, version: Oracle TopLink Essentials - 2.0.1 (Build b09d-fcs (12/06/2007))
  07/10/2008 11:09:54 AM org.jdesktop.application.Application$1 run
  GRAVE: Application class desktopapplication2.DesktopApplication2 failed to launch
  Local Exception Stack:
  Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0.1 (Build b09d-fcs (12/06/2007))): oracle.toplink.essentials.exceptions.DatabaseException
  Internal Exception: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
  

  Posted by: jarwilsis on October 07, 2008 at 09:33 AM

• Hey Patrick

  I found this blog very helpful. I had been try to do this for sometime. ...Cool!

  Posted by: quest53216 on November 03, 2008 at 02:23 PM

• Hi patrick
  Please i need a site that can help me with writing a school project report on a desktop database application i built
  

  Posted by: manjude on May 01, 2009 at 10:34 AM

• First of all...great post! Very useful :)

  I've encountered a problem when i was running the app in netbeans 6.5 :(
  The output displays these error warnings:

  "SEVERE: Application class recordings.RecordingsApp failed to launch
  javax.persistence.PersistenceException: No Persistence provider for EntityManager named jdbc:derby:Recordings;create=truePU: The following providers:
  oracle.toplink.essentials.PersistenceProvider
  oracle.toplink.essentials.ejb.cmp3.EntityManagerFactoryProvider
  Returned null to createEntityManagerFactory.

  at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:154)

  (...)
  (...)
  
  org.jdesktop.application.Application$1.run(Application.java:171)
  ... 8 more"

  
  Can anyone tel me how to resolve this or what i've done wrong?
  

  Posted by: indiodoido on May 02, 2009 at 07:27 AM

This work is licensed under a Creative Commons License.