Extracting Database Metadata Using JDBC – 使用JDBC提取数据库元数据

1. Overview

1.概述

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.

JDBC提供了一个Java API来读取存储在数据库表中的实际数据。除此之外，同一API还可以用来读取关于数据库的元数据。元数据是指关于数据的数据，如表名、列名和列类型。

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

在本教程中，我们将学习如何使用DatabaseMetaData接口提取不同类型的元数据。

2. DatabaseMetaData Interface

2.DatabaseMetaData接口

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.

DatabaseMetaData是一个接口，它提供了各种方法来获取数据库的综合信息。这些信息对于创建允许用户探索不同数据库结构的数据库工具非常有用。当我们想检查底层数据库是否支持某些功能时，它也很有帮助。

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的实例来获得这个信息。因此，让我们在代码中看看我们如何从Connection对象中获得这些信息。

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.

这里，connection是JdbcConnection的一个实例。因此，getMetaData()方法返回一个JdbcDatabaseMetaData的对象，它实现了DatabaseMetaData接口。

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

3.表的元数据

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.

有时，我们想知道所有用户定义的表、系统表或视图的名称。另外，我们可能想知道关于这些表的一些解释性的评论。所有这些都可以通过使用DatabaseMetaData对象的getTables()方法完成。

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.

这里，前两个参数是catalog和schema。第三个参数是一个表名的模式。例如，如果我们提供 “CUST%”，这将包括所有名称以 “CUST “开头的表。最后一个参数是一个String数组，包含表的类型。使用TABLE表示用户定义的表。

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

接下来，如果我们想寻找系统定义的表，我们所要做的就是把表的类型改为”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“.

最后，要找出所有现有的视图，我们只需将类型改为”VIEW“。

4. Columns Metadata

4.列的元数据

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

我们也可以使用相同的DatabaseMetaData对象来提取某个特定表的列.让我们看看这个动作。

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.

在这里，getColumns()调用返回一个ResultSet，我们可以通过迭代来找到每一列的描述。每个描述都包含许多有用的列，如COLUMN_NAME、COLUMN_SIZE和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.

这里，CUSTOMER_ADDRESS表有一个外键列CUST_ID，引用CUSTOMER表的ID列。上面的代码片段将产生 “CUSTOMER “作为主表，”CUSTOMER_ADDRESS “作为外表。

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

在下一节，我们将看到如何获取关于用户名和可用模式名称的信息。

5. Username and Schemas Metadata

5.用户名和模式元数据

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:

同样，我们可以使用getSchemas()方法来检索数据库中可用模式的名称。

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

6.数据库级的元数据

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

现在，让我们看看如何使用相同的DatabaseMetaData对象获得数据库级别的信息。

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:

例如，我们可以获取数据库产品的名称和版本，JDBC驱动程序的名称，JDBC驱动程序的版本号，等等。现在让我们看一下代码片断。

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

7.支持的数据库功能 元数据

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

不同的数据库支持不同的功能。例如，H2不支持全外链，而MySQL支持。

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.

另外，可以查询的全部功能清单可以在官方Java文档中找到。

8. Conclusion

8.结语

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

在这篇文章中，我们已经学习了如何使用DatabaseMetaData接口来检索数据库的元数据和支持的功能。

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

该项目的完整源代码，包括此处使用的所有代码示例，可以在GitHub上找到。