db2doc.java

Doc2Db.bat：用来将数据表描述文档转换成sql脚本。由于不方便解析WORD

import java.io.File;
import java.io.FileWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.ResourceBundle;

import jxl.Cell;
import jxl.CellType;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;

public class Db2Doc {
	private static String fileName;
	private static String tableNameCell;
	private static String tableCommentCell;
	private static int startRow;
	private static String enNameCol;
	private static String zhNameCol;
	private static String dataTypeCol;
	private static String nullCol;
	private static String notNullFlag;
	private static int reserveNum = 10;//默认
	private static String reserveCell;

	private static String driver;
	private static String url;
	private static String user;
	private static String password;
	private static String table;

	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception {
		readConfigFile();
		if (args == null || args.length == 0 || "1".equals(args[0])) {
			doc2Db();
		} else if ("2".equals(args[0])) {
			db2Doc();
		} else {
			System.out.println("Error params");
		}
	}

	private static void readConfigFile() throws UnsupportedEncodingException {
		ResourceBundle resourcebundle = ResourceBundle.getBundle("conf");
		fileName = getProperty(resourcebundle, "FileName");
		tableNameCell = resourcebundle.getString("TableNameCell");
		tableCommentCell = resourcebundle.getString("TableCommentCell");
		startRow = Integer.parseInt(resourcebundle.getString("StartRow"));
		enNameCol = resourcebundle.getString("EnNameCol");
		zhNameCol = resourcebundle.getString("ZhNameCol");
		dataTypeCol = resourcebundle.getString("DataTypeCol");
		nullCol = resourcebundle.getString("NullCol");
		notNullFlag = getProperty(resourcebundle, "NotNullFlag");
		try {
			reserveNum = Integer.parseInt(resourcebundle
					.getString("ReserveNum"));
		} catch (Exception e) {
		}
		reserveCell = resourcebundle.getString("ReserveCell");

		driver = resourcebundle.getString("driver");
		url = resourcebundle.getString("url");
		user = resourcebundle.getString("user");
		password = resourcebundle.getString("password");
		table = resourcebundle.getString("table");
	}

	private static String getProperty(ResourceBundle resourcebundle, String key)
			throws UnsupportedEncodingException {
		String prop = resourcebundle.getString(key);
		if (prop == null) {
			return null;
		} else {
			prop = prop.trim();
			return new String(prop.getBytes("ISO8859_1"), "GBK");
		}
	}

	private static void doc2Db() throws Exception {
		Workbook wb = Workbook.getWorkbook(new File(fileName));
		String dbFileName = fileName.substring(0, fileName.indexOf("."))
				+ ".sql";
		Sheet[] sheets = wb.getSheets();
		FileWriter filewriter = new FileWriter(new File(dbFileName));
		for (int i = 0; i < sheets.length; i++) {
			Sheet sheet = sheets[i];
			if (sheet.getRows() == 0) {
				continue;
			}
			doc2Db(filewriter, sheets[i]);
		}
		filewriter.flush();
		filewriter.close();
	}

	private static void doc2Db(FileWriter filewriter, Sheet sheet)
			throws Exception {
		String tableName = getCellContent(sheet, tableNameCell);
		String tableComment = getCellContent(sheet, tableCommentCell);
		filewriter
				.write("/*==============================================================*/\r\n");
		filewriter.write("/* Table: " + tableName
				+ "                                           */\r\n");
		filewriter
				.write("/*==============================================================*/\r\n");
		filewriter
		.write("prompt creating table "+tableComment+"......\r\n");
		filewriter.write("create table " + tableName + "  (\r\n");
		Map colComment = new LinkedHashMap();

		String keyName = "";
		String _reserveCell = getCellContent(sheet, reserveCell);
		int _reserveNum = reserveNum;
		if (_reserveCell != null && _reserveCell.length() != 0) {
			_reserveNum = Integer.parseInt(_reserveCell);
		}
		
		for (int i = startRow - 1; i < sheet.getRows(); i++) {
			Cell[] cells = sheet.getRow(i);
			String enName = getRowCell(cells, getCol(enNameCol));
			if (enName == null || enName.trim().length() == 0) {
				continue;
			}
			String zhName = getRowCell(cells, getCol(zhNameCol));
			colComment.put(enName, zhName);
			String dataType = getRowCell(cells, getCol(dataTypeCol));
			String null1 = getRowCell(cells, getCol(nullCol));
			String notnullStr = "";
			if (null1 != null && null1.equals(notNullFlag)) {
				notnullStr = " not null";
			}
			if (i == startRow - 1) {
				keyName = enName;
			}
			filewriter.write("\t" + enName + "\t\t\t" + dataType + "\t"
					+ notnullStr + ",\r\n");
		}
		
		for(int j=1;j<=_reserveNum;j++){
			String num = j<10?("0"+j):(""+j);
			filewriter.write("\tRESERVATION" + num + "\t\t\tVARCHAR2(200)\t"
					+ "" + ",\r\n");
		}
		filewriter.write("\tconstraint PK_" + tableName + " primary key ("
				+ keyName + ")\r\n");
		filewriter.write(");\r\n");
		filewriter.write("comment on table " + tableName + " is '"
				+ tableComment + "';\r\n");

		for (Iterator iterator = colComment.keySet().iterator(); iterator
				.hasNext();) {
			String col = (String) iterator.next();
			String comment = (String) colComment.get(col);
			filewriter.write("comment on column " + tableName + "." + col
					+ " is '" + comment + "';\r\n");
		}
		for(int j=1;j<=_reserveNum;j++){
			String num = j<10?("0"+j):(""+j);
			filewriter.write("comment on column " + tableName + ".RESERVATION" + num
					+ " is '保留字段" + j + "';\r\n");
		}

		filewriter.write("\r\n");
	}

