9

I'm familiar with the java.sql.DatabaseMetaData interface, but I find it quite clunky to use. For example, in order to find out the table names, you have to call getTables and loop through the returned ResultSet, using well-known literals as the column names.

Is there an easier way to obtain database metadata?

Andrew Swan
  • 13,427
  • 22
  • 69
  • 98

2 Answers2

11

It's easily done using DdlUtils:

import javax.sql.DataSource;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.platform.hsqldb.HsqlDbPlatform;

public void readMetaData(final DataSource dataSource) {
  final Platform platform = PlatformFactory.createNewPlatformInstance(dataSource);
  final Database database = platform.readModelFromDatabase("someName");
  // Inspect the database as required; has objects like Table/Column/etc.
}
Andrew Swan
  • 13,427
  • 22
  • 69
  • 98
6

Take a look at SchemaCrawler (free and open source), which is another API designed for this purpose. Some sample SchemaCrawler code:

    // Create the options
final SchemaCrawlerOptions options = new SchemaCrawlerOptions();
// Set what details are required in the schema - this affects the
// time taken to crawl the schema
options.setSchemaInfoLevel(SchemaInfoLevel.standard());
options.setShowStoredProcedures(false);
// Sorting options
options.setAlphabeticalSortForTableColumns(true);

// Get the schema definition 
// (the database connection is managed outside of this code snippet)
final Database database = SchemaCrawlerUtility.getDatabase(connection, options);

for (final Catalog catalog: database.getCatalogs())
{
  for (final Schema schema: catalog.getSchemas())
  {
    System.out.println(schema);
    for (final Table table: schema.getTables())
    {
      System.out.print("o--> " + table);
      if (table instanceof View)
      {
        System.out.println(" (VIEW)");
      }
      else
      {
        System.out.println();
      }

      for (final Column column: table.getColumns())
      {
        System.out.println("     o--> " + column + " (" + column.getType()
                           + ")");
      }
    }
  }
}

http://schemacrawler.sourceforge.net/

Sualeh Fatehi
  • 4,700
  • 2
  • 24
  • 28
  • Do you need to close the connection yourself or does the getDatabase() method do that for you? – Andrew Swan Feb 27 '12 at 04:27
  • @AndrewSwan - SchemaCrawler will not close the connection for you. You need to close it yourself. – Sualeh Fatehi Feb 28 '12 at 16:21
  • In that case you might like to update your example so that it closes the connection in a finally block? – Andrew Swan Mar 06 '12 at 04:29
  • 4
    Andrew, I added a comment to indicate this. I believe that stackoverflow examples should be kept minimal to illustrate the answer, rather than be complete production code taking best practices into account. – Sualeh Fatehi Mar 07 '12 at 16:23
  • @SualehFatehi Does the latest release of Schematic Crawler support Java 6? I have seen only Java 7 in FAQS. For Java 6 which release should I use? Please let me know. – phoenix Jan 25 '15 at 02:56
  • @BRS - You will need to use version 9.6. SchemaCrawler moved over to Java 7 in version 10.1. – Sualeh Fatehi Jan 25 '15 at 03:48