|
根據數據庫名生成數據庫結構說明：
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.}
本文出自：億恩科技【www.23lll.com】
服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]
|
0371-60135900
京公網安備41019702002023號
香港服務器租用
美國服務器租用
電信骨干機房
聯通骨干機房
