elational databases are a constant in today's development world. Despite advances in alternative storage and indexing technologies, such as full-text searching and indexing with Lucene and object-oriented and XML databases, the vast majority of modern J2EE applications still work with at least one relational database in one form or another. Yet this aspect of Java development has long been neglected in the standard Eclipse tool set, and developers have had to look for third-party plug-ins or external tools for their database needs.

Eclipse 3.2 has changed all that. The Data Tools Platform (DTP), and to a lesser extent the Web Tools Platform (WTP), both part of the new Callisto product suite released with Eclipse 3.2, provide a range of data-centric tools, including JDBC connectivity, SQL querying, database browsing, and more.

DTP provides a comprehensive set of tools for data-centric development, including SQL querying (including a visual SQL query modeler), execution plan analysis, and script history tracing. It also includes a framework for editing and debugging queries and stored procedures, and it enables you to connect to data sources other than relational ones. However, this promising project is still very much under development; only a subset of these features is currently available. Currently in its 0.91 release, the version 1.0 of DTP is scheduled for release in December 2006.

While the DTP project is devoted exclusively to data-centric development, the WTP project contains mostly tools and editors for J2EE development. But some WTP tools concern database development as well. This article looks at the tools provided by both these projects to make life easier when you work with relational databases in a J2EE application.

Installing DTP and WTP

Although these tools are part of the Callisto package, they do not come with the standard Eclipse 3.2 installation out of the box. However, they are relatively straightforward to install. The simplest approach is to use the remote update site, which is pre-configured in Eclipse 3.2. Go to the Remote Update window ("Help -> Software Updates -> Find and Install"), and select the "Callisto Discovery Site".

Eclipse will propose the full range of Callisto plug-ins and updates (see Figure 1). You will need to select at least the following plug-ins, plus any others you may be interested in:

• Database development
• Enabling features
• Graphical editors and frameworks
• Models and model development
• Web and J2EE development