	private static void db2Doc() throws Exception {
		String docFileName = (table == null || table.length() == 0 ? "AllUserTable"
				: table)
				+ ".xls";
		FileWriter filewriter = new FileWriter(new File(docFileName));
		filewriter.write("<html>\n");
		filewriter.write("<head>\n");
		filewriter
				.write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">\n");
		filewriter.write("</head>\n");
		filewriter.write("<body style='font-size:10pt'>\n");
		Connection con = null;
		try {
			con = getCon();
			String sql = "select t.table_name from user_all_tables t where t.table_name  like '%"+table.toUpperCase()+"%'";
			System.out.println(sql);
			PreparedStatement psmt = con.prepareStatement(sql);
			ResultSet rs = psmt.executeQuery();
			while (rs != null && rs.next()) {
				String tableName = rs.getString(1);
				db2Doc(filewriter, tableName, con);
			}
		} finally {
			if (con != null) {
				con.close();
			}
		}

		filewriter.write("</body>\n");
		filewriter.write("</html>\n");

		filewriter.flush();
		filewriter.close();
	}

	private static void db2Doc(FileWriter filewriter, String tableName,
			Connection con) throws Exception {
		// 获取表的中文名
		String sql = "select comments from user_tab_comments Where table_name='"
				+ tableName + "'";
		PreparedStatement psmt = con.prepareStatement(sql);
		ResultSet rs = psmt.executeQuery();
		String tableComment = tableName;
		if (rs != null && rs.next()) {
			tableComment = rs.getString(1);
		}
		if (tableComment == null) {
			tableComment = tableName;
		}

		// 字段中文注释
		sql = "select column_name,comments from User_Col_Comments WHERE table_name='"
				+ tableName + "'";
		psmt = con.prepareStatement(sql);
		rs = psmt.executeQuery();
		Map colComment = new HashMap();
		while (rs != null && rs.next()) {
			String name = rs.getString(1);
			String comment = rs.getString(2);
			if (comment == null)
				comment = name;
			colComment.put(name, comment);
		}

		sql = "select * from " + tableName;
		psmt = con.prepareStatement(sql);
		rs = psmt.executeQuery();
		ResultSetMetaData meta = rs.getMetaData();
		int colNum = meta.getColumnCount();
		List colList = new ArrayList();
		for (int i = 1; i <= colNum; i++) {
			Map colInfo = new HashMap();
			colInfo.put("Name", meta.getColumnName(i));
			colInfo.put("Comment", colComment.get(meta.getColumnName(i)));
			String type = meta.getColumnTypeName(i);

			if (type.indexOf("char") >= 0 || type.indexOf("CHAR") >= 0)
				type += "(" + meta.getColumnDisplaySize(i) + ")";
			else if (type.indexOf("decimal") >= 0
					|| type.indexOf("NUMBER") >= 0) {
				type += "("
						+ (meta.getPrecision(i) == 0 ? 10 : meta
								.getPrecision(i)) + "," + meta.getScale(i)
						+ ")";
			}
			colInfo.put("Type", type);
			if (meta.isNullable(i) == 0)
				colInfo.put("NotNull", "<FONT COLOR='RED'>N</FONT>");
			else
				colInfo.put("NotNull", "Y");

			colList.add(colInfo);
		}

		filewriter.write("<table border=1 width=600 style='font-size:10pt'>\n");
		filewriter.write("\t<tr>\n");
		filewriter.write("\t\t<td >库表名</td>\n");
		filewriter.write("\t\t<td >" + tableName + "</td>\n");
		filewriter.write("\t\t<td >表中文名</td>\n");
		filewriter.write("\t\t<td colspan='2'>" + tableComment + "</td>\n");
		filewriter.write("\t</tr>\n");
		filewriter.write("\t<tr>\n");
		filewriter.write("\t\t<td>主键</td>\n");
		Map keyInfo = (Map) colList.get(0);
		filewriter.write("\t\t<td colspan='4'>" + keyInfo.get("Comment") + "("
				+ keyInfo.get("Name") + ")</td>\n");
		filewriter.write("\t</tr>\n");
		filewriter.write("\t<tr>\n");
		filewriter.write("\t\t<td>外键</td>\n");
		filewriter.write("\t\t<td colspan='4'></td>\n");
		filewriter.write("\t</tr>\n");
		filewriter.write("\t<tr>\n");
		filewriter.write("\t\t<td>索引</td>\n");
		filewriter.write("\t\t<td colspan='4'></td>\n");
		filewriter.write("\t</tr>\n");
		filewriter.write("\t<tr>\n");
		filewriter.write("\t\t<td>关系</td>\n");
		filewriter.write("\t\t<td colspan='4'></td>\n");
		filewriter.write("\t</tr>\n");
		filewriter.write("\t<tr>\n");
		filewriter.write("\t\t<td>库表说明</td>\n");
		filewriter.write("\t\t<td colspan='4'>" + tableComment
				+ "<br><br><br></td>\n");
		filewriter.write("\t</tr>\n");
		filewriter.write("\t<tr>\n");
		filewriter
				.write("\t\t<td bgcolor='#D9D9D9' height='30' width='20%'>字段名</td>\n");
		filewriter.write("\t\t<td bgcolor='#D9D9D9' width='25%'>中文名</td>\n");
		filewriter
				.write("\t\t<td bgcolor='#D9D9D9' width='20%'>\u5B57\u6BB5\u7C7B\u578B</td>\n");
		filewriter.write("\t\t<td bgcolor='#D9D9D9' width='10%'>可否为空</td>\n");
		filewriter.write("\t\t<td bgcolor='#D9D9D9' width='25%'>约束与说明</td>\n");
		filewriter.write("\t</tr>\n");

		for (int i = 0; i < colList.size(); i++) {
			Map colInfo = (Map) colList.get(i);
			filewriter.write("\t<tr>\n");
			filewriter.write("\t\t<td>" + colInfo.get("Name") + "</td>\n");
			filewriter.write("\t\t<td>" + colInfo.get("Comment") + "</td>\n");
			filewriter.write("\t\t<td>" + colInfo.get("Type") + "</td>\n");
			filewriter.write("\t\t<td>" + colInfo.get("NotNull") + "</td>\n");
			filewriter
					.write("\t\t<td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>\n");
			filewriter.write("\t</tr>\n");
		}

		filewriter.write("</table>\n");
		rs.close();
		psmt.close();
		filewriter.write("</table>\n");

		filewriter
				.write("<br><br><br><br>\n");
	}

	private static Connection getCon() throws Exception {
		Class.forName(driver);
		Connection connection = DriverManager
				.getConnection(url, user, password);
		return connection;
	}

	/**
	 * 将EXCEL的列字符转换成列数字
	 * 
	 * @param colName
	 *            A/B/C
	 * @return
	 * @throws Exception
	 */
	private static int getCol(String colName) {
		colName = colName.toUpperCase();
		int length = colName.length();
		char chr;
		int num = 0;
		for (int i = 0; i < length; i++) {
			chr = colName.charAt(i);
			num += (chr - 'A' + 1) * Math.pow(26, length - i - 1);
		}
		return num - 1;
	}

	/**
	 * 
	 * @param cell
	 * @return
	 */
	private static String getCellContent(Sheet sheet, String cell) {
		String col = cell.substring(0, 1);
		String row = cell.substring(1);
		return sheet.getCell(getCol(col), Integer.parseInt(row) - 1)
				.getContents().trim();
	}

	/**
	 * 获取Excel某行的某一列的值
	 * 
	 * @param row
	 * @param index
	 * @return
	 */
	public static String getRowCell(Cell[] row, int index) {
		if (row == null || row.length == 0) {
			return null;
		}
		if (index >= row.length) {
			return null;
		}
		String data = "";
		if (row[index].getType() == CellType.NUMBER) {
			NumberCell numc = (NumberCell) row[index];
			data = new Double(numc.getValue()).toString();
		} else {
			data = row[index].getContents();
		}
		if (data != null) {
			data = data.trim();
		}
		return data;

	}
}