JAVA连接MYSQL通过查询返回的结果集获取表结构字段类型

static void exec(ResultSet rs, PreparedStatement mstmt) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();

		int i = -1;
		int type = -1;
		try {
			for (i = 1; i <= rsmd.getColumnCount(); i++) {
				String name = rsmd.getColumnName(i);
				type = rsmd.getColumnType(i);//返回列类型对应的整形数表示。package java.sql.Type类有各类型对应的整数表示。

				switch (type) {
				case Types.BIGINT:
					mstmt.setLong(i, rs.getLong(name));
					break;
				case Types.BOOLEAN:
					mstmt.setBoolean(i, rs.getBoolean(name));
					break;
				case Types.DATE:
					mstmt.setDate(i, rs.getDate(name));
					break;
				case Types.DOUBLE:
					mstmt.setDouble(i, rs.getDouble(name));
					break;
				case Types.FLOAT:
					mstmt.setFloat(i, rs.getFloat(name));
					break;
				case Types.INTEGER:
					mstmt.setInt(i, rs.getInt(name));
					break;
				case Types.SMALLINT:
					mstmt.setInt(i, rs.getInt(name));
					break;
				case Types.TIME:
					mstmt.setTime(i, rs.getTime(name));
					break;
				case Types.TIMESTAMP:
					mstmt.setTimestamp(i, rs.getTimestamp(name));
					break;
				case Types.TINYINT:
					mstmt.setShort(i, rs.getShort(name));
					break;
				case Types.VARCHAR:
					mstmt.setString(i, rs.getString(name));
					break;
				case Types.NCHAR:
					mstmt.setString(i, rs.getNString(name));
					break;
				case Types.NVARCHAR:
					mstmt.setString(i, rs.getNString(name));
					break;
				case Types.BIT:
					mstmt.setByte(i, rs.getByte(name));
					break;
				}
			}
		} catch (Exception e) {
			System.out.println("id=" + i + ", type=" + type);
		}

		mstmt.execute();
	}

 java.sql.Type

/*
 * @(#)Types.java	1.29 06/08/29
 *
 * Copyright 2006 Sun Microsystems, Inc. All rights reserved.
 * SUN PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */

package java.sql;

/**
 * <P>The class that defines the constants that are used to identify generic
 * SQL types, called JDBC types.
 * <p>
 * This class is never instantiated.
 */
public class Types {

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>BIT</code>.
 */
	public final static int BIT 		=  -7;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>TINYINT</code>.
 */
	public final static int TINYINT 	=  -6;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>SMALLINT</code>.
 */
	public final static int SMALLINT	=   5;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>INTEGER</code>.
 */
	public final static int INTEGER 	=   4;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>BIGINT</code>.
 */
	public final static int BIGINT 		=  -5;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>FLOAT</code>.
 */
	public final static int FLOAT 		=   6;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>REAL</code>.
 */
	public final static int REAL 		=   7;


/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>DOUBLE</code>.
 */
	public final static int DOUBLE 		=   8;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>NUMERIC</code>.
 */
	public final static int NUMERIC 	=   2;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>DECIMAL</code>.
 */
	public final static int DECIMAL		=   3;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>CHAR</code>.
 */
	public final static int CHAR		=   1;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>VARCHAR</code>.
 */
	public final static int VARCHAR 	=  12;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>LONGVARCHAR</code>.
 */
	public final static int LONGVARCHAR 	=  -1;


/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>DATE</code>.
 */
	public final static int DATE 		=  91;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>TIME</code>.
 */
	public final static int TIME 		=  92;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>TIMESTAMP</code>.
 */
	public final static int TIMESTAMP 	=  93;


/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>BINARY</code>.
 */
	public final static int BINARY		=  -2;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>VARBINARY</code>.
 */
	public final static int VARBINARY 	=  -3;

/**
 * <P>The constant in the Java programming language, sometimes referred
 * to as a type code, that identifies the generic SQL type 
 * <code>LONGVARBINARY</code>.
 */
	public final static int LONGVARBINARY 	=  -4;

/**
 * <P>The constant in the Java programming language
 * that identifies the generic SQL value 
 * <code>NULL</code>.
 */
	public final static int NULL		=   0;

