Java H2：如何判断表是否存在？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19518265/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
H2: how to tell if table exists?
提问by
I'm trying to write Java code that checks to see if an H2 table exists: if it doesn't exist, it first executes a CREATE TABLEquery that creates the table before it proceeds any further.
我正在尝试编写 Java 代码来检查 H2 表是否存在：如果它不存在，它首先执行一个CREATE TABLE创建表的查询，然后再继续进行。
I followed the advice in this Google Groups questionbut it simply does not work.
我遵循了这个 Google Groups question 中的建议，但它根本不起作用。
If I run the following query:
如果我运行以下查询：
SELECT COUNT(*) AS count FROM information_schema.tables WHERE table_name = 'word_types'
I get back a single row with a COUNTfield which has a value of 0; this indicates that the word_typestables doesn't exist. But when I run:
我得到一个COUNT字段值为 0的单行；这表明word_types表不存在。但是当我运行时：
SELECT * FROM word_types
I get back 0 result sets, but the SQL frontend/GUI that I'm using shows me all the fields/columns that exist in the word_typestable. Additionally, when I drill down into my database's list of available tables (using the same GUI), I see word_typesexists.
我得到 0 个结果集，但我使用的 SQL 前端/GUI 向我显示了表中存在的所有字段/列word_types。此外，当我深入到我的数据库的可用表列表（使用相同的 GUI）时，我看到word_types存在。
So what is the correct query to use when trying to determine if an H2 table exists or not? Using v1.3.173. Thanks in advance!
那么在尝试确定 H2 表是否存在时使用的正确查询是什么？使用 v1.3.173。提前致谢！
采纳答案by qiGuar
First:check the case in which you type tables' names. It's very important. word_typesand WORD_TYPESare two different tables.
Second:If you want to check if table exists and if it doesn't then create one, I recommend you to use the following example:
首先：检查您键入表格名称的情况。这很重要。word_types并且WORD_TYPES是两个不同的表。
第二：如果您想检查表是否存在，如果不存在则创建一个，我建议您使用以下示例：
CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));
回答by Kayaman
If the second query doesn't throw an exception or return any rows, it just means the table exists but is empty.
如果第二个查询没有抛出异常或返回任何行，则仅表示该表存在但为空。
回答by Rohit Goyla
Your above query will return the records count not existence of your table, for that you have to fire the follwing query. This query will return you all table of your Database and from there you can check your "word_types" exists or not.
您的上述查询将返回不存在您的表的记录计数，为此您必须触发以下查询。此查询将返回您数据库的所有表，您可以从那里检查您的“word_types”是否存在。
USE YOURDBNAME GO SELECT * FROM sys.Tables GO
使用 YOURDBNAME GO SELECT * FROM sys.Tables GO
reply me if it work or not works also
回复我是否有效
回答by Stefan Winkler
There is also a JDBC API which you can use to query the existence of one or more tables.
This is (in theory) more portable than a direct query which uses information_schema.
还有一个 JDBC API，您可以使用它来查询一个或多个表的存在。这（理论上）比使用information_schema.
(In practice, the portability is still somewhat limited by the fact that different DBMS define and use the concepts schema and catalog slightly differently).
（实际上，由于不同的 DBMS 定义和使用模式和目录的概念略有不同这一事实，可移植性仍然受到一定程度的限制）。
This is how it works:
这是它的工作原理：
boolean tableExists = false;
Connection conn = getConnection(); // get a DB connection from somewhere
ResultSet rset = conn.getMetaData().getTables(null, null, "WORD_TYPES", null);
if (rset.next())
{
tableExists = true;
}
Instead of "WORD_TYPES"you can also use SQL-Style wildcards, e.g. "WORD_%".
相反的"WORD_TYPES"，你也可以使用SQL样式的通配符，例如"WORD_%"。
Note that H2 has a configuration setting DATABASE_TO_UPPERwhich is set to trueper default. So any table name is converted to upper case which is why you need to query for the table in upper case (or set DATABASE_TO_UPPERto false).
请注意，H2 具有默认DATABASE_TO_UPPER设置为的配置设置true。因此，任何表名都会转换为大写，这就是为什么您需要以大写（或设置DATABASE_TO_UPPER为false）来查询表的原因。
Also, using the other parameters (which I have set to nullhere), you can further restrict the search scope to a specific scema or table type.
此外，使用其他参数（我已在null此处设置），您可以进一步将搜索范围限制为特定的场景或表类型。
The resultset also contains meta-information about the table, if you need that, e.g., the schema or table comment.
结果集还包含有关表的元信息（如果您需要），例如模式或表注释。
See the JavaDocfor a complete list of options and available metadata.
有关选项和可用元数据的完整列表，请参阅JavaDoc。
回答by MikaAll
Connection con = getConnection();
Boolean tableExist = false;
PreparedStatement preparedStatement = con.prepareStatement("SHOW TABLES FROM INFORMATION_SCHEMA");
Boolean rq = preparedStatement.execute();
if(rq) {
ResultSet rs = preparedStatement.getResultSet();
while (rs.next()) {
if(rs.getString(rs.getRow()).equals("WORD_TYPES")) {
isExist = true;
break;
}
}
}
