Skip to main content

A Personal Data Storage Application With Embedded Java DB

Posted by pkeegan on August 30, 2008 at 6:57 AM PDT

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. embedded-connectusing.png

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

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.
    embedded-executecommand.png
  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. embedded-executescript.png

  5. Right-click the jdbc:derby:Recordings;create=true node and choose Refresh.
  6. 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.

    embedded-javadesktop.png

  3. In the Name and Location page of the wizard, select the Database Application skeleton.
    embedded-wiznamelocation.png
  4. In the Master Table page of the wizard, select the connection for the Recordings database.
    embedded-wizmastertable.png
  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:

embedded-designview.png

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.

embedded-libraries.png

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.

embedded-addlibrary.png

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.

embedded-runningapp.png

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.

embedded-fileswindow.png

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).

embedded-dist.png

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.

Related Topics >>

Comments

this was very helpful indeed... can i ask a favor? if i ...

this was very helpful indeed...

can i ask a favor?
if i would be inserting a search button, which will have a function of displaying the data i have entered previously, what would be the code i have to modify and where could i insert the command code for that?

kindly answer this please.

thanks!

This was very enlightening.  I'm new to Java, but have ...

This was very enlightening. I'm new to Java, but have many languages under my belt as well as experience with Databases and design. So... I'm researching for my company a solution to replace and old foxpro application. It has to be a desktop application with an embedded database such as this. Moreover, it must also allow for data refreshes that come over on a CD.

What would you recommend, an XML file that would parse the data to update the database, or text delimited file. The system that the data would be coming from is never going to be more than one system and the data is highly controlled. The users are in the field, so they cannot download data unless they have access to the internet, but that could be far and few between situations.

rushtonjj

Thank you - This helped!

This documentation really helped me out. I struggled for the past couple days with the NetBeans documentation in regards to executing a Java DB application outside an IDE. While I noticed that there would be some tweaking involved with that application, I am have made a lot more progress now because of this document. Thank you very much for sharing that with us.

getting multiple elements from the table

Hi Patrick, Thanks for all of your helpful blogs - they are much better than the NetBeans tutorials! I am wondering if you can perform an operation on only the elements that you have selected from a table. For example, if I had a database of sales that I had made with dates, names, etc, then how could I produce a button or have a text field displaying the sum of the selected rows? Consider wanting to know how many dollars Mr. Smith spends in a year or something. Can you refer me to something like this? Thanks for any help.

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

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

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?

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

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

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

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

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?

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