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.

Share:

Susan Cline (clines@us.ibm.com), Cloudscape Community Developer, IBM

Susan Cline works in Cloudscape to develop technical content for Cloudscape developers and users.



27 October 2005

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.)

WTP plug-ins

The Eclipse Web Tools Platform (WTP) project allows Eclipse users to develop J2EE Web applications. Included in this platform are multiple editors, graphical editors, natures, builders, a Web Service wizard, database access and query tools, and other components.

Size: 45 MB

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.

Intended users: Derby database casual users -- provides database capabilities only.

Intended use cases: Connecting to and browsing Derby or DB2 databases, creating schema objects, running and creating SQL scripts, loading and unloading tables, and Derby to DB2 migration.

Includes tool-specific help: Yes

Bundles Derby: No

Starts the Network Server from the tool: No

Packaging: Either as a stand-alone tool or as a set of Eclipse plug-ins. The plug-ins require Eclipse 3.1 and the 0.7 WTP plug-ins.

Where to download: IBM developerWorks. (See Resources.)

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.

Size: 45 MB for WTP, 3 MB for the Workbench plug-ins, and 3 MB for the Derby plug-ins = 51 MB

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:

  1. The ability to add the Derby jar files to the class path of a project.
  2. The ability to manage the Network Server from Eclipse.
  3. 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.

Figure 1. Tool functionality and relationships
Tool functionality

Tool Comparison Table

Table 1 summarizes the functionality each component provides in relationship to Derby database tooling.

Table 1. Functionality of each component in relationship to Derby database tooling
Eclipse Derby plug-insWTP plug-insCWB plug-ins 1CWB RCP
Size (MB)110345325 2
Connect to DBNoYesYesYes 3Yes
Additional Derby and DB2
support (above WTP)
NoNoNoYesYes
Execute SQLNoYesYesUses WTPYes
Manage network serverNoYesNoNoNo
Browse schemaNoNoYesUses WTPYes
Includes Derby jarsNoYesNoNoNo
Get Derby system infoNoYesNoNoNo
Migrate Derby to DB2 databaseNoNoNoYesYes
Includes Derby-specific helpNoYesYes 4YesYes
  1. 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".
  2. The CWB RCP (standalone) version is a total of 25 MB total in size, since an Eclipse installation is not required.
  3. 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.
  4. A limited amount of help is provided with WTP that relates to Derby.

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
Tool functionality

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
Issuing SQL queries with 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
Using ij with the Derby plug-ins

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
Running an SQL Script using the Derby plug-ins

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

  • "Develop Apache Derby applications in Eclipse" (developerWorks, January 2005) goes into detail about developing stand-along Java applications with the Derby plug-in using Eclipse.
  • 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 Cloudscape information center contains all of the Cloudscape documentation online.
  • developerWorks DB2 zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  • developerWorks Open source projects zone: Find how-to information, tools, and project updates to help you develop with open source technologies and use them with IBM's products.

Get products and technologies

  • Eclipse WTP project: Download Eclipse 3.1 and WTP 0.7 as a single bundle.
  • 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.
  • Apache Derby Core and UI plug-ins: Download The Derby Core 10.1.1 and UI 1.1.0 plug-ins as zip files from the Distributions section.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


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.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, Open source
ArticleID=97519
ArticleTitle=Eclipse-based tools for Apache Derby
publish-date=10272005