Try on Derby for size

Get started using the Apache Derby database

Apache Derby is a transactional, embeddable, pure-Java relational database, derived from the original Cloudscape codebase. Derby is lightweight enough to be embedded directly into Java applications and/or accessed remotely by multiple users connected to Derby's network server.

In this article, I provide an overview and tutorial of the Apache Derby database along with its ancillary components.

Note: This article includes source code that can be downloaded from Resources.

An introduction to the Apache Derby database

Apache Derby, available under the Apache License Version 2.0, is a pure-Java relational database based on Java Database Connectivity (JDBC) and SQL standards. Apache Derby occupies about 2 megabytes of disk space for the core database engine and JDBC driver.

Apache Derby supports two modes: embedded and network server. The embedded mode allows Apache Derby to be used as an embedded database accessed from within the same JVM. The network server mode allows Apache Derby to be used as a standalone database that can be accessed from multiple clients outside the JVM that hosts the database.

The following technologies are the primary components of the Apache Derby distribution:

  • The Apache Derby database engine
  • The Apache Derby Network Server

The Apache Derby embedded database engine

The nucleus of Apache Derby is an embedded database engine instantiated and executed by a Java application running in the same JVM.

Figure 1: The Apache Derby embedded engine architecture

The Apache Derby Network Server

The Apache Derby Network Server extends the Apache Derby database engine to provide multi-user connectivity to Derby databases within a single system or over a network. The Apache Derby Network Server uses the DRDA protocol standard (Distributed Relational Database Architecture) to handle queries and commands from clients. Databases exposed with the Apache Derby Network Server are accessed using the Derby Network client driver.

To expose an Apache Derby database using the network server, the following files must be in the classpath:

  • derby.jar and derbynet.jar on the server side
  • derbyclient.jar on the client side
Figure 2: The Apache Derby Network Server architecture. Click on thumbnail to view full-sized image.

ij: The Apache Derby JDBC scripting tool

The Apache Derby tool, ij, is a scripting tool that allows JDBC-based SQL scripts to be executed against an Apache Derby database. The ij tool runs in its own JVM; therefore, it needs to access an Apache Derby database via the Apache Derby Network Server.

Startup scripts for Windows and Unix environments are provided with the Derby installation to start ij. If you are using Derby as a client-server environment, start the server before connecting to the Derby database. You can start ij by running the ij scripts for your environment in the %DERBY_HOME%/frameworks/embedded/bin/ directory or in the %DERBY_HOME%/frameworks/NetworkServer/bin/ directory.

To start ij, run the script provided or use this command:

 java [<options>] org.apache.derby.tools.ij [-p <propertyFile>] [<inputFile>]
database supporting multiple connections.

Install the Apache Derby software

You can download the Apache Derby distribution from the Derby Website. Extract the archive appropriate for your environment to the directory of your choosing.

Create an environment variable named DERBY_INSTALL and set it to the location where you extracted the Derby distribution archive. In addition, navigate to the frameworks\embedded\bin directory of the Derby install and edit the script for your environment (setEmbeddedCP.bat for Windows and setEmbeddedCP.ksh for Unix), and set the reference to DERBY_INSTALL to the distribution directory.

Preparing to use the embedded framework for Apache Derby

To use Derby in its embedded mode, the following jar file must be included in your classpath: derby.jar. This file contains the Derby engine and the Derby Embedded JDBC driver.

The following illustrates one way to set your classpath on Windows to include derby.jar:

 C:\> set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar

The following illustrates one way to set your classpath on Unix to include derby.jar:

 $ export CLASSPATH=$DERBY_INSTALL/lib/derby.jar

Included with the Derby installation are command line scripts for Unix and Windows that initialize your classpath environment correctly. These scripts are located in the DERBY_INSTALL/frameworks/embedded/bin directory. The setEmbeddedCP.bat (Windows) and setEmbeddedCP.ksh (Unix) scripts rely on the DERBY_INSTALL environment variable to execute correctly.

Execute the appropriate script for Windows as follows:

 C:\> %DERBY_INSTALL%\frameworks\embedded\bin> setEmbeddedCP.bat

Execute the appropriate script for Unix as follows:

 $ cd $DERBY_INSTALL/frameworks/embedded/bin
