Java 通过JDBC查询数据库表结构(字段名称,类型,长度等)

我们如何知道，我们访问的数据库的表，有哪些字段，字段的类型是什么，长度限制是什么？

在实际工作中，我就遇到过，要做动态sql，比如insert，如果是数字就不要加引号，如果是字符就要加引号，还比如做基于数据库表的代码生成器等。我们都可能需要查表的表结构。

下面我就介绍一种通过JAVA最原始的JDBC查表结构的方法。

Java 通过JDBC查询数据库表结构(字段名称,类型,长度等)

在JDBC中，PreparedStatement.executeQuery().getMetaData();后，我们可以通过ResultSetMetaData对象查询返回结果集的源数据信息，也就是表结构信息。

示例代码如下：

 

 

package astar.sutil.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;/**  * @author 鲁炬**/public class DbMetaDataUtilTest {  public static void main(String[] args) throws SQLException, ClassNotFoundException {    String user = "user";    String password = "pwd";    String jdbcDriver = "com.ibm.db2.jcc.DB2Driver";    String jdbcUrl = "jdbc:db2://localhost:50000/DBNAME";    Connection conn = null;    Class.forName(jdbcDriver);    conn = DriverManager.getConnection(jdbcUrl, user, password);    PreparedStatement pst = null;    try {      pst = conn.prepareStatement("select * from t_table where 1=2");      ResultSetMetaData rsd = pst.executeQuery().getMetaData();      for(int i = 0; i < rsd.getColumnCount(); i++) {        System.out.print("java类型："+rsd.getColumnClassName(i + 1));        System.out.print("  数据库类型:"+rsd.getColumnTypeName(i + 1));        System.out.print("  字段名称:"+rsd.getColumnName(i + 1));        System.out.print("  字段长度:"+rsd.getColumnDisplaySize(i + 1));        System.out.println();      }    } catch(SQLException e) {      throw new RuntimeException(e);    } finally {      try {        pst.close();        pst = null;      } catch(SQLException e) {        throw new RuntimeException(e);      }    }  }}

 

以上代码运行打印如下：

 

java类型：java.lang.String  数据库类型:VARCHAR  字段名称:CORP_CODE  字段名称:20java类型：java.lang.String  数据库类型:VARCHAR  字段名称:CORP_NAME  字段名称:100java类型：java.lang.String  数据库类型:VARCHAR  字段名称:SIMPLY_NAME  字段名称:50java类型：java.lang.String  数据库类型:CHAR  字段名称:CORP_LEVEL  字段名称:1java类型：java.lang.String  数据库类型:VARCHAR  字段名称:SUP_CORP_CODE  字段名称:20java类型：java.lang.String  数据库类型:VARCHAR  字段名称:PROVINCE  字段名称:8java类型：java.lang.String  数据库类型:VARCHAR  字段名称:STAT_CODE  字段名称:8java类型：java.lang.String  数据库类型:CHAR  字段名称:CORP_KIND  字段名称:4java类型：java.lang.String  数据库类型:CHAR  字段名称:IS_LEAF  字段名称:1java类型：java.lang.String  数据库类型:CHAR  字段名称:IS_USE  字段名称:1java类型：java.lang.String  数据库类型:CHAR  字段名称:IS_UPDATE  字段名称:1java类型：java.lang.Integer  数据库类型:SMALLINT  字段名称:ORDER_CODE  字段名称:6java类型：java.lang.String  数据库类型:VARCHAR  字段名称:MAP_REGIE_CODE  字段名称:20java类型：java.lang.String  数据库类型:CHAR  字段名称:IS_SUPER  字段名称:1java类型：java.lang.Integer  数据库类型:SMALLINT  字段名称:DISPLAY_ID  字段名称:6java类型：java.lang.String  数据库类型:CHAR  字段名称:IS_GIS  字段名称:1

 

更多详细的属性输出可以读一下ResultSetMetaData对象的源码。

 

