Try on Derby for size

Get started using the Apache Derby database

By Jeff Hanson, JavaWorld.com, 09/29/06

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.

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

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:

Discuss

Subject

Replies

Last post

Free Download - 5 Minute Product Review. When slow equals Off: Manage the complexity of Web applications - Symphoniq

Free Download - 5 Minute Product Review. Realize the benefits of real user monitoring in less than an hour. - Symphoniq