$ . setEmbeddedCP.ksh

Preparing to use the Apache Derby server framework

To use Derby in its network server mode, the following jar files must be included in your classpath:

  • derby.jar, which contains the Derby engine
  • derbynet.jar, which contains the code for the Derby Network Server

The following illustrates one way to set your classpath on Windows to include derby.jar and derbynet.jar:

 C:\> set CLASSPATH=%DERBY_INSTALL%\lib\derby.jar;%DERBY_INSTALL%\lib\derbynet.jar

The following illustrates one way to set your classpath on Unix to include derby.jar and derbynet.jar:

 $ export CLASSPATH=$DERBY_INSTALL/lib/derby.jar:$DERBY_INSTALL\lib\derbynet.jar

Also included with the Derby installation are setNetworkServerCP.bat (Windows) and setNetworkServerCP.ksh (Unix), command line scripts for correctly executing your classpath environment. These scripts are located in the DERBY_INSTALL/frameworks/NetworkServer/bin directory and rely on the DERBY_INSTALL environment variable to execute correctly.

Execute the appropriate script for Windows as follows:

 C:\> %DERBY_INSTALL%\frameworks\NetworkServer\bin> setNetworkServerCP.bat

Unix:

 $ cd $DERBY_INSTALL/frameworks/NetworkServer/bin
$ . setNetworkServerCP.ksh

The following message should appear after running the script:

 Server is ready to accept connections on port 1527

Databases running in network server mode are accessed using the Derby Network Client driver. To use the Derby Network Client JDBC driver, the following jar file must be included in your classpath: derbyclient.jar. This file contains the network client JDBC driver.

The following illustrates one way to set your classpath on Windows to include derbyclient.jar:

 C:\> set CLASSPATH=%DERBY_INSTALL%\lib\derbyclient.jar

The following illustrates one way to set your classpath on Unix to include derbyclient.jar:

 $ export CLASSPATH=$DERBY_INSTALL/lib/derbyclient.jar

Use the setNetworkClientCP.bat (Windows) and setNetworkClientCP.ksh (Unix) command line scripts included with the Derby installation to correctly execute the classpath environment. These scripts are located in the DERBY_INSTALL/frameworks/NetworkServer/bin directory and, as with the other scripts mentioned, rely on the DERBY_INSTALL environment variable to execute correctly.

Execute the appropriate script for Windows as follows:

 C:\> %DERBY_INSTALL%\frameworks\NetworkServer\bin> setNetworkClientCP.bat

Execute the appropriate script for Unix as follows:

 $ cd $DERBY_INSTALL/frameworks/NetworkServer/bin
$ . setNetworkClientCP.ksh

Verifying an Apache Derby installation

To verify your Apache Derby installation, execute the script (setEmbeddedCP.bat for Windows and setEmbeddedCP.ksh for Unix) for your particular environment and then run the Apache Derby sysinfo command, as shown below:

 java org.apache.derby.tools.sysinfo

You should see output that appears similar to the following:

 ------------------ Java Information ------------------
Java Version:    YourJavaVersion
Java Vendor:     Sun Microsystems Inc.
Java home:       YourJAVA_HOME
Java classpath:  DERBY_INSTALL\lib\derby.jar;DERBY_INSTALL\lib\derbytools.jar;.;
C:\Program Files\Java\jre1.5.0_07\lib\ext\QTJava.zip
OS name:         Windows XP
OS architecture: x86
OS version:      5.1
Java user name:  YourUserName
Java user home:  C:\Documents and Settings\YourUserName
Java user dir:   DERBY_INSTALL\frameworks\embedded\bin
java.specification.name: Java Platform API Specification
java.specification.version: 1.5
--------- Derby Information --------
JRE - JDBC: J2SE 1.5.06 - JDBC 3.0
[DERBY_INSTALL\lib\derby.jar] 10.1.3.1 - (417277)
[DERBY_INSTALL\lib\derbytools.jar] 10.1.3.1 - (417277)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------

Creating and dropping an Apache Derby database

You create new databases and access existing ones by simply specifying the create=true attribute in the JDBC connection URL to a Derby database:

 Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
DriverManager.getConnection("jdbc:derby:"
                          + "APP.DerbyTutorialDB;"
                          + "create=true"));

