HSQLDB how to have multiple in memory databases

Question

For instance, for each unit test I'd like to have a "different" database to use, but within the same JVM.

Answer 1

Appears that the "first time" you create an HSQL in-memory database, that becomes the "canonical" password:

String DB_CONNECTION_STR = "jdbc:hsqldb:mem:MySpecialTestDb"; 
String DB_USERNAME_STR = "sa";
String DB_USERNAME_PASSWORD = "";
DriverManager.getConnection(DB_CONNECTION_STR, DB_USERNAME_STR, DB_USERNAME_PASSWORD);

after this point, if you create a new connection to jdbc:hsqldb:mem:MySpecialTestDb it will connect to the same DB (and you'll need that same username and password unless you've run some privilege granting within it). So to create a second DB, just specify a different name, and/or password:

String DB_CONNECTION_STR = "jdbc:hsqldb:mem:AnotherTestDb"; 
String DB_USERNAME_STR = "sa"; // could use different here, doesn't matter
String DB_USERNAME_PASSWORD = "";
DriverManager.getConnection(DB_CONNECTION_STR, DB_USERNAME_STR, DB_USERNAME_PASSWORD);

and it will effectively create a new in memory database for you.

See also https://stackoverflow.com/a/23544323/32453