Java JDBC Metadata

Metadata is data about the data — the shape of a result set and the capabilities of the database, rather than the rows themselves. JDBC exposes two metadata interfaces, and they answer different questions. They power generic tools: row printers, schema browsers, ORMs, and migration scripts that must work without hard-coded knowledge of the tables.

This chapter covers both interfaces — ResultSetMetaData (the columns a query returned) and DatabaseMetaData (the database and driver themselves) — how to read SQL type codes, and a runnable example that builds a type-code dictionary. It assumes you already know how to open a JDBC connection and iterate a ResultSet.

ResultSetMetaData — describing a query's columns

A ResultSet carries the rows; its ResultSetMetaData carries the column descriptions. Call rs.getMetaData() to learn what columns a query returned: how many, their names, and their SQL types. This is how a generic grid renders any query:

ResultSetMetaData md = rs.getMetaData();
int n = md.getColumnCount();
for (int i = 1; i <= n; i++) {   // 1-based, of course
  System.out.println(md.getColumnLabel(i)
      + " : " + md.getColumnTypeName(i)
      + " (java.sql.Types " + md.getColumnType(i) + ")");
}

Column indexes are 1-based, not 0-based — getColumnLabel(0) throws. The most useful methods:

Prefer getColumnLabel over getColumnName when you display headers: it respects SELECT total AS revenue, so the header reads revenue.

DatabaseMetaData — describing the database

Call conn.getMetaData() for facts about the database and driver themselves: product name and version, supported features, and the catalog of tables, columns, keys, and indexes.

DatabaseMetaData dbmd = conn.getMetaData();
System.out.println(dbmd.getDatabaseProductName() + " " + dbmd.getDatabaseProductVersion());
System.out.println("supports transactions: " + dbmd.supportsTransactions());

// the schema catalog comes back as ResultSets you read normally
try (ResultSet tables = dbmd.getTables(null, null, "%", new String[]{"TABLE"})) {
  while (tables.next()) System.out.println(tables.getString("TABLE_NAME"));
}

Notice that getTables, getColumns, and friends return ResultSets — the catalog is queried with the same cursor API as any other data. Each row of getColumns has well-known column labels you read by name:

// columns of the "users" table, in any catalog/schema
try (ResultSet cols = dbmd.getColumns(null, null, "users", "%")) {
  while (cols.next()) {
    System.out.println(cols.getString("COLUMN_NAME")
        + " " + cols.getString("TYPE_NAME")
        + (cols.getInt("NULLABLE") == DatabaseMetaData.columnNoNulls ? " NOT NULL" : ""));
  }
}

The four arguments to getTables/getColumns are catalog, schemaPattern, a name pattern, and (for getTables) the table types. null means "don't filter", and % is the SQL wildcard for "any name". Common companions are getPrimaryKeys, getImportedKeys (foreign keys), and getIndexInfo.

When to use which interface

• Use ResultSetMetaData when you have a query result and need to describe its columns — a grid viewer, a CSV exporter, an object mapper.
• Use DatabaseMetaData when you need facts about the database before you query it — feature detection (does it support batch updates? savepoints?), schema discovery for a migration tool, or branching SQL by getDatabaseProductName().

Mapping type codes to names

getColumnType(i) gives an int — one of the java.sql.Types constants such as INTEGER (4), VARCHAR (12), or TIMESTAMP (93). The raw int is awkward to read and to switch on, so a generic reader builds a code-to-name dictionary once (by reflecting over java.sql.Types) and reuses it for every result set. The same code also tells you which typed getter to call — getInt, getString, getTimestamp — when you map columns to fields, the way a PreparedStatement-driven mapper does.

A worked example: a type-code dictionary and a column report

This program builds the int→name dictionary for every java.sql.Types constant, then uses it to describe a hypothetical three-column result the way ResultSetMetaData would — and lists the kinds of questions DatabaseMetaData answers — without a live database.

What to take from the run:

• There are 39 standard java.sql.Types codes, and the program builds the whole code→name map by reflecting over the Types class. A real generic reader builds this dictionary once and reuses it for every result set.
• ResultSetMetaData.getColumnType(i) returns one of these int codes; the dictionary turns code 4 into INTEGER, 12 into VARCHAR, 93 into TIMESTAMP. That lookup is exactly what lets a tool render any query without knowing its columns in advance.
• The per-column report — name plus type — is what getColumnLabel/getColumnType give you for a real query. It is the foundation of grid viewers, CSV exporters, and ORMs that map columns to fields.
• DatabaseMetaData answers a different class of question: not "what did this query return" but "what can this database do" — its product name, driver version, feature support, and table catalog.
• Crucially, the catalog methods (getTables, getColumns) hand back ResultSets, so you read database structure with the very same cursor loop you use for data. Metadata is not a special API — it is data about data, delivered the same way.

Where to go next

• JDBC ResultSet — the cursor API both getMetaData() and the catalog methods return.
• JDBC Connection — where getMetaData() on the connection comes from.
• JDBC PreparedStatement — feed metadata-driven column mapping with safe parameter binding.
• JDBC Transactions — supportsTransactions() and savepoint support are reported by DatabaseMetaData.

Practice