Java获取Mysql表结构和获取ResultSet对象中列的类型和属性信息的方法

ゝ一纸荒年。 2024-05-09 09:04 38阅读 0赞

## 获取表结构的方法 ##

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/MALL?user=root&password=123456");
    	DatabaseMetaData meta = (DatabaseMetaData) conn.getMetaData();
    
    	ResultSet  rs = meta.getColumns(null, "%", "T_Mall_ReturnOrderInfo", "%");
    		
    	while (rs.next())  {  
                // table catalog (may be null)  
                String tableCat = rs.getString("TABLE_CAT");  
                // table schema (may be null)  
                String tableSchemaName = rs.getString("TABLE_SCHEM");  
                // table name  
                String tableName_ = rs.getString("TABLE_NAME");  
                // column name  
                String columnName = rs.getString("COLUMN_NAME");  
                
                // SQL type from java.sql.Types  
                int dataType = rs.getInt("DATA_TYPE");  
                
                // Data source dependent type name, for a UDT the type name is  
                // fully qualified  
                String dataTypeName = rs.getString("TYPE_NAME"); 
                System.out.println(columnName + "    " + dataTypeName);
                // table schema (may be null)  
                int columnSize = rs.getInt("COLUMN_SIZE");  
                // the number of fractional digits. Null is returned for data  
                // types where DECIMAL_DIGITS is not applicable.  
                int decimalDigits = rs.getInt("DECIMAL_DIGITS");  
                // Radix (typically either 10 or 2)  
                int numPrecRadix = rs.getInt("NUM_PREC_RADIX");  
                // is NULL allowed.  
                int nullAble = rs.getInt("NULLABLE");  
                // comment describing column (may be null)  
                String remarks = rs.getString("REMARKS");  
                // default value for the column, which should be interpreted as  
                // a string when the value is enclosed in single quotes (may be  
                // null)  
                String columnDef = rs.getString("COLUMN_DEF");  
                //                
                int sqlDataType = rs.getInt("SQL_DATA_TYPE");  
                //                
                int sqlDatetimeSub = rs.getInt("SQL_DATETIME_SUB");  
                // for char types the maximum number of bytes in the column  
                int charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");  
                // index of column in table (starting at 1)  
                int ordinalPosition = rs.getInt("ORDINAL_POSITION");  
                // ISO rules are used to determine the nullability for a column.  
                // YES --- if the parameter can include NULLs;  
                // NO --- if the parameter cannot include NULLs  
                // empty string --- if the nullability for the parameter is  
                // unknown  
                String isNullAble = rs.getString("IS_NULLABLE");  
                // Indicates whether this column is auto incremented  
                // YES --- if the column is auto incremented  
                // NO --- if the column is not auto incremented  
                // empty string --- if it cannot be determined whether the  
                // column is auto incremented parameter is unknown  
                String isAutoincrement = rs.getString("IS_AUTOINCREMENT");  
                System.out.println(tableCat + "-" + tableSchemaName + "-" + tableName_ + "-" + columnName + "-"  
                        + dataType + "-" + dataTypeName + "-" + columnSize + "-" + decimalDigits + "-" + numPrecRadix  
                        + "-" + nullAble + "-" + remarks + "-" + columnDef + "-" + sqlDataType + "-" + sqlDatetimeSub  
                        + charOctetLength + "-" + ordinalPosition + "-" + isNullAble + "-" + isAutoincrement + "-");
            }

## 字符串常量信息（序号实际与他的index值是一致的） ##

