Calling these database engines "small footprint" is something of a misnomer. The SQLite 2.8 engine I use for my thesis work handles databases with several million records with ease, and even HSQL is well capable of those loads. Unfortunately, my experience with HSQL (formerly named Hypersonic SQL) is that it's a bit of a memory hog. Both these databases are almost completely compliant with the SQL-92 specification (most of the SQL I know seems to work on it, which is good enough), and of course, their main advantages are minimal setup and incredibly fast speeds. Concurrent access is a bit of a problem though, and obviously the bells and whistles of databases such as MySQL, Postgres, not to mention monsters like Oracle, DB2 and MSSQL are well beyond these modest offerings.
The only place I've seen Cloudscape is in the Websphere setup, I think; but my traumatic experiences with that abomination of an application server and work environment prevent me from dredging up further details *grin* Another contender that I picked up from the Slashdot discussion was McKoi, which apparently has a bit of a following too, judging from the comments.
As far as Java/JDBC support goes, SQLite is probably the underfed stepchild of the lot; only having a JNI based wrapper. The other mini databases are written in Java themselves, thus they're far simpler to deploy. However, SQLite probably has the best press of the lot, with PHP5 recently shipping it by default and AOL actively supporting development of the 3.0x branch.
I've been a big fan of these mini databases for a while now, and they're gradually starting to replace many jobs that I performed previously with text files. They do introduce an additional dependency. But provided the amount of data to be stored is sufficiently large to justify the extra effort, a mini database is worth considering in place of CSV, INI or even XML in all but the most trivial of applications.