datadictionary.java

读取Oracle数据库中的所有表

package datadictionary;

import java.io.*;
import java.sql.*;
import java.util.*;

import org.jdom.*;
import org.jdom.input.SAXBuilder;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;
////////////////////////////////////////////////////////////////////////////////
/**
 * <p>Title:数据字典
 *
 * <p>Description: 连接数据库并读取数据库，获取所有表。整理输出一一对应的XML表，形成可查询的数据字典。
 *
 * <p>Copyright: Copyright (c) 2006</p>
 *
 * <p>Company: </p>
 *
 * @author
 * @version 1.0 2006.3.21
 */
public class DataDictionary {
    private String databaseDriver = " ";
    private String databaseURL = " ";
    private String userName = " ";
    private String password = " ";

    public DataDictionary() {
    }
    /**
     *连接到指定的数据库。
     *用户将自己负责数据库驱动器、数据库URL定位、以及数据库的用户名和密码的正确性。
     */
    public void connectDatabase(String databaseDriver,String databaseURL,
                                String userName,String password) throws SQLException {

        try {//注册驱动器
            Class.forName(databaseDriver);
        } catch (ClassNotFoundException ex)  {
        }

        Properties sysProps = new Properties();
        sysProps.put("user", userName);
        sysProps.put("password", password);

        Connection conn = null;
        conn = DriverManager.getConnection(databaseURL, sysProps);//连接数据库

        if(conn != null) {
            System.out.println("数据库连接成功");//测试用
            this.databaseDriver = databaseDriver;
            this.databaseURL = databaseURL;
            this.userName = userName;
            this.password = password;
        }
    }
    /**
     *退出已存在的数据库连接。形式上的退出，既只是将各数据库相关字段清空。
     */
    public void closeConnect() {
        if(isConnected()) {
            databaseDriver = " ";
            databaseURL = " ";
            userName = " ";
            password = " ";
        }
    }
    /**
     *检查是否连接到某数据库。是则返回true，否则返回false。
     */
    public boolean isConnected() {
        if(databaseURL != " ")
            return true;
        else
            return false;
    }
    /**
     *连接到指定的数据库，处理该数据库内所有的表形成一一对应的名字为"fileName"的XML表文档。
     */
    public void createXML(String fileName) throws SQLException {
        try {//注册驱动器
            Class.forName(databaseDriver);
        } catch (ClassNotFoundException ex)  {
        }

        Properties sysProps = new Properties();
        sysProps.put("user", userName);
        sysProps.put("password", password);

        Connection conn = null;
        conn = DriverManager.getConnection(databaseURL, sysProps);//连接数据库

        DatabaseMetaData dbmdOfDatabase = conn.getMetaData(); //获取数据库数据
        ResultSet rsOfTables = dbmdOfDatabase.getTables(null,null,"%",null);//获取数据库中表的集合

        Document doc = new Document();//建立XML文档
        Element root = new Element("根索引");
        doc.setRootElement(root);

        while(rsOfTables.next()) {//逐一处理数据库中所有的表
            String tableName = rsOfTables.getString(3);
            ResultSet rsOfColumns = null;
            try{//检查表的合法性，如果不合法则推出该次操作
                rsOfColumns = dbmdOfDatabase.getColumns(null, null, tableName, null);//获取当前表中所有字段
            } catch(SQLException e) {
                break;//continue;???
            }
            Element tableElement = new Element("表元素" + tableName);
            root.addContent(tableElement);

            tableName = rsOfTables.getString(3);
            String tableType = rsOfTables.getString(4);
            ResultSet rsOfPrimaryKeys = dbmdOfDatabase.getPrimaryKeys(null,null,tableName);
            rsOfPrimaryKeys.next();
            String primaryKey = rsOfPrimaryKeys.getString(6);

            Element nameOfTableElement = new Element("表名字");
            nameOfTableElement.setText(tableName);
            Element comment = new Element("注释");
            comment.setText("待定");
            Element typeOfTableElement = new Element("表类型");
            typeOfTableElement.setText(tableType);
            Element PKOfTableElement = new Element("主键");
            PKOfTableElement.setText(primaryKey);

            tableElement.addContent(nameOfTableElement);
            tableElement.addContent(comment);
            tableElement.addContent(typeOfTableElement);
            tableElement.addContent(PKOfTableElement);

            rsOfColumns.next();
            do{//逐一处当前表中所有字段
                Element columnElement = new Element("字段元素" + rsOfColumns.getString(4));
                tableElement.addContent(columnElement);

                Element columnName = new Element("字段名");
                columnName.setText(rsOfColumns.getString(4));
                comment = new Element("注释");
                comment.setText("待定");
                Element columnType = new Element("字段类型");
                columnType.setText(rsOfColumns.getString(6));
                Element columnSize = new Element("字段长度");
                columnSize.setText(new Integer(rsOfColumns.getString(7)).toString());
                Element isNullable = new Element("是否允许空值");
                isNullable.setText(rsOfColumns.getString(18));

                columnElement.addContent(columnName);
                columnElement.addContent(comment);
                columnElement.addContent(columnType);
                columnElement.addContent(columnSize);
                columnElement.addContent(isNullable);
            } while (rsOfColumns.next());
        }
        XMLOutputter outputter = new XMLOutputter(Format.getPrettyFormat().setEncoding("GB2312"));
        try {
            outputter.output(doc, new FileWriter(fileName));
        } catch (IOException ex) {
        }
        rsOfTables.close();
    }
    /**
     *将数据库中已经存在的数据字典导入，主要部分为表名与表注释，字段名与字段注释部分的对应关系。
     */
    public void guideXML(String fileName,//XML文档的名字
                         String dictionary,//要求用户正确给出当前数据库的数据字典名字
                         String tableNameInDict,//数据字典中表示表名字的字段的名字
                         String commentOfTable,//数据字典中表示表注释的字段的名字
                         String dictColumn,//要求用户正确给出当前数据库的字段部分数据字典的名字
                         String columnNameInDict,//字段的数据字典中表示字段名字的字段的名字
                         String commentOfColumn//字段的数据字典中表示字段注释的字段的名字
            ) throws SQLException {
        try {//注册驱动器
            Class.forName(databaseDriver);
        } catch (ClassNotFoundException ex)  {
        }

        Properties sysProps = new Properties();
        sysProps.put("user", userName);
        sysProps.put("password", password);

        Connection conn = null;
        conn = DriverManager.getConnection(databaseURL, sysProps);//连接数据库

        Statement stat01 = conn.createStatement();
        Statement stat02 = conn.createStatement();
        ResultSet rsOfTables = stat01.executeQuery("select "
                                                 +tableNameInDict+","
                                                 +commentOfTable+" "
                                                 +"from "
                                                 +dictionary);
        Document doc = new Document();//建立XML文档
        Element root = new Element("根索引");
        doc.setRootElement(root);

        int mm = 0;//测试用，只找10个表
        while(rsOfTables.next()&mm++<10) {
            String tableName = rsOfTables.getString(1);
            tableName = convertName(tableName);
            String commentary = rsOfTables.getString(2);

            Element tableElement = new Element("表元素" + tableName);
            root.addContent(tableElement);

            Element nameOfTableElement = new Element("表名字");
            nameOfTableElement.setText(tableName);
            Element comment = new Element("注释");
            comment.setText(commentary);
            Element typeOfTableElement = new Element("表类型");
            typeOfTableElement.setText("_");
            Element PKOfTableElement = new Element("主键");
            PKOfTableElement.setText("_");

            tableElement.addContent(nameOfTableElement);
            tableElement.addContent(comment);
            tableElement.addContent(typeOfTableElement);
            tableElement.addContent(PKOfTableElement);

            System.out.println(tableName);//测试用
            ResultSet rsOfColumns=stat02.executeQuery("select "
                                                    +columnNameInDict+","
                                                    +commentOfColumn
                                                    +" from "+dictColumn
                                                    +" where TABLE_NAME="+"'"+tableName+"'");

            while(rsOfColumns.next()) {
                Element columnElement = new Element("字段元素" + convertName(rsOfColumns.getString(1)));
                tableElement.addContent(columnElement);

                Element columnName = new Element("字段名");
                columnName.setText(rsOfColumns.getString(1));
                comment = new Element("注释");
                comment.setText(rsOfColumns.getString(2));
                Element columnType = new Element("字段类型");
                columnType.setText("_");
                Element columnSize = new Element("字段长度");
                columnSize.setText("_");
                Element isNullable = new Element("是否允许空值");
                isNullable.setText("_");

                columnElement.addContent(columnName);
                columnElement.addContent(comment);
                columnElement.addContent(columnType);
                columnElement.addContent(columnSize);
                columnElement.addContent(isNullable);
            }
        }

        XMLOutputter outputter = new XMLOutputter(Format.getPrettyFormat().setEncoding("GB2312"));
        try {
            outputter.output(doc, new FileWriter(fileName));
        } catch (IOException ex) {
        }
        rsOfTables.close();
    }
    /**
     *在所给定名字的XML文档中，收集所有表的名字、类型及主键名，
     *并将这些名字做成一个二维Vector返回。
     */
    public Vector collectTable(String fileName) {
        Document doc = new Document();
        SAXBuilder sb= new SAXBuilder();

        try {
            doc = sb.build(new FileInputStream(fileName));
        } catch (IOException ex1) {
            return null;
        } catch (JDOMException ex1) {
        }

        Element root = doc.getRootElement();
        List listOfRoot = root.getChildren();
        Iterator itr = listOfRoot.iterator();
        Vector listOfTable = new Vector();

        while(itr.hasNext()){
            Element tableElement = (Element)itr.next();

            Vector dataOfTable = new Vector();
            dataOfTable.addElement(tableElement.getChild("表名字").getText());
            dataOfTable.addElement(tableElement.getChild("注释").getText());
            dataOfTable.addElement(tableElement.getChild("表类型").getText());
            dataOfTable.addElement(tableElement.getChild("主键").getText());

            listOfTable.addElement(dataOfTable);
        }
        return listOfTable;
    }
    /**
     *在指定的XML文件中，收集指定表的字段属性，并将这些属性做成一个二维表，以Vector形式返回。
     */
    public Vector collectColumn(String fileName,String tableName) {
        Document doc = new Document();
        SAXBuilder sb= new SAXBuilder();

        try {
            doc = sb.build(new FileInputStream(fileName));
        } catch (IOException ex1) {
            return null;
        } catch (JDOMException ex1) {
        }

        Element root = doc.getRootElement();
        Element tableElement = root.getChild("表元素" + tableName);
        List listOfTable = tableElement.getChildren();
        Iterator itr = listOfTable.iterator();
        Vector listOfColumn = new Vector();

        itr.next();itr.next();itr.next();itr.next();//跳过前四个无关的结点元素

        while(itr.hasNext()){
            Element columnElement = (Element)itr.next();
            List listOfAttribute = columnElement.getChildren();
            Iterator it = listOfAttribute.iterator();
            Vector attributes = new Vector();

            while(it.hasNext()) {
                Element attribute = (Element)it.next();
                attributes.addElement(attribute.getText());
            }
            listOfColumn.addElement(attributes);
        }
        return listOfColumn;
    }
    /**
     *指定名字的XML文档中，查找一个指定名字的表。若找到，返回值为true，否则为false。
     */
    public boolean findTheTable(String fileName,String tableName) {
        if(tableName == null)
            return false;
        Document doc = new Document();
        SAXBuilder sb = new SAXBuilder();

        try {
            doc = sb.build(new FileInputStream(fileName));
        } catch (IOException ex1) {
            return false;
        } catch (JDOMException ex1) {
        }

        Element root = doc.getRootElement();
        Element tableElement = root.getChild("表元素" + tableName);