mysql/jdbc:设置useInformationSchema=true读取表注释信息(table_comment)

超、凢脫俗 2022-06-10 12:28 285阅读 0赞

# 问题描述 #

今天在读取表的注释信息(COMMENT)时,发现返回的REMARKS字段返回居然是null.  
以下是代码示例:

DatabaseMetaData meta = this.pConnection.getMetaData();
    // 获取所有表信息
    ResultSet resultSet = this.meta.getTables(this.catalog, tableSchema, pattern, this.tableTypes);
    while (resultSet.next()) {
        Table table = new Table();
        # 返回null
        String comment=resultSet.getString("REMARKS");
    }
    resultSet.close();

# 原因分析 #

google找了半天，总算知道原因:  
Connector/J 5.0.0以后的版本有一个名为`useInformationSchema`的数据库连接参数，  
在默认连接参数情况下,`useInformationSchema=false`,导致`Connection.getMetaData()`方法返回的DatabaseMetaData 对象是`com.mysql.jdbc.DatabaseMetaData`，而不是`com.mysql.jdbc。DatabaseMetaDataUsingInfoSchema`,  
`DatabaseMetaDataUsingInfoSchema`是`DatabaseMetaData`是的子类,看名称就能联想到是通过 `INFORMATION_SCHEMA` 数据库获取数据库的metadata，可以正确返回`table_comment`字段。

下面是`useInformationSchema`的官方说明

> useInformationSchema
> 
> When connected to MySQL-5.0.7 or newer, should the driver use the INFORMATION\_SCHEMA to derive information used by DatabaseMetaData?
> 
> Default: false  
> Since version: 5.0.0  
> 摘自[《5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J》][5.1 Driver_Datasource Class Names_ URL Syntax and Configuration Properties for Connector_J]

而父类`DatabaseMetaData`并不一定能正常返回`table_comment`字段.

> 关于INFORMATION\_SCHEMA 这里不深入探讨，参见[《Chapter 24 INFORMATION\_SCHEMA Tables》][Chapter 24 INFORMATION_SCHEMA Tables]

# 解决方法 #

解决的方法也很简单：  
数据库连接时设置`useInformationSchema=true`  
如何设置数据库连接参数呢？有两个途径

## 方法一：java代码实现 ##

# 将所有参数装入java.util.Properties 对象
    Properties props = new Properties();
    props.setProperty("username",this.username);
    props.setProperty("password",this.password); 
    props.setProperty("useInformationSchema", "true");
    # 调用getConnection(String,Properties)方法创建连接
    this.pConnection = java.sql.DriverManager.getConnection(this.url, props);

## 方法二：连接url参数 ##

直接将参数加到数据库连接url,如下代码中在数据连接url中添加了两个参数`characterEncoding=utf8`和`useInformationSchema=true`

String url="jdbc:mysql://localhost:3306/test?characterEncoding=utf8&&useInformationSchema=true"
    this.pConnection = DriverManager.getConnection(this.url, this.username,this.password);

> 关于mysql 连接URL的语法参见:  
> [《5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J》][5.1 Driver_Datasource Class Names_ URL Syntax and Configuration Properties for Connector_J]

# 参考资料 #

[《Connector/J does not retrieve the table comment in a InnoDB table》][Connector_J does not retrieve the table comment in a InnoDB table]  
[《Retrieve mysql table comment using DatabaseMetaData》][Retrieve mysql table comment using DatabaseMetaData]  
[《Chapter 24 INFORMATION\_SCHEMA Tables》][Chapter 24 INFORMATION_SCHEMA Tables]  
[《5.1 Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J》][5.1 Driver_Datasource Class Names_ URL Syntax and Configuration Properties for Connector_J]

[5.1 Driver_Datasource Class Names_ URL Syntax and Configuration Properties for Connector_J]: https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html
[Chapter 24 INFORMATION_SCHEMA Tables]: https://dev.mysql.com/doc/refman/5.7/en/information-schema.html
[Connector_J does not retrieve the table comment in a InnoDB table]: https://bugs.mysql.com/bug.php?id=65213
[Retrieve mysql table comment using DatabaseMetaData]: https://stackoverflow.com/questions/14146230/retrieve-mysql-table-comment-using-databasemetadata