Eclipse-based Apache Derby tools

About Apache Derby and IBM Cloudscape

Apache Derby is an Apache DB project that 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 deploy and run, and it's easy to develop on top of it.

In August 2004, IBM donated a copy of the Cloudscape 10.0 relational database product to the Apache software Foundation (ASF), which therefore had a new name: "Derby". Since then, IBM Cloudscape has become the commercial version of the open source Apache Derby database and is completely free. The IBM software includes Apache Derby code that has not been modified, plus features that are not included in Apache Derby software, such as user-friendly installers, including Java Runtime Enviro that help the novice Java developer improve levels Nment (JRE) and some translated manuals. You can also purchase technical support from IBM Cloudscape. The latest version of the current Cloudscape is versions 10.1.

Eclipse

It is natural that the Eclipse platform has become very popular in the Java development community. This platform provides rich functionality in the form of plugins and is easy to learn and use.

Eclipse-based tools for Apache Derby database

The audience for this article is the Eclipse user who wants to interact with the Derby database. However, other Derby users will also find this article helpful in understanding the various approaches to using Derby in Eclipse or using a standalone Rich Client Platform (RCP) application Cloudscape Workbench.

There are other tool options, such as the Squirrelsql client, but they are beyond the scope of this article.

Back to top of page

Tools

• Apache Derby Plugin
• WTP Plugin
• IBM Cloudscape Workbench, RCP, or Eclipse plug-ins
• All tools: WTP, Cloudscape Workbench and Derby plugins

Apache Derby Plugin

The development environment was enhanced by adding the Derby plug-in to eclipse, where you can now add a derby jar file to the classpath of a project, start and stop the Derby network server, run IJ (SQL command-line tools), and use SysInfo output Derby System information.

Size: 3 MB

Ease of Use: New users of Eclipse need to take some time to familiarize themselves with the IDE. For the Derby plugin, the Derby user can quickly get started. New users of Derby will find it helpful to learn IJ for using the tool in Eclipse.

user-facing: This solution is ideal for all Eclipse and Derby users who are primarily interested in developing a standalone database application. New users of Eclipse or Derby can also benefit from this solution, but the learning curve is steeper.

Suitable for the scene:

• Development of a standalone database application that can unleash the power of Eclipse IDE, such as its debugging and testing capabilities.
• The Derby database stored procedures, because they are written in Java, can be debugged in eclipse using an existing Eclipse debugging framework.

whether to include tool-specific help: yes.

whether to bundle Derby: bundle.

Whether you can start Network Server from within the tool: yes.

Package form: a set of Eclipse plugins. The Derby plugin requires Eclipse 3.1.

Download location: Derby_core_plugin_10.1.1.zip and Derby_ui_plugin_1.1.0.zip files can be downloaded from the release page of Apache Derby, 10.1.1.0 (see Resources).

WTP Plugin

The Eclipse Web Tools Platform (WTP) project allows eclipse users to develop a Java EE Web application. The platform includes multiple editors, graphical editors, features (nature), builders, a Web Service wizard, some database access and query tools, and several other components.

Size: MB

Ease of Use: Eclipse's learning curve is a bit of a twist, especially if you want to institute the functionality of a tool. However, the WTP has many of the same views as Cloudscape Workbench (CWB), because CWB is built on the WTP, so learning from either of the two can make sense of the other.

user-facing: WEB application developers who might use one or more databases, including Derby. WTP allows multiple connections to different database systems at the same time.

Suitable for the scene:

• A WEB application that uses the Derby database to store and query data. Because WTP allows you to start, stop, and deploy an entire Web application to an application server, it provides very easy Web application development.
• Use the data tools included in a WTP set of plug-ins to create a Derby database for data storage for WEB applications, run and save SQL scripts, load and unload tables, and generate DDL.

whether to include Derby-specific help: there are some.

whether to bundle Derby: No.

Whether you can start Network Server from this tool: not available.

Package form: a set of Eclipse plugins.

Download Location: The WTP site has many WTP plugins available for download, including all relevant plugins, and a separate zip file containing Eclipse and all required plugins (see Resources).

IBM Cloudscape Workbench, RCP, or Eclipse plug-ins

Cloudscape Workbench (CWB) is provided as an Eclipse Rich Client Platform (RCP) application, as a standalone tool, or as a set of Eclipse plug-ins. It is a small tool that is easy to use and learn. CWB is built on a subset of the WTP set of plug-ins and obtains most of the basic functionality from these plugins. Features added in Workbench include enhanced Derby and DB2 connectivity, migration from Derby to DB2, and tool-specific help. CWB added Derby, Version 10.1 support in the Database Manager list in the New Connection Wizard. This additional DB2 feature includes support for DB2 JDBC Universal drivers, improved system table information, and DB2 DDL generation.

Size: standalone version occupies 3 MB, plug-in.

Usability: easy to use, short learning curve. Prior knowledge of Eclipse is not required, but this knowledge will help.

user-facing: Derby Database Temporary user-provides only database functionality.

Suitable scenarios: Connect and browse the Derby or DB2 databases, create schema objects, run and create SQL scripts, load and unload tables, and migrate from Derby to DB2.

whether to include tool-specific help: yes.

whether to bundle Derby: not bundled.

Whether you can start Network Server from this tool: not available.

packaged form: packaged as a standalone tool or packaged into a set of Eclipse plugins. These plugins require the Eclipse 3.1 and 0.7 WTP plugins.

Download Location: IBM DeveloperWorks (see Resources).

Use WTP, Cloudscape Workbench, and Derby plugins together

Because all of these components are packaged as Eclipse plug-ins, they can coexist on Eclipse 3.1. Each set of plugins brings some functionality to the Eclipse IDE that uses the Derby database.

