1. Overview

JDBC provides a Java API to read the actual data stored in database tables. Besides this, the same API can also be used to read metadata about the database. Metadata means data about the data such as table names, column names, and column types.

In this tutorial, we’ll learn how to extract different types of metadata using the DatabaseMetaData interface.

2. DatabaseMetaData Interface

DatabaseMetaData is an interface that provides a variety of methods to obtain comprehensive information about the database. This information is useful for creating database tools that allow users to explore the structure of different databases. It’s also helpful when we want to check whether the underlying database supports some features or not.

We’ll need an instance of DatabaseMetaData to get this information. So, let’s see in code how we can obtain this from a Connection object:

DatabaseMetaData databaseMetaData = connection.getMetaData();

Here, the connection is an instance of_ JdbcConnection_. Therefore, getMetaData() method returns an object of JdbcDatabaseMetaData, which implements the _DatabaseMetaData _interface.

In the next few sections, we’ll use this object to fetch different types of metadata. Afterward, we’ll also learn how to check if the database supports a particular feature.

3. Tables Metadata

Sometimes, we want to know the names of all the user-defined tables, system tables, or views. Also, we may like to know some explanatory comments on the tables. All of this can be done by using the getTables() method of the DatabaseMetaData object.

First, let’s see how we can extract the names of all the existing user-defined tables:

ResultSet resultSet = databaseMetaData.getTables(null, null, null, new String[]{"TABLE"}); while(resultSet.next()) {     String tableName = resultSet.getString("TABLE_NAME");     String remarks = resultSet.getString("REMARKS"); }

Here, the first two parameters are catalog and schema. The third parameter takes a pattern of table names. For instance, if we provide “CUST%”, this will include all the tables whose name starts with “CUST”. The last parameter takes a String array containing the types of tables. Use TABLE for user-defined tables.

Next, if we want to look for system-defined tables, all we have to do is to replace the table type with “SYSTEM TABLE“:

ResultSet resultSet = databaseMetaData.getTables(null, null, null, new String[]{"SYSTEM TABLE"});while(resultSet.next()) {     String systemTableName = resultSet.getString("TABLE_NAME"); }

Finally, to find out all the existing views, we’d simply change the type to “VIEW“.

#persistence #jdbc #database