如何快速的为现有数据库建立数据字典？
大部分项目在验收时都需要向客户提供一份详细的数据字典，而编写数据字典是一件既耗时又耗力的事情。为了解决这个问题，提高工作效率，本人写了一个工具类。该工具类能够根据现有的数据库快速的生成对应的数据字典，它可以为我们完成80%的工作量，我们只需要做剩下的20%的工作就可以了。该工具类目前支持Oracle、SqlServer数据库，操作简单，快捷。
1、以下是部分关键代码：
public class MetadataUtil {private Connection cn = null;private String catalog = null;//SqlServer useprivate String schemaPattern = "AGENTSKY";//Oracle usepublic MetadataUtil()throws Exception{String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@localhost:1521:CRM"; String uid = "agentsky"; String pwd = "agentsky"; Class.forName(driver); this.cn = DriverManager.getConnection(url,uid,pwd);}private String getTablePrimaryKeys(String tableName){try{DatabaseMetaData dbmd = cn.getMetaData();ResultSet rs = dbmd.getPrimaryKeys(catalog, schemaPattern, tableName);StringBuffer sb = new StringBuffer(",");while(rs.next()){sb.append(rs.getString("COLUMN_NAME") + ",");}rs.close();return sb.toString();}catch(Exception ex){return "";}}private boolean containFieldType(String fieldType){List types = new ArrayList();types.add("CHAR");types.add("NCHAR");types.add("NVARCHAR");types.add("VARCHAR");types.add("VARCHAR2");return types.contains(fieldType.toUpperCase());}/** * 取得表的备注信息 */private Map<String, String> getTableComments()throws Exception{Map<String, String> colMap = new HashMap<String, String>();StringBuffer sb = new StringBuffer();sb.append("select TABLE_NAME,TABLE_TYPE,COMMENTS from user_tab_comments");PreparedStatement pstm = cn.prepareStatement(sb.toString());ResultSet rs = pstm.executeQuery();while(rs.next()){colMap.put(rs.getString("TABLE_NAME").toUpperCase(), rs.getString("COMMENTS"));} rs.close(); pstm.close();return colMap;}/** * 取得表字段的备注信息 */private Map<String, String> getColumnComments(String tableName)throws Exception{Map<String, String> colMap = new HashMap<String, String>();StringBuffer sb = new StringBuffer();sb.append(" select TABLE_NAME,COLUMN_NAME,COMMENTS from user_col_comments ");sb.append(" where upper(TABLE_NAME)=upper('" + tableName + "') ");PreparedStatement pstm = cn.prepareStatement(sb.toString());ResultSet rs = pstm.executeQuery();while(rs.next()){colMap.put(rs.getString("COLUMN_NAME").toUpperCase(), rs.getString("COMMENTS"));} rs.close(); pstm.close();return colMap;}public void createTableMetadata(String fileName){try{if(fileName == null || fileName.trim().length() == 0){throw new IllegalArgumentException("argument fileName can not be null");}File file = new File(fileName);//delete old fileif(file.exists() && file.isFile()) file.delete();//create sheetFileOutputStream out = new FileOutputStream(file);WritableWorkbook book = Workbook.createWorkbook(out);WritableSheet sheet = book.createSheet("数据字典",0);//表备注Map<String, String> tableMap = getTableComments();DatabaseMetaData dbmd = cn.getMetaData();String[] types = {"TABLE"};ResultSet rs = dbmd.getTables(catalog ,schemaPattern, null, types);int rowIndex = 0;int tableCount = 0;while(rs.next()){try{String tableName = rs.getString("TABLE_NAME");if(tableName.indexOf("=")!=-1) continue;tableCount++;System.out.println(tableCount + "、" + tableName + " doing...");//表字段备注信息Map<String, String> colMap = getColumnComments(tableName);//表备注String tableComment = tableMap.get(tableName);if(CommonUtil.isNotEmpty(tableComment)){tableComment = "：" + tableComment;}else{tableComment = CommonUtil.trim(tableComment);}//表名sheet.mergeCells(0,rowIndex,6,rowIndex); //合并单元格，6数字要与表头的cell个数一致sheet.addCell(new Label(0,rowIndex,tableName + tableComment));rowIndex++;//表头sheet.addCell(new Label(0,rowIndex,"序号"));sheet.addCell(new Label(1,rowIndex,"字段名"));sheet.addCell(new Label(2,rowIndex,"字段描述"));sheet.addCell(new Label(3,rowIndex,"字段类型"));sheet.addCell(new Label(4,rowIndex,"主键"));sheet.addCell(new Label(5,rowIndex,"可空"));sheet.addCell(new Label(6,rowIndex,"备注"));rowIndex++;//主键String strPrimaryKeys = getTablePrimaryKeys(tableName);Statement stm = cn.createStatement();stm.setMaxRows(1);ResultSet rsColumn = stm.executeQuery("select * from " + tableName);ResultSetMetaData rsmd = rsColumn.getMetaData();int recordIndex = 1;for(int i=1;i<=rsmd.getColumnCount();i++){sheet.addCell(new Label(0,rowIndex,String.valueOf(recordIndex))); //序号sheet.addCell(new Label(1,rowIndex,rsmd.getColumnName(i))); //字段名sheet.addCell(new Label(2,rowIndex,colMap.get(rsmd.getColumnName(i).toUpperCase()))); //描述//字段类型String fieldType = rsmd.getColumnTypeName(i);if(containFieldType(fieldType)){fieldType += "(" + String.valueOf(rsmd.getColumnDisplaySize(i)) + ")";}sheet.addCell(new Label(3,rowIndex,fieldType));//是否主键if(strPrimaryKeys.indexOf("," + rsmd.getColumnName(i) + ",") != -1){sheet.addCell(new Label(4,rowIndex,"Y"));}else{sheet.addCell(new Label(4,rowIndex,""));}//是否可空sheet.addCell(new Label(5,rowIndex,(rsmd.isNullable(i)==1)?"":"N"));//备注sheet.addCell(new Label(6,rowIndex,""));rowIndex++;recordIndex++;}rowIndex += 2;rsColumn.close();stm.close();}catch(Exception e){e.printStackTrace();}}rs.close();book.write();book.close();}catch(Exception ex){ex.printStackTrace();}finally{try{if(cn != null)cn.close();}catch(Exception e){e.printStackTrace();}}}public static void main(String[] args) {try{System.out.println("start...");MetadataUtil md = new MetadataUtil();md.createTableMetadata("c:\\agentsky_audit.xls");System.out.println("end");}catch(Exception ex){ex.printStackTrace();}}}?