About
First steps with Java DB and the ij client tool.
The semicolon (;) is the ij command terminator.
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));
The default schema is the user app
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).