    /**
     * The constant in the Java programming language that indicates
     * that the SQL type is database-specific and
     * gets mapped to a Java object that can be accessed via
     * the methods <code>getObject</code> and <code>setObject</code>.
     */
	public final static int OTHER		= 1111;

        

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type
     * <code>JAVA_OBJECT</code>.
     * @since 1.2
     */
        public final static int JAVA_OBJECT         = 2000;

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type
     * <code>DISTINCT</code>.
     * @since 1.2
     */
        public final static int DISTINCT            = 2001;
	
    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type
     * <code>STRUCT</code>.
     * @since 1.2
     */
        public final static int STRUCT              = 2002;

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type
     * <code>ARRAY</code>.
     * @since 1.2
     */
        public final static int ARRAY               = 2003;

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type
     * <code>BLOB</code>.
     * @since 1.2
     */
        public final static int BLOB                = 2004;

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type
     * <code>CLOB</code>.
     * @since 1.2
     */
        public final static int CLOB                = 2005;

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type
     * <code>REF</code>.
     * @since 1.2
     */
        public final static int REF                 = 2006;
        
    /**
     * The constant in the Java programming language, somtimes referred to
     * as a type code, that identifies the generic SQL type <code>DATALINK</code>.
     *
     * @since 1.4
     */
    public final static int DATALINK = 70;

    /**
     * The constant in the Java programming language, somtimes referred to
     * as a type code, that identifies the generic SQL type <code>BOOLEAN</code>.
     *
     * @since 1.4
     */
    public final static int BOOLEAN = 16;
    
    //------------------------- JDBC 4.0 -----------------------------------
    
    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type <code>ROWID</code>
     * 
     * @since 1.6
     *
     */
    public final static int ROWID = -8;

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type <code>NCHAR</code>
     *
     * @since 1.6
     */
    public static final int NCHAR = -15;

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type <code>NVARCHAR</code>.
     *
     * @since 1.6
     */
    public static final int NVARCHAR = -9;

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type <code>LONGNVARCHAR</code>.
     *
     * @since 1.6
     */
    public static final int LONGNVARCHAR = -16;

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type <code>NCLOB</code>.
     *
     * @since 1.6
     */
    public static final int NCLOB = 2011;

    /**
     * The constant in the Java programming language, sometimes referred to
     * as a type code, that identifies the generic SQL type <code>XML</code>.
     *
     * @since 1.6 
     */
    public static final int SQLXML = 2009;

    // Prevent instantiation
    private Types() {}
}

常见数据库字段类型与java.sql.Types的对应：

Oracle与java.sql.Types的对应 

Oracle                                java.sql.Types 
blob                                     blob 
char                                     char 
clob                                     clob 
date                                    date 
number                               decimal 
long                                     varbinary 
nclob,nvarchar2                   other 
smallint                                smallint 
timestamp                            timstamp 
raw                                      varbinary 
varchar2                               varchar 

Sql server与java.sql.Types的对应 

Sql server                           java.sql.Types 
   bigint (2005,2008)                bigint 
   timstamp,binary                    binary 
   bit                                         bit 
   char,nchar,unqualified          char 
   datetime                               date 
   money,smallmoney,decimal  decimal 
   float (2005,2008)                  double 
   float(2000)                            float 
   int                                          integer 
   image                                    longvarbinary 
   text,ntext,xml                        longvarchar 

    numeric                                 numeric 
    real                                       real 
    smallint                                smallint 
    datetime,smalldatetime       timestamp 
    tinyint                                  tinyint 
    varbinary                             varbinay 
    nvarchar,varchar                 varchar 

DB2与java.sql.Types的对应 

bigint                                       bigint 
   blob                                      blob 
   character,graphic                 char 
   clob                                      clob 
   date                                     date 
   decimal                                decimal 
   double                                 double 
    integer                               integer 
    longvargraphic                   longvarchar 
    longvarchar 

real                                        real 
smallint                                 smallint 
time                                      time 
timestamp                            timestamp 
vargraphic                            varchar 
varchar 

MySQL与java.sql.Types的对应 

MySQL                          java.sql.Types 
  bigint                              bigint 
   tinyblob                         binary 
   bit                                  bit 
   enum,set,char               char 
   date,year                      date 
   decimal,numeric            decimal 
   double,real                   double 
   mediumint,int                integer 
   blob,mediumblob           blob 
   longblob 
   float                               real 

smallint                           smallint 
   time                             time 
   timestamp,datetime     timestamp 
   tinyint                           tinyint 
   varbinary,binary           varbinay 
   varchar,tinytext,     varchar 

   text                     longvarchar

 注意：此收集帖子只是部分并不完全，有少数可能因新版改变而有所不同，比如MYSQL


Sybase与java.sql.Types的对应 Sybase                            java.sql.Types    binary                                 binary    bit                                       bit    char,nchar,                            char    money,smallmoney,decimal    decimal    float                                     double     int                                       integer    image                                    longvarbinary    text                                      longvarchar numeric                                   numeric     real                                     real     smallint                              smallint     datetime,smalldatetime     timestamp     tinyint                                 tinyint     varbinar,timestamp            varbinay     nvarchar,varchar ,sysname   varchar