How to check if a database exists in Hsqldb/Derby?

Question

I am looking for information how to check if a database exists -- from Java code -- in hsqldb and in apache derby. In Mysql it is quite easy, because I can query a system table -- INFORMATION_SCHEMA.SCHEMATA -- but these two databases seem not to have such a table.

What is an alternative to mysql query:

 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = <DATABASE NAME>

to find if a database exists in hsqldb and apache derby?

Answer 1

Checking if a Database exists

One solution is to append ";ifexists=true" to the database URL and try opening the database in this way. If the database doesn't exist, you will get an exception. This works for and HSQLDB and the H2 database. For Apache Derby, append ";create=false" (actually, just make sure there is no ";create=true"). The ";create=false" also works for the H2 database, but not for HSQLDB (it's simply ignored there). The disadvantage of this ";ifexists=true" / ";create=false" trick is: you would be using exception handling for application flow control, which should be avoided (not only because throwing exceptions is slow). Also, you would open a connection which you may not want. Update: HSQLDB 2.x seems to print the stack trace to System.err(!) if the database doesn't exists and you use ";ifexists=true", in addition to throwing an exception.

You could check if the database file(s) exist(s). The disadvantage is this depends on how the database URL is mapped to a file name, which depends on database internals such as the database type and database version(!), and maybe on system properties. For Derby, you need to check if the directory exists, and additionally for some file, such as "service.properties" (it seems). For HSQLDB, you could check if the file databaseName.properties exists. For H2, check for the file databaseName.h2.db. That's with current versions of Derby / HSQLDB / H2, and may change in the future.

The question is, of course: why do you need to know if the database already exists?

Checking if a Schema exists

Maybe you don't actually want to check if a database exists. Instead, you only want to check if the given schema exists within the database. For that, you can use

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '<SCHEMA NAME>'

This works for both HSQLDB (since version 2.x) and H2. It also works with other databases. One exception is Derby, which doesn't support the standardized INFORMATION_SCHEMA schema.

Answer 2

For HSQLDB 2.0 (use the latest snapshot for better compatibility with MySQL) you don't have to change much, so long as your database / schema is not called 'PUBLIC'

Connecting to the test database, which can be an all-in-memory database will create an empty database by default (which contains the PUBLIC schema). Ater this you use

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DATABASE NAME'

Note the name should be in single quotes. I don't know if MySQL accepts the name in single quotes or not.

If the schema does not exist, then you run your schema creation code, as you would with MySQL.