Extreme Java

HSQLDB is not the kind of database I can use in enterprise systems (with terabytes of data), but it is specially useful if I need a simple RDBMS to small (or temporary) databases.
While trying it with Glassfish, I got a big problem: if you create an in-process database (using “jdbc:hsqldb:mem” or “jdbc:hsqldb:file” or “jdbc:hsqldb:res”), HSQLDB creates a default user named “SA” with an empty password. But Glassfish does not accept data sources without passwords (I get a “No PasswordCredential found”).
To solve this, I grab the HSQLDB sources and changed the org.hsqldb.Database class. The “reopen” method has the following command:

if (isNew) {
  sessionManager.getSysSession().sqlExecuteDirectNoPreChecks(
    "CREATE USER SA PASSWORD \"\" ADMIN");
  logger.synchLogForce()
}

I just changed it to:

if (isNew) {
  sessionManager.getSysSession().sqlExecuteDirectNoPreChecks(
    "CREATE USER " + urlProperties.getProperty("user") +
    " PASSWORD \"" + urlProperties.getProperty("password") +
    "\" ADMIN");
  logger.synchLogForce();
}

Compiling with “ant jar” (in build dir), the new hsqldb.jar will now allow you to create the user based on the credentials passed as login. But this will only work if you compile with a JDK 5, because a lot of methods required by JDBC in JDK 6 are implemented by throwing UnsupportedOperationException.

For some reason, Glassfish and HSQLDB with in-process only works with OpenJPA. I’ve tryed with Hibernate and TopLink, with really strange results. If I find a solution, I’ll post later.