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