SELECT A COUNTRY/REGION |
Social Applications
Networks
Knowledge Base
Support
|
C-Level Executives
Other Roles
|
Support
Education
Partner
Other Tasks
|
|
By John O'Conner, March 2006 |
|
![]() |
Sun Microsystems recently announced that it is distributing and supporting Java DB based on the 100 percent Java technology, open-source Apache Derby database. Derby was previously available under its earlier name, Cloudscape, from its former owners: Cloudscape, Informix, and IBM. IBM donated the Derby product source code to the Apache Foundation as an open-source project. Sun, IBM, other companies, and individuals have been actively involved in development of the relational database as part of the Apache Derby community. Sun distributes Java DB in many of its products, including the Sun Java Enterprise System and the Sun Java System Application Server. The NetBeans integrated development environment (IDE) 5.0 also supports Java DB.
Java DB is lightweight at 2 megabytes and embeddable within desktop Java technology applications. Desktop applications can now access powerful database storage with triggers, stored procedures, and support for SQL, Java DataBase Connectivity (JDBC) software, and Java Platform, Enterprise Edition (Java EE, formerly referred to as J2EE), all embedded within the same Java virtual machine (JVM). *
This article describes how to download, install, integrate, and deploy Java DB within desktop Java technology applications. A demo application called Address Book demonstrates how to work with Java DB as an embedded database.
![]() |
The Address Book demo uses Java DB to store address
information. This demo stores names, phone numbers, email addresses, and
postal addresses. It allows you to create new address entries and to
save, edit, and delete them. The application creates its database in the
user's home directory within an
.addressbook
subdirectory. The database is
embedded with the application, so there is no need to set up or manage a
separate server or system. To deploy this embedded database
application, we need only the application JAR file and the database
library JAR file. Figure 1 shows the demo's user interface (UI).
|
Address Book's main frame window is an
AddressFrame
class that extends a Java Foundation Classes/Swing (JFC/Swing)
JFrame
. The
AddressFrame
is a container for other graphical
components and also acts as a controller by handling various events
generated by the child components. The child components are
JPanel
subclasses, each with a different responsibility:
AddressPanel
represents an
address record. It also provides the UI for editing existing records and
creating new records. It contains text fields for all the major
properties of an
Address
object.
AddressActionPanel
provides buttons for all the major use cases that the application supports. This panel generates events that
AddressFrame
must handle. For example, when the user clicks Save, this panel generates an event.
AddressFrame
listens to and handles all important events from this panel.
AddressListPanel
provides a scrollable list of names on the far left of the
AddressFrame
. The list holds
ListEntry
objects. A
ListEntry
stores a database record's unique
identifier. The record identifier (ID) allows the application to
retrieve an entire record's contents into the
AddressPanel
.
The application uses a Data Access Object (DAO) to
isolate the database-specific code. The DAO encapsulates database
connections and statements. A DAO is a useful design pattern that allows
loose coupling between an application and the underlying
persistence-storage mechanism. The application's
AddressDao
class is an example of a DAO. When the
AddressFrame
edits, saves, or deletes an
Address
object, it always uses an instance of the
AddressDao
class. Although the Address Book
application uses Java DB, you could change it to use an entirely
different database just by modifying this one class.
![]() |
The easiest way to get Java DB is to download a copy from the Sun Developer Network's Java DB site. The binary distribution provides everything you need to begin working with embedded database applications. After you download the binary distribution, you'll find a Java DB directory structure that contains the following subdirectories:
demo
subdirectory has two demonstration
programs. One example shows how to create a trivial embedded
application. The other shows how to use Java DB in a client-server
environment.
frameworks
subdirectory contains utilities
for setting up environment variables and for creating and working with
databases. This directory is not useful for our demo because our
application will be entirely self-contained. No outside utilities will
be used for the Address Book application.
javadoc
subdirectory contains API
documentation. This directory is particularly useful if you configure
your IDE to point to it as the Java DB API Javadoc.
docs
subdirectory contains documentation about the Java DB product itself: setup, administrator, and reference guides.
lib
subdirectory contains the Java DB
libraries packaged as JAR files. Read the Java DB documentation to find
out about the various libraries. For an embedded database application,
we will use only the
derby.jar
library file.
Installing Java DB for development requires only that you make the
derby.jar
file part of your application classpath. It's that simple. You can
set the
CLASSPATH
variable of your Solaris, Linux, Windows, or other host
environment to include the JAR file, or you can include the file as
part of your command-line options when compiling or running. The Address
Book demo's ANT script shows you how to include this JAR file during
project development if you use ANT. Alternatively, some IDEs, including
NetBeans IDE 5.0, let you configure classpaths.
![]() |
Most IDEs provide a way to add libraries to the development classpath. Follow these instructions to add the Java DB libraries to NetBeans IDE 5.0:
|
|
derby.jar
file to the JavaDBEmbedded library,
click on Add JAR/Folder... in the Library Manager window. Navigate the
file chooser to the
derby.jar
file and select it as shown in Figure 4.
|
javadoc
subdirectory from your Java DB
installation. Now, the Java DB API Javadoc is available when you use the
JavaDBEmbedded library in your NetBeans IDE projects.
You can now add the JavaDBEmbedded library to your
NetBeans IDE 5.0 project by using the project's property settings. When
you compile, debug, and run the application within the IDE, the IDE will
be able to find the needed
derby.jar
file.
To accommodate users of other IDEs, I have placed the
derby.jar
library within the
lib
subdirectory of the downloadable Address
Book project. The accompanying ANT script can build and run the demo
without any dependencies on the NetBeans IDE.
![]() |
Loading the JDBC technology driver starts the database management system. Java DB's drivers come with the
derby.jar
file, so you don't need to download anything else. Load the JDBC driver by referencing it using the
Class.forName
method. The embedded driver name is
org.apache.derby.jdbc.EmbeddedDriver
, and you can load it as you would other JDBC drivers.
Class.forName("org.apache.derby.jdbc.EmbeddedDriver"); |
The Address Book demo reads the driver name from a configuration property file and passes the name to a
loadDriver
method. Additionally, as mentioned
earlier, Address Book encapsulates all database functionality into a
Data Access Object (DAO), a
core Java EE design pattern
used to access data from a variety of sources. The DAO pattern works
equally well for Java SE applications like Address Book. The following
code snippet shows how
AddressDao
reads the driver name and loads the driver:
private Properties bProperties = null; public AddressDao(String addressBookName) { this.dbName = addressBookName; setDBSystemDir(); dbProperties = loadDBProperties(); String driverName = dbProperties.getProperty("derby.driver"); loadDatabaseDriver(driverName); ... } private Properties loadDBProperties() { InputStream dbPropInputStream = null; dbPropInputStream = AddressDao.class.getResourceAsStream("Configuration.properties"); dbProperties = new Properties(); try { dbProperties.load(dbPropInputStream); } catch (IOException ex) { ex.printStackTrace(); } return dbProperties; } private void loadDatabaseDriver(String driverName) { // Load the Java DB driver. try { Class.forName(driverName); } catch (ClassNotFoundException ex) { ex.printStackTrace(); } } |
![]() |
A JDBC technology connection identifies a specific database and allows you to perform administrative tasks. Tasks include starting, stopping, copying, and even deleting a database. The driver manager provides all database connections.
Retrieve a connection from the driver manager by providing a URL string that identifies the database and a set of properties that influence the connection's interaction with the database. A very common use of properties is to associate a user name and password with a connection.
All connection URLs have the following form:
jdbc:derby:<dbName>[propertyList] |
The
dbName
portion of the URL identifies a specific
database. A database can be in one of many locations: in the current
working directory, on the classpath, in a JAR file, in a specific Java
DB database home directory, or in an absolute location on your file
system. The easiest way to manage your database location in an embedded
environment is to set the
derby.system.home
system property. This
property tells Java DB the default home location of all databases. By
setting this property, the Address Book demo ensures that Java DB always
finds the correct application database. The application database is
named DefaultAddressBook, and it will exist within the directory
indicated by the
derby.system.home
property. The connection URL for this database would look like this:
jdbc:derby:DefaultAddressBook |
The optional
propertyList
is a set of properties that you
can pass to the database system. You can pass properties into the Java
DB system either on the URL itself or as a separate
Properties
object. If properties are part of the URL, a semicolon (
;
) should precede each property. The most common properties are these:
create=true
databaseName=nameOfDatabase
user=userName
password=userPassword
shutdown=true
To connect to the DefaultAddressBook database, the demo must first set the
derby.system.home
system property. The demo uses the
.addressbook
subdirectory of the user's home directory. Use the
System
class to find out the user's home directory. Then use the class again to set the
derby.system.home
property:
private void setDBSystemDir() { // Decide on the db system directory: <userhome>/.addressbook/ String userHomeDir = System.getProperty("user.home", "."); String systemDir = userHomeDir + "/.addressbook"; // Set the db system directory. System.setProperty("derby.system.home", systemDir); } |
Once the application has specified where all databases will exist, it can retrieve a database connection. In this example, notice that I have appended connection properties to the database URL.
Connection dbConnection = null; String strUrl = "jdbc:derby:DefaultAddressBook;user=dbuser;password=dbuserpwd"; try { dbConnection = DriverManager.getConnection(strUrl); } catch (SQLException sqle) { sqle.printStackTrace(); } |
Alternatively, you can put those properties into a
Properties
object. Pass the
Properties
object as an argument when retrieving a connection:
Connection dbConnection = null; String strUrl = "jdbc:derby:DefaultAddressBook"; Properties props = new Properties(); props.put("user", "dbuser"); props.put("password", "dbuserpwd"); try { dbConnection = DriverManager.getConnection(strUrl, props); } catch(SQLException sqle) { sqle.printStackTrace(); } |
![]() |
The Address Book demo application does not have a ready-made database. Instead, the application must create the database when it starts. One of the biggest advantages of using an embedded database is that the application -- not the user -- takes care of the details of setting up a database. The application can control where the database exists, what tables exist, and how permissions are handled.
Address
Book creates a database called DefaultAddressBook in a subdirectory of
the user's home directory, and it does so without asking the user for
any additional information. You can create a new database in Java DB by
using the
create=true
property when retrieving a
connection to a database. Because our application will use the
DefaultAddressBook database, we should first create this database.
Assuming that the application has already set the
derby.system.home
property as discussed earlier, the application creates the database and connects to it like this:
Connection dbConnection = null; String strUrl = "jdbc:derby:DefaultAddressBook;create=true"; try { dbConnection = DriverManager.getConnection(strUrl); } catch (SQLException ex) { ex.printStackTrace(); } |
Because the
create=true
property is included, Java DB will
attempt to create the database files for the first time. Creating the
database doesn't actually create any application tables. However, you
should now be able to find a new subdirectory named
.addressbook/DefaultAddressBook
in your home directory.
After it has created the database, the application creates the tables. The demo uses only one
ADDRESS
table in the default application
APP
schema. The following SQL code creates the
ADDRESS
table:
CREATE table APP.ADDRESS ( ID INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), LASTNAME VARCHAR(30), FIRSTNAME VARCHAR(30), MIDDLENAME VARCHAR(30), PHONE VARCHAR(20), EMAIL VARCHAR(30), ADDRESS1 VARCHAR(30), ADDRESS2 VARCHAR(30), CITY VARCHAR(30), STATE VARCHAR(30), POSTALCODE VARCHAR(20), COUNTRY VARCHAR(30) ) |
Each record has a record identifier or
ID
field. Java DB generates this value for each new record that it adds to the database. The
ID
field is the primary key for each address record.
All remaining address record fields contain
varchar
elements of various lengths. For example, the
LASTNAME
field can contain a maximum of 30
varchar
characters. The
varchar
type is equivalent to a UTF-16 Java
char
code unit.
The Java technology code that uses the above SQL statement to create the
ADDRESS
table looks like the following code. The
dbConnection
is the same as the one shown in the previous code. We simply pass it into
createTables
, create a new
Statement
, and call the
execute
method to run the SQL code on the newly formed database. The
strCreateAddressTable
instance variable holds the SQL statement text.
private boolean createTables(Connection dbConnection) { boolean bCreatedTables = false; Statement statement = null; try { statement = dbConnection.createStatement(); statement.execute(strCreateAddressTable); bCreatedTables = true; } catch (SQLException ex) { ex.printStackTrace(); } return bCreatedTables; } |
At this point, the database and the
ADDRESS
table exist in a subdirectory named
.addressbook/DefaultAddressBook
in your home
directory. Although you can browse this subdirectory, avoid modifying
any files. If you edit or delete any of these database files directly,
you can destroy the integrity of your database.
![]() |
Once the database and its tables have been created,
your application can create new connections and statements to add, edit,
delete, or retrieve records. In Address Book, these actions are
controlled by buttons within the
AddressActionPanel
. Figure 5 shows the available options:
|
The main window of the application is
AddressFrame
, which acts as a controller and as a view at the same time. It registers itself with the
AddressActionPanel
to receive notification when a user clicks anywhere on the action bar.
The New command clears the address entry panel and enables the user to edit all fields. No SQL commands are issued at this point, but the UI should allow you to enter a new address.
The Delete command attempts to delete the currently selected address record.
AddressFrame
retrieves the currently selected
Address
identifier from the
AddressPanel
and uses
AddressDao
to delete the record. The panel calls its own
deleteAddress
method, which calls the DAO's
deleteRecord
method with the correct
ID
. After deleting the record from the database, the application must delete the
ListEntry
from the
AddressListPanel
too.
private void deleteAddress() { int id = addressPanel.getId(); if (id != -1) { db.deleteRecord(id); int selectedIndex = addressListPanel.deleteSelectedEntry(); ... } ... } |
In the
AddressDao
, the
deleteRecord
method handles the actual deletion of the record from the database. The
AddressDao
creates a
PreparedStatement
when it first connects to the database.
stmtDeleteAddress = dbConnection.prepareStatement( "DELETE FROM APP.ADDRESS " + "WHERE ID = ?"); |
The
PreparedStatement
can be used multiple times, and this one uses a parameter to determine which record to delete. The
deleteRecord
method executes the update after setting the
ID
parameter:
public boolean deleteRecord(int id) { boolean bDeleted = false; try { stmtDeleteAddress.clearParameters(); stmtDeleteAddress.setInt(1, id); stmtDeleteAddress.executeUpdate(); bDeleted = true; } catch (SQLException sqle) { sqle.printStackTrace(); } return bDeleted; } |
The Edit command allows the user to edit the currently selected
Address
record in the
AddressPanel
. For example, you can change the name, city, or phone number of a saved record.
The Save command retrieves either the newly created or edited
Address
from the
AddressPanel
and attempts to either update the
existing record or create a new record. If the user has been editing a
record, Save will update that record with the new information. If the
user has created a new record, Save will insert a new record in the
database. New records have not yet been saved. At this point, their
ID
field is still set to the default
-1
value. This value changes to an autogenerated, unique record identifier once you save the record.
The following code in
AddressFrame
will save edited and newly created address records by calling the DAO's
editRecord
or
saveRecord
method, respectively. Of course, when you create a new record, the application must also update the
AddressListPanel
.
private void saveAddress() { if (addressPanel.isEditable()) { Address address = addressPanel.getAddress(); int id = address.getId(); if (id == -1) { id = db.saveRecord(address); address.setId(id); String lname = address.getLastName(); String fname = address.getFirstName(); String mname = address.getMiddleName(); ListEntry entry = new ListEntry(lname, fname, mname, id); addressListPanel.addListEntry(entry); } else { db.editRecord(address); } addressPanel.setEditable(false); } } |
The DAO's
editRecord
method must update the fields that change in the
Address
record. Because the demo application
doesn't distinguish between edited and unedited fields, it simply
updates all fields in the record. Following are the
PreparedStatement
object and the
editRecord
method:
stmtUpdateExistingRecord = dbConnection.prepareStatement( "UPDATE APP.ADDRESS " + "SET LASTNAME = ?, " + " FIRSTNAME = ?, " + " MIDDLENAME = ?, " + " PHONE = ?, " + " EMAIL = ?, " + " ADDRESS1 = ?, " + " ADDRESS2 = ?, " + " CITY = ?, " + " STATE = ?, " + " POSTALCODE = ?, " + " COUNTRY = ? " + "WHERE ID = ?"); ... public boolean editRecord(Address record) { boolean bEdited = false; try { stmtUpdateExistingRecord.clearParameters(); stmtUpdateExistingRecord.setString(1, record.getLastName()); stmtUpdateExistingRecord.setString(2, record.getFirstName()); stmtUpdateExistingRecord.setString(3, record.getMiddleName()); stmtUpdateExistingRecord.setString(4, record.getPhone()); stmtUpdateExistingRecord.setString(5, record.getEmail()); stmtUpdateExistingRecord.setString(6, record.getAddress1()); stmtUpdateExistingRecord.setString(7, record.getAddress2()); stmtUpdateExistingRecord.setString(8, record.getCity()); stmtUpdateExistingRecord.setString(9, record.getState()); stmtUpdateExistingRecord.setString(10, record.getPostalCode()); stmtUpdateExistingRecord.setString(11, record.getCountry()); stmtUpdateExistingRecord.setInt(12, record.getId()); stmtUpdateExistingRecord.executeUpdate(); bEdited = true; } catch(SQLException sqle) { sqle.printStackTrace(); } return bEdited; } |
Saving a new
Address
creates a new database record, and that new record has a new primary key or record identifier. When we create the
PreparedStatement
, we can tell the database that we want to know the generated keys. Notice how we can retrieve a
ResultSet
of generated keys after inserting the record. The
saveRecord
method returns the primary key of the newly created record.
stmtSaveNewRecord = dbConnection.prepareStatement( "INSERT INTO APP.ADDRESS " + " (LASTNAME, FIRSTNAME, MIDDLENAME, " + " PHONE, EMAIL, ADDRESS1, ADDRESS2, " + " CITY, STATE, POSTALCODE, COUNTRY) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS); ... public int saveRecord(Address record) { int id = -1; try { stmtSaveNewRecord.clearParameters(); stmtSaveNewRecord.setString(1, record.getLastName()); stmtSaveNewRecord.setString(2, record.getFirstName()); stmtSaveNewRecord.setString(3, record.getMiddleName()); stmtSaveNewRecord.setString(4, record.getPhone()); stmtSaveNewRecord.setString(5, record.getEmail()); stmtSaveNewRecord.setString(6, record.getAddress1()); stmtSaveNewRecord.setString(7, record.getAddress2()); stmtSaveNewRecord.setString(8, record.getCity()); stmtSaveNewRecord.setString(9, record.getState()); stmtSaveNewRecord.setString(10, record.getPostalCode()); stmtSaveNewRecord.setString(11, record.getCountry()); int rowCount = stmtSaveNewRecord.executeUpdate(); ResultSet results = stmtSaveNewRecord.getGeneratedKeys(); if (results.next()) { id = results.getInt(1); } } catch(SQLException sqle) { sqle.printStackTrace(); } return id; } |
![]() |
Now that you have written the application, you must deploy it to users. Java technology applications can use a variety of deployment strategies, including Java Web Start software, applets, and stand-alone JAR files. I distribute the Address Book application as a stand-alone application with JAR files.
The ANT build file,
build.xml
, uses a
dist
target to create
AddressBook.jar
. It also places the database JAR file in the
lib
subdirectory directly under the
AddressBook.jar
location. The final distribution structure for the application looks like this:
AddressBook.jar lib/derby.jar |
In many cases, applications that use third-party libraries such as
derby.jar
require an external script to run.
The script usually places the third-party JAR files on the classpath and
then executes the application from its JAR. This method is troublesome,
however, because it requires multiple scripts, typically one for each
supported host operating system. For example, to support distribution on
Windows, Solaris, and Linux platforms, I would create a
run.bat
batch file for the Windows platform and a
run.csh
script for a Solaris or Linux platform. Instead, we can avoid these clumsy execution scripts.
If our build process includes classpath information in the
AddressBook.jar
manifest file, you can run the application by simply passing the
AddressBook.jar
file on the execution command
line. On most platforms, you can also just double-click on the JAR file
name in a graphical window. On a command line, you can use this simple
execution command:
java -jar AddressBook.jar |
This simple deployment and execution scenario can be accomplished by creating a
manifest.mf
file that becomes part of the
AddressBook.jar
file. You can include
information in the manifest that tells the Java programming language
interpreter which class contains the main method and what other JAR
files should become part of the classpath. The following manifest does
both, and we can include it when building
AddressBook.jar
.
Manifest-Version: 1.0 Main-Class: com.sun.demo.addressbook.AddressFrame Class-Path: lib/derby.jar |
Once
your build process generates the application distribution structure
shown previously, you can simply distribute this structure as a ZIP
file. Users can simply unzip the file into any location and run the
AddressBook.jar
file. The
AddressBook.jar
file will contain the manifest
file mentioned earlier and will tell your runtime environment what JAR
files should also be on the classpath. Of course, because Java DB is
embedded into this application, it must be able to find
lib/derby.jar
in order to run correctly.
![]() |
Working with Java DB is easy and fun. Java DB makes it possible to create and distribute an embedded database with minimal effort. Just remember a few tips to make your work with Java DB successful:
derby.jar
file in your development
environment's classpath so that your Java technology compiler and
runtime environment can find the libraries to compile and run the
application.
derby.system.home
system property to tell Java DB where to find your database. You can set this property programmatically or on the command line.
derby.jar
file in a
lib
subdirectory immediately below your application's own directory.
derby.jar
to the application classpath by including a
Class-Path
property in your application JAR's manifest file.
![]() |
The terms "Java Virtual Machine" and "JVM" mean a Virtual Machine for the Java platform."
![]() |