Eclipse-based tools for Apache Derby

Connect to and browse Apache Derby databases using Eclipse technology. Explore the possibilities of using Derby from within Eclipse or using a stand-alone tool, the IBM® Cloudscape™ Workbench, which does not require the Eclipse IDE, but instead is an Eclipse Rich Client Platform (RCP) application.

Cloudscape support

On March 13, 2007, IBM announced that it would no longer produce IBM Cloudscape and would be phasing out support for releases 10.0 and 10.1 of the software. The IBM Cloudscape referral page contains links intended to help you find information on Apache Derby sites as an alternative to the information previously posted at IBM.

Introduction

Apache Derby and IBM Cloudscape

Apache Derby, an Apache DB project, is a lightweight, embeddable, relational engine in the form of a Java class library. Its native interface is Java Database Connectivity (JDBC) with Java-relational extensions. Derby is very easy to develop on, easy to run, and easy to deploy.

In August of 2004, IBM contributed a copy of its Cloudscape 10.0 relational database product to the Apache Software Foundation (ASF) as "Derby". Moving forward, IBM Cloudscape is a commercial release of the open source Apache Derby database, and it is available at no charge. The IBM software includes the Apache Derby code without any modification whatsoever and adds a few features you won't find in the Apache Derby software, such as user-friendly installers that include the Java Runtime Environment (JRE), which are helpful for jumpstarting new Java developers, and translated manuals. You can also purchase technical support for IBM Cloudscape. The latest release of Cloudscape available is Version 10.1.

Eclipse

The Eclipse platform has become very popular with the Java development community, and rightfully so. The platform provides extensive functionality in the form of plug-ins and is easy to learn and use.

Eclipse-based tooling for the Apache Derby database

This article is oriented towards Eclipse users who wish to interact with Derby
databases. However, existing Derby users will also find the article useful to learn about the possibilities of using Derby from within Eclipse, as well as with the stand-alone Rich Client Platform (RCP) application, the Cloudscape Workbench.

Other tool options are available, such as the SQuirreLSQL client, but
they are beyond the scope of this article.

The tools

Apache Derby plug-ins

The addition of the Derby plug-ins to Eclipse enhances the development
environment by adding the Derby jar files to a project's class path, starting and stopping the Derby network server, running ij (the command line SQL tool), and outputting Derby system information using sysinfo from within Eclipse.

Size: 3 MB

Ease of use: Users new to Eclipse will need to spend some time becoming familiar with the IDE. Derby users will be able to get up and running using the
Derby plug-ins very quickly. New Derby users will find it helpful to learn ij in order to use it from within Eclipse.

Intended users: This solution is ideal for any existing Eclipse and Derby
users who are primarily interested in standalone database application development. Users new to Eclipse or Derby can also benefit from this solution; however, the learning curve
will be a little bit steeper.

Intended use cases:

Development of standalone database applications where the power of the Eclipse IDE can be put to use, such as debugging and testing

Derby database stored procedures, since they are written in Java, can be debugged from within Eclipse using the existing Eclipse debugging framework

Includes tool-specific help: Yes

Bundles Derby: Yes

Starts the Network Server from the tool: Yes

Packaging: A set of Eclipse plug-ins. The Derby plug-ins require Eclipse 3.1.

Where to download: Download the files derby_core_plugin_10.1.1.zip and derby_ui_plugin_1.1.0.zip from the Apache Derby, 10.1.1.0 release page. (See Resources.)

Ease of use: Learning Eclipse takes some spin-up, especially if you would like to investigate all of the tools capabilities. WTP shares many of the same views as the Cloudscape Workbench (CWB) since the CWB is built on top of WTP, so learning one means understanding the other.

Intended users: Web application developers who may use one or multiple databases, including Derby. WTP allows for multiple simultaneous connections to different database systems.

Intended use cases:

A Web application that makes use of a Derby database for storing and querying data. Since WTP allows for starting, stopping, and deploying entire Web applications to application servers, it makes for very easy Web application development.

Creating the Derby database, used as the data store in a Web application, running and saving SQL scripts, loading and unloading tables, and generating DDL using the datatools included in the WTP set of plug-ins.

Includes Derby-specific help: Some.

Bundles Derby: No

Starts the Network Server from the tool: No

Packaging: A set of Eclipse plug-ins.

Where to download: The WTP site has just the WTP plug-ins, including all of the dependent plug-ins or a single zip file containing Eclipse and all required plug-ins for download. (See Resources.)

IBM Cloudscape Workbench, RCP, or Eclipse plug-ins

The Cloudscape Workbench (CWB) is available as an Eclipse Rich Client Platform (RCP) application, a stand-alone tool, or as a set of Eclipse plug-ins. It is a compact tool and easy to use and learn. The CWB is built on top of a subset of the WTP set of plug-ins and obtains most of its base functionality from them. Additional capabilities added in the Workbench include enhanced Derby and DB2 connectivity, Derby to DB2 migration, and tool-specific help. Derby, Version 10.1 support is added to the list of database managers in the new connection wizard using the CWB. The additional DB2 functionality includes support for the DB2 JDBC Universal driver, improved system table information, and DDL generation for DB2.

Size: 25 MB for the standalone version, 3 MB for the plug-ins.

Ease of use: Easy to use and short learning curve. No prior knowledge of Eclipse or Derby is required, although helpful.

