Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

H2: how to tell if table exists?

Tags:

java

sql

jdbc

h2

People also ask

How do I access my H2 database table?

Accessing the H2 Console H2 database has an embedded GUI console for browsing the contents of a database and running SQL queries. By default, the H2 console is not enabled in Spring. Then, after starting the application, we can navigate to http://localhost:8080/h2-console, which will present us with a login page.

How do I know if H2 is running?

Step 3: Verify H2 Database InstallationClick Windows → type H2 Console → Click H2 console icon. Connect to the URL http://localhost:8082. At the time of connecting, the H2 database will ask for database registration as shown in the following screenshot.

What is H2 default schema?

Default schema is PUBLIC For the record, the Commands page of the H2 Database site for the SET SCHEMA command says: The default schema for new connections is PUBLIC .

Does H2 support triggers?

Yes, H2 doesn't support PL/SQL (BEGIN ... END) currently, and triggers need to call a Java method. Support for this is on the roadmap (procedural language), but it will still take some time.


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));

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.