使用JDBC提取数据库元数据

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:

try(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“:

try(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“.

4. Columns Metadata

We can also extract the columns of a particular table using the same DatabaseMetaData object*.* Let’s see this in action:

try(ResultSet columns = databaseMetaData.getColumns(null,null, "CUSTOMER_ADDRESS", null)){
  while(columns.next()) {
    String columnName = columns.getString("COLUMN_NAME");
    String columnSize = columns.getString("COLUMN_SIZE");
    String datatype = columns.getString("DATA_TYPE");
    String isNullable = columns.getString("IS_NULLABLE");
    String isAutoIncrement = columns.getString("IS_AUTOINCREMENT");
  }
}

Here, the getColumns() call returns a ResultSet that we can iterate to find the description of each column. Each description contains many useful columns such as COLUMN_NAME, COLUMN_SIZE, and DATA_TYPE.

Besides regular columns, we can also find out the primary key columns of a particular table:

try(ResultSet primaryKeys = databaseMetaData.getPrimaryKeys(null, null, "CUSTOMER_ADDRESS")){ 
 while(primaryKeys.next()){ 
    String primaryKeyColumnName = primaryKeys.getString("COLUMN_NAME"); 
    String primaryKeyName = primaryKeys.getString("PK_NAME"); 
 }
}

Similarly, we can retrieve the description of foreign key columns along with the primary key columns referenced by the given table. Let’s see an example:

try(ResultSet foreignKeys = databaseMetaData.getImportedKeys(null, null, "CUSTOMER_ADDRESS")){
 while(foreignKeys.next()){
    String pkTableName = foreignKeys.getString("PKTABLE_NAME");
    String fkTableName = foreignKeys.getString("FKTABLE_NAME");
    String pkColumnName = foreignKeys.getString("PKCOLUMN_NAME");
    String fkColumnName = foreignKeys.getString("FKCOLUMN_NAME");
 }
}

Here, the CUSTOMER_ADDRESS table has a foreign key column CUST_ID that references the ID column of the CUSTOMER table. The above code snippet will produce “CUSTOMER” as the primary table and “CUSTOMER_ADDRESS” as a foreign table.

In the next section, we’ll see how to fetch the information about the username and available schema names.

5. Username and Schemas Metadata

We can also get the name of the user whose credentials have been used while fetching the database connection:

String userName = databaseMetaData.getUserName();

Similarly, we can use the method getSchemas() to retrieve the names of the available schemas in the database:

try(ResultSet schemas = databaseMetaData.getSchemas()){
 while (schemas.next()){
    String table_schem = schemas.getString("TABLE_SCHEM");
    String table_catalog = schemas.getString("TABLE_CATALOG");
 }
}

In the next section, we’ll see how to fetch some other useful information about the database.

6. Database-Level Metadata

Now, let’s see how the database-level information can be obtained using the same DatabaseMetaData object.

For instance, we can fetch the name and version of the database product, name of the JDBC driver, the version number of the JDBC driver, and so on. Let’s now look at the code snippet:

String productName = databaseMetaData.getDatabaseProductName();
String productVersion = databaseMetaData.getDatabaseProductVersion();
String driverName = databaseMetaData.getDriverName();
String driverVersion = databaseMetaData.getDriverVersion();

Knowing this information can sometimes be useful, especially when an application is running against multiple database products and versions. For instance, a certain version or product may lack a particular feature or contain a bug where the application needs to implement some kind of workaround.

Next, we’ll see how we can come to know if the database lacks or supports a particular feature.

7. Supported Database Features Metadata

Different databases support different features. For instance, H2 doesn’t support full outer joins, while MySQL does.

So, how can we find out if the database we are using supports a certain feature or not? Let’s see some examples:

boolean supportsFullOuterJoins = databaseMetaData.supportsFullOuterJoins();
boolean supportsStoredProcedures = databaseMetaData.supportsStoredProcedures();
boolean supportsTransactions = databaseMetaData.supportsTransactions();
boolean supportsBatchUpdates = databaseMetaData.supportsBatchUpdates();

Also, the full list of features that can be queried can be found on the official Java documentation.

8. Conclusion

In this article, we’ve learned how to use the DatabaseMetaData interface to retrieve metadata and supported features of a database.

The complete source code for the project, including all the code samples used here, can be found over on GitHub.