Getting Started with Derby

Date: May 11, 2007

Have you ever wanted to take advantage of the benefits of relational database technology on small host machines? Well, now you can. Derby is a Java-based, fully functional, low footprint relational database management system. Derby can be squeezed into the tightest of places, thereby releasing database server technology into the wild. Want to find out more? Stephen Morris helps you get started with Derby.

If Derby were the only product to come out of the open source arena, I think I’d be happy with that! That’s an exaggeration of course, but it points to the importance of Derby. Database technology has traditionally occupied an exalted and expensive position in the world of software. The advent of Derby ends this dominance and opens up the database field to us all. Sun Microsystems has already released its own distribution of Derby called JavaDB. So, you’re likely to be hearing more about Derby as time goes on.

Derby: A Database for All Seasons

In this article, I want to introduce you to Derby and get you started using the product with a simple Java program. Before we get started, let’s get some background information out of the way.

What Is Derby?

Derby is an open source, fully functional relational database management system. It supports many of the features found only in high-end, commercial database engines, such as transactions. Derby itself is written in Java, so it is amenable to the needs of Java programmers. Another important Derby attribute is its really light footprint (about 2MB RAM) that obviates the need for very powerful host machines. Indeed, this light footprint means that we can expect to see Derby programs running on low-end, resource-constrained devices such as PDAs, mobile phones, and so on. This will allow for a whole new generation of sophisticated data-centric applications on such devices.

Derby can comfortably run on a low-end PC and it supports two deployment modes: embedded and network server. In this article, I’ll focus on the embedded option. To be completely accurate, the deployment modes are known as frameworks. The embedded framework is the simplest to use because it features Derby running embedded in a Java application. Much of the associated complexity is then handled by the application because it does the following:

Loads the JDBC driver
Starts the Derby engine
shuts down the Derby engine when the application exits

A limitation of the embedded framework is that only a single application can access the Derby database at any one time. However, for low-end applications, the embedded framework is likely to suit just fine. The server framework, on the other hand, differs because it provides a continuously running server-style Java application that allows multiple, remote JDBC applications to connect to Derby.

Originally, Derby emerged from an IBM database product called Cloudscape and has since been open sourced by IBM.

How to Use Derby

The novice Derby user might be a little confused by all the documentation that comes with the product. This wealth of detail somewhat belies the fact that Derby is an open source product! However, Derby also comes complete with fully worked examples that describe how to use the product.

Getting Started

The first step is to download and install a copy of Derby. Next, open a command prompt and change the directory to the folder demo\simple underneath your Derby install folder. On my PC, the full path is this:

C:\java\db-derby-10.1.2.1-bin\demo\simple

In the remainder of the article, we’ll be using the Java program called SimpleApp.java contained in this folder. There are just a couple of steps required to get this program up and running. These steps are the usual pesky CLASSPATH things that go with using Java. So, let’s get these out of the way.

Setting Up Your CLASSPATH

The file derby.jar must be on your CLASSPATH. The following command added it on my system (from within the "simple" folder described above):

set CLASSPATH=.;C:\java\db-derby-10.1.2.1-bin\lib\derby.jar;%CLASSPATH%

Notice that I added a period at the beginning of the command in order to pick up the compiled version of SimpleApp.java—SimpleApp.class. The only part of the above command that might differ on your system is the part left of the "lib" word.

Bitter experience has taught me that the simplest way to install Java software is to use only short paths. That is, don’t use paths that default to your Windows Program Files folder because you often then have to add quotes to the elements of the path to get them to work. This type of unproductive work always makes my head hurt, so I prefer to opt for simple path names with no embedded spaces as above.

Testing Your Setup

The Derby distribution includes a handy tool to verify that your CLASSPATH is correctly configured. This command is the following:

java org.apache.derby.tools.sysinfo -cp embedded SimpleApp.class

If your setup is in order, you should see something like the program output in Listing 1.

Listing 1 All’s well with your Derby setup

FOUND IN CLASS PATH:
  Derby embedded engine library (derby.jar)
  user-specified class (SimpleApp)
SUCCESS: All Derby related classes found in class path.

If your setup is incomplete, you might see something like Listing 2:

Listing 2 Problems, problems

FOUND IN CLASS PATH:
  Derby embedded engine library (derby.jar)
NOT FOUND IN CLASS PATH:
  user-specified class (SimpleApp)
  (SimpleApp not found.)

Hopefully, your installation is properly configured, and you’re now ready to run the example application.

Running Your First Derby Program

To run the SimpleApp.java program, type the following commands:

javac SimpleApp.java
java SimpleApp

If all is well, you should see something like that illustrated in Listing 3.

Listing 3 SimpleApp.java application output

SimpleApp starting in embedded mode.
Loaded the appropriate driver.
Connected to and created database TestDB
Created table derbyDB
Inserted 1956 Webster
Inserted 1910 Union
Updated 1956 Webster to 180 Grand
Updated 180 Grand to 300 Lakeshore
Verified the rows
Dropped table derbyDB
Closed result set and statement
Committed transaction and closed connection
Database shut down normally
SimpleApp finished

If you made it this far, you successfully completed your first Derby program. The first time the program is run, the database is created, and you’ll notice a new folder called TestDB appearing in the same folder in which the program runs. TestDB contains the Derby database tables and represents the persistent data from the program. So, any data created by the program and stored in the database will find its way into the TestDB files.

In the next section, we’ll look at the elements that make up the SimpleApp.java program.

Derby Application Components

To make it a little easier to understand the code, I divided it up into the following sections:

Startup
Loading the Derby driver
Database and connection creation
Statement creation
Table creation
Table access
Shutdown

