Java DB (Derby)

Manuals and Tutorials:

Official Apache Derby Tutorial: a great tutorial for a quick start. This tutorial uses the sample application called "SimpleApp.java" included with the Apache Derby installation at DERBY_INSTALL/demo/programs/simple/;
Java DB Reference: the main official repository of articles, manuals and tutorials for Java DB (Apache Derby). Some useful links are: Java DB Technical Documentation; Getting Started with Java DB (great introduction with sample tutorial);
Using Java DB in Desktop Applications: This article describes how to download, install, integrate, and deploy Java DB within desktop Java technology applications. A demo application called Address Book demonstrates how to work with Java DB as an embedded database.

Installation

JavaDB already comes bundled with Java SE JDK.
Look for Java DB (Apache Derby) instructions on the installation post.
Or Check the official article: Step 1: Install Software

Intro

Java DB is Oracle's supported distribution of the open source Apache Derby 100% Java technology database. It is fully transactional, secure, easy-to-use, standards-based — SQL, JDBC API, and Java EE — yet small, only 2.5 MB. Java DB is installed automatically as part of the Java SE Development Kit (JDK).

We think Derby's best "feature" is its wide domain of applicability - that is it can be used anywhere from say a very small embedded database like say SQLite, all the way up to concurrent database applications matching MySQL, etc.

Java DB is lightweight at 2 megabytes and embeddable within desktop Java technology applications. Desktop applications can now access powerful database storage with triggers, stored procedures, and support for SQL, Java DataBase Connectivity (JDBC) software, and Java Platform, Enterprise Edition (Java EE, formerly referred to as J2EE), all embedded within the same Java virtual machine (JVM). [1]

Characteristics:

Derby is a full-featured, open source relational database management system (RDBMS) that is based on Java technology and SQL. Derby is written and implemented completely in the Java programming language. The database will run in any certified Java Virtual Machine (JVM).
Derby provides users with a small-footprint standards-based database engine that can be tightly
embedded into any Java based solution.
Derby ensures data integrity and provides sophisticated transaction support. In the default configuration there is no separate database server to be installed or maintained by the end user. For more information on Derby, visit the Derby Web site at http://db.apache.org/derby.
The on-disk database format used by Derby is portable and platform-independent. You can move Derby databases from machine to machine without needing to modify the data.
A Derby application can include a pre-built, populated database if it needs to, and that database will work in any Derby configuration.

The Derby software distribution provides two basic deployment options (also referred to as frameworks):

Embedded: Refers to Derby being started by a simple single-user Java application. With this option Derby runs in the same Java virtual machine (JVM) as the application. Derby can be almost invisible to the end user because it is started and stopped by the application and often requires no administration.
Server (or Server-based): Refers to Derby being started by an application that provides multi-user connectivity to Derby databases across a network. With this option Derby runs in the Java virtual machine (JVM) that hosts the Server. Applications connect to the Server from different JVMs to access the database. The Derby Network Server is part of the Derby software distribution and provides this type of framework for Derby. Derby also works well with other, independently developed Server applications.The Derby documentation often refers to this as the Network Server configuration or client/server configuration.
You can also use the previous two options together on what is called the "embedded server": allows an application to load the embedded JDBC driver for its own use and start the Network Server to allow remote access by applications running in other JVMs. The application that does this can access the database with either the embedded driver or the client driver. [3]

JavaDB (Apache Derby) tools:

The tools are available at the bin folder of your Derby installation path (ex.: C:\Program Files\Java\jdk1.7.0_01\db\bin\ij.bat). To use it from any folder just add its folder to you PATH environment variable. [2]

sysinfo: The Derby sysinfo tool displays information about your Java environment and your version of Derby.
ij: The Derby ij tool is a JDBC tool that you can use to run scripts or interactive queries against a Derby database.
dblook: The Derby dblook utility is a Data Definition Language (DDL) generation utility.The dblook utility is a simple utility that dumps all or parts of the DDL of a user-specified database to either a console or a file. The generated DDL can then be used for such things as recreating all or parts of a database, viewing a subset of the objects in a database (for example, those objects that pertain to specific tables and schemas), or documenting the schema of a database.

Server mode specific commands:

Start the Network Server up on port 1527:
C:\ startNetworkServer.bat
or start the server with:
java -jar derbyrun.jar server start
NOTE: the database files will be created on the directory were you started the server (in this case it would be created in "C:\"!!! so make sure to create a new dir before starting the server);
Stop Network Server:
C:\ stopNetworkServer.bat
or stop it with
C:\> java -jar %DERBY_INSTALL%\lib\derbyrun.jar server shutdown

IJ:

IJ is an interactive SQL scripting tool that comes with Derby (ir runs on a console and you can use it to query, run scripts; create databases and tables, etc.).

IJ manuals and tutorials:

ij commands and errors reference;
ij Basics: official ij tutorial;

IJ sample commands:
An ij command can be in either uppercase or lowercase.

Connect command
Create a new database called "sample" and open a connection to it using the embedded driver:
ij> connect 'jdbc:derby:sample;create=true'

Connect to an existing DB called sample:
ij> connect 'jdbc:derby:sample';

Connect using user and password:
ij> connect 'jdbc:derby:sample' user 'yourNick' password 'yourPass';

The previous connections were all for embedded mode, to connect to a Server mode just add //localhost:1527/ before the database name:
ij> connect 'jdbc:derby://localhost:1527/sample;create=true';
Run Command: used to run SQL scripts
Ex.: ij> run 'my_file.sql';
Note: you can also run sql scripts directly from the command line:
C:\ ij my_file.sql
or with C:\ java org.apache.derby.tools.ij my_file.sql
Quit ij:
ij> exit;

References

[1] - Using Java DB in Desktop Applications
[2] - Getting Started with Java DB
[3] - Derby Network Server

Java

Pages