Size: The WTP Mb,workbench plug-in accounted for 3 Mb,derby plug-ins accounted for 3 MB, altogether is Wuyi.

Ease of use: as long as users are familiar with Eclipse, almost any additional plug-in installed on this platform is easy to learn and understand.

for users: WEB application developers who need Cloudscape Workbench plug-in Derby and DB2 connection support as well as the Derby plug-in to start and stop the Derby Network Server capability. By adding the Derby plug-in, you can add three features that are not available in the WTP or Workbench Plugin's menu items:

1. Add the Derby jar file to the classpath of a project.
2. Manage Network Server in Eclipse.
3. Enter the Derby system information using SysInfo.

suitable scenario: A WEB application that needs to store data in a Derby database. Because WTP allows you to start, stop, and deploy an entire Web application to an application server, it provides very easy Web application development. The Derby database can be manipulated more easily with the WTP data tool. Also, if a WEB application developer wants to start a Derby network server from Eclipse, using the Derby plugin is the only way to do so.

whether to include tools and Derby-specific help: included.

whether to bundle Derby: bundle.

Whether you can start Network Server from this tool: yes.

Package form: All of these components are Eclipse plug-ins that require Eclipse 3.1 or later.

Download location: See the previous description of each tool, including download information for each component.

Back to top of page

Tool comparison

For current Eclipse users, the previous descriptions of the tools effectively illustrate the additional functionality achieved by using plug-ins. Figure 1 shows how the Cloudscape Workbench plugin is built on a group of WTP plugins. Instead of using all of the WTP plugins, CWB uses some of these data tool plug-ins and adds functionality by providing additional plugins.

Also, the Derby plugin has nothing to do with the WTP plugin or Cloudscape Workbench, which provides different functionality.

Figure 1. The function of the tool and the relationship between

Tool comparison Table

Table 1 summarizes the functionality provided by each component, as well as its relationship to the Derby database Tools.

Table 1. The functionality of each component and its relationship to the Derby database Tools plugin

	Eclipse	Derby	WTP plugin	CWB plug-in 1	CWB RCP
Size (MB)	110	3	45	3	25 2
Whether to connect to DB	Whether	Is	Is	is 3	Is
Are there additional Derby and DB2 Support (WTP above)	No	No	No	Yes	Yes
Can execute SQL	No	Yes	Yes	Using WTP	Yes
Can I manage a network server	No	Yes	No	No	No
Can browse mode	No	No	Yes	Using WTP	Yes
Whether to include Derby jar	Not included	Including	Not included	Not included	Not included
Can I get the Derby system information?	No	Yes	No	No	No
Can I migrate from Derby to the DB2 database	No	No	No	Yes	Yes
Whether to include Derby-specific help	Not included	Including	Including 4	Including	Including

1. Cloudscape Workbench (CWB) plugin requires WTP plugin. The features listed for the CWB plugin reflect all the features provided. If a feature was first provided by the WTP plug-in, it is marked as "using WTP."
2. The size of the CWB RCP (standalone) version is only a total of only one MB, as there is no need to install Eclipse.
3. The CWB plugin adds Plug and Play support for Cloudscape and Apache Derby 10.1 connections. WTP supports 10.0 Derby database connections, but can also connect to the 10.1 derby database, except that the Connection Wizard does not use the 10.1 Derby database as an option.
4. The WTP offers a limited number of Derby-related help.

Back to top of page

Examples of connecting databases and issuing SQL using these tools

This section shows some examples of using these tools to connect to the Derby database and issue queries on the Derby database.

WTP plug-ins and Cloudscape Workbench

Figure 2 shows the Cloudscape Workbench standalone version of the New Connection Wizard. The New Connection Wizard is very similar when using the WTP plugin. However, the Database Manager region also lists other database vendors.

Figure 2. New Connection Wizard for Cloudscape Workbench and WTP plugins

The SQL editor is available in WTP and Cloudscape Workbench. SQL templates are available in the SQL Editor, as shown in Figure 3, and of course you can enter and run a temporary constructed query.

Figure 3. Issue SQL queries using Cloudscape Workbench or WTP plug-ins

Derby Plugin

Shows the JDBC connection URL used to connect to the Cloudscape database when using IJ in the Eclipse console. The connection URL can be entered directly by the user in IJ, or it can be provided using a script.

Figure 4. Connect to the Derby database using the Derby plug-in and IJ in the Eclipse console

Figure 5 creates an SQL script with a text editor and includes multiple SQL statements. To run the script using the Derby plugin, you can select the context menu item Apache Derby > Run SQL script using ' ij '.

Figure 5. Run SQL scripts using the Derby plug-in and IJ

Back to top of page

Concluding remarks Cloudscape Workbench and WTP plugin

The standalone version of Cloudscape Workbench is a small tool that contains some of the functionality of the database tools provided by the WTP plugin. Whether it's a standalone version of CWB or a CWB plugin, it offers additional Derby and DB2 connectivity options, a migration feature from Derby to DB2, and tool-specific help.

The standalone version of the Cloudscape Workbench is an ideal tool for manipulating the Derby database.

A set of plugins that use WTP in an Eclipse environment is especially well-suited for the development of WEB applications that require access to and manipulation of databases. The WTP provides most of the existing data tools, which should be sufficient to manipulate the Derby database if it does not require the migration capability of Derby to DB2.

Derby Plugin

Using IJ from the command line in Eclipse, starting and stopping Network Server, running the sysinfo command, and adding a derby jar file to a project are the features that the Derby plug-in brings to Eclipse. The Derby plug-in can be used with the WTP or Workbench plug-in, or even three of them.

Eclipse-based Apache Derby tools