Using them all together: WTP, Cloudscape Workbench, and the Derby plug-ins

Since all of these components are packaged as Eclipse plug-ins, they can all
co-exist on Eclipse 3.1. Each set of plug-ins brings a little bit more functionality to the Eclipse IDE in respect to working with Derby databases.

Ease of use: Once a user is familiar with Eclipse, almost any additional
plug-in installed on the platform is easy to learn and understand.

Intended users: Web application developers who want additional Derby and DB2 connection support with the Cloudscape Workbench plug-ins and the ability to start
and stop the Derby Network Server with the Derby plug-ins. The addition of the Derby plug-ins adds three capabilities that are not available as a menu item in WTP or the Workbench plug-ins:

The ability to add the Derby jar files to the class path of a project.

The ability to manage the Network Server from Eclipse.

The ability to output Derby system information using sysinfo.

Intended use cases: A Web application that requires a Derby database to store data. Since WTP allows for starting, stopping, and deploying entire Web applications,
it makes for very easy Web application development environment. With the datatools portion of WTP, manipulating the Derby database is even easier. Also, if existing Web application developers want a way to start the Derby network server from within Eclipse, currently using the Derby plug-ins is the only way to achieve this.

Includes tool and Derby-specific help: Yes

Bundles Derby: Yes

Starts the Network Server from the tool: Yes

Packaging: All of these components are Eclipse plug-ins and require
Eclipse 3.1 or higher.

Where to download: See the previous descriptions of the individual tools
for download information of each component.

Tool comparison

For current Eclipse users, the description of the tools above will make sense in terms of additional functionality being achieved through the use of plug-ins. Figure 1 shows how the Cloudscape Workbench plug-ins build on the WTP set of plug-ins. The CWB does not use all of the WTP plug-ins, but instead uses some of the data tool plug-ins, and adds functionality by providing additional plug-ins.

Also, the Derby plug-ins are in no way related to the WTP plug-ins or the
Cloudscape Workbench, but provide different functionality.

The Cloudscape Workbench (CWB) plug-ins require the WTP plug-ins.
The functionality listed for the CWB plug-ins reflects all functionality provided. If it is primarily achieved by the WTP plug-ins, it is noted as "Uses WTP".

The CWB RCP (standalone) version is a total of 25 MB total in size, since an Eclipse installation is not required.

The CWB plug-ins add out-of-the-box support for Cloudscape and Apache Derby 10.1 connections. WTP supports 10.0 Derby database connections, and although it
can connect to a 10.1 Derby database, the connection wizard does not list it as an option.

Examples of connecting to and issuing SQL with the tools

This section shows examples of connecting to and issuing queries against
a Derby database using the tools.

The WTP plug-ins and Cloudscape Workbench

Figure 2 shows the New Connection wizard for the Cloudscape Workbench, stand-alone version. Using the WTP plug-ins, the New Connection wizard is very similar; however, other database vendors are listed in the database manager area.

Figure 2. The New Connection wizard for the Cloudscape Workbench and WTP
plug-ins

SQL editors are available in WTP and the Cloudscape workbench. SQL templates are provided, as shown in Figure 3, or ad-hoc queries can be typed in and run.

Figure 3. Issuing an SQL query using the Cloudscape Workbench or the WTP plug-ins

The Derby plug-ins

The JDBC connection URL to connect to a Cloudscape database is shown using ij in the console of Eclipse. The connection URL is entered either in ij directly by the user or using a script.

Figure 4. Connecting to a Derby database using the Derby plug-ins and ij in the Eclipse console

As shown in Figure 5, an SQL script is created using a text editor, and multiple SQL statements are made. To run the script using the Derby plug-ins, select the context-menu item Apache Derby > Run SQL Script using 'ij'.

Figure 5. Running an SQL script using the Derby plug-ins and ij

Summary

The Cloudscape Workbench and WTP plug-ins

The stand-alone version of the Cloudscape Workbench is a compact tool that
contains a subset of the database tooling functionality provided by the WTP plug-ins. Both the stand-alone version of the CWB and the plug-ins provide additional Derby and DB2 connectivity options, Derby to DB2 migration capabilities, and tool-specific help.

The stand-alone version of the Cloudscape Workbench is an ideal tool for Derby
database manipulation.

Using the WTP set of plug-ins in the Eclipse environment is targeted towards Web application development where database access and manipulation is required. A
generous portion of existing data tooling is provided by WTP and can be sufficient for Derby database manipulation, if Derby to DB2 migration capabilities are not required.

The Derby plug-ins

The ability to use ij from the command line within Eclipse, start and stop the
Network Server, run the sysinfo command, and add the Derby jar files to a project are the functionalities the Derby plug-ins bring to Eclipse. The Derby plug-ins can be used alongside the WTP or Workbench plug-ins or on their own.

Resources

Learn

The Apache Derby site contains online documentation, downloads (source and binary), and integration information with other open source projects. Both developer's and user's mailing lists are available to subscribe to or browse as archives.

"Building Web Applications with Eclipse, WTP and Derby" (developerWorks, September 2005) is a complete tutorial on using Eclipse and WTP with the Derby plug-ins to develop a web application. The source for the jsp, servlets, java classes and a complete war file are available for download.

The Apache Derby Integration area contains a section, "Eclipse Plug-ins," that has a brief presentation on the Derby plug-ins and a lab on how to use them, available for download.

The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.