Using Java DB in Desktop Applications

Articles Index

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

Figure 1: Address Book uses Java DB as an embedded database.

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:

• The 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.
  
• The 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.
  
• The 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.
  
• The docs subdirectory contains documentation about the Java DB product itself: setup, administrator, and reference guides.
  
• Finally, the 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:

1. From the Tools menu, select Library Manager, as shown in Figure 2.
   
   

   Figure 2: The library manager lets you add third-party libraries to your project.

   
   
2. In the Library Manager window, create a new library named JavaDBEmbedded, as shown in Figure 3. Click OK.
   
   

   Figure 3: Name the set of libraries that your project will need.

   
   
3. To add the 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.
   
   

   Figure 4: Add the derby.jar file to the JavaDBEmbedded library.

   
   
4. In the same Library Manager window for the JavaDBEmbedded library, select the Javadoc tab. Add the 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:

• New. Create a new address record.
  
• Delete. Delete the displayed address record.
  
• Edit. Edit the displayed address record.
  
• Save. Save the new or edited address record that is displayed.
  
• Cancel. Cancel any edits or any attempt to create a new record.

Figure 5: Address Book has several options for interacting with records.

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:

1. Place the 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.
   
2. Set the 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.
   
3. Create a build process that places the derby.jar file in a lib subdirectory immediately below your application's own directory.
   
4. Add 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."

• Download the Address Book demo
  
• Java DB download site
  
• Using Derby in a client-server environment
  
• Download the NetBeans IDE
  
• Apache Derby Project