/* * @(#)ResultSetMetaData.java1.33 05/12/01 * * Copyright 2006 Sun Microsystems, Inc. All rights reserved. * SUN PROPRIETARY/CONFIDENTIAL. Use is subject to license terms. */package java.sql;/** * An object that can be used to get information about the types  * and properties of the columns in a <code>ResultSet</code> object. * The following code fragment creates the <code>ResultSet</code> object rs, * creates the <code>ResultSetMetaData</code> object rsmd, and uses rsmd * to find out how many columns rs has and whether the first column in rs * can be used in a <code>WHERE</code> clause. * <PRE> * *     ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2"); *     ResultSetMetaData rsmd = rs.getMetaData(); *     int numberOfColumns = rsmd.getColumnCount(); *     boolean b = rsmd.isSearchable(1); * * </PRE> */public interface ResultSetMetaData extends Wrapper {    /**     * Returns the number of columns in this <code>ResultSet</code> object.     *     * @return the number of columns     * @exception SQLException if a database access error occurs     */    int getColumnCount() throws SQLException;    /**     * Indicates whether the designated column is automatically numbered.     *     * @param column the first column is 1, the second is 2, ...     * @return <code>true</code> if so; <code>false</code> otherwise     * @exception SQLException if a database access error occurs     */    boolean isAutoIncrement(int column) throws SQLException;    /**     * Indicates whether a column's case matters.     *     * @param column the first column is 1, the second is 2, ...     * @return <code>true</code> if so; <code>false</code> otherwise     * @exception SQLException if a database access error occurs     */    boolean isCaseSensitive(int column) throws SQLException;    /**     * Indicates whether the designated column can be used in a where clause.     *     * @param column the first column is 1, the second is 2, ...     * @return <code>true</code> if so; <code>false</code> otherwise     * @exception SQLException if a database access error occurs     */    boolean isSearchable(int column) throws SQLException;    /**     * Indicates whether the designated column is a cash value.     *     * @param column the first column is 1, the second is 2, ...     * @return <code>true</code> if so; <code>false</code> otherwise     * @exception SQLException if a database access error occurs     */    boolean isCurrency(int column) throws SQLException;    /**     * Indicates the nullability of values in the designated column.     *     * @param column the first column is 1, the second is 2, ...     * @return the nullability status of the given column; one of <code>columnNoNulls</code>,     *          <code>columnNullable</code> or <code>columnNullableUnknown</code>     * @exception SQLException if a database access error occurs     */    int isNullable(int column) throws SQLException;    /**     * The constant indicating that a     * column does not allow <code>NULL</code> values.     */    int columnNoNulls = 0;    /**     * The constant indicating that a     * column allows <code>NULL</code> values.     */    int columnNullable = 1;    /**     * The constant indicating that the     * nullability of a column's values is unknown.     */    int columnNullableUnknown = 2;    /**     * Indicates whether values in the designated column are signed numbers.     *     * @param column the first column is 1, the second is 2, ...     * @return <code>true</code> if so; <code>false</code> otherwise     * @exception SQLException if a database access error occurs     */    boolean isSigned(int column) throws SQLException;    /**     * Indicates the designated column's normal maximum width in characters.     *     * @param column the first column is 1, the second is 2, ...     * @return the normal maximum number of characters allowed as the width     *          of the designated column     * @exception SQLException if a database access error occurs     */    int getColumnDisplaySize(int column) throws SQLException;    /**     * Gets the designated column's suggested title for use in printouts and     * displays. The suggested title is usually specified by the SQL <code>AS</code>      * clause.  If a SQL <code>AS</code> is not specified, the value returned from      * <code>getColumnLabel</code> will be the same as the value returned by the      * <code>getColumnName</code> method.     *     * @param column the first column is 1, the second is 2, ...     * @return the suggested column title     * @exception SQLException if a database access error occurs     */    String getColumnLabel(int column) throws SQLException;    /**     * Get the designated column's name.     *     * @param column the first column is 1, the second is 2, ...     * @return column name     * @exception SQLException if a database access error occurs     */    String getColumnName(int column) throws SQLException;    /**     * Get the designated column's table's schema.     *     * @param column the first column is 1, the second is 2, ...     * @return schema name or "" if not applicable     * @exception SQLException if a database access error occurs     */    String getSchemaName(int column) throws SQLException;    /**     * Get the designated column's specified column size.      * 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 the      * maximum 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. 0 is returned for data types where the     * column size is not applicable.     *     * @param column the first column is 1, the second is 2, ...     * @return precision     * @exception SQLException if a database access error occurs     */    int getPrecision(int column) throws SQLException;    /**     * Gets the designated column's number of digits to right of the decimal point.     * 0 is returned for data types where the scale is not applicable.     *     * @param column the first column is 1, the second is 2, ...     * @return scale     * @exception SQLException if a database access error occurs     */    int getScale(int column) throws SQLException;    /**     * Gets the designated column's table name.      *     * @param column the first column is 1, the second is 2, ...     * @return table name or "" if not applicable     * @exception SQLException if a database access error occurs     */    String getTableName(int column) throws SQLException;    /**     * Gets the designated column's table's catalog name.     *     * @param column the first column is 1, the second is 2, ...     * @return the name of the catalog for the table in which the given column     *          appears or "" if not applicable     * @exception SQLException if a database access error occurs     */    String getCatalogName(int column) throws SQLException;    /**     * Retrieves the designated column's SQL type.     *     * @param column the first column is 1, the second is 2, ...     * @return SQL type from java.sql.Types     * @exception SQLException if a database access error occurs     * @see Types     */    int getColumnType(int column) throws SQLException;    /**     * Retrieves the designated column's database-specific type name.     *     * @param column the first column is 1, the second is 2, ...     * @return type name used by the database. If the column type is     * a user-defined type, then a fully-qualified type name is returned.     * @exception SQLException if a database access error occurs     */    String getColumnTypeName(int column) throws SQLException;    /**     * Indicates whether the designated column is definitely not writable.     *     * @param column the first column is 1, the second is 2, ...     * @return <code>true</code> if so; <code>false</code> otherwise     * @exception SQLException if a database access error occurs     */    boolean isReadOnly(int column) throws SQLException;    /**     * Indicates whether it is possible for a write on the designated column to succeed.     *     * @param column the first column is 1, the second is 2, ...     * @return <code>true</code> if so; <code>false</code> otherwise     * @exception SQLException if a database access error occurs     */    boolean isWritable(int column) throws SQLException;    /**     * Indicates whether a write on the designated column will definitely succeed.     *     * @param column the first column is 1, the second is 2, ...     * @return <code>true</code> if so; <code>false</code> otherwise     * @exception SQLException if a database access error occurs     */    boolean isDefinitelyWritable(int column) throws SQLException;    //--------------------------JDBC 2.0-----------------------------------    /**     * <p>Returns the fully-qualified name of the Java class whose instances      * are manufactured if the method <code>ResultSet.getObject</code>     * is called to retrieve a value      * from the column.  <code>ResultSet.getObject</code> may return a subclass of the     * class returned by this method.     *     * @param column the first column is 1, the second is 2, ...     * @return the fully-qualified name of the class in the Java programming     *         language that would be used by the method      * <code>ResultSet.getObject</code> to retrieve the value in the specified     * column. This is the class name used for custom mapping.     * @exception SQLException if a database access error occurs     * @since 1.2     */    String getColumnClassName(int column) throws SQLException;}