根据数据库名生成数据库结构说明

根据数据库名生成数据库结构说明

根据数据库名生成数据库结构说明：

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. }