JDBC中的DatabaseMetaData用法

我们在项目连接数据库，通常我们已经知道了数据库的表结构，无需获取表的基本元信息，但是有时候为了升级或者验证一下内容我们需要访问数据库的元信息，例如我们升级的时候需要删除一些表，但是我们已经无法确定这些表是否存在（可能因为之前的开发没有严格遵守一些约定导致，同一个版本数据库的表结构竟然不同）。当然我们可以使用数据库特定的一下sql语句。例如DROP TABLE IF EXISTS tbName; 如果我们的产品支持多种数据库这样就不要方便，特别是判断表是否存在的时候，每种数据库有所不同。

首先，我们需要获得Connection，然后DatabaseMetaData meta = conn.getMetaData();

之后我们可以通过
getDatabaseProductName();
getDatabaseProductVersion();
getSchemas();
getTables(null, dbSchema, “%”, null);
getPrimaryKeys(null, “”, tableName);
getColumns(null, “”, tableName, null);
getImportedKeys(null, dbSchema, tableName);
getExportedKeys(null, dbSchema, tableName);

获取数据的名称，版本，schema，该数据库的表，表的主键，表的结构，表的索引外键等信息。

详细情况如代码所示

        try {
            DatabaseMetaData meta =  conn.getMetaData();
            String dbVendorName = meta.getDatabaseProductName();
            String dbVersion = meta.getDatabaseProductVersion();
            System.out.println("dbVendorName:" + dbVendorName + ", dbVersion:" +dbVersion);

            ResultSet rsSchema = meta.getSchemas();
            System.out.println("Schema ...");
            String dbSchema = null;
            while(rsSchema.next()) {
                //TABLE_SCHEM  
                String tableSchema = rsSchema.getString(1);
                dbSchema = tableSchema;
                //TABLE_CATALOG   
                String tableCatalog = rsSchema.getString(2);
                System.out.println("tableSchema:" + tableSchema + ", tableCatalog:" + tableCatalog );
            }

            //ResultSet rs = meta.getTables(null, "", null, null);
            ResultSet rs = meta.getTables(null, dbSchema, "%", null);
            System.out.println("Tables ...");
            while(rs.next()) {
                //TABLE_CAT 
                String tableCatalog = rs.getString(1);
                //TABLE_SCHEM 
                String tableSchema = rs.getString(2);
                //table name 
                String tableName = rs.getString(3);
                //table type
                String tableType = rs.getString(4);

                //TYPE_CAT 
                String typeCatalog = rs.getString(5);

                //TYPE_NAME
                String typeName = rs.getString(5);

                System.out.println("tableCatalog:" + tableCatalog + ", tableSchema:" + tableSchema +", tableName:" + tableName
                        + ", tableType:" +tableType
                        + ", typeCatalog:" + typeCatalog 
                        + ", typeName:" + typeName 
                        + ", tableType:" +tableType);

                ResultSet rsPrimaryKeys = meta.getPrimaryKeys(null, "", tableName);
                while(rsPrimaryKeys.next()) {
                    // COLUMN_NAME
                    String keyColName = rsPrimaryKeys.getString(4);
                    //PK_NAME String 
                    String pkName = rsPrimaryKeys.getString(6);
                    System.out.println("    PrimaryKeyColumn:" + keyColName + ", primaryKeyName:" + pkName );
                }

                ResultSet rsColumns = meta.getColumns(null, "", tableName, null);
                while(rsColumns.next()) {
                    // COLUMN_NAME
                    String columnName = rsColumns.getString(4);
                    // DATA_TYPE
                    int colType = rsColumns.getInt(5);
                    //TYPE_NAME String
                    String colTypeName = rsColumns.getString(6);
                    // COLUMN_SIZE int => column size.
                    int colSize = rsColumns.getInt(7);
                    // IS_NULLABLE 
                    String isNullAble = rsColumns.getString(18);
                    // COLUMN_DEF
                    String columnDef = rsColumns.getString(13);

                    // IS_AUTOINCREMENT 
                    String isAutoIncrement = rsColumns.getString(22);

                    System.out.println("    columnName:" + columnName + ", colTypeName:" + colTypeName +", colSize:" + colSize
                            + ", isNullAble:" +isNullAble
                            + ", columnDef:" + columnDef
                            + ", isAutoIncrement:" + isAutoIncrement);
                }

                ResultSet rsImportedKeys = meta.getImportedKeys(null, dbSchema, tableName);
                ResultSet rsExportedKeys = meta.getExportedKeys(null, dbSchema, tableName);
                System.out.println("ExportedKeys for table '" + tableName + "'");
                while(rsExportedKeys.next()) {
                    // COLUMN_NAME
                    String pkTableName = rsExportedKeys.getString("PKTABLE_NAME");
                    String pkColumnName = rsExportedKeys.getString("PKCOLUMN_NAME");
                    String fkTableName = rsExportedKeys.getString("FKTABLE_NAME");
                    String fkColumnName = rsExportedKeys.getString("FKCOLUMN_NAME");
                    String fkName = rsExportedKeys.getString("FK_NAME");
                    System.out.println("    pkTableName:" + pkTableName + ", pkColumnName:" + pkColumnName +", fkTableName:" + fkTableName
                            + ", fkColumnName:" +fkColumnName
                            + ", fkName:" + fkName);
                    System.out.println("");
                }
                System.out.println("------");
            }

        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                if (rset != null)
                    rset.close();
            }
            catch (Exception e) {
            }
            try {
                if (stmt != null)
                    stmt.close();
            }
            catch (Exception e) {
            }
            try {
                if (conn != null)
                    conn.close();
            }
            catch (Exception e) {
            }
        }

运行结果