test3.java

用途： 自动查询所有数据库的所有表

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

public class test3 {
	public static void main(String[] args) {
		//获取数据库基本信息
		DBAccess db = DBAccess.getDBAccess("Northwind");
		try {
			DatabaseMetaData dbmd=db.getConnection().getMetaData( );
			
			System.out.println("DB Name:"+dbmd.getDatabaseProductName()); 
			System.out.println("DB Version:"+dbmd.getDatabaseProductVersion()); 
           	System.out.println("Driver Name:"+dbmd.getDriverName()); 
            
		} catch (SQLException e1) {
			// TODO 自动生成 catch 块
			e1.printStackTrace();
		}
		db.closeDB();
		
/*		//设置为 非 单例模式，因为单例模式一次运行中只有一个连接（只能访问固定的数据库）
		DBAccess.setOnlyOneMode(false);
		test3 test = new test3();
		
		//数据库
		ArrayList list1 = test.getDatabaseList();		
		System.out.println("\n获得如下数据库列表：");
		for(int i=0;i<list1.size();i++){
			System.out.println((String)list1.get(i));
		}
		
		//表
		ArrayList list2 = test.getTableList("Northwind");		
		System.out.println("\n获得数据库的表的如下列表：");		
		for(int j=0;j<list2.size();j++){
			System.out.println((String)list2.get(j));
		}
		
		//字段
		ArrayList list = test.getTableInfoList("Northwind","Orders");		
		System.out.println("\n获得数据库的表的字段信息如下列表：");		
		for(int k=0;k<list.size();k++){
			ColumnType key = (ColumnType)list.get(k);
			System.out.println("字段名："+key.getName());
			System.out.println("字段类型："+key.getType());
		}*/		
		
		//自动查询所有数据库的所有表，列出所有表的字段的属性：字段名＋字段类型
		showAll();
	}
	
	private static void showAll(){
		//设置为 非 单例模式，因为单例模式一次运行中只有一个连接（只能访问固定的数据库）
		DBAccess.setOnlyOneMode(false);
		test3 test = new test3();
		
		ArrayList list1 = test.getDatabaseList();	
		for(int i=0;i<list1.size();i++){//
			String databaseName = (String)list1.get(i);
			System.out.println("数据库："+databaseName+"");
			System.out.println("------------------------------------------------------------------");
			
			ArrayList list2 = test.getTableList(databaseName);		
			for(int j=0;j<list2.size();j++){
				String tableName = (String)list2.get(j);
				System.out.println("表："+tableName+"");
				System.out.println("--------------------------------");
				
				ArrayList list = test.getTableInfoList(databaseName,"["+tableName+"]");		
				for(int k=0;k<list.size();k++){
					ColumnType key = (ColumnType)list.get(k);
					System.out.println("字段名称："+key.getName());
					System.out.println("字段类型："+key.getType()+"\n");
				}
				
				//System.out.println("\n");
			}
			
			System.out.println("\n\n");
		}
	}

	//获得数据库列表
	private ArrayList getDatabaseList(){
		String databaseName = "master";
		ArrayList list = new ArrayList();
		DBAccess db = DBAccess.getDBAccess(databaseName);
		ResultSet rst = db.query("select name from sysdatabases");
		try {
			while(rst.next()) {
				list.add(rst.getString(1));
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		
		db.closeDB();
		
		return list;		
	}
	
	
	//获得指定数据库的表列表
	private ArrayList getTableList(String databaseName){
		ArrayList list = new ArrayList();
		DBAccess db = DBAccess.getDBAccess(databaseName);		
		try {
			DatabaseMetaData dbmd=db.getConnection().getMetaData( );
			//获取数据库表名结果集
			ResultSet mrs = dbmd.getTables( null, null, null,new String[] { "TABLE" } );
			while(mrs.next()){
				list.add(mrs.getString(3));  //table is in column 3
			}	
			mrs.close();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		
		db.closeDB();
		
		return list;		
	}
	
	
	//获得指定数据库的表的字段列表 ResultSet.getMetaData()方法
	private ArrayList getTableInfoList(String databaseName,String tableName){
		ArrayList list = new ArrayList();
		DBAccess db = DBAccess.getDBAccess(databaseName);	
		
		try {			
			ResultSet rstable = db.query("select top 0 * from "+tableName+"");
			ResultSetMetaData rsmd = rstable.getMetaData();
			
			for(int i=1; i<rsmd.getColumnCount(); i++){
				String name = rsmd.getColumnName(i);
				String type = rsmd.getColumnTypeName(i);
				
				ColumnType key = new ColumnType();
				key.setName(name);
				key.setType(type);
				
				list.add(key);
			}

            rstable.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		db.closeDB();
		
		return list;		
	}
	
/*	//获得指定数据库的表的字段列表
	private ArrayList getTableInfoList(String databaseName,String tableName){
		ArrayList list = new ArrayList();
		DBAccess db = DBAccess.getDBAccess(databaseName);		
		try {
			DatabaseMetaData dbmd=db.getConnection().getMetaData( );
			//获得表的结构
			ResultSet results = dbmd.getColumns(null, null,tableName, null);

            while (results.next()) {   
            	String name = results.getString("COLUMN_NAME").toString().trim();
            	String type;

            	int dbType = results.getInt ("DATA_TYPE");  
				
            	switch (dbType){
            	case Types.VARCHAR:
            		type="String";
            		break;
            	case Types.INTEGER:
            		type="int";
            		break;
            	case Types.FLOAT:
            		type="float";
            		break;
            	case Types.DOUBLE:
				case Types.REAL: 
					type="double";
					break; 
				case Types.DATE:
				case Types.TIME:
				case Types.TIMESTAMP:
					type="date";
					break;
				default:
					type ="unkonw type";
					break;
            	};     

            	ColumnType key = new ColumnType();
				key.setName(name);
				key.setType(type);
				list.add(key);
            }
            results.close();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}
		
		db.closeDB();
		
		return list;		
	}*/


}