Java 通过JDBC查询数据库表结构(字段名称,类型,长度等)
Java 通过JDBC查询数据库表结构(字段名称,类型,长度等)
Java 通过JDBC查询数据库表结构(字段名称,类型,长度等)
在JDBCPreparedStatement.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 字段名称:20 java类型：java.lang.String 数据库类型:VARCHAR 字段名称:CORP_NAME 字段名称:100 java类型：java.lang.String 数据库类型:VARCHAR 字段名称:SIMPLY_NAME 字段名称:50 java类型：java.lang.String 数据库类型:CHAR 字段名称:CORP_LEVEL 字段名称:1 java类型：java.lang.String 数据库类型:VARCHAR 字段名称:SUP_CORP_CODE 字段名称:20 java类型：java.lang.String 数据库类型:VARCHAR 字段名称:PROVINCE 字段名称:8 java类型：java.lang.String 数据库类型:VARCHAR 字段名称:STAT_CODE 字段名称:8 java类型：java.lang.String 数据库类型:CHAR 字段名称:CORP_KIND 字段名称:4 java类型：java.lang.String 数据库类型:CHAR 字段名称:IS_LEAF 字段名称:1 java类型：java.lang.String 数据库类型:CHAR 字段名称:IS_USE 字段名称:1 java类型：java.lang.String 数据库类型:CHAR 字段名称:IS_UPDATE 字段名称:1 java类型：java.lang.Integer 数据库类型:SMALLINT 字段名称:ORDER_CODE 字段名称:6 java类型：java.lang.String 数据库类型:VARCHAR 字段名称:MAP_REGIE_CODE 字段名称:20 java类型：java.lang.String 数据库类型:CHAR 字段名称:IS_SUPER 字段名称:1 java类型：java.lang.Integer 数据库类型:SMALLINT 字段名称:DISPLAY_ID 字段名称:6 java类型：java.lang.String 数据库类型:CHAR 字段名称:IS_GIS 字段名称:1
更多详细的属性输出可以读一下ResultSetMetaData对象的源码。
/* * @(#)ResultSetMetaData.java 1.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; }
