根据数据库名生成数据库结构说明

根据数据库名生成数据库结构说明

 

package com.cjm.common;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class CreateMetadata {
	private Connection cn = null;
	private List fieldTypes = null;
	private List executedTables = null;
	//private String[] types = {"TABLE", "VIEW"}; //只生成表和视图的数据字典
	private String[] types = {"TABLE"}; //只生成表和视图的数据字典
	
	private String driver;
	private String url;
	private String uid;
	private String pwd;
	private String catalog;
	private String schema;
	
	public CreateMetadata()throws Exception{
		initData();
		
		//initSqlServerDBParams();
		initOracleDBParams();
		
   		Class.forName(driver);
	   	this.cn = DriverManager.getConnection(url, uid, pwd);
	}
	
	private void initData(){
		//字符串类型
		fieldTypes = new ArrayList();
		fieldTypes.add("CHAR");
		fieldTypes.add("NCHAR");
		fieldTypes.add("VARCHAR");
		fieldTypes.add("NVARCHAR");
		fieldTypes.add("VARCHAR2");
		fieldTypes.add("NVARCHAR2");
		
		//排除以下表
		executedTables = new ArrayList();
		executedTables.add("dtproperties");
		executedTables.add("sysconstraints");
		executedTables.add("syssegments");
	}
	
	/**
	 * SqlServer数据库连接参数
	 */
	private void initSqlServerDBParams(){
		catalog = "test"; //SqlServer的数据库名
		schema = null;
		
		driver = "net.sourceforge.jtds.jdbc.Driver";
   		url = "jdbc:jtds:sqlserver://localhost:1433;DatabaseName=test";
   		uid = "test";
   		pwd = "test";
	}
	
	/**
	 * Oracle数据库连接参数
	 */
	private void initOracleDBParams(){
		catalog = null;
		schema = "GISAP"; //Oracle的用户名
		
		driver = "oracle.jdbc.driver.OracleDriver";
   		url = "jdbc:oracle:thin:@localhost:1521:ORCL";
   		uid = "gisap";
   		pwd = "1";
	}
	
	/**
	 * 取得一个表的所有主键字段
	 */
	private String getTablePrimaryKeys(String tableName){
		try{
			DatabaseMetaData dbmd = cn.getMetaData();
			ResultSet rs = dbmd.getPrimaryKeys(catalog, schema, tableName);
			StringBuffer sb = new StringBuffer(",");
			while(rs.next()){
				sb.append(rs.getString("COLUMN_NAME") + ",");
			}
			rs.close();
			
			return sb.toString();
		}catch(Exception ex){
			return "";
		}
	}
	/**
	 * 取得一个表的所有主键字段
	 */
	private String getSqlStr(String tableName){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT A.COLUMN_NAME    字段名, ");
		sql.append("        A.DATA_TYPE      数据类型, ");
		sql.append("        A.DATA_LENGTH    长度, ");
		sql.append("        A.DATA_PRECISION 整数位, ");
		sql.append("        A.DATA_SCALE     小数位, ");
		sql.append("        A.NULLABLE       允许空值, ");
		sql.append("        A.DATA_DEFAULT   缺省值, ");
		sql.append("        B.COMMENTS       备注, ");
		sql.append("        C.INDEXCOUNT     索引次数 ");
		sql.append("   FROM USER_TAB_COLUMNS A, ");
		sql.append("        USER_COL_COMMENTS B, ");
		sql.append("        (SELECT COUNT(*) INDEXCOUNT, COLUMN_NAME ");
		sql.append("           FROM USER_IND_COLUMNS ");
		sql.append("          WHERE TABLE_NAME = '"+tableName+"' ");
		sql.append("          GROUP BY COLUMN_NAME) C ");
		sql.append("  WHERE A.TABLE_NAME = B.TABLE_NAME ");
		sql.append("    AND A.COLUMN_NAME = B.COLUMN_NAME ");
		sql.append("    AND A.COLUMN_NAME = C.COLUMN_NAME(+) ");
		sql.append("    AND A.TABLE_NAME = '"+tableName+"' ");
		return sql.toString();

	}
	
	/**
	 * 生成数据字典
	 */
	public void createTableMetadata(String fileName){
		try{
			if(fileName == null || fileName.length() == 0){
				throw new IllegalArgumentException("fileName is null");
			}

			System.out.println("fileName："+fileName);			
			File file = new File(fileName);
			
			//delete old file
			if(file.exists() && file.isFile()) file.delete();
			
			//create sheet
			WritableWorkbook book = Workbook.createWorkbook(new FileOutputStream(file));
			WritableSheet sheet = book.createSheet("数据字典",0);
			
			DatabaseMetaData dbmd = cn.getMetaData();
			ResultSet rs = dbmd.getTables(catalog ,schema,"%", types);
			int rowIndex = 0;
			int tableCount = 0;
			while(rs.next()){
				try{
					String tableName = rs.getString("TABLE_NAME");
					System.out.println("tableName："+tableName);
					
					//排除表
					if(executedTables.contains(tableName.toLowerCase())) continue;

					tableCount++;
					System.out.println(tableCount + "、" + tableName + " doing...");
					
					//表名
					sheet.mergeCells(0, rowIndex, 9, rowIndex);  //合并单元格，5数字要与表头的cell个数一致
					sheet.addCell(new Label(0, rowIndex, tableCount + "、" + tableName));
					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,"允许空值"));
					sheet.addCell(new Label(7,rowIndex,"缺省值"));
					sheet.addCell(new Label(8,rowIndex,"备注说明"));
					sheet.addCell(new Label(9,rowIndex,"索引次数"));
					rowIndex++;
					
					PreparedStatement ps = null;
					ps = cn.prepareStatement(this.getSqlStr(tableName));
					ResultSet res = ps.executeQuery();
					int colCnt = res.getMetaData().getColumnCount();
					int recordIndex = 1;
					while (res.next()) {
						sheet.addCell(new Label(0,rowIndex,String.valueOf(recordIndex)));
						for (int i = 1; i <= colCnt; i++) {
							sheet.addCell(new Label(i,rowIndex,res.getString(i)));
						}
						recordIndex++;
						rowIndex++;
					}
					rowIndex += 2;
					res.close();
					ps.close();
				}catch(Exception e){
					e.printStackTrace();
				}
			}
			rs.close();
			
			System.out.println("DONE");
			
			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{
			CreateMetadata md = new CreateMetadata();
			md.createTableMetadata("C:\\temp\\md.xls");
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
	
}