Derby

From Knowitall
Jump to: navigation, search

Introduction

Derby is a SQL database implementation for Java. Derby can be run using an "Embedded Client" or a "Network Client". With the embedded client, you can only have one connection to the database at a time. With the network client, you can have many connections open to the database at once.

Installing Derby

Download Derby from here.

Add the following to your ~/.bash_profile:
export DERBY_HOME=/path/to/derby/db-derby-10.9.1.0-bin (You may have to change the version number.)
export PATH=$PATH:$DERBY_HOME/bin

IJ

You can inspect a Derby DB using the ij command-line utility that comes with it. You can create a Derby DB by typing:
ij
connect 'jdbc:derby:mydb; create=true'; (Omit create=true if you're connecting to an already existing DB.)
create table mytable ...

Derby creates the database in a directory named mydb in the current directory.

You can connect to any database on the filesystem:
connect 'jdbc:derby:/path/to/mydb';

If you are using the network server, then the connection URL is different:
connect 'jdbc:derby://localhost:1527//path/to/mydb';

String fields are delimited using single quotes:
select * from crosswikis where anchor='billclinton' fetch first 10 rows only;

Bulk import

There are some docs on how to bulk import here. The main trick is that string fields must be delimited with some character. If that character appears in your string fields, then you have to double-quote those.

Some scripts for bulk-importing a tab-separated file can be found here.

Programmatic access

An example is CrosswikisHandler in the openie-backend project. The Derby documentation also provides examples.

Network server

To make your database available to multiple clients simultaneously, go to $DERBY_HOME/lib and run java -jar derbyrun.jar server start. This starts a long-running process, so you may want to start it in screen first. To stop it, you can just kill it or run java -jar derbyrun.jar server shutdown.