JDBC Mysql Connection 获取不到表的Comments

前言

通过Connection获取当前Schema下所有表的信息，获取不到表的注释

---

一、怎么获取表信息

先获取JDBC的链接，调用connection.getMetaData()获取链接元数据信息，之后通过

ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[]) throws SQLException;

获取表的信息

二、出现的问题

遍历ResultSet结果，获取每个表的信息

protected ColumnDefinition createTablesFields() {
        Field[] fields = new Field[10];
        fields[0] = new Field("", "TABLE_CAT", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 255);
        fields[1] = new Field("", "TABLE_SCHEM", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0);
        fields[2] = new Field("", "TABLE_NAME", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 255);
        fields[3] = new Field("", "TABLE_TYPE", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 5);
        fields[4] = new Field("", "REMARKS", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0);
        fields[5] = new Field("", "TYPE_CAT", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0);
        fields[6] = new Field("", "TYPE_SCHEM", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0);
        fields[7] = new Field("", "TYPE_NAME", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0);
        fields[8] = new Field("", "SELF_REFERENCING_COL_NAME", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0);
        fields[9] = new Field("", "REF_GENERATION", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0);
        return new DefaultColumnDefinition(fields);
    }

通过ResultSet.getString(“TABLE_NAME”)获取表明，通过ResultSet.getString(“REMARKS”)获取表注释，结果表注释获取不到

三、分析

1. 分析DatabaseMetaData

注意到java.sql.DatabaseMetaData在mysql有两个实现：
com.mysql.cj.jdbc.DatabaseMetaData和com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema
分析两个类getTables的实现，发现DatabaseMetaData执行的sql语句是SHOW FULL TABLES，DatabaseMetaDataUsingInfoSchema执行的sql语句是SELECT ... FROM INFORMATION_SCHEMA.TABLES。
到这就很明显了，出现问题的时候，DatabaseMetaData的实现类是DatabaseMetaData，我们要改变方法，让其实现DatabaseMetaDataUsingInfoSchema。

2. 分析怎么获取DatabaseMetaData

追踪代码

@Override
    public java.sql.DatabaseMetaData getMetaData() throws SQLException {
        return getMetaData(true, true);
    }

    private java.sql.DatabaseMetaData getMetaData(boolean checkClosed, boolean checkForInfoSchema) throws SQLException {
        if (checkClosed) {
            checkClosed();
        }

        com.mysql.cj.jdbc.DatabaseMetaData dbmeta = com.mysql.cj.jdbc.DatabaseMetaData.getInstance(getMultiHostSafeProxy(), this.database, checkForInfoSchema,
                this.nullStatementResultSetFactory);

        if (getSession() != null && getSession().getProtocol() != null) {
            dbmeta.setMetadataEncoding(getSession().getServerSession().getCharacterSetMetadata());
            dbmeta.setMetadataCollationIndex(getSession().getServerSession().getMetadataCollationIndex());
        }

        return dbmeta;
    }

继续找DatabaseMetaData.getInstance()方法

protected static DatabaseMetaData getInstance(JdbcConnection connToSet, String databaseToSet, boolean checkForInfoSchema, ResultSetFactory resultSetFactory)
            throws SQLException {
        if (checkForInfoSchema && connToSet.getPropertySet().getBooleanProperty(PropertyKey.useInformationSchema).getValue()) {
            return new DatabaseMetaDataUsingInfoSchema(connToSet, databaseToSet, resultSetFactory);
        }

        return new DatabaseMetaData(connToSet, databaseToSet, resultSetFactory);
    }

这里就很明显了，checkForInfoSchema 一直为true，需要判断useInformationSchema的属性，当其为true的时候，实例化DatabaseMetaDataUsingInfoSchema，反之就实例化DatabaseMetaData。

四、解决

修改useInformationSchema为true，有两种方案

1.jdbcUrl后边追加&&useInformationSchema=true
2.Properties里面设置useInformationSchema为true

五、后续研究

在connection链接获取后续操作afterHandshake里，有下面一段代码

 // Changing defaults for 8.0.3+ server: PNAME_useInformationSchema=true
        RuntimeProperty<Boolean> useInformationSchema = this.propertySet.<Boolean>getProperty(PropertyKey.useInformationSchema);
        if (versionMeetsMinimum(8, 0, 3) && !useInformationSchema.getValue() && !useInformationSchema.isExplicitlySet()) {
            useInformationSchema.setValue(true);
        }

很明显可以看出，服务器版本在8.0.3(包含)以上的时候，代码自动添加useInformationSchema=true