Although i'm not very experienced with databases not running on webservers, i can gather from the description of it that it's a small mySQL engine that is quite fast and can be run and closed without having to have some type of service up all the time?
Just to be picky, it's a implementation of a SQL engine - MySQL's a different implementation, as is SQL Server, Oracle etc.
Yes, HSQLDB can be run in-memory very easily. Try something like the following:
Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:mydatabase", "sa", "");
Statement s = c.createStatement();
s.execute("CREATE TABLE users (username VARCHAR, password VARCHAR)");
If you want to persist the database, use a connection string of the form "jdbc:hsqldb:file:/path/to/file" and I think you'll need to run an s.execute("SHUTDOWN") to clean up properly.