You use java.sql.DatabaseMetaData by:
- Opening a
Connection - Calling
conn.getMetaData() - Querying catalogs/schemas/tables/columns/keys/indexes via the
DatabaseMetaData“getXxx” methods (they mostly returnResultSets) - Reading those result sets like normal query results
Below are the most common inspection tasks and the JDBC calls that power them.
1) Get the DatabaseMetaData
package org.kodejava.jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
public class DatabaseMetadata1 {
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
DatabaseMetaData md = conn.getMetaData();
System.out.println(md.getDatabaseProductName() + " " + md.getDatabaseProductVersion());
System.out.println(md.getDriverName() + " " + md.getDriverVersion());
} catch (Exception e) {
e.printStackTrace();
}
}
}
If you’re using a DataSource, it’s the same idea: try (Connection conn = dataSource.getConnection()) { ... }.
2) Understand the “filter” parameters (the #1 gotcha)
Many methods take filters like:
catalog(often the database name; sometimes unused)schemaPattern(SQL pattern:%wildcard; sometimes case-sensitive depending on DB)tableNamePattern(pattern)types(e.g.,{"TABLE","VIEW"})
Patterns are not regex; they’re SQL LIKE-style patterns:
%= any sequence_= any single character
Also: some databases store identifiers in upper-case/lower-case internally. Use:
md.storesUpperCaseIdentifiers()md.storesLowerCaseIdentifiers()md.supportsSchemasInTableDefinitions()
3) List catalogs and schemas
package org.kodejava.jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class DatabaseMetadata2 {
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
DatabaseMetaData md = conn.getMetaData();
try (ResultSet rs = md.getCatalogs()) {
while (rs.next()) {
System.out.println("Catalog: " + rs.getString("TABLE_CAT"));
}
}
try (ResultSet rs = md.getSchemas()) {
while (rs.next()) {
System.out.println("Schema: " + rs.getString("TABLE_SCHEM")
+ " (catalog=" + rs.getString("TABLE_CATALOG") + ")");
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
Column names like "TABLE_SCHEM" are specified by the JDBC spec for each metadata result set. (They feel a bit “old-school”, but they’re standard.)
4) List tables (and views)
package org.kodejava.jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class DatabaseMetadata3 {
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
DatabaseMetaData md = conn.getMetaData();
String catalog = null; // or your catalog name
String schemaPattern = "public"; // adjust for your DB; or "%"
String tablePattern = "%";
String[] types = {"TABLE", "VIEW"};
try (ResultSet rs = md.getTables(catalog, schemaPattern, tablePattern, types)) {
while (rs.next()) {
String schema = rs.getString("TABLE_SCHEM");
String table = rs.getString("TABLE_NAME");
String type = rs.getString("TABLE_TYPE");
System.out.println(type + " " + schema + "." + table);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
5) Inspect columns for a table
package org.kodejava.jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class DatabaseMetadata4 {
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
DatabaseMetaData md = conn.getMetaData();
String catalog = null;
String schema = "public";
String table = "users";
try (ResultSet rs = md.getColumns(catalog, schema, table, "%")) {
while (rs.next()) {
String col = rs.getString("COLUMN_NAME");
int jdbcType = rs.getInt("DATA_TYPE"); // java.sql.Types
String dbType = rs.getString("TYPE_NAME"); // vendor type name
int size = rs.getInt("COLUMN_SIZE");
int nullable = rs.getInt("NULLABLE"); // DatabaseMetaData.columnNullable/NoNulls
String def = rs.getString("COLUMN_DEF");
System.out.printf("%s %s(%d) nullable=%s default=%s%n",
col, dbType, size,
(nullable == DatabaseMetaData.columnNullable),
def);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
If you need precision/scale for numeric columns, also read:
DECIMAL_DIGITSNUM_PREC_RADIX
6) Primary keys, foreign keys, and indexes
Primary key
try (ResultSet rs = md.getPrimaryKeys(null, "public", "users")) {
while (rs.next()) {
System.out.println("PK column: " + rs.getString("COLUMN_NAME")
+ " (name=" + rs.getString("PK_NAME") + ")");
}
}
Foreign keys (imported keys = FKs from this table to others)
try (ResultSet rs = md.getImportedKeys(null, "public", "users")) {
while (rs.next()) {
System.out.println("FK " + rs.getString("FK_NAME")
+ ": " + rs.getString("FKCOLUMN_NAME")
+ " -> " + rs.getString("PKTABLE_NAME") + "." + rs.getString("PKCOLUMN_NAME"));
}
}
Indexes
package org.kodejava.jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class DatabaseMetadata5 {
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/postgres", "postgres", "postgres")) {
DatabaseMetaData md = conn.getMetaData();
boolean unique = false; // set true to request only unique indexes
boolean approximate = false;
try (ResultSet rs = md.getIndexInfo(null, "public", "users", unique, approximate)) {
while (rs.next()) {
String indexName = rs.getString("INDEX_NAME");
String column = rs.getString("COLUMN_NAME");
boolean nonUnique = rs.getBoolean("NON_UNIQUE");
short type = rs.getShort("TYPE"); // tableIndexStatistic / tableIndexClustered / etc.
if (indexName != null && column != null) {
System.out.println((nonUnique ? "IDX" : "UNIQUE") + " " + indexName + " on " + column + " type=" + type);
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
7) “Dump the schema” pattern (tables → columns → keys)
For a basic schema inspection tool, you typically:
getTables(...)- For each table:
getColumns(...) - Optionally:
getPrimaryKeys(...),getImportedKeys(...),getIndexInfo(...)
Keep it read-only; it’s metadata only.
8) Practical tips / pitfalls
- Case sensitivity: If your DB stores identifiers uppercase, but you pass lowercase (or vice versa), you may get empty results. Check
storesUpperCaseIdentifiers()/storesLowerCaseIdentifiers()and normalize. - Use
%liberally while exploring: Start withschemaPattern="%"and narrow down once you see what the DB reports. - Some drivers are quirky: Not all metadata is perfectly implemented. When something looks missing, verify against the DB’s own system catalogs.
- Close metadata
ResultSets: They’re real JDBC resources. Use try-with-resources as shown.
