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.

Let’s create a table and add some records to it:

CREATE TABLE EMP
(
  EMPNO     INT,
  ENAME     VARCHAR(10),
  JOB       VARCHAR(9),
  MGR       INT,
  HIREDATE  DATE,
  SAL       NUMERIC(7,2),
  COMM      NUMERIC(7,2),
  DEPTNO    NUMERIC(2)
);

CREATE UNIQUE INDEX PK_EMP ON EMP (EMPNO);

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7839, 'KING', 'PRESIDENT', NULL, '11/17/1981', 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7698, 'BLAKE', 'MANAGER', 7839, '05/01/1981', 2850, NULL, 30);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7782, 'CLARK', 'MANAGER', 7839, '06/09/1981', 2450, NULL, 10);
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) VALUES ( 7566, 'JONES', 'MANAGER', 7839, '04/02/1981', 2975, NULL, 20);

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:

java -jar %JAVA_HOME%\db\lib\derbynet.jar start

You will see a message that Derby is started:

Apache Derby Network Server - 10.2.1.7 - (453926) started and ready to accept connections on port 1527 at 2007-05-22 09:29:29.225 GMT

/>
To shutdown the database:

java -jar %JAVA_HOME%\db\lib\derbynet.jar shutdown

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

<dependency>
    <groupId>org.apache.derby</groupId>
    <artifactId>derbyclient</artifactId>
    <version>10.2.2.0</version>
</dependency>

Maybe it is possible to use a dependency from Sun, but this way I’m sure I have an up to date version of the client.

I created a simple Spring project to run our select * from emp test from a Java environment. I added two beans to my Spring config file:

    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource"
          destroy-method="close">
        <property name="driverClassName" value="org.apache.derby.jdbc.ClientDriver"/>
        <property name="url" value="jdbc:derby://localhost:1527/firstdb"/>
    </bean>

    <bean id="testDao" class="nl.amis.TestDao">
        <property name="dataSource" ref="dataSource"/>
    </bean>

Then a simple DAO to get some records and print them to the debug console:

package nl.amis;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import java.util.List;

public class TestDao extends NamedParameterJdbcDaoSupport {
    private static final Logger log = Logger.getLogger(TestDao.class);

    public void getEmp() {
        JdbcTemplate template = getJdbcTemplate();

        List list = template.queryForList("SELECT * FROM EMP");

        log.debug(list);
    }
}

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.

Sources

http://db.apache.org/derby/docs/dev/workingwithderby/workingwithderby-single.html
http://db.apache.org/derby/docs/10.2/workingwithderby/
http://db.apache.org/derby/derby_charter.html
http://rgarg.blogspot.com/2007/05/java-6-feature-working-with-derby.html