H2: how to tell if table exists?
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
.
(In practice, the portability is still somewhat limited by the fact that different DBMS define and use the concepts schema and catalog slightly differently).
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_%"
.
Note that H2 has a configuration setting DATABASE_TO_UPPER
which is set to true
per 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_UPPER
to false
).
Also, using the other parameters (which I have set to null
here), you can further restrict the search scope to a specific scema or table type.
The resultset also contains meta-information about the table, if you need that, e.g., the schema or table comment.
See the JavaDoc for a complete list of options and available metadata.
First: check the case in which you type tables' names. It's very important. word_types
and WORD_TYPES
are 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:
CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY, NAME VARCHAR(255));