Alternatively, you can select all the Callisto tools, as they are all useful (with the possible exception, from a Java developer's point of view, of "C and C++ development").

Figure 1. Installing DTP from the Remote Site

Once you have installed the DTP tools, you need to restart your IDE. You are now ready to take a look at the main features of DTP and the database-oriented features of WTP. Some overlap currently exists between the two projects. The first part of the article investigates the DTP tools, and the second part examines WTP's database tools and how they compare to the ones in DTP.

Connecting to a Database Using DTP

The first thing you need to do in DTP is set up a connection to your target database. Open the Preferences screen and choose "Connectivity -> Driver Definitions". DTP knows how to connect to a wide range of databases, including DB2, Derby, MySQL Oracle, and Sybase, as well as a generic JDBC connector.

Suppose you want to connect to a MySQL database. Choose the MySQL 4.1 entry and click on "Add". This will open the "New Driver Entry" window (see Figure 2), in which you select a JDBC driver and provide a name for your connection (you can have several connections of the same type to different databases, identified by different names).

Figure 2. Creating a New Database Connection

Next, you fill in the details for this particular connection. If this is the first time you've added a connection using this JDBC driver, you have to provide the path to the driver's jar file. And, not unreasonably, you also need to provide the usual JDBC connection details, such as the connection URL, user ID, and password (see Figure 3). Once this is done, your connection is ready for use!

Figure 3. Provide the Usual JDBC Connection Details

The Database Development Perspective

The DTP project provides a new perspective tailored for data-centric development: the Database Development perspective. Open this perspective ("Window -> Open Perspective -> Other -> Database Development"). One of its principal views is the Data Source Explorer, which lets you browse database structures using a feature called a Connection Profile. Think of a Connection Profile as an instance of the database connection you created previously. As you will see, you can either use a preexisting database connection as the basis for a Connection Profile or create a new one entirely from scratch.

To create a new Connection Profile to the database you just set up, go to the "Databases" node in the Data Source Explorer and choose "New..." in the contextual menu. This will open the "New Connection Profile" screen, which proposes a choice between a connection to an embedded Derby database ("Derby-Embedded Data") or a more general connection to a traditional JDBC database ("SQL Model – JDBC Connection"). Choose the latter. You will be prompted to provide a name for your profile, and then to enter the connection configuration details (see Figure 4).

Figure 4. Configuring the New Database Connection Profile

Here, you select one of the drivers configured in the Driver Definition screen as the basis for your connection. You can also supply supplementary properties to the JDBC driver and test the connection.

When you're done, the new connection profile will appear in the Data Source Explorer. From here, you can (as the name indicates) explore the structure of the database, which is represented in an intuitive tree-view structure (see Figure 5).

Figure 5. Exploring a Database Structure

This view provides a comprehensive look at the database structure: tables, columns, stored procedures, indexes, triggers, and so on. However, it is strictly read-only—no modification allowed. You can't query the database from this view either (you will see how to query the database further on). The only exception is you can run stored procedures from within this view. If your database has stored procedures, they will be listed under the entry of the same name. To run a stored procedure, simply select it and choose "Run..." in the contextual menu.

DTP Data Sources are not limited to relational databases. The ODA (Open Data Access) Data Sources also provide access to data from other sources, such as CSV and XML files, or even custom data sources.

Querying the Database

Being able to see the structure of the database is all well and good, but it is often useful to be able to see inside the database as well. You may simply need to list the contents of a table, or you may want to test and analyze the performance of complex SQL queries. The Callisto tools cater well to these needs as well.

The basic tool for SQL querying in DTP is the SQL File editor, which provides syntax coloring and limited auto-completion functionalities. Create a new SQL File (New -> Other... -> SQL Development -> SQL File), and enter a query. When you have finished writing your query, use the "Set Connection Info" to associate the SQL File with a given Database Connection Profile. Choose the "Generic JDBC" connection profile type, and then select your connection profile and database names from the drop-down lists.

Now your query is ready to run. You can either use "Execute All" to run the entire script, or select just one SQL statement and run "Execute Selected Text". This feature can be quite useful for debugging SQL scripts.

Results are displayed in the "SQL Results" view (see Figure 6). Eclipse keeps a list of the queries you've executed, along with their results, which is convenient for future reference. Results are displayed in a convenient tabular form and can be sorted by each column.

Figure 6. A DTP Query

Another neat feature is the ability to export query results in a variety of formats: just select "Save" (to export data to a file within your project) or "Export" (to export data anywhere else) in the contextual menu in the results table.

Many other interesting tools are planned for the DTP project, including a visual SQL editor and an Execution Plan analysis view. Although this project is still fairly recent, its scope is definitely ambitious. It seems to promise great things.

Using the WTP Tools for Database Development

The Data Tools Platform is not the only Callisto project to provide database support. The Web Tools Platform project also provides some interesting querying tools, which are definitely worth a look. Unfortunately, in the current release, the database connections you configured earlier on for the Data Source Explorer are not visible to the WTP tools, so you have to reconfigure a new WTP-friendly connection. You do this in the "Database Explorer" view: use "New..." in the contextual menu to create a new connection (see Figure 7). Entering a new connection is not the easiest of tasks. You need to provide all the details (database, JDBC driver, connection URL, and so on) with little prompting or assistance.

Figure 7. Setting Up a WTP JDBC Connection

If creating a new WTP-style connection is a little less user-friendly than in DTP, the results in the Database Explorer view (see Figure 8) makes it worth it. This view presents the database structure in a hierarchical format like the Data Source Explorer, but with many more possibilities than the DTP equivalent.

Figure 8. The Database Explorer View

In addition to browsing through the structure of the database, you can actively query the contents. By simply right-clicking on a table or column, you can display a subset of the table data using the "Data -> Sample Contents" entry. If you have sufficient rights, you can modify the database structure by dropping columns or even entire tables. You can also display the SQL used to create the table, export or import data, or even edit the contents of the table (using "Data -> Edit").

WTP also provides an alternative way to run ad-hoc SQL queries: the SQL Scrapbook. Create a new SQL Scrapbook ("File -> New -> Other -> Data -> SQL Scrapbook Page") and enter a SQL query. The SQL Scrapbook provides SQL syntax coloring, but does not support auto-completion. You can associate a connection using the contextual menu ("Use Database Connection"). Run the query with "Run SQL". Query results appear in the "Data Output" view.

Although similar to the DTP SQL Page, the WTP SQL Scrapbook feature is somewhat less sophisticated. Results cannot be sorted, for example, nor can you execute a selected query within the scrapbook.

DTP Tools Ambitious in Scope

Eclipse 3.2 and Callisto offer a number of powerful tools for database-oriented development activities with the WTP and DTP toolsets. The two projects overlap considerably, but the WTP tools are mature and feature rich while the DTP tools are more ambitious in scope. So WTP probably will meet the needs of many of today's Java developers, and DTP will become the reference tool set for database development under Eclipse.

However, both objectively are still quite young, with many enticing features promised for future versions. Nevertheless, in areas such as ad-hoc querying, the DTP functionality already out-does its WTP equivalent. All in all, if you need to work with relational databases in your Java application development project, both of these tool sets are definitely worth a look.