概要： 使用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
http://dev.mysql.com/downloads/connector/j/
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:
"+ip);
}
else
{
ip="127.0.0.1";
System.out.println("Invaild IP address use default:
"+ip);
}
System.out.println("please enter the ODBC port");
int port=input.nextInt();
if(1000<port && port < 50000)
{
System.out.println("your port is :
"+port);
}
else
{
port=3306;
System.out.println("Invaild port use defalt port:
"+port);
}
System.out.println("please enter the UserName");
String user=input.next();
System.out.println("please enter the Password");
String password =input.next();
String url="jdbc:mysql://"+ip+":"+port+"/"+"study";
// String url="jdbc:mysql://localhost:3306/study";
System.out.println(url);
String driver ="com.mysql.jdbc.Driver";
Class.forName(driver);
con = DriverManager.getConnection(url,"root", "3edc4rfv");
DatabaseMetaData dma = con.getMetaData();//get the database info
System.out.println("Connected to:" + dma.getURL());
System.out.println("Driver " + dma.getDriverName());
return con;
}
catch (Exception e) {
System.out.println("[Error] Connection refused: connect");
e.printStackTrace();
return null;
}
}
}
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
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
ex. 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