概要： 使用jdbc 如果在不知道表结构的情况下，如何读出表信息？

使用ResultSetMetaData;

然后使用getColumnType 获取column 类型

使用getColumnName 获取column名字

根据类型，使用ResultSet 的getInt("column1")....获取每个字段的值

本文使用 Vector 做为容器，把拿到的查询结果，临时放在容器内。

1.   数据库准备

     a. create database study;

　　b. create table 

 

CREATE TABLE `test` (   `id` int(11) NOT NULL DEFAULT '0',   `name` varchar(10) DEFAULT NULL,   `birthday` datetime DEFAULT NULL,   `score` double DEFAULT NULL,   `info` text,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

mysql> desc test;

+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| id       | int(11)     | NO   | PRI | 0       |       |

| name     | varchar(10) | YES  |     | NULL    |       |

| birthday | datetime    | YES  |     | NULL    |       |

| score    | double      | YES  |     | NULL    |       |

| info     | text        | YES  |     | NULL    |       |

+----------+-------------+------+-----+---------+-------+

　　c. 插入几条数据

mysql> insert into test values(20131026,'Marry','1983-10-18 21:11:13',65.5,'she

is so nice');

2. 下载mysql jdbc connector

3.创建相应的类

import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.util.Scanner;public class DBConnection {    public static Connection getDBConnection() throws Exception {       try{        Connection con = null;        Scanner input=new Scanner(System.in);        System.out.println("please enter the IP");        String ip=input.next();        if(ip.matches("\\d{1,3}.\\d{1,3}.\\d{1,3}.\\d{1,3}"))        {            System.out.println("Your IP is:\n"+ip);        }        else        {            ip="127.0.0.1";            System.out.println("Invaild IP address use default:\n"+ip);        }        System.out.println("please enter the ODBC port");        int port=input.nextInt();        if(1000

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.sql.Types;import java.util.Vector;public class mysql {    static void runquery() throws SQLException    {        Connection con=null ;                         long start=System.currentTimeMillis();          //count runtime        ResultSetMetaData resultMetaData;        try{                        con = DBConnection.getDBConnection();            if(con==null){                System.out.println("can't open DBConnection");            }            con.setAutoCommit(false);                          System.out.println("enter the sql you want excute\n eg select * from test;");               Statement stmt = con.createStatement();               String sql=new Scanner(System.in).nextLine();                            ResultSet rs =stmt.executeQuery(sql);              resultMetaData=rs.getMetaData();              int cols = resultMetaData.getColumnCount();  //get the count of all the coulums ,this will be 5                 Vector currentRow = new Vector();                           while(rs.next())              {                             for (int j = 1; j < cols; j++) {                       switch (resultMetaData.getColumnType(j))                     //translate the column of table type to java type then write to vector                        {                      case Types.VARCHAR:                        currentRow.addElement(rs.getString(resultMetaData.getColumnName(j)));                        break;                      case Types.INTEGER:                        currentRow.addElement(new Integer(rs.getInt(resultMetaData.getColumnName(j))));                        break;                      case Types.TIMESTAMP:                        currentRow.addElement(rs.getDate(resultMetaData.getColumnName(j)));                        break;                      case Types.DOUBLE:                          currentRow.addElement(rs.getDouble(resultMetaData.getColumnName(j)));                        break;                      case Types.FLOAT:                          currentRow.addElement(rs.getFloat(resultMetaData.getColumnName(j)));                        break;                      case Types.CLOB:                          currentRow.addElement(rs.getBlob(resultMetaData.getColumnName(j)));                          break;                      default:                          currentRow.add("error");                      }                                 }                             System.out.println(currentRow);               currentRow.clear();                         }                       }        catch (Exception e) {            e.printStackTrace();        }         con.close();         long end=System.currentTimeMillis();         System.out.println(end-start);    } public static void main(String[] args) throws SQLException {        // TODO Auto-generated method stub         System.out.println("enter the query SQL you want run\nex. select * from test ") ;         runquery();}

 

运行结果：

ex. select * from test 

please enter the IP

localhost

Invaild IP address use default:

127.0.0.1

please enter the ODBC port

3306

your port is :

3306

please enter the UserName

root

please enter the Password

3edc4rfv

jdbc:mysql://127.0.0.1:3306/study

Connected to:jdbc:mysql://127.0.0.1:3306/study

Driver MySQL Connector Java

enter the sql you want excute

 eg select * from test;

select * from test;

[20131024, Jason, 1980-05-07, 60.5]

[20131025, Young, 1988-01-09, 56.8]

[20131026, Marry, 1983-10-18, 65.5]

31977

enter the  instert SQL you want run

http://www.cnblogs.com/tobecrazy/p/3390021.html