日期:2014-05-16 浏览次数:20490 次
大部分项目在验收时都需要向客户提供一份详细的数据字典,而编写数据字典是一件既耗时又耗力的事情。为了解决这个问题,提高工作效率,本人写了一个工具类。该工具类能够根据现有的数据库快速的生成对应的数据字典,它可以为我们完成80%的工作量,我们只需要做剩下的20%的工作就可以了。该工具类目前支持Oracle、SqlServer数据库,操作简单,快捷。
1、以下是部分关键代码:
public class MetadataUtil { private Connection cn = null; private String catalog = null;//SqlServer use private String schemaPattern = "AGENTSKY";//Oracle use public 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 file if(file.exists() && file.isFile()) file.delete(); //create sheet FileOutputStream 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.addCel