Sounds complicated, doesn’t it? Well, it’s not! Let’s get started.

Derby Application Startup

Listing 4 illustrates the command line argument parsing procedure.

Listing 4 Starting up the program

private void parseArguments(String[] args)
{
  int length = args.length;
  for (int index = 0; index < length; index++)
  {
    if (args[index].equalsIgnoreCase("jccjdbcclient"))
    {
      framework = "jccjdbc";
      driver = "com.ibm.db2.jcc.DB2Driver";
      protocol = "jdbc:derby:net://localhost:1527/";
    }
    if (args[index].equalsIgnoreCase("derbyclient"))
    {
      framework = "derbyclient";
      driver = "org.apache.derby.jdbc.ClientDriver";
      protocol = "jdbc:derby://localhost:1527/";
    }
  }
}

The program command line is java SimpleApp, so the above for loop doesn’t execute because the length is zero. This means that the variables: framework, driver, and protocol are all set to default values. These values are set in the public members, as illustrated in Listing 5.

Listing 5 Public member data

public String framework = "embedded";
public String driver = "org.apache.derby.jdbc.EmbeddedDriver";
public String protocol = "jdbc:derby:";

This means that the code in Listing 4 is bypassed. If you prefer, you can supply command-line parameters, but this is an optional step to make it easier to run the example code.

Loading the Derby Driver

Now that the SimpleApp object members have been set, it’s time to load the Derby driver, which is achieved using the code in Listing 6.

Listing 6 Loading the Derby driver

Class.forName(driver).newInstance();

For the code in Listing 6 to work, the Derby JAR file must be on the CLASSPATH. This was done as part of the post-install configuration.

Database and Connection Creation

At this stage, the Derby driver should be loaded. We can now create the database and make a connection to it. It can be done in one step as illustrated in Listing 7.

Listing 7 Database and connection creation

Connection conn = null;
Properties props = new Properties();
props.put("user", "user1");
props.put("password", "user1");
conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);

In Listing 7, a Connection object is created and set to null. Next, a Properties object is created to store a user account for database access. Then, the database called dbName is created based on the user credentials contained in the props object. After this code executes, you should see the appearance of the database files in the folder indicated by the value of dbName; that is, TestDB.

Statement Creation

Now that we have a brand new database, we can start to manipulate it. Before doing this, an object of the class Statement must be instantiated, as illustrated in Listing 8.

Listing 8 Making a statement

Statement s = conn.createStatement();

Notice that the Statement object in Listing 8 uses the Connection object. In a sense, the Statement object talks to the Connection. The Connection object, in turn, talks to the Derby database.

Table Creation

Before we can manipulate some database data, we must create and populate some tables. Table creation is illustrated in Listing 9.

Listing 9 Table creation

s.execute("create table derbyDB(num int, addr varchar(40))");

Listing 9 illustrates the creation of a table called derbyDB. This table has two columns: an integer column and a variable character column. The latter can store strings of up to 40 characters in length. Let’s now get some data into the table.

Table Access

Listing 10 populates the derbyDB table with two rows.

Listing 10 Table population

s.execute("insert into derbyDB values (1956,’Webster St.’)");
s.execute("insert into derbyDB values (1910,’Union St.’)");

Listing 11 retrieves the inserted data from the derbyDB table and creates an object of the ResultSet class. If you examine Listing 11, you’ll notice that it contains an embedded SQL command. The result of the SQL command is to retrieve all instances of num and addr columns from the derbyDB table and order the results by num.

Listing 11 Table data retrieval into a ResultSet object

ResultSet rs = s.executeQuery("SELECT num, addr FROM derbyDB ORDER BY num");

One way to think about the ResultSet object is as a cursor—the entity that stores the data retrieved from the database. So, you can iterate through the cursor, displaying the data as required, as illustrated in Listing 12.

Listing 12 Extracting data from a cursor

while (rs.next())
{
  System.out.println("Cursor value " + rs.getInt(1) + " " + rs.getString("addr"));
}

The code in Listing 12 produces output similar to that in Listing 13.

Listing 13 Iterating through a ResultSet object

Cursor value: 300 Lakeshore Ave.
Cursor value: 1910 Union St.

So, it’s a pretty straightforward process to get data into and then out of a Derby database. Shutting down the database is also easy enough.

Shutdown

Listing 14 illustrates the steps required to release all the resources allocated during the Derby program. I removed the exception handling code just to show the bare minimum steps. The SimpleApp.java program includes all the necessary exception handling.

Listing 14 Closing up shop with Derby

rs.close();
s.close();
System.out.println("Closed result set and statement");
conn.commit();
conn.close();
DriverManager.getConnection("jdbc:derby:;shutdown=true");

In Listing 14, the ResultSet object and the Statement object are both explicitly closed. The Connection object is committed and then explicitly closed. Finally, Derby itself is shut down after which the program exits.

Running the Code

I haven’t included the program SimpleApp.java with this article because you need the Derby distribution to run the program and the latter is included as part of the distribution. So, if you download Derby, the steps described here should still apply to the accompanying example programs.

Conclusion

I really like Derby! It’s a no-nonsense piece of software that requires no particularly complex setup. It also requires no associated frameworks. It just works. I’m actually using Derby in one of my company’s products due for release in the next few weeks. So, I really do believe in Derby!

Derby has a very light footprint, which facilitates its use in low-end software entities. There’s no need for top-of-the range servers. Derby runs happily on entry-level PCs.

The workflows required for writing Derby programs are also fairly straightforward. Just a few steps are needed to get your code up and running. I can’t recommend Derby highly enough! Try it and see what you think.