How do I get name of the target table and column of foreign key column with plain JDBC

Question

I'm trying to make a piece of code using plain JDBC that fetches me the name of both target table and column of a foreign key of a specific column in specific table but going through the core interfaces I can't seem to find a direct way to do this.

Is there a way to get such information about foreign keys through JDBC directly or do I have to resort to metadata queries to specific database, in this case HSQLDB.

If I have to use the database specific metadata queries, which HSQLDB internal metadata tables hold that information?

Answer 1

Your best bet is Connection#getMetaData() which returns DatabaseMetaData with all methods to obtain information about all tables, columns, primary keys, foreign keys, etcetera. You're however dependent on the JDBC implementation (read: the JDBC driver make/version) whether this is fully supported.

Answer 2

JDBC does have support for this. Check out DatabaseMetaData.getCrossReference class. Other methods on DatabaseMetdata support for querying schema, catalog, tables, columns, etc. Bear in mind some databases require extra parameters on your URL to turn on Metadata (i.e. Oracle) to optimize the calls. Don't know if HQLSB requires this.