There is usual to develop application works with database, often RDBMS. And if you are good developer, you write Unit Tests. As result, you have to use lightweight rdbms for testing purposes, like H2 or HSQLDB.
So, you can ask yourself what is the right choose? What should you pick up for your project? What is the main difference between hsqldb, h2 and derby?
In the article I'd like to compare H2 and hsqldb and pick up one of them for testing purposes. If you don't have willing to read full article - h2 win.
So, let's start. H2 vs HSQLDB
1) H2 has compatibility mode with Oracle, DB2 and MS SQL as well as with hsqldb, derby, mysql and so on; otherwise it seems poor quality of support, for example I'm not sure, that h2 also support hierarchy queries as Oracle do (needs to check); and it defently doesn't have materialized views. So, this compatible mode is very virtual and limited
2) H2 claims to be faster that hsqldb
3) Hibernate support for hsqldb is more stable, in this point HSQLDB wins, and if you prefer to use Hibernate, you probably must to get hsqldb either h2; also, I have experience of using EclipseLink with h2 and any problems were here
4) as for me, H2 is more easy to use than hsqldb
5) both have views, subqueries, triggers, user defined functions, scrollable result set
6) Indexes available in H2: hash index and full text search
7) h2 is the next project hsdldb's creator :)
Licensing (if you care about it): h2 has mozilla public license and hsqldb has BSD
Also, I've seen XML format support in H2 on several sites, but I've not found detail description or prove link that it's real exist feature
As you understand, it's very simple to switch between these db, usual you have to change connection string if any special functionality is used
Full list of the features of H2 is available here http://www.h2database.com/html/features.html
Also, you can compare both engines and make personal decision:
H2 http://database-management-systems.findthebest.com/l/16/H2
HSQLDB http://database-management-systems.findthebest.com/l/15/HSQLDB
