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();
				}
			}
		}
	}
}