根據數據庫名生成數據庫結構說明

根據數據庫名生成數據庫結構說明：

1.package com.cjm.common; 
2. 
3.import java.io.File;  4.import java.io.FileOutputStream;  5.import java.sql.Connection;  6.import java.sql.DatabaseMetaData;  7.import java.sql.DriverManager;  8.import java.sql.PreparedStatement;  9.import java.sql.ResultSet;  10.import java.sql.ResultSetMetaData;  11.import java.sql.Statement;  12.import java.util.ArrayList;  13.import java.util.HashMap;  14.import java.util.List;  15. 
16.import jxl.Workbook;  17.import jxl.write.Label;  18.import jxl.write.WritableSheet;  19.import jxl.write.WritableWorkbook;  20. 
21.public class CreateMetadata {  22.    private Connection cn = null;  23.    private List fieldTypes = null;  24.    private List executedTables = null;  25.    //private String[] types = {"TABLE", "VIEW"}; //只生成表和視圖的數據字典   26.    private String[] types = {"TABLE"}; //只生成表和視圖的數據字典   27.     
28.    private String driver;  29.    private String url;  30.    private String uid;  31.    private String pwd;  32.    private String catalog;  33.    private String schema;  34.     
35.    public CreateMetadata()throws Exception{  36.        initData(); 
37.         
38.        //initSqlServerDBParams();   39.        initOracleDBParams(); 
40.         
41.        Class.forName(driver); 
42.        this.cn = DriverManager.getConnection(url, uid, pwd);  43.    } 
44.     
45.    private void initData(){  46.        //字符串類型   47.        fieldTypes = new ArrayList();  48.        fieldTypes.add("CHAR");  49.        fieldTypes.add("NCHAR");  50.        fieldTypes.add("VARCHAR");  51.        fieldTypes.add("NVARCHAR");  52.        fieldTypes.add("VARCHAR2");  53.        fieldTypes.add("NVARCHAR2");  54.         
55.        //排除以下表   56.        executedTables = new ArrayList();  57.        executedTables.add("dtproperties");  58.        executedTables.add("sysconstraints");  59.        executedTables.add("syssegments");  60.    } 
61.     
62.    /** 63.     * SqlServer數據庫連接參數
64.     */ 
65.    private void initSqlServerDBParams(){  66.        catalog = "test"; //SqlServer的數據庫名   67.        schema = null;  68.         
69.        driver = "net.sourceforge.jtds.jdbc.Driver";  70.        url = "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=test";  71.        uid = "test";  72.        pwd = "test";  73.    } 
74.     
75.    /** 76.     * Oracle數據庫連接參數
77.     */ 
78.    private void initOracleDBParams(){  79.        catalog = null;  80.        schema = "GISAP"; //Oracle的用戶名   81.         
82.        driver = "Oracle.jdbc.driver.OracleDriver";  83.        url = "jdbc:Oracle:thin:@localhost:1521:ORCL";  84.        uid = "gisap";  85.        pwd = "1";  86.    } 
87.     
88.    /** 89.     * 取得一個表的所有主鍵字段
90.     */ 
91.    private String getTablePrimaryKeys(String tableName){  92.        try{  93.            DatabaseMetaData dbmd = cn.getMetaData(); 
94.            ResultSet rs = dbmd.getPrimaryKeys(catalog, schema, tableName); 
95.            StringBuffer sb = new StringBuffer(",");  96.            while(rs.next()){  97.                sb.append(rs.getString("COLUMN_NAME") + ",");  98.            } 
99.            rs.close(); 
100.             
101.            return sb.toString();  102.        }catch(Exception ex){  103.            return "";  104.        } 
105.    } 
106.    /** 107.     * 取得一個表的所有主鍵字段
108.     */ 
109.    private String getSqlStr(String tableName){  110.        StringBuffer sql = new StringBuffer();  111.        sql.append(" SELECT A.COLUMN_NAME    字段名, ");  112.        sql.append("        A.DATA_TYPE      數據類型, ");  113.        sql.append("        A.DATA_LENGTH    長度, ");  114.        sql.append("        A.DATA_PRECISION 整數位, ");  115.        sql.append("        A.DATA_SCALE     小數位, ");  116.        sql.append("        A.NULLABLE       允許空值, ");  117.        sql.append("        A.DATA_DEFAULT   缺省值, ");  118.        sql.append("        B.COMMENTS       備注, ");  119.        sql.append("        C.INDEXCOUNT     索引次數 ");  120.        sql.append("   FROM USER_TAB_COLUMNS A, ");  121.        sql.append("        USER_COL_COMMENTS B, ");  122.        sql.append("        (SELECT COUNT(*) INDEXCOUNT, COLUMN_NAME ");  123.        sql.append("           FROM USER_IND_COLUMNS ");  124.        sql.append("          WHERE TABLE_NAME = '"+tableName+"' ");  125.        sql.append("          GROUP BY COLUMN_NAME) C ");  126.        sql.append("  WHERE A.TABLE_NAME = B.TABLE_NAME ");  127.        sql.append("    AND A.COLUMN_NAME = B.COLUMN_NAME ");  128.        sql.append("    AND A.COLUMN_NAME = C.COLUMN_NAME(+) ");  129.        sql.append("    AND A.TABLE_NAME = '"+tableName+"' ");  130.        return sql.toString();  131. 
132.    } 
133.     
134.    /** 135.     * 生成數據字典
136.     */ 
137.    public void createTableMetadata(String fileName){  138.        try{  139.            if(fileName == null || fileName.length() == 0){  140.                throw new IllegalArgumentException("fileName is null");  141.            } 
142. 
143.            System.out.println("fileName："+fileName);             144.            File file = new File(fileName);  145.             
146.            //delete old file   147.            if(file.exists() && file.isFile()) file.delete();  148.             
149.            //create sheet   150.            WritableWorkbook book = Workbook.createWorkbook(new FileOutputStream(file));  151.            WritableSheet sheet = book.createSheet("數據字典",0);  152.             
153.            DatabaseMetaData dbmd = cn.getMetaData(); 
154.            ResultSet rs = dbmd.getTables(catalog ,schema,"%", types);  155.            int rowIndex = 0;  156.            int tableCount = 0;  157.            while(rs.next()){  158.                try{  159.                    String tableName = rs.getString("TABLE_NAME");  160.                    System.out.println("tableName："+tableName);  161.                     
162.                    //排除表   163.                    if(executedTables.contains(tableName.toLowerCase())) continue;  164. 
165.                    tableCount++; 
166.                    System.out.println(tableCount + "、" + tableName + " doing...");  167.                     
168.                    //表名   169.                    sheet.mergeCells(0, rowIndex, 9, rowIndex);  //合并單元格，5數字要與表頭的cell個數一致   170.                    sheet.addCell(new Label(0, rowIndex, tableCount + "、" + tableName));  171.                    rowIndex++; 
172.                     
173.                    //表頭   174.                    sheet.addCell(new Label(0,rowIndex,"序號"));  175.                    sheet.addCell(new Label(1,rowIndex,"字段名"));  176.                    sheet.addCell(new Label(2,rowIndex,"字段類型"));  177.                    sheet.addCell(new Label(3,rowIndex,"長度"));  178.                    sheet.addCell(new Label(4,rowIndex,"整數位"));  179.                    sheet.addCell(new Label(5,rowIndex,"小數位"));  180.                    sheet.addCell(new Label(6,rowIndex,"允許空值"));  181.                    sheet.addCell(new Label(7,rowIndex,"缺省值"));  182.                    sheet.addCell(new Label(8,rowIndex,"備注說明"));  183.                    sheet.addCell(new Label(9,rowIndex,"索引次數"));  184.                    rowIndex++; 
185.                     
186.                    PreparedStatement ps = null;  187.                    ps = cn.prepareStatement(this.getSqlStr(tableName));  188.                    ResultSet res = ps.executeQuery(); 
189.                    int colCnt = res.getMetaData().getColumnCount();  190.                    int recordIndex = 1;  191.                    while (res.next()) {  192.                        sheet.addCell(new Label(0,rowIndex,String.valueOf(recordIndex)));  193.                        for (int i = 1; i <= colCnt; i++) {  194.                            sheet.addCell(new Label(i,rowIndex,res.getString(i)));  195.                        } 
196.                        recordIndex++; 
197.                        rowIndex++; 
198.                    } 
199.                    rowIndex += 2;  200.                    res.close(); 
201.                    ps.close(); 
202.                }catch(Exception e){  203.                    e.printStackTrace(); 
204.                } 
205.            } 
206.            rs.close(); 
207.             
208.            System.out.println("DONE");  209.             
210.            book.write(); 
211.            book.close(); 
212.        }catch(Exception ex){  213.            ex.printStackTrace(); 
214.        }finally{  215.            try{  216.                if(cn != null) cn.close();  217.            }catch(Exception e){  218.                e.printStackTrace(); 
219.            } 
220.        } 
221.    } 
222.     
223.    public static void main(String[] args) {  224.        try{  225.            CreateMetadata md = new CreateMetadata();  226.            md.createTableMetadata("C:\\temp\\md.xls");  227.        }catch(Exception ex){  228.            ex.printStackTrace(); 
229.        } 
230.    } 
231.     
232.} 

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]