You can also create a database with the ij tool using the following command:

 ij> connect 'jdbc:derby:APP.DerbyTutorialDB;create=true';

A Derby database consists of a file system directory containing several files. The APP.DerbyTutorialDB database created in one of the preceding steps will consist of the following directories and files:

  • The derby.log file contains general Derby error messages and status messages
  • The APP.DerbyTutorialDB directory embodies the APP.DerbyTutorialDB database
  • The APP.DerbyTutorialDB/log directory contains transaction logs for the APP.DerbyTutorialDB database
  • The APP.DerbyTutorialDB/seg0 directory contains the data for the APP.DerbyTutorialDB database
  • The APP.DerbyTutorialDB/service.properties file contains configuration parameters that Derby uses while starting the APP.DerbyTutorialDB database

There is no command for dropping a database in Apache Derby. Therefore, dropping a database is simply an exercise of deleting the database directory from the file system.

Establish a connection to an Apache Derby database

Connecting to an Apache Derby database from the same JVM is accomplished with the same technique used to create the database; i.e., load the Derby Embedded JDBC driver and create a connection using the DriverManager.getConnection method.

Databases exposed with the Apache Derby Network Server are accessed using the Derby Network Client driver as follows:

 public String driver = "org.apache.derby.jdbc.ClientDriver";
Class.forName(driver).newInstance();
Connection conn =
  DriverManager.getConnection("jdbc:derby:"
                            + "//localhost:1527/APP.DerbyTutorialDB;"
                            + "create=true");

Notice the host name and port number in the connection URL preceding the database name.

Shutting Apache Derby down

Applications running in the Apache Derby embedded mode shut down a Derby database/system by specifying the shutdown=true attribute in the connection URL.

To shut down a database, specify the database name in the URL as follows:

 DriverManager.getConnection("jdbc:derby:APP.DerbyTutorialDB;shutdown=true");

To shut down all databases and the entire Derby system, omit the database name from the URL as follows:

 DriverManager.getConnection("jdbc:derby:;shutdown=true");

When running in network server mode, you can stop the network server in many ways. You can stop the network server programmatically as follows:

 InetAddress inetAddr = InetAddress();
int port = 1527;
new org.apache.derby.drda.NetworkServerControl(inetAddr, port).shutdown();

You can stop the network server from a command line as follows:

 java org.apache.derby.drda.NetworkServerControl shutdown -h myhost -p 1527

You can also stop the network server by executing the stopNetworkServer.bat script on Windows, as shown below:

 

C:\< cd %DERBY_INSTALL%\frameworks\NetworkServer\bin C:\Apache\db-derby-10.1.2.1-bin\frameworks\NetworkServer\bin> setNetworkServerCP.bat

C:\Apache\db-derby-10.1.2.1-bin\frameworks\NetworkServer\bin> stopNetworkServer.bat

You can also stop the network server by executing the stopNetworkServer.ksh script on Unix, as shown below:

 $ cd $DERBY_INSTALL/frameworks/NetworkServer/bin
$ . setNetworkServerCP.ksh
$ stopNetworkServer.ksh

The output from either environment should display a "Shutdown successful" message.

Running with ij in a network server environment

The DERBY_INSTALL\demo\nserverdemo directory contains a class (SimpleNetworkServerSample) that starts the Derby embedded database engine and exposes it to remote clients via the Derby Network Server. Running the SimpleNetworkServerSample class will start the Derby Network Server and load the embedded driver. It then creates the NSSimpleDB database (if it does not already exist) and waits for clients to connect. At this point, the ij tool can be used to interact with the NSSimpleDB database. This interaction scenario is discussed next.

To execute the SimpleNetworkServerSample demo, open a command window/shell and navigate to the DERBY_INSTALL\demo\programs\nserverdemo directory. Set the CLASSPATH environment variable to point to the appropriate directories and archives for the nserverdemo server, as follows:

 DERBY_INSTALL\demo\nserverdemo>set classpath=.;../../lib/derby.jar;../../lib/derbynet.jar;.
./../lib/derbytools.jar

Verify the CLASSPATH settings by running the following command:

 java org.apache.derby.tools.sysinfo

The output should be similar to the following:

1 2 Page
Join the discussion
Be the first to comment on this article. Our Commenting Policies
See more