How to check if a table exists in jOOQ?

h2, java, jooq, sql

Solution

You cannot use:

db.Public.PUBLIC.getTables().isEmpty()

Because generated meta information is not connected to the database. Instead, you may want to take a look at `DSLContext.meta()`. In your case, you'd simply write:

DSL.using(configuration).meta().getTables().isEmpty();

If you run this test very often, that's of course not a very performant way of checking if there are any tables, as it will fetch all tables into memory just to run an `isEmpty()` check. I suggest issuing an actual query instead:

int numberOfTables =
DSL.using(configuration)
   .select(count())
   .from("information_schema.tables")
   .where("table_schema = 'PUBLIC'")
   .fetchOne(0, int.class);

A future jOOQ version (after 3.11) will be able to offer you actual object existence predicates that can be used in SQL or elsewhere:

https://github.com/jOOQ/jOOQ/issues/8038

Problem

After opening a database connection, I want to check if the database is newly minted or not. I am using H2 which automatically creates a DB if one doesn't exist. I tried this check: `db.Public.PUBLIC.getTables().isEmpty()` but that returns a static list of tables (without querying the schema in the database). I could write raw SQL to get the table list, but that would be specific to the database engine. Is there a generic alternative in jOOQ?

Original source