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”)获取表注释，结果表注释获取不到

注意到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。

追踪代码

@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 useInformationSchema = this.propertySet.getProperty(PropertyKey.useInformationSchema);
        if (versionMeetsMinimum(8, 0, 3) && !useInformationSchema.getValue() && !useInformationSchema.isExplicitlySet()) {
            useInformationSchema.setValue(true);
        }

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