getmetadata.java

本代码以J2SE 5.0为开发环境

package book.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 获取数据库和表的元数据
 * 使用DatabaseMetaData获得数据库的元数据
 * 使用ResultSetMetaData获得表的元数据
 */
public class GetMetadata {
	/**
	 * 获得数据库的元数据
	 * @param con	与数据库的连接
	 */
	public static void showDatabaseMetadata(Connection con){
		try {
			// 得到数据库的元数据
			DatabaseMetaData md = con.getMetaData();
			System.out.println("数据库" + md.getURL() + "的元数据如下：");
			
			// 显示元数据信息
			System.out.println("驱动: " + md.getDriverName());
			System.out.println("驱动版本号: " + md.getDriverVersion());
			System.out.println("登陆用户名: " + md.getUserName());
			System.out.println("数据库产品名: " + md.getDatabaseProductName());
			System.out.println("数据库产品版本号: " + md.getDatabaseProductVersion());
			System.out.println("支持的SQL关键字: ");
			System.out.println(md.getSQLKeywords());
			System.out.println("操作数字的函数: ");
			System.out.println(md.getNumericFunctions());
			System.out.println("操作字符串的函数: ");
			System.out.println(md.getStringFunctions());
			System.out.println("系统函数: ");
			System.out.println(md.getSystemFunctions());
			System.out.println("时间和日期函数: ");
			System.out.println(md.getTimeDateFunctions());
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 显示数据表的元数据，主要是列的信息
	 * @param con	与数据库的连接
	 * @param tableName	 数据表名
	 */
	public static void showTableMetadata(Connection con, String tableName){
		String sql = "SELECT * FROM " + tableName;
		Statement sm = null;
		try {
			// 首先获得表的所有数据
			sm = con.createStatement();
			ResultSet rs = sm.executeQuery(sql);
			
			// 得到结果集的元数据
			ResultSetMetaData md = rs.getMetaData();
			
			System.out.println("数据表" + tableName + "的元数据如下：");
			// 表的列数
			int columnCount = md.getColumnCount();
			System.out.println("column count: " + columnCount);
			System.out.println();
			StringBuffer sb = new StringBuffer("");
			sb.append("sn\tname\t\t").append("type\t\t");
			sb.append("scale\t").append("isNullable");
			System.out.println(sb);
			sb.delete(0, sb.length());
			// 输出列的属性信息
			for (int i=1; i<=columnCount; i++){
				sb.append(i).append("\t");
				sb.append(md.getColumnName(i)).append("\t\t");
				sb.append(md.getColumnTypeName(i)).append("\t\t");
				sb.append(md.getScale(i)).append("\t");
				sb.append(md.isNullable(i));
				System.out.println(sb);
				sb.delete(0, sb.length());
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			// 关闭Statement
			if (sm != null){
				try {
					sm.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		}
	}

	public static void main(String[] args) throws ClassNotFoundException,
			SQLException {
		String dbName = "studentdb";
		String tableName = "student_basic";
		String userName = "test";
		String password = "test";

		Connection con = null;
		try {
			// 获得数据库连接
			con = DBConnector.getMySQLConnection(null, null, null, dbName,
					userName, password);
			// 显示数据库的元信息
			GetMetadata.showDatabaseMetadata(con);
			System.out.println();
			// 显示数据表的元信息
			GetMetadata.showTableMetadata(con, tableName);
		} catch (ClassNotFoundException e1) {
			throw e1;
		} catch (SQLException e2) {
			throw e2;
		} finally {
			// 关闭数据库连接
			if (con != null) {
				try {
					con.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
		}
	}
}