Getting started with Derby – The java database bundled with Java 6

I often need a database to perform some quick tests. I usually start my Oracle instance. This takes some time and for most things it’s overkill. So a database that starts up quickly and is easy to use and install was on my wish list. Derby is very easy to install and because it’ss bundled with Java 6 you don’t even have to download or install it (not that downloading 2 Mb is such a bad thing by the way). My main goal is to get you up and running with Derby in no-time. There are enough reviews of Derby and strangely enough it is also documented pretty well.

Derby was named Cloudscape and JBMS, so that explains why there is a version 10 and you never heard of Derby before. Derby is bundled with JDK 1.6.0. You can find the files that make up Derby in the \db\lib directory of your JDK.

Today I will show how to run Derby as a network server. It is possible to run an embedded version of Derby, but for most web projects I prefer to keep my database and code separated. The purpose of using Derby for me is a temporary replacement for a â€˜normalâ€™ database like Oracle or MySQL.

Creating the database

The databse consists of a directory with some files in it. Go to a directory on your file system where you want the database to be created and perform the following command:

java -jar %JAVA_HOME%\db\lib\derbyrun.jar ij

ij is the command line tool for your database. I couldnâ€™t find out how they came up with the name of this tool, probably just two random characters. Create and connect to the database on the command line:

CONNECT 'jdbc:derby:firstdb;create=true';

Derby does not have a CREATE DATABASE command, this is the only way to create a database.

This is just a simple table as an example. Derby is a full blown database with support for views, triggers, stored procedures and even an XML data type.

You can use the ij tool to see the results of a select * from emp; But you probably believe me when I say that youâ€™ll get some results. Letâ€™s run Derby in network mode and hook it up via JDBC to a new application.

Starting and stopping the network server

Go to the directory where your database is created and execute the following command:

The shutdown command can be performed in any directory. The JDBC url of this database is jdbc:derby://localhost:1527/firstdb

Connecting via JDBC

The driver needed to connect to Derby via JDBC is org.apache.derby.jdbc.ClientDriver. This driver can be found in the derbyclient.jar file (also located in the db\lib dir of your Java 6 JDK). Since I do everything with Maven2 my dependency is

Of course you can use plain JDBC to connect to the database, for me Spring is the fastest way to connect to a database and print formatted debug statements (instead of something like ResultSet@2535).

Conclusion

Derby is around for a while and I did a Hibernate course with it (but then everything was set up for me and I couldnâ€™t imagine how easy it was to install and run Derby). But I shouldâ€™ve worked with it a lot earlier. Derby isnâ€™t some kind of â€˜stupidâ€™ database where you are limited in it’s usage. I was surprised Derby supports the XML Data Type. Before I started this article I thought of Derby as a temporary replacement for a normal database. But Derby IS a normal database and Iâ€™m going to use it for my home brewn projects in the near future.

meta

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 236 other subscribers

Email Address

About

AMIS is internationally recognized for its deep technological insight in Oracle technology. This knowledge is reflected in the presentations we deliver at international conferences such as Oracle OpenWorld, Hotsos and many user conferences around the world. Our AMIS Technology Blog, the most referred Oracle technology knowledge base outside the oracle.com domain. However you arrived here, we appreciate your interest in AMIS. Link to our Google+ Profile AMIS