J2EE --- JDBC获取数据库元数据信息

字段信息     

       字段在表里就是一个Column,关于Column在JDBC里面有20多个参数来描述它，称为元数据,Metadata。包括：

1.                TABLE_CAT String => table catalog (may be null)

2.                TABLE_SCHEM String => table schema (may be null)

3.                TABLE_NAME String => table name

4.                COLUMN_NAME String => column name

5.                DATA_TYPE int => SQL type from java.sql.Types

6.                TYPE_NAME String => Data source dependent type name, for a UDT the type name is fully qualified

7.                COLUMN_SIZE int => column size.

8.                BUFFER_LENGTH is not used.

9.                DECIMAL_DIGITS int => the number of fractional digits. Null is returned for data types where DECIMAL_DIGITS is not applicable.

10.            NUM_PREC_RADIX int => Radix (typically either 10 or 2)

11.            NULLABLE int => is NULL allowed.

o                                            columnNoNulls - might not allow NULL values

o                                            columnNullable - definitely allows NULL values

o                                            columnNullableUnknown - nullability unknown

12.            REMARKS String => comment describing column (may be null)

13.            COLUMN_DEF String => default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be null)

14.            ……

    通常关注的是COLUMN_NAME、DATA_TYPE、TYPE_NAME等。

getColumns()获取字段信息

       JDBC里有DatabaseMetadata接口，通过它可以拿到数据库的元数据，也就是数据库的相关描述信息。果然，getColumns()就可以拿到表所有的字段信息：

Connection conn = dbms.getConnection();

DatabaseMetaData dmd = conn.getMetaData();

ResultSet rs = dmd.getColumns( null, “%”, strTableName, “%”);

Whiel( rs.next() )

{

    String strFieldName = Rs.getString( 4 );

    String strFieldType = Rs.getString( 5 );

}

       这种方式的特点是不需要去访问表内数据，看上去也很简洁。在mysql, postgresql中都很顺利，但很可惜，在连Oracle(JDBC 10.2.0.4)的时候，rs.next()为false了。

ResultSetMetaData获取字段信息

除了直接查看数据库元数据，还可以通过访问表数据来获取记录集元数据。利用ResultMetaData来获取字段信息是比较好的方式，无论表内是否有数据都可返回字段信息，同时测试^①发现在实验数据库当中都是可行的。

       Connection conn = dbms.getConnection();

       DatabaseMetaData dmd = conn.getMetaData();

      

       Statement st = conn.createStatement();

       String sql = "SELECT * FROM "+table;

       ResultSet rs = st.executeQuery(sql);

       ResultSetMetaData rsmd = rs.getMetaData();

             

       for( int i=1; i<=rsmd.getColumnCount(); i++ )

       {

           String field = rsmd.getColumnName(i);

           fields.add( field );

          

           String type = Integer.toString( rsmd.getColumnType(i) ); //5--DATA_TYPE int => SQL type from java.sql.Types

           SqlType sqlT = SqlMapper.mapId( type );

           type = sqlT.sName;

           fieldTypes.add( type );

       }

 

 

注：

①测试数据库：Mckoi 1.0.3, MySQL 5.0, PostgreSQL 8.3, Oracle 10g r2, Oracle 10g XE