Each column description has the following columns: 
    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 whereDECIMAL_DIGITS is not applicable. 
    10.NUM_PREC_RADIX int => Radix (typically either 10 or 2) 
    11.NULLABLE int => is NULL allowed. ◦ columnNoNulls - might not allow NULL values 
    ◦ columnNullable - definitely allows NULL values 
    ◦ 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.SQL_DATA_TYPE int => unused 
    15.SQL_DATETIME_SUB int => unused 
    16.CHAR_OCTET_LENGTH int => for char types themaximum number of bytes in the column 
    17.ORDINAL_POSITION int => index of column in table(starting at 1) 
    18.IS_NULLABLE String => ISO rules are used to determine the nullability for a column. ◦ YES --- if the column can include NULLs 
    ◦ NO --- if the column cannot include NULLs 
    ◦ empty string --- if the nullability for thecolumn is unknown 
    
    19.SCOPE_CATALOG String => catalog of table that is the scopeof a reference attribute (null if DATA_TYPE isn't REF) 
    20.SCOPE_SCHEMA String => schema of table that is the scopeof a reference attribute (null if the DATA_TYPE isn't REF) 
    21.SCOPE_TABLE String => table name that this the scopeof a reference attribute (null if the DATA_TYPE isn't REF) 
    22.SOURCE_DATA_TYPE short => source type of a distinct type or user-generatedRef type, SQL type from java.sql.Types (null if DATA_TYPEisn't DISTINCT or user-generated REF) 
    23.IS_AUTOINCREMENT String => Indicates whether this column is auto incremented ◦ YES --- if the column is auto incremented 
    ◦ NO --- if the column is not auto incremented 
    ◦ empty string --- if it cannot be determined whether the column is auto incremented 
    
    24.IS_GENERATEDCOLUMN String => Indicates whether this is a generated column ◦ YES --- if this a generated column 
    ◦ NO --- if this not a generated column 
    ◦ empty string --- if it cannot be determined whether this is a generated column 
    
    
    The COLUMN_SIZE column specifies the column size for the given column.For numeric data, this is the maximum precision. For character data, this is the length in characters.For datetime datatypes, this is the length in characters of the String representation (assuming themaximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes. For the ROWID datatype,this is the length in bytes. Null is returned for data types where thecolumn size is not applicable.

## 获取ResultSet 对象中列的类型和属性信息的方法 ##

<table> 
 <tbody> 
 <tr> 
 <th colspan="2">方法摘要</th> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>String</code></td> 
 <td><code>getCatalogName(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 获取指定列的表目录名称。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>String</code></td> 
 <td><code>getColumnClassName(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 如果调用方法 <code>ResultSet.getObject</code> 从列中检索值，则返回构造其实例的 Java 类的完全限定名称。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>int</code></td> 
 <td><code>getColumnCount()</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 返回此 <code>ResultSet</code> 对象中的列数。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>int</code></td> 
 <td><code>getColumnDisplaySize(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 指示指定列的最大标准宽度，以字符为单位。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>String</code></td> 
 <td><code>getColumnLabel(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 获取用于打印输出和显示的指定列的建议标题。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>String</code></td> 
 <td><code>getColumnName(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 获取指定列的名称。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>int</code></td> 
 <td><code>getColumnType(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 检索指定列的 SQL 类型。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>String</code></td> 
 <td><code>getColumnTypeName(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 检索指定列的数据库特定的类型名称。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>int</code></td> 
 <td><code>getPrecision(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 获取指定列的小数位数。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>int</code></td> 
 <td><code>getScale(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 获取指定列的小数点右边的位数。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>String</code></td> 
 <td><code>getSchemaName(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 获取指定列的表模式。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>String</code></td> 
 <td><code>getTableName(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 获取指定列的名称。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>boolean</code></td> 
 <td><code>isAutoIncrement(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 指示是否自动为指定列进行编号，这样这些列仍然是只读的。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>boolean</code></td> 
 <td><code>isCaseSensitive(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 指示列的大小写是否有关系。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>boolean</code></td> 
 <td><code>isCurrency(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 指示指定的列是否是一个哈希代码值。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>boolean</code></td> 
 <td><code>isDefinitelyWritable(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 指示在指定的列上进行写操作是否明确可以获得成功。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>int</code></td> 
 <td><code>isNullable(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 指示指定列中的值是否可以为 null。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>boolean</code></td> 
 <td><code>isReadOnly(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 指示指定的列是否明确不可写入。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>boolean</code></td> 
 <td><code>isSearchable(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 指示是否可以在 where 子句中使用指定的列。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>boolean</code></td> 
 <td><code>isSigned(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 指示指定列中的值是否带正负号。</td> 
 </tr> 
 <tr> 
 <td style="vertical-align:top;"><code>boolean</code></td> 
 <td><code>isWritable(int column)</code> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 指示在指定的列上进行写操作是否可以获得成功。</td> 
 </tr> 
 </tbody> 
</table>