In JPA, is there a DB agnostic way to check if a table exists?

Question

I'm wondering if there's a db agnostic way to check if a certain table exists in JPA (specifically, eclipse link). Right now the way we do it is with a native query like this:

select count(*) from table_name where 1=2

If this throws an exception, we know the table doesn't exist. And, as far as I know, this will work on most SQL databases. The thing I don't is it's a hacky query and it throws a SQL exception when the table doesn't exist. I'd prefer to be able to do a query that returns a true/false instead of a no error/error. But the only way I know how to do that is to query data dictionaries, and that won't be database agnostic.

In JPA, is there a DB agnostic way to check if a table exists?

Answer 1

I'll raise Joe Rinehart's comment to answer level. Using DatabaseMetaData.getTables() helped me query a table's existence in a mostly agnostic way without raising errors. A couple of comments.

1. getTables may have issues, for example with Teradata as pointed out by Durandal.
2. The resulting table names case is database dependent. For example, PostgreSQL converts to lower case by default whereas H2 converts to upper case by default.

A more agnostic method is to define a count method in a repository, such as the following (where Todo class is a JPA entity).

@org.springframework.stereotype.Repository
public interface TodoRepository extends Repository<Todo, Long> {
    Long count();
}

The above is agnostic but will throw errors which must be caught, as shown below.

private long countTodos() {
    try {
        return todoRepository.count();
    } catch (Exception e) {
        getLogger().info("Count error: {}", e.getMessage());
    }
    return 0;
}

Errors will also show up in the log when no table is present.

2016-08-04 12:02:11.190  INFO 5788 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
2016-08-04 12:02:11.296  WARN 5788 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 42102, SQLState: 42S02
2016-08-04 12:02:11.296 ERROR 5788 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Table "TodosTable_name" not found; SQL statement:
select count(*) as col_0_0_ from "TodosTable_name" todo0_ [42102-192]
2016-08-04 12:02:11.303  INFO 5788 --- [           main] c.s.e.SpringJpaDemoApplicationTests      : Count error: could not prepare statement; SQL [select count(*) as col_0_0_ from "TodosTable_name" todo0_]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement