JDBC获取数据库表字段信息

随着项目的需要，对于数据库支持要求越多越好，最好是Generic JDBC Connection。为此，笔者要求项目程序内只允许使用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了。查看Oracle JDBC的doc,这也是支持的该接口的，并没有任何特别的说明。那奇怪了。再在OracleJDBC的文档上看到该版本Bug列表

The following table lists the JDBC bugs addressed in this patch set:

5892966

No columns from getColumns() when includeSynonyms=true

原来这里有个特殊的，当includeSysonyms设为true时, getColumns是没有返回的。这个OracleOracle就有点不太符合JDBC的标准了。Oracle文档有这一段话:

By default, the getColumns method does not retrieve information about the columns if a synonym is specified. To enable the retrieval of information if a synonym is specified, you must call the setIncludeSynonyms method on the connection as follows:

( (oracle.jdbc.driver.OracleConnection)conn ).setIncludeSynonyms(true)

This will cause all subsequent getColumns method call on the connection to include synonyms. This is similar to setRemarksReporting. Alternatively, you can set the includeSynonymsconnection property. This is similar to the remarksReporting connection property.

这段话基本上也是讲了这个问题。针对Oracle特别的设置这是项目所不允许的，怎么办？

 

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