A Personal Data Storage Application With Embedded Java DB

by Patrick Keegan

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.

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Learning in Second Life

Second Life (SL) is easy to misunderstand, and many people have the wrong idea that it is some kind of game environment, or a place where people just chat and act silly. On the contrary, Second Life has many places of learning from Science Friday, to Buddhist meditations, to technical seminars[Read More]

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Virtual Classrooms in Geeksville

Over the last few years I've been spending a lot of time online learning a whole lot of stuff. There is no better tool to teach us about computing, programming languages, and software than the computer itself. Of course, I realize it's not the computer but the site or the program, but virtual learning is hugely successful and for good reasons. And it's not limited to computer topics, thank goodness.[Read More]

\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

Participate in MySQL-GlassFish Student Contest and Win $500

Use MySQL database and GlassFish application server to develop a web application and write a review for...

  • A chance to win a grand prize of $500 in Visa debit card, and
  • Five chances to win a prize of $250 in Visa debit card


Comments:

thnx 4 d guidance

Posted by Anoop Pandey on September 18, 2008 at 01:26 PM PDT #

Second Life is still a huge waste of time for most people, including myself. I've participated in a couple of Sun's SL events...not worth one's time really.

Posted by guest on September 18, 2008 at 05:08 PM PDT #

Good.

Posted by kayode on September 18, 2008 at 10:19 PM PDT #

The netbean 6.1 say it can't see my table RECORD. Can you tell me why????

Posted by Khari on September 19, 2008 at 08:21 AM PDT #

Amazing!!, simple and brillant, thanks a lot for the example.

Posted by Johny on September 20, 2008 at 04:05 AM PDT #

Very interesting, This feauture will simplify the development of many GUIs.

Posted by Mikey on September 22, 2008 at 05:08 AM PDT #

Nice

Posted by guest on September 22, 2008 at 05:12 PM PDT #

thanks ;)

Posted by Toygar Dündaralp on September 22, 2008 at 06:03 PM PDT #

A little help?

2008-09-23 15:29:42 org.jdesktop.application.Application$1 run
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)
at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:83)
at recordings.RecordingsView.initComponents(RecordingsView.java:290)
at recordings.RecordingsView.<init>(RecordingsView.java:36)
at recordings.RecordingsApp.startup(RecordingsApp.java:19)
at org.jdesktop.application.Application$1.run(Application.java:171)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:597)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
Exception in thread "AWT-EventQueue-0" java.lang.Error: Application class recordings.RecordingsApp failed to launch
at org.jdesktop.application.Application$1.run(Application.java:177)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:597)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)
Caused by: 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)
at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:83)
at recordings.RecordingsView.initComponents(RecordingsView.java:290)
at recordings.RecordingsView.<init>(RecordingsView.java:36)
at recordings.RecordingsApp.startup(RecordingsApp.java:19)
at org.jdesktop.application.Application$1.run(Application.java:171)
... 8 more
BUILD SUCCESSFUL (total time: 2 seconds)

Posted by a8o2ge on September 22, 2008 at 11:31 PM PDT #

It seems to me that this example would only be complete if the deliverable took the form of an installer rather than a .jar file.

Posted by Samuel Richter on September 23, 2008 at 12:26 AM PDT #

Good.
Fast Netbeans!!!!!

Posted by Tinto on September 23, 2008 at 02:52 AM PDT #

very good

Posted by 一卡多号 on September 23, 2008 at 04:56 AM PDT #

It would be helpful to provide more details on distribution aspect. How a user getting some intergrated jar and executing
javaw -jar mypersonaldataappp.jar
can finalize setup of database selecting its location, backup procedures and so on

Posted by Дмитрий on September 24, 2008 at 03:49 AM PDT #

which plugins must be installed to run this example?

Posted by rafael on September 25, 2008 at 11:08 PM PDT #

Very Good

Posted by Nilay on September 29, 2008 at 05:59 PM PDT #

Nice, I'm a new Java developer and I really like the tutorial.

Posted by Shiller on September 30, 2008 at 11:59 AM PDT #

For info on Packaging and Deploying your application, check out the following articles:

http://www.netbeans.org/kb/articles/javase-deploy.html

http://wiki.netbeans.org/FaqpackageAndDistribute

Posted by Shiller on October 01, 2008 at 11:11 PM PDT #

i want learn more in java manipulator

Posted by joven on October 02, 2008 at 04:44 PM PDT #

It did not work for me!
got following error!

init:
deps-jar:
compile:
run:
[TopLink Info]: 2008.10.03 12:35:17.962--ServerSession(28541929)--TopLink, version: Oracle TopLink Essentials - 2.0 (Build b58g-fcs (09/07/2007))
[TopLink Info]: 2008.10.03 12:35:23.931--ServerSession(28541929)--file:/C:/Documents%20and%20Settings/umamaheswara.rao/My%20Documents/NetBeansProjects/Recording/build/classes/-jdbc:derby:Recordings;create=truePU login successful
[TopLink Warning]: 2008.10.03 12:35:24.134--UnitOfWork(13926634)--Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0 (Build b58g-fcs (09/07/2007))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Table/View 'RECORD' does not exist.
Error Code: 30000
Call: SELECT TITLE, FORMAT, RATING, CONDITION, ARTIST, COMMENTS FROM RECORD
Query: ReportQuery(recording.Record)
Oct 3, 2008 12:35:24 PM org.jdesktop.application.Application$1 run
SEVERE: Application class recording.RecordingApp failed to launch
Local Exception Stack:
Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0 (Build b58g-fcs (09/07/2007))): oracle.toplink.essentials.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: Table/View 'RECORD' does not exist.
Error Code: 30000
Call: SELECT TITLE, FORMAT, RATING, CONDITION, ARTIST, COMMENTS FROM RECORD
Query: ReportQuery(recording.Record)
at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:566)
at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:452)
at oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:473)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:214)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:285)
at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:615)
at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2416)
at oracle.toplink.essentials.internal.queryframework.ExpressionQueryMechanism.selectAllReportQueryRows(ExpressionQueryMechanism.java:2382)
at oracle.toplink.essentials.queryframework.ReportQuery.executeDatabaseQuery(ReportQuery.java:802)
at oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:628)
at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:692)
at oracle.toplink.essentials.queryframework.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:746)
at oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2233)
at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:952)
at oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:924)
at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.executeReadQuery(EJBQueryImpl.java:367)
at oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.getResultList(EJBQueryImpl.java:478)
at recording.RecordingView.initComponents(RecordingView.java:276)
at recording.RecordingView.<init>(RecordingView.java:35)
at recording.RecordingApp.startup(RecordingApp.java:19)
at org.jdesktop.application.Application$1.run(Application.java:171)
at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:597)
at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:121)

Posted by Mahesh on October 02, 2008 at 05:03 PM PDT #

that's pretty cool - thank you.

Posted by batman567 on January 21, 2009 at 05:05 AM PST #

Post a Comment:
Comments are closed for this entry.
About

Java Technology Fundamentals are now covered as part of the Core Java Technology Tech Tips. Please go there for more tech tips.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today