Beginning Derby

Derby is a full-featured, open source relational database management system (RDBMS) that is based on Java and SQL. Derby is written and implemented completely in the Java programming language. Derby provides users with a small-footprint standards-based database engine that can be tightly embedded into any Java based solution. In the default configuration there is no separate database server to be installed or maintained by the end user.


  1. Ensure Java Development Kit (JDK) is installed on your computer. Derby requires Java 2 Standard Edition (J2SE) 1.4.2 or higher. In fact only the Java Runtime Environment (JRE) is required to run Derby, but normally we compile Java applications during the practice, so it requires the Java Development Kit (JDK).

After installing Java, open a command prompt and verify the Java installation:

 Example of the output:


  1. Get the Derby package. The installation package can be obtained from the following source:

Download the package according to your operating system. It is recommended to download the latest official release available in the website. Normally Derby is available in ZIP file.

  1. Create a directory named Apache and extract the Derby package into it. Create a sub-directory under the Apache directory with the name the same as the name of the Derby package.

For example, this tutorial assumes the Derby version is 10.4.2. So the directory structure will be as follow.


Please adjust the name of the sub-directory according to your installation.

  1. Set the environment variables, by opening the Control Panel –> System. In the System properties window, point to the Advance tab and click the Environment Variables button.


Create a new variable named DERBY_INSTALL and enter the value as the path of the Derby installation directory.

Create the second variable named DERBY_HOME and set the value as the same as the prior variable.

  1. Create the third variable named CLASSPATH and set the value as follow.


  1. Verify whether the environment variables have been set correctly.

Example of the output:


  1. Verify whether Derby has been installed correctly.

 Example of the output:



ij is an interactive SQL scripting tool that comes with Derby. With this tool we are able to create a new database, tables, insert new rows, retrieve data, etc. It is a text based tool and no graphical user interface, however it is quite handy and easy to use so long you are familiar with the SQL commands.

Starting ij

  1. Create a working directory, for instance DERBYTUTOR.
  2. Open a command prompt window and go to the working directory that created in the prior step. From within the directory, start the ij tool using the following command.

 Example of the output:


Creating and Connecting to a Database

  1. After ij is started, a database connection can be established using the connect command.
    • Example to connect to an existing database:


    • Example to create a database if it doesn’t exist and connect to it subsequently:

After the command is invoked, a new sub-directory named MyTestDb will be created under the DERBYTUTOR directory. The MyTestDb directory contains physical files of the new created database.

    • Example to create/connect to a database with a user ID and password:


    • Example to create an encrypted database. An encrypted database must be equipped with a boot password. The boot password must always be specified when the database is to be opened. If you loose the boot password, the database cannot be opened anymore.


  1. To disconnect from the current database:

  1.  To exit from ij:

  1.  To run SQL scripts in ij:


You can also run SQL scripts from the command prompt:


To have the complete reference of ij commands, please download the documentations from the link at the end of this article.


Referencing Derby to the Java Project

Referencing the Derby library is the first step that must be done with the Java project before we can work with the database. This tutorial assumes the Java development tool is Netbeans. However if you use other tool like Eclipse, the step should be quite similar.

  1. In Netbeans, right-click on the project name in the project explorer pane then choose Properties.


  1. In the project properties window, select Libraries. In the Compile tab, click the Add JAR/Folder button then locate the derby.jar file within the Derby installation directory. The JAR file is normally stored in ..\bin\lib, for example C:\Apache\db-derby-\lib.
  2. Click OK to save the configuration.

Loading Driver

In the program source code, the Derby driver must be loaded before any database operation can be performed. The following source code shows an example to load the database.



Following is the minimum import statements that should be specified in the beginning of the Java program.


Connecting to a Database

A connection to the database should be established before any database operation can be performed. A piece of the code below shows an example to connect to a database. The code is executed following the loading driver command lines in the previous.

The complete reference of the Connection object is available in:


Executing SQL Statements

After the database is connected, the next things to do is to run the SQL commands like SELECT, INSERT or UPDATE. The SQL commands can be executed through the PreparedStatement or Statement objects. The piece of code below shows a simple example of SQL command execution in Derby.


The complete reference of the PreparedStatement and Statement objects is available in:


Retrieving Table Records

To retrieve existing records from tables through the SELECT command, we can use the ResultSet object to store the query results. The following example shows how we can do that.

  • To move the pointer among the records, use the navigation functions like first, previous, next and last.
  • To get the value of the fields, use the get functions like getInt, getDouble, getDate, getString etc.


The complete reference of the ResultSet object is available in:



To get the complete Derby documentations such as reference manual, developer’s guide and administration guide, please go to:



Leave a Reply

Your email address will not be published. Required fields are marked *