如何检查Hsqldb / Derby中是否存在数据库？ [英] How to check if a database exists in Hsqldb/Derby?

问题描述

我正在寻找有关如何通过hsqldb和Apache derby中的Java代码检查数据库是否存在的信息。在Mysql中，这很容易，因为我可以查询系统表- INFORMATION_SCHEMA.SCHEMATA -但是这两个数据库似乎没有这样的表。

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.

什么是mysql查询的替代方法：

What is an alternative to mysql query:

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

查找hsqldb和apache derby中是否存在数据库？

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

推荐答案

检查数据库是否存在

一种解决方案是将; ifexists = true附加到数据库中URL，然后尝试以这种方式打开数据库。如果数据库不存在，您将获得一个例外。这适用于HSQLDB和H2数据库。对于Apache Derby，请附加; create = false（实际上，只需确保没有; create = true）。 ; create = false也适用于H2数据库，但不适用于HSQLDB（在此处被忽略）。这种; ifexists = true /; create = false技巧的缺点是：您将对应用程序流控制使用异常处理，这应该避免（不仅因为抛出异常的速度很慢）。此外，您将打开您可能不需要的连接。更新：如果数据库不存在并且除了引发异常之外，您还使用; ifexists = true，则HSQLDB 2.x似乎会将堆栈跟踪打印到System.err（！）。

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.

您可以检查数据库文件是否存在。缺点是，这取决于如何将数据库URL映射到文件名，这取决于数据库内部，例如数据库类型和数据库版本（！），还可能取决于系统属性。对于Derby，您需要检查目录是否存在，以及其他一些文件，例如 service.properties（似乎）。对于HSQLDB，您可以检查文件databaseName.properties是否存在。对于H2，请检查文件databaseName.h2.db。这是Derby / HSQLDB / H2的当前版本，并且将来可能会更改。

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?

也许您实际上不想检查数据库存在。相反，您只想检查给定的 schema 在数据库中是否存在。为此，您可以使用

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>'

这适用于HSQLDB（从2.x版本开始）和H2。它也可以与其他数据库一起使用。 Derby是一个例外，它不支持标准化的INFORMATION_SCHEMA模式。

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.

这篇关于如何检查Hsqldb / Derby中是否存在数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！