organic thoughts

I have been using Apache Derby for a while. It was a delight to see that Sun has opted to include the little db engine in the JDK starting with version 6. Now, you have a robust embeddable db engine at your disposal whenever you are developing. This blog is a quick intro to Derby (by no means exhaustive). You can view a google.doc version here for printing.

What is Java DB (Apache Derby)

Apache Derby is a secure, fully-transactional, zero-admin, embeddable, 100% pure Java database managed by Apache. With the release of Java 6, Sun now distributes Derby under the moniker Java DB. When you hear Java DB from Sun, they are talking about version 10.2 of Apache Derby. Now that it is included in the JDK, you will get Derby every time you download the JDK. If you find the 2 MB too hefty, you still have many options including the other popular embeddable db HSQLDB.

Apache Derby comes with several jar files that let you create and manage your database.

- derby.jar: the derby engine

- derbynet.jar: files to run derby as network server

- derbyrun.jar: provides shortcuts to run the Derby tools

- derbytools.jar: contains various Derby tools including a interactive command-line db manager.

- derbyclient.jar: JDBC driver to connect to a standalone networked Derby server.

By default, when you create a database, Derby creates a directory and files where your data is stored. That location is used as part of your database URL when specifying JDBC parameters. You can further customize your Derby database by providing a derby.properties file which lets specify numerous settings including security, repository location, etc.

For more details about how to setup and configure Derby (Java DB) see documentation.

Using Derby

Once you have placed derby's jars in you CLASSPATH, you can be used it in two modes. You can embed Derby where it runs in the same VM as the client application. Derby can also run in a standalone network mode where it can be used in the traditional client/server mode. Each mode uses a different driver and the database engine has slight differences in behavior that you should be aware of.

Derby Database URL

Regardless of how you plan to use Derby, you should understand how the URL works. Since Derby's data repository is file-based, when specifying the database URL, you actually point to a directory where the database files are saved.

Here are some examples of Derby URL

1. jdbc:derby:MyDatabase

The database is in a directory called MyDatabase which is relative to the system directory (main location where db is setup).

2. jdbc:derby:/user/db/MyDatabase

This URL points directly to a location that can be anywhere on the file system.

Creating a Derby Database

Derby's API does not provide a well-defined componentized interface to create a database. So, you must rely on a call to a connection object to create the database as follows:

DriverManager.getConnection("jdbc:derby:databaseName;create=true");

This call causes Derby to create the database (at the location specified by the databaseName).

Embedding Derby

When using Derby as an embedded db, the database runs within the same VM instance the client application. By default, an embedded Derby instance starts upon the first connection requests.

It runs until it's explicitly shutdown or when the VM shuts down.

The embedded driver can be loaded using the pre-java6 approach of using the Class.forName

Class.forName("org.apache.derby.jdbc.EmbeddedDriver");

NOTE: Starting with Java6, it's not necessary to explicitly load the database driver classes using this method.

Shutting Down an Embedded Derby Instance

When using Derby in embedded mode, the API does not offer a programmatic way to shutdown the database. So, to shutdown Derby, you must use the connection object to initiate the shutdown sequence:

DriverManager.getConnection("jdbc:derby:databaseName;shutdown=true");

Once the db is shutdown in memory, there's really no straight forward way of restarting it. So, once the db is running, don't stop it until the application is shutting down.

Double-booting the Database System

You should be aware that Derby does not allow multiple VM to boot a database while running in embedded mode. When your application starts and boot the database system, no other VM can be client to that database. It will throw an error if any another application attempts to connect to the system. Recall that by default Derby boots up upon the first connection to the database system.

The double-booting issue can be annoying in during development where you may want your IDE's SQL explorer connect to a database that has been booted by your application. One solution is to use Derby as a networked database server (see below). This mode allows you to have multiple connections from any VM’s.

Derby Standalone Network Mode

When using Derby as a networked server, it can accept connections from any number of VM's.

The networked server can be started using the command-line tools that come with Derby as follows:

java -jar derbyrun.jar server start

You will see

Apache Derby Network Server - 10.2.1.6 - (452058) started and ready to accept connections on port 1527 at 2007-04-11 19:51:49.997 GMT

The server will use localhost port 1527 by default. However you can select the address/port to bind to using command-line arguments.

NOTE: You can also start Derby programmatically (see future blog entry) where you can use Derby API to start the database system (not covered here).

Connecting to a Networked Derby Server

Connecting to the Derby server requires the use of a different db driver from that of the embedded driver:

Class.forName("org.apache.derby.jdbc.ClientDriver")

The JDBC connection URL for networked db server is slightly different. In addition to the database name (directory location, Derby requires that the server's network address/port precedes the db name:

DriverManager.getConnection("jdbc:derby://localhost:1527/databaseName");

Once Derby is running in network mode, it behaves just like any other database system.

In this mode, you do not have the issue of double-booting (see above). Any number of VM's (local or remote) can connect to your database. This is my preferred mode for development since I can use the IDE to connect to the database while the application is running with no problem.

Shutting Down A Networked DB System

In network mode, Derby uses a command issued from the command line (or programmatically) to shutdown the server. To shutdown a running server using the command-line tools use:

java -jar derbyrun.jar server shutdown

You should see this on the screen:

Apache Derby Network Server - 10.2.1.6 - (452058) shutdown at 2007-04-11 20:27:06.172 GMT

Conclusion

Hope this will get you started with Apache Derby. If you can look past its 2 MB size, Derby is a great addition to Java and should be considered for your next development effort. Derby can be embedded in your application or be used as a robust departmental client/server database with all the features expected from a first-class RDBMS.

References

1. Full article in Google Doc
   http://docs.google.com/Doc?id=df3qdpw5_21cf9x38
   

2. Sun's Developer Network Java DB Home Page
   http://developers.sun.com/javadb/

3. Using Java DB in Swing Application
   http://java.sun.com/developer/technicalArticles/J2SE/Desktop/javadb/

4. Apache Derby's Home Page
   http://db.apache.org/derby/

5. Apache Derby Tutorial
   http://db.apache.org/derby/papers/DerbyTut/index.html

6. Apache Derby Documentation
   http://db.apache.org/derby/manuals/index.html