亚洲精品无码永久在线观看,亚洲综合网站久久久,一区二区三区久久

根據數據庫名生成數據庫結構說明

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();
		}
	}
	
}

根據數據庫名生成數據庫結構說明

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频