Enumerate database tables with JDBI

Question

Is there a way, using JDBI, to get information about what tables are contained in the schema?

I have two databases A and B, and I need to be able to

1. query A and retrieve a list of tables from it;
2. query B and check that those same tables exist and have the same definitions (I don't care about the data in the tables).

I can do this using mysqldump from the command line: retrieve the CREATE TABLE statements from each database and compare them. But now I need to do it as part of a test, in a Dropwizard/JDBI context.

The documentation is a bit sparse, but I can't find anything suitable to enable me to do it. I might be able to

handle.execute("show tables");

etc., but that seems a bit low level, and I was hoping there would be a better way.

Or do I need to resort to JDBC?

Answer 1

JDBC has several database provider independent mechanisms.

From the connection you can get DatabaseMetaData, and do a query-by-expression on it (null = any).

DatabaseMetaData meta = connection.getMetaData();
ResultSet rs = meta.getColumns(...);

Beware: names may be case-sensitive here and such.

Answer 2

I just ran into this myself. JDBI doesn't provide a high-level API to do what you're asking so you'll need to use the JDBC APIs. That said, just in case you aren't aware, JDBI provides access to the underlying Connection object as an entry point to JDBC:

DBI dbi = ...;
try (Handle h = dbi.open()) {
  try (Connection c = h.getConnection()) {
    try (ResultSet tables = c.getMetaData().getColumns(null, schema, "%", "%")) {
      // loop over columns/tables here...
    }
  }
}

Depending on your requirements you could also take a look at Liquibase's diff feature which does what you're looking for out of the box:

liquibase.sh --driver=oracle.jdbc.OracleDriver \
        --url=jdbc:oracle:thin:@testdb:1521:test \
        --username=bob \
        --password=bob \
    diff \
        --referenceUrl=jdbc:oracle:thin:@localhost/XE \
        --referenceUsername=bob \
        --referencePassword=bob