Pop quiz: you're writing a standalone cross-platform application that needs to handle a large number of data files. The data may be disparate in type but it has some qualities in common. You want the user to be able to browse or search the data and, of course, you want your application to be responsive.

What are some possible ways to handle the data? The most rudimentary would be to allow the user to specify the location of the data files and have the application search those files each time the user needs data, much like a file finder may search for text in a file.

A slightly more elegant and faster method would be to create a file containing information about the data files; this metadata could then be searched to locate the correct data file. This is reasonably easy to implement, but complex queries are difficult to perform and adding support for future data types or additional parameters requires careful coding. The most powerful solution would be to store the metadata in a relational database.

A well-designed database offers flexibility, speed, and easy data manipulation through JDBC support. This database must be 100% Java to maintain the cross-platform compatibility as well as be easy to use so the average Java programmer can integrate it with little difficulty. FirstSQL provides such a database in FirstSQL/J Professional.

FirstSQL/J Professional v2.0
FirstSQL/J Professional is an object-relational database management system (ORDBMS) written in 100% Java. It conforms to the intermediate-level SQL92 standard and supports objects in the database. This is a powerful feature that allows Java objects to be used as the type for table columns, and static functions to be called either as a function in SQL or as a stored procedure. In addition to the database support classes, FirstSQL/J also ships with utilities that execute SQL commands and browsing tables (see Figure 1) and for database maintenance, such as backing up, restoring, creating, and building new databases (see Figure 2). There is an Enterprise Server version available, but this review focuses on the Professional version.

The recently released version 2 adds support for an in-memory mode of operation that keeps tables in memory for faster access, writing them to disk at the end of a database run. FirstSQL/J Professional also optimizes complex queries and efficiently handles deeply nested queries.

Installing and Using FirstSQL/J Professional
Installation is straightforward and requires no modification to the system environment. The installer is distributed as an executable JAR file and, after selecting a target directory, the necessary files are copied into place. In addition to a comprehensive user's guide and the API, a series of 10 tutorials is also included.

Adding FirstSQL/J Professional to an application is as easy as including a JAR file in the application's classpath and pointing to a database. FirstSQL/J Professional doesn't support runtime creation of databases, so a new database must be created using the included utility or the empty database installed with the product must be copied. From there it's just a matter of manipulating the database with SQL statements or FirstSQL extensions.

Real-World Use
My experience with FirstSQL/J Professional comes from being a project lead of a team developing a commercial weather satellite imagery visualization application. We decided to use an ORDBMS to make it possible to deliver a better experience to the user and to allow for future expansion of the application. It was envisioned that the program would be expanded to include other types of meteorological data such as surface observations or output from weather models. The amount of data available for display would quickly accumulate and it would become difficult for programmers to devise a scalable, flexible method to allow the user to easily find desired data.

While an RDBMS (one that does not support objects in the database) would suffice, we felt embedding objects directly in the database would add considerable power and flexibility. For example, consider a spatial query. The user wants to show all the available satellite imagery containing a specific latitude and longitude point (see Figure 3). Without objects, the bounds of each image have to be stored as four columns in a table, one column for each corner point. A very complex query or iteration over the whole dataset would be necessary to determine if the desired point fell within the bounds of the image. Our solution was to create a bounds object consisting of the corner points and comparison methods such as a point within the bounds, an area contained within the bounds, and an area intersecting the bounds. The following snippet of SQL illustrates how the object was used.

WHERE(img.sat_image_bounds).containsPoint
(CAST (-75.2 AS DOUBLE),CAST (40.5 AS DOUBLE)) <> 0

The method returns an integer because a method in a SQL must have arguments and a return type that are convertible to a SQL data type or be a user-defined class, and BOOLEAN is not a supported data type.

To define a class for use by the database is equally simple. Here's the entire SQL statement to register a class for use by the database:

CREATE CLASS geobounds FROM 'com.directmet.geo.GeoBounds'

This ease of use fulfilled one of the requirements I had in selecting a database: easy to implement. I have a working familiarity with SQL and relational database design though I'm not an expert by any means.

Another requirement was speed. While FirstSQL provides benchmark code, huge numbers of INSERTs and SELECTs alone are not representative of the type of query our database would most often see. I anticipated spatial queries against objects in the database would be the most common and potentially the most time-consuming. I wrote a short program to fill a table with an object representing geographic bounds and containing methods to compare against those bounds, then ran queries against the table calling the methods in the stored objects. Performance was good enough so that the database would not be the source of delays affecting the user's experience. Using the in-memory mode improved performance several fold.

FirstSQL/J Professional held up well through development and testing under both Windows and OS X. Through many crashed applications no data was ever lost, even when the problem occurred in code related to the database, nor was the database ever left in an invalid state. FirstSQL provides a short block of code to recover after an abnormal program termination, so all that's required to maintain a healthy database is to run the recovery code if the program did not exit gracefully.

Deployment
FirstSQL/J Professional has a relatively light footprint, requiring only the inclusion of a 635KB JAR file. If the embedding application is packaged in a JAR file, the class files for any objects included in the database have to be outside it. Another minor annoyance is the need to install an empty database with the application as FirstSQL/J Professional doesn't allow the creation of a database programmatically.

Support
During the past year I've contacted FirstSQL a number of times with questions or to report bugs. I've always received a prompt response and any bugs have been taken care of in subsequent releases. Naturally I'm satisfied with the support I've received, but this personal type of support won't scale well as the product gathers more users. Online support could use some work: the Web site is a little difficult to navigate and the layout can be confusing.

Summary
FirstSQL/J's ease of use and deployment and zero administration requirement mean programmers with basic knowledge of relational databases and familiarity with SQL can be productive in no time. Included tutorials and a comprehensive manual provide not only basic information but a good treatment of more advanced topics.

SIDEBAR

FirstSQL, Inc.
PO Box 1519
El Cerrito, CA 94530
Phone: 425 828-4552
Fax: 707 222-4913
Web: www.firstsql.com
E-mail: info@firstsql.com

Specifications
Platforms: JDK 1.1 or higher
Pricing: Contact company

Test Platform
Processor: 866MHz Intel Pentium III
Hard Drive: 40GB
Memory: 512MB
OS: Windows 2000

Processor: Apple iMac G3 500MHz
Hard Drive: 40GB
Memory: 256MB
OS: 10.1 and 10.2

SIDEBAR

Product Snapshot
Target Audience: Java programmers developing for the desktop
Level: Intermediate
Pros:

Support on Web site needs improvement

Published May. 1, 2003— Reads 12,031
Copyright © 2003 SYS-CON Media, Inc. — All Rights Reserved.
Syndicated stories and blog feeds, all rights reserved by the author.

Related Links

▪ Figure 1

▪ Figure 2

▪ Figure 3

About Paul Gifford