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

Question

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

What is an alternative to mysql query:

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

find if database exists in hsqldb and apache derby?

Answer 1

Check for database availability

One solution is to add "; ifexists = true" to the database URL and try to open the database this way. If the database does not exist, you will get an exception. This works for HSQLDB and H2 database. For Apache Derby, add "; create = false" (in fact, just make sure there is no "; create = true"). "; Create = false" also works for the H2 database, but not for HSQLDB (it is simply ignored there). The disadvantage of this "ifexists = true" / "; create = false" trick is this: you will use exception handling to control application flow, which should be avoided (not only because throwing exception is slow). In addition, you will open a connection that you might not need. Update: HSQLDB 2.x seems to print a stack trace to System.err (!), If the database does not exist and you use "; ifexists = true", in addition to throwing an exception.

You can check if the database file exists. The disadvantage is that it depends on how the URL of the database is mapped to a file name, which depends on the internal elements of the database, such as the type of database and version of the database (!) And, possibly, on the properties of the system. For Derby, you need to check if the directory exists, as well as for some file, for example "service.properties" (it seems). For HSQLDB, you can check if the databaseName.properties file exists. For H2, check the databaseName.h2.db file. This is with current versions of Derby / HSQLDB / H2 and may change in the future.

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

Check for circuit availability

Perhaps you really do not want to check for a database. Instead, you only need to check if a given schema exists in the database. For this you can use

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

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

Answer 2

For HSQLDB 2.0 (use the latest snapshot for better MySQL compatibility) you do not need to change much if your database / schema is not called "PUBLIC"

Connecting to a test database, which may be a database in the database, will create an empty default database (which contains the PUBLIC schema). Use it

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

Please note that the name must 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 the schema creation code, as with MySQL.