根据数据库名生成数据库结构说明
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();
}
}
}