导出ORACLE的数据字典到文本（可直接拷贝到EXCEL）

来源：互联网发布：es6 数组去重编辑：程序博客网时间：2024/05/10 07:52

马上要换工作，手头的系统要进行交接。要写很多交接文档，单单数据字典就要花很多时间，就想到用简单的方法把数据字典导出到WORD里，效率快很多。我一直认为生产力是因为人的惰性才提高的。

package mytest;
import java.io.*;
import java.sql.*;

public class DatabaseDictionary {
public static void main(String[] args) {
  try {
   FileOutputStream fileOut = new FileOutputStream("c://数据字典.txt");

   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection con = DriverManager.getConnection(
   "jdbc:oracle:thin:@192.168.132.33:1521:rnd", "user", "password");
   Statement stmt = con.createStatement();
   StringBuffer strbuf = new StringBuffer();
   strbuf.append("SELECT A.*,B.comments");
   strbuf.append(" FROM all_tab_columns A,DBA_COL_COMMENTS B");
   strbuf.append(" WHERE A.owner=B.owner");
   strbuf.append(" AND A.table_name=B.table_name");
   strbuf.append(" AND A.COLUMN_NAME=B.COLUMN_NAME");
//   owner是建立表的用户名
   strbuf.append(" AND A.owner='user'");
   strbuf.append(" ORDER BY A.TABLE_NAME");
   ResultSet rs = stmt.executeQuery(strbuf.toString());

   String tb = "";
   int k = 0;
   while (rs.next()) {
//   对每个表生成一个新的sheet,并以表名命名
    String line = "";
    if (!tb.equals(rs.getString("TABLE_NAME"))) {
     String tablename = rs.getString("TABLE_NAME");
     System.out.println(tablename);
     line = "/nTABLE/t"+tablename+" /n";
     fileOut.write(line.getBytes());

     line = "字段名/t字段类型/t字段长度/t能否为NULL/t字段说明/n";
     fileOut.write(line.getBytes());
    }
    tb = rs.getString("TABLE_NAME");
    line = rs.getString("COLUMN_NAME")+"/t";
    line = line+rs.getString("DATA_TYPE")+"/t";
    String type = rs.getString("DATA_TYPE");
    if (!type.equals("NUMBER")) {
     line = line+rs.getString("DATA_LENGTH")+"/t";
    }else {
     String scale = rs.getString("DATA_SCALE");
     if (scale == "null")
      scale = "";
     else
      scale = ","+scale;
     line = line+rs.getString("DATA_PRECISION")+scale+"/t";
    }
    line = line+rs.getString("NULLABLE")+"/t";
    line = line+rs.getString("COMMENTS")+"/n";
    fileOut.write(line.getBytes());
   }

   fileOut.close();
   rs.close();
   stmt.close();
   con.close();
  } catch (Exception e) {
  e.printStackTrace();
  }
}
}