Hypersonic SQL is an open-source database written in Java. Its compact size and generous licensing terms make it ideal for standalone applications and for testing and prototyping.
The remainder of this article will introduce Hypersonic SQL. You can download the latest version from http://hsql.oron.ch.
The Case for a Small Database
I started looking for an alternative Java database out of frustration with mainstream offerings. Granted there are excellent Java drivers for the major databases in the market: Oracle, IBM DB2, Sybase, and Microsoft SQL server.
Still, these databases were designed for enterprise-level applications. They are complex and costly products. Their power, which is a strength for server-side development, becomes a liability for desktop or standalone projects.
I have found at least three problems. First, CPU and memory consumption requires a dedicated server. Granted, any of these databases run on a development machine but a developer’s computer is not typical of desktop computers. Second, they are too difficult to set up and maintain for your average desktop user. Finally, the licensing costs can be prohibitive when doing a large-scale deployment.
There are few alternatives for standalone projects. Microsoft Access is one popular option, but its Java API is limited to the JDBC/ODBC bridge (making it difficult to create new databases from the application). I have had some success with InterBase, although some users found it intimidating.
This has led me to search for a standalone Java database. Ideally, it should be cost-effective to deploy and transparent for the user. Although it need not be as powerful as its server counterparts, it should support JDBC and SQL to allow me to reuse as much existing code as possible.
Hypersonic SQL
The most interesting database I have found is Hypersonic SQL, an open-source project with its roots in Switzerland. Hypersonic SQL has several interesting characteristics: It is written entirely in Java, so it will run on all the Java platforms; it is compact, weighing only 155 K and, since it is an open source project, there is no per CPU license.
Hypersonic SQL shines for standalone or desktop applications where its small size makes the difference. Furthermore, since it implements the JDBC interface, it provides some compatibility with existing code.
Don’t throw away your Oracle, DB2, Sybase, or SQL Server, however. For all its qualities, Hypersonic SQL is not multithreaded so it is not appropriate for server-side developments. Also, it is limited to a subset of SQL (most notably, no foreign keys). Perhaps more significantly, it lacks the backup and management tools required for mission-critical applications.
To summarize, Hypersonic SQL is well adapted for standalone desktop applications written in Java. It is also useful for testing and prototyping. The Web site further recommends using it with databases stored on CD-ROMs, although I have not tested this feature. I think it may also be useful for embedded applications.
Three Modes of Operation
Hypersonic SQL operates in one of three modes: in-memory, standalone, and client/server. The most useful one is the standalone mode where one application accesses the database exclusively.
In client/server mode, applications connect to a database server (implemented as a servlet). Client/server offers some level of database sharing amongst applications, but remember that the database engine is not multithreaded so it is not appropriate if you have many users.
In-memory is unique in that the database is not written to disk. In other words, information is lost after the application terminates. In-memory has been designed specifically for applets and, as you would expect, is the fastest mode.
Connecting to Databases
Hypersonic SQL supports JDBC, so most of the database code will be familiar.
The JDBC driver is implemented in the class org.hsql.jdbcDriver. Database URLs start with jdbc:HypersonicSQL: followed by a single dot (.), a filename or an URL — respectively for in-memory, standard, and client/server modes. The following examples illustrates the possibilities:
jdbc:HypersonicSQL:.
jdbc:HypersonicSQL:db/demo
jdbc:HypersonicSQL:http://db.psol.com
Notice that, in standalone mode, you can specify a path before the filename. If the database does not exist already, it will be created automatically.
The following code snippet connects to a standalone database called “demo” in the “db” folder. It connects as user SA and an empty password:
new org.hsql.jdbcDriver();
String url = “jdbc:HypersonicSQL:db/demo”,
uname = “SA”,
pwd = null;
conn = DriverManager.getConnection(url,uname,pwd);
SQL Statements
Before reading this section, you should download the demonstration application. This simple application lets you execute SQL statements against a small database.
Hypersonic SQL recognizes a subset of SQL. SELECT, INSERT, UPDATE and DELETE works as you would expect, but foreign keys, views, and triggers are not supported (visit the Web site for updates).
By default, the database relies on a rudimentary file format: It simply writes the SQL statements in a script file. To load a database, it simply re-executes the statements.
For large tables, it is more efficient to retain only a few records in memory and cache the remainder on disk. You must use the special CREATE CACHED TABLE statement instead of the regular CREATE TABLE to create cached tables. The following statement creates a cached table called “cached_demo”:
create cached table cached_demo (value varchar);
Other useful extensions to SQL include CREATE USER, SET PASSWORD, and SET IGNORECASE. New databases are created with the default user “SA” and an empty password. CREATE USER and SET PASSWORD let you manage users. As the name implies, SET IGNORECASE controls case sensitivity for text comparisons. The following statements demonstrate this:
set password mypassword;
create user bmarchal password mypassword;
set ignorecase false;
Hypersonic SQL also supports auto-numbering columns, called identity columns. Since each record is automatically assigned a unique number, identity columns are ideal primary keys. The following statement creates a table called with two columns, one of them (id) is an identity column:
create cached table identity_demo (id integer identity,value varchar);
Conclusion
You should not throw your regular database away but you may want to explore Hypersonic SQL for standalone projects. It is also ideally suited for testing and prototyping.
About the Author
Benoît Marchal is the author of XML by Example and Applied XML Solutions. His own Web site is http://www.marchal.com.
