Java databases get small

PDAs sound like the ideal home for database applications. You’d like to carry gobs of information around in your purse or pocket, ready to whip out and reference at a moment’s notice, right?

That could be an ideal use of Java’s “run everywhere” capability, and the two database products I tested seek to make both those ideals into a reality.

Although these products may have the same goal, they take different paths to get there. PointBase is a relational database, although it allows augmenting SQL queries using Java methods. db4o creates pure object-oriented databases, but implements a novel query technique.

The relational vs. object-oriented database decision often comes down to complexity: How explicitly do you want to structure your data? Object-oriented databases link your data together for simple retrieval; relational databases use multiple searches. As is so often the case, the database technology you choose will depend on the nature of the data your application must manipulate, the degree of interoperability you must support, and your developers’ skill level.

PointBase Micro 5.0

PointBase is a 100% Pure Java relational database system available in two flavors, Embedded and Micro. Micro is architected for J2ME apps, but Micro version applications can run in a J2SE environment as well.

I explored PointBase Micro Version 5.0 running on my reliable Palm PDA, running Palm OS 3.5. PointBase Micro’s footprint is an amazingly small 90KB for J2ME CDC and PersonalJava environments, and an even more amazing 45KB for J2ME MIDP (Mobile Information Device Profile) environments. Necessarily, the MIDP version has fewer capabilities than the CDC version.

Configuring a PointBase environment for a specific target environment and application is a matter of selecting the proper library (.JAR) file. You can choose from a number of libraries, each of which is crafted with a particular combination of features for a specific platform. Version 5.0 also adds support for the J2ME CDC DataSource interface, making it easier to create a connection to the database.

Putting a relational database engine in such a small space requires some judicious corner cutting. PointBase uses a JDBC subset API, as well as a subset of SQL92 as the query language. The JDBC subset, called the Lite API, implements the Metadata API calls only in part and supports only one transaction isolation level. The SQL92 subset provides a restricted set of data types, no triggers, and no views, among other omissions.

Still, PointBase delivers capabilities you wouldn’t expect with such limited elbowroom. You can encrypt a database as well as control access with username and password restrictions. The SQL syntax is a healthy subset of the essentials, allowing you to quickly access and create, insert, and delete indexes, tables, and more.

Creating and transferring a PointBase application was as easy as it gets, after I had downloaded Sun’s MIDP toolkit with the additional Palm libraries. One clever tool provided with PointBase Micro is MicroConsole, a midlet that allows you to examine tables within a database on an MIDP device.

With MicroConsole, you can explore tables in the database, navigate through table definitions, and even examine table contents. It comes in handy if you’re debugging a new J2ME database app and want to make sure your database is properly constructed.

Perhaps PointBase’s most interesting capability is its remote load/unload feature, which allows a PointBase client to talk to a PointBase server over HTTP and exchange SQL scripts. Consequently, remote loading/unloading is a clever way of transmitting not only data but code, too, from point A to point B via HTTP. If the client is a PDA running J2ME, then remote loading is a quick-and-easy way to create a new database on the PDA and load it with data without building a midlet middleman.

PointBase excels at jamming a lot into a little. It does an unparalleled job of creating an RDBMS environment that makes even programmers of large scale desktop systems feel at home.

db4o 2.9

db4o provides a pure object-oriented database system that takes up about 250KB of space, brags of benchmark tests that suggest speeds on par with larger and more elaborate databases, and uses a query mechanism that is well off the beaten path.

After testing db4o 2.9, there’s no doubt that it is fast. And, db4o is straightforward -- just link to the library and start writing your code. In comparison, PointBase’s many tuned configurations allow you to somewhat tailor an app for the target, but also provide more choices and therefore more decisions to make.

db4o is not specifically for PDAs, but generally speaking, it will run on any device that supports at least JDK 1.1.x. -- the small size and good performance make it a good fit for mobile devices.

In db4o, objects are stored to, retrieved from, and queried within an objectContainer, which is an object representation of the database. Persistence is not transparent in db4o; you must explicitly store and retrieve objects from the database before manipulating them, adding an extra step to the application creation process.

The db4o API includes methods for controlling the depth at which object trees are read and written. You don’t have to do anything special to classes whose objects will be stored in the database beyond ensuring that they are serializable, but you do have to get used to db4o’s unusual method of data access.

The db4o query system uses a variation of QBE (query by example) and supports two flavors of QBE, old and new.

The older QBE starts with a template object of the class of database objects you wish to query; fill the fields of the template object with values that you want matched and execute the query on the objectContainer to receive a collection object with your query matches.

This older method is a simple query mechanism with painful limitations -- it can only perform equivalence queries. It cannot, for example, create a query to retrieve all Employee objects with Salary fields greater than 60,000. In addition, the only way to omit a field from the query is to set it to Null or, for numeric fields, zero.

So why use the older version? It’s easier to work with; plus, it allows backward compatibility, and you can mix the old version with the new version without having to change the structure of the database -- and that’s a good thing.

The newer query method is called SODA (Simple Object Database Access). The SODA query mechanism is also a kind of QBE, but with embellishments that eliminate the older format’s restrictions. You still begin by creating a template object, but with SODA you ornament the template with constraint objects that describe how the comparisons should be made for each field.

Constraints can specify equals, smaller than, greater than, and even “like” comparisons. Constraints can also be ORed with one another, so you can query for all Employee objects with Salary fields greater than 60,000 or equal to zero. As with the older QBE, the results of a SODA query are returned in a collection object, which can be examined.

db4o’s simple, older version of QBE is quick to learn, but hampered by powerful limitations. The newer, more complicated version is harder to figure out, but with equally powerful benefits. db4o’s older querying method is definitely more limited than PointBase, but the newer is not. However, with SODA’s steep learning curve, you’re investing time and energy in learning and producing code for a query mechanism that isn’t supported in many other databases.

Apart from QBE, the db4o library provides classes and methods that simplify running database applications in servlet/JSP environments. db4o is thread-safe, supports transactions, and handles all the issues of associating a database with a servlet context, closing the database properly when the last session terminates, and, as does PointBase, it supports crash recovery.

db4o has no GUI tools, but it comes with some built-in tools, including a defragmenter program that you can turn loose on your database to reclaim space lost after numerous delete operations. It also has some data-access smarts, silently handling changes in an object schema, for example.

db4o is more like a database engine than anything else --  it’s definitely geared toward the technically savvy, but worth the effort put into figuring it out. It’s fast and small, and you don’t have to rearchitect your object model to use it. That’s its advantage over an RDBMS such as PointBase: With db4o, when you read an object from the database, all its fields are automatically and properly populated.

Let’s get small

These two small Java databases lie at opposite ends of the database spectrum, so you’ll need to consider the expertise of your staff and existing database structure when choosing the one for your enterprise.

PointBase is good for organizations that have programmers who are already well-versed in relational databases and familiar with SQL. Enterprise RDBMSes use tables, as does PointBase, and PointBase has the tools for trimming down existing relational databases. PointBase would be the better choice for companies that want to migrate existing apps from the enterprise to the handheld.

On the other hand, if the app you’re designing has a complex data model that might not break down well or easily into an RDBMS, then db4o is a good choice. If your developers aren’t used to doing database work and are unfamiliar with SQL, db4o will allow them to work with persistent objects without having to learn RDBMS skills.

In short, if you’re a Java programmer looking to fit your database into your purse or pocket, the choices await. Nothing’s stopping you from getting small.