How to check if a table exists in jOOQ?

Question

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?

Answer 1

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