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