6

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?

Daniel Kaplan
  • 62,768
  • 50
  • 234
  • 356
  • 1
    What's configuring the underlying connection (java.sql.Connection)? You should be able to get to it, get a connection, and then do connection.getMetaData(). That'll return an instance of DatabaseMetaData, which has a getTables() method. It'll return a ResultSet that you can then scan. – Joe Rinehart Jan 15 '14 at 00:34
  • @JoeRinehart I can only guess that JPA is. I'll see if your suggestion works. This documents how to get it in JPA: http://stackoverflow.com/a/3497206/61624 – Daniel Kaplan Jan 15 '14 at 01:29
  • 2
    @tieTYT I've use that unwrapped mechanism before with JPA, it has worked for me in the past. For most DB implementations `getTables()` works correctly but I ran into an issue with the Teradata driver. For some reason `getTables()` didn't work correctly and basically never came back. As long as the driver implements `getTables()` correctly you should be good to go using JoeRinehart's suggestion. – Durandal Jan 15 '14 at 02:16

1 Answers1

2

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
Community
  • 1
  • 1
EricGreg
  • 1,098
  • 1
  • 10
  • 18