Tutorial

http://db.apache.org/derby/

Download

Download db-derby-10.2.2.0-bin.zip. I extracted to:

C:\tools\db-derby-10.2.2.0-bin\

Tutorial

http://db.apache.org/derby/papers/DerbyTut/index.html

To test the installation, and extract:

cd db-derby-10.2.2.0-bin
# windows
java -cp lib\derby.jar;lib\derbytools.jar org.apache.derby.tools.sysinfo
# linux
java -cp lib/derby.jar:lib/derbytools.jar org.apache.derby.tools.sysinfo

Or set the CLASSPATH environment variable:

# windows
SET CLASSPATH=C:\tools\db-derby-10.2.2.0-bin\lib\derby.jar;C:\tools\db-derby-10.2.2.0-bin\lib\derbytools.jar
# linux (fish shell)
set -x CLASSPATH "/home/patrick/dev/learn/4588-derby-george/db-derby-10.15.1.3-lib/lib/derby.jar:/home/patrick/dev/learn/4588-derby-george/db-derby-10.15.1.3-lib/lib/derbytools.jar"
#
java org.apache.derby.tools.sysinfo

ij

ij is an interactive SQL scripting tool that comes with Derby.

In this example we want to create a test database in the TEMP folder. We will start ij after moving to our folder and setting the CLASSPATH:

java org.apache.derby.tools.ij

This will give you the ij version followed by the prompt:

ij version 10.1
ij>

Create a database

Start ij (see above).

To create a database, specify the create=true attribute in the connection URL. For example, the command below creates a new database called MyDbTest:

ij> connect 'jdbc:derby:MyDbTest;create=true';
ij> connect 'jdbc:derby:POS';

Note

A Derby Database directory contains log, seg0, tmp directories and a service.properties file.

Quit

Now quit out of ij:

ij> exit;

The exit command quits out of ij and, in embedded mode, shuts down the Derby database.

Connect to a database

Start ij (see above).

Connect to the database you just created:

ij> connect 'jdbc:derby:MyDbTest';

This connect command takes two parameters:

Parameter	Name	Description
`jdbc:derby`	Protocol	Driver to load. In this case, it knows to load the embedded JDBC driver
`MyDbTest`	Database	In the connection URL above works because the `MyDbTest` database directory is in the current working directory.

Let’s say that your current directory location is /home/bill/databases and that you decide to change your directory to a different place entirely. You can connect to the MyDbTest database by specifying the complete directory path, like this:

ij> connect 'jdbc:derby:/home/bill/databases/MyDbTest';

The protocol can also be specified with a property as shown below:

java -Dij.protocol=jdbc:derby: org.apache.derby.tools.ij
ij> connect 'MyDbTest';

You could also specify the Derby system home for the database like this:

java org.apache.derby.tools.ij -Dderby.system.home=/home/bill/databases
ij> connect 'jdbc:derby:MyDbTest';

Execute SQL statements

Once you connect to a database, you can execute SQL statements. ij expects each statement to be terminated with a semicolon; for example:

ij> create table derbyDB(num int, addr varchar(40));
ij> insert into derbyDB values (1956,'Webster St.');
ij> insert into derbyDB values (1910,'Union St.');
ij> update derbyDB set num=180, addr='Grand Ave.' where num=1956;
ij> select * from derbyDb;

Disconnect from a database

The disconnect command disconnects from the current database:

ij> disconnect;

Run SQL Scripts

You can execute SQL scripts in ij as shown below:

ij> run 'my_file.sql';

You can also run SQL scripts from the command line:

java org.apache.derby.tools.ij my_file.sql