Apache Derby, an Apache DB subproject, is a relational database implemented entirely in Java and available under the Apache License, Version 2.0.

Some key advantages include:

• Derby has a small footprint -- about 2 megabytes for the base engine and embedded JDBC driver.

• Derby is based on the Java, JDBC, and SQL standards.

• Derby provides an embedded JDBC driver that lets you embed Derby in any Java-based solution.

• Derby also supports the more familiar client/server mode with the Derby Network Client JDBC driver and Derby Network Server.

• Derby is easy to install, deploy, and use.

The /frameworks/embedded/bin directory contains scripts for running some of the Derby tools and utilities in embedded mode. You can find similar scripts for running tools and utilities for the Network Server in the /frameworks/NetworkServer/bin directory.

Installation

1. Download the latest release ( the bin distribution )

2. Extract to a folder ( e.g C:\jdbc\derby )

3. Configure Embedded Derby by adding these to the CLASSPATH  C:\jdbc\derby\bin\lib\derby.jar; C:\jdbc\derby\bin\lib\derbytools.jar;

Using sysinfo

Derby's sysinfo tool displays information about your Java environment and your version of Derby. The sysinfo script sets the appropriate environment variables, including the classpath, and executes the sysinfo program. Once you have the /bin directory in your PATH, run sysinfo by entering the following in a command window: sysinfo
 

Running ij

ij is an interactive SQL scripting tool that comes with Derby. You can use the ij tool to connect to a Derby database. You must include the /bin directory in your PATH environment variable to run ij ( eg C:\jdbc\derby\bin\frameworks\embedded\bin; )

• You can run ij by typing the following command:
  ij

The ij script executes the ij program and sets up environment variables like CLASSPATH.

• To create a database with ij, type the following command:
  ij> connect 'jdbc:derby:testdb;create=true';

This command creates a database called testdb in the current directory, populates the system tables, and connects to the database. You can then execute any SQL statements from the ij command line.

• Connect to a database
  ij> connect 'jdbc:derby:testdb';

• ij expects each statement to be terminated with a semicolon (;)

  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;

   

• Run SQL Scripts

ij> run 'my_file.sql';

The DB will be stored in the folder that you started the ij from.

• Disconnect from a database

Command disconnects from the current database:

ij> disconnect;

• EXIT

Quits out of ij and, in embedded mode, shuts down the Derby database:
ij> exit;

Environments/Frameworks in which Derby can run

(1) Embedded environment

An environment in which only a single application can access a database at one time, and no network access occurs. When an application starts an instance of Derby within its JVM, the application runs in an embedded environment. Loading the embedded driver starts Derby.

Driver : org.apache.derby.jdbc.EmbeddedDriver 

(2) Client/server environment
 

An environment in which multiple applications connect to Derby over the network. These applications run in a client/server environment. Derby runs embedded in a server framework that allows multiple network connections. (The framework itself starts an instance of Derby and runs in an embedded environment. However, the client applications do not run in the embedded environment). You can also embed Derby in any Java server framework.

Driver : org.apache.derby.jdbc.ClientDriver

Database connection URL

(1) For the Derby-provided embedded driver, the format for the database connection URL for connecting to a database is this:
jdbc:derby:databaseName;URLAttributes

• databaseName :The name of the database that you want to connect to

• URLAttributes : One or more of the supported attributes of the database connection URL, such as ;locale=ll_CC or ;create=true.

(2) For the Derby-provided network client driver, the format for the database connection URL for connecting to a database is this:
jdbc:derby://<server>[:<port>]/databaseName[;URLAttributes=<value> [;...]]

where the <server> and <port> specify the host name (or IP address) and port number where the server is listening for requests. The URLAttributes can be either Derby embedded or network client attributes.