Database metadata

Since JDBC is a specification that covers many databases, there is a need for extensive discovery functionality. This way, tools such as a desktop database client, or a query designer, can inspect the target database to find out details on tables, views, columns, supported types. JDBC supports this kind of discovery through metadata that is retrieved directly on the Connection instance.

Firstly, retrieve the metadata object itself.

DatabaseMetaData meta = conn.getMetaData();

Next, we can query for column information, list of tables, supported types, driver version, and so forth. The metadata available is quite extensive, and in this guide only covers the discovery of tables and columns, but the rest of the methods work in the same way.

Table Level Information 

We can retrieve the list of tables known to the driver.

DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getTables(null, null, "sometable", null);
while (rs.next()) {
    System.out.println("Table=" + rs.getString(3));
    System.out.println("Type=" + rs.getString(4));
}

The method getTables() accepts four parameters. The first two refer to schema-name and catalog, which are typically used by relational databases to namespace tables. Note that schema-name here is not related to the concept of a record having a schema type. The third parameter allows us to specify a table to search by, and the fourth parameter accepts an array of table types. The last parameter allows us to limit our search to user-level topics or system topics.

Column level information 

Let’s retrieve information about columns for a single table (Kafka topic).

DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getColumns(null, null, "sometable", "name*");
while (rs.next()) {
    System.out.println("Table=" + rs.getString(3));
    System.out.println("Column=" + rs.getString(4));
    System.out.println("Datatype=" + rs.getString(5));
}

The method getColumns() accepts four parameters. The first two refer to schema-name and catalog as in the table’s metadata. The third parameter allows us to specify a table to search by, and the fourth parameter allows a regex to limit the column search. These parameters can be null, which is equivalent to a match all, or include * as a wildcard.

The return type of this method is a regular ResultSet which is queryable in the same way any other resultset is. The example above prints out some key parts of the column info, but there are many other attributes.