---

Apache Derby, also known as IBM Cloudscape or Java DB (the different names are evidence of a long and complex history…) is an open source relational database written entirely in Java. It offers a high-degree of SQL standards compliance, speaks native JDBC and is available in client/server or embedded mode. It has support for transactions, stored procedures, database triggers and other high-end features. All of this in a package that has a small footprint (the jar file comes in at around 2MB), and is incredibly easy to use.

As mentioned, Derby can be used either in client/server mode or as a data store embedded in a Java application. In client/server mode it functions pretty much as your would expect any relational database management system (RDBMS). It functions as a server listening on a TCP/IP socket for connections from a client (or clients). In embedded mode it is the application that is responsible for loading Derby, initialising the database and handling interactions and shut-down.

Installation of Derby is as simple as downloading the zip file from Apache and dropping the appropriate jar files into your project directories (or pointing Eclipse, NetBeans or other IDE at them). From there on it's just a case of using standard JDBC code to register the database driver, create a statement object and then interact with the database.

For standalone database creation, population, debugging or testing there is also a command-line interface available as standard. The ij tool presents a command-line prompt to a Derby database instance, and it can be used to create a database, connect to an existing database, issue SQL commands to create tables, populate data, perform queries and so on. Setting up ij is as simple as setting a couple of environment variables and running the ij batch file or shell script. It's also possible to create text files containing SQL commands and then loading them into ij for execution.

However it's used, ij makes it simple to create and test databases interactive rather than depending on writing Java code in your application. An alternative is to use a standalone SQL client, such as the excellent SQuirreL SQL client (also written in Java), which in this case comes with built-in support for Derby.

It's also worth mentioning that there is a high-level of support for Derby in other tools, IDEs and libraries. For example Derby is one of the dialects supported by Hibernate 3.0. This trend is likely to grow once Derby, in the guise of Java DB, ships with the Java 6.0 release from Sun.

To conclude, Derby is both easy to use but also incredibly powerful. For applications looking for a persistence layer Derby makes for a low cost and flexible solution. Highly recommended.