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

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

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