Java DB - Getting Started

About

First steps with Java DB and the ij client tool.

Steps

Installation

There is two installation option:

• Derby may have been already installed with the Jdk
• or you need to download it

subdirectory of the JDK installation

Java DB/Derby may be installed automatically as part of the Java SE Development Kit (JDK).

Java DB has been available as part of the JDK since JDK 6.

Java DB can be found in the db subdirectory of the JDK installation.

The distribution contains scripts and libraries. The installation contains the following subdirectories:

• The bin subdirectory contains the scripts for executing utilities and setting up the environment.
• The lib subdirectory contains the Java DB jar files.

Set the derby_home environment variable.

Download and Unzip

If Derby is not installed

• Download the bin version at the derby download page
• And unzip it

For instance, for Java8

unzip db-derby-10.14.2.0-bin.zip
cd C:\db-derby-10.14.2.0-bin
λ ls -A1

bin/                         
demo/                        
docs/                        
index.html                   
javadoc/                     
KEYS                         
lib/                         
LICENSE                      
NOTICE                       
RELEASE-NOTES.html           
test/                        

Environment variable Configuration

To be able to use shortened commands to start the Derby tools:

• set the DERBY_HOME environment variable
• and add the bin directory to the PATH environment variable.

DERBY_HOME

The DERBY_HOME Environment variable is a variable that store the location of the root Derby directory.

• With a SDK installation, the DERBY_HOME environment variable is JAVA_HOME/db.

SET DERBY_HOME=C:\Program Files\Java\jdk1.7.0\db

• With a download installation, the DERBY_HOME environment variable is where you have unzipped the

SET DERBY_HOME=C:\db-derby-10.14.2.0-bin

Verification In the bin directory, you should have the ij tool

cd %DERBY_HOME%%\bin
ls -A1

dblook*                   
dblook.bat                
derby_common.bat          
ij*                       
ij.bat                    
NetworkServerControl*     
NetworkServerControl.bat  
setEmbeddedCP*            
setEmbeddedCP.bat         
setNetworkClientCP*       
setNetworkClientCP.bat    
setNetworkServerCP*       
setNetworkServerCP.bat    
startNetworkServer*       
startNetworkServer.bat    
stopNetworkServer*        
stopNetworkServer.bat     
sysinfo*                  
sysinfo.bat               

PATH

Add the DERBY_HOME/bin directory to the PATH environment variable so that you can run the Derby scripts from any directory.

CLASSPATH

In most development environments, it is best to temporarily set the CLASSPATH environment variable in the command line shell.

Derby provides several scripts in the DERBY_HOME/bin directory to help you set your classpath quickly. These scripts are:

• setEmbeddedCP. To set the classpath when the database engine is used in embedded mode. This script adds the derby.jar and derbytools.jar files to the classpath.
• setNetworkServerCP To set the classpath when you want to start the network server. This script adds the derbynet.jar file to the classpath.
• setNetworkClientCP To set the classpath when you want to access databases using the network client. This script adds the derbyclient.jar and derbytools.jar files to the classpath.

If you want to call the Derby tools directly using Java and not using the scripts, you must manually set the CLASSPATH environment variable.

Creation of the database

Call of the ij tool

%DERBY_HOME%\bin\ij

ij version 10.8
ij>

Creation of the database on the first connection with the embedded driver.

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

where:

• connect is the ij command to establish a connection to a database.
• firstdb is the name of the database. The name can be any string. Because no filepath is specified, the database is created in the default working directory.
• create=true is a Derby URL attribute that is used to create a database. Derby does not have an SQL create database command.

Creation of a table

CREATE TABLE FIRSTTABLE
    (ID INT PRIMARY KEY,
    NAME VARCHAR(12));

Insert

INSERT INTO FIRSTTABLE VALUES (10,'TEN'),(20,'TWENTY'),(30,'THIRTY');

Select

ij> SELECT * FROM FIRSTTABLE;

ID         |NAME
------------------------
10         |TEN
20         |TWENTY
30         |THIRTY

Running a script

run 'mypath/to/the/script.sql';

Exit

exit;

Impact on the File System

derby.log

The derby.log file is a message and error log that, under normal circumstances, contains a set of startup messages and a shutdown message.

database directory

Under the current directory you will find a directory with the name of the database. (firstdb). Within the directory are the subdirectories:

• seg0 (containing the data files)
• and log (containing the transaction log files).

Documentation / Reference

• Creating a Derby database and running SQL statements