JDBC中的DatabaseMetaData用法

来源：互联网发布：linux下删除ftp用户编辑：程序博客网时间：2024/05/24 06:58

我们在项目连接数据库，通常我们已经知道了数据库的表结构，无需获取表的基本元信息，但是有时候为了升级或者验证一下内容我们需要访问数据库的元信息，例如我们升级的时候需要删除一些表，但是我们已经无法确定这些表是否存在（可能因为之前的开发没有严格遵守一些约定导致，同一个版本数据库的表结构竟然不同）。当然我们可以使用数据库特定的一下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) {            }        }

运行结果

0 0