大部分项目在验收时都需要向客户提供一份详细的数据字典，而编写数据字典是一件既耗时又耗力的事情。为了解决这个问题，提高工作效率，本人写了一个工具类。该工具类能够根据现有的数据库快速的生成对应的数据字典，它可以为我们完成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