导出数据库表结构到csv,word

导出数据库表结构到csv,word

package com.app.common.util.word;
import com.jacob.activeX.*;
import com.jacob.com.*;

/**
 * Microsoft Word工具类,使用jacob实现(须将相应的dll拷贝到%JAVA_HOME%/BIN下)
 * @author Administrator
 *
 */
public class MSWordManager {

	//word 文档
	private Dispatch doc;
	//word运行程序对象
	private ActiveXComponent word;
	//所有word文档集合
	private Dispatch documents;
	//选定的范围或插入点
	private Dispatch  selection;

	private boolean saveOnExit =true;

	public MSWordManager(boolean visible){
		if(word==null){
			word = new ActiveXComponent("Word.Application");
		    word.setProperty("Visible", visible);
		    if(documents == null){
		    	documents = word.getProperty("Documents").toDispatch();
		    }

		}
	}
	/**
	 * 设置退出时参数
	 * @param saveOnExit
	 */
	public void setSaveOnExit(boolean saveOnExit){
		this.saveOnExit = saveOnExit;
	}
	/**
	 * 创建一个新的word文档
	 */
	public void createNewDocument(){
		doc = Dispatch.call(documents, "Add").toDispatch();
		selection = Dispatch.get(word, "Selection").toDispatch();
	}

	/**
	 * 打开一个已存在的文档
	 */
	public void openDocument(String docPath){
		closeDocument();
		doc = Dispatch.call(documents,"Open",docPath).toDispatch();
		selection = Dispatch.get(word,"Selection").toDispatch();
	}
	/**
	 * 把选定的内容或插入点向上移动
	 * @param pos 移动的距离
	 */
	public void moveUp(int pos){
		if(selection==null){
			selection = Dispatch.get(word, "Selection").toDispatch();
		}
		for(int i=0;i<pos;i++){
			Dispatch.call(selection, "MoveUp");
		}
	}
	/**
	 * 把选定的内容或插入点向下移动
	 * @param pos 移动的距离
	 */
	public void moveDown(int pos){
		if(selection==null){
			selection = Dispatch.get(word, "Selection").toDispatch();
		}
		for(int i=0;i<pos;i++){
			Dispatch.call(selection, "MoveDown");
		}
	}

	public void moveLeft(int pos){
		if(selection==null){
			selection = Dispatch.get(word, "Selection").toDispatch();
		}
		for(int i=0;i<pos;i++){
			Dispatch.call(selection, "MoveLeft");
		}
	}

	public void moveRight(int pos){
		if(selection==null){
			selection = Dispatch.get(word, "Selection").toDispatch();
		}
		for(int i=0;i<pos;i++){
			Dispatch.call(selection, "MoveRight");
		}
	}

	public void moveStart(){
		if(selection==null){
			selection = Dispatch.get(word, "Selection").toDispatch();
		}
	    Dispatch.call(selection, "HomeKey",new Variant(6));
	}

	public void moveEnd(){
		if(selection==null){
			selection = Dispatch.get(word, "Selection").toDispatch();
		}
	    Dispatch.call(selection, "EndKey",new Variant(6));
	}
	/**
	 * 从选定内容或插入点开始查找文本
	 * @param toFindText 要查找的文本
	 * @return　true=查找到并选中该文本,false=未查找到文本
	 */
	public boolean find(String toFindText){
		if(toFindText==null||toFindText.equals(""))
			return false;
		Dispatch find = word.call(selection, "Find").toDispatch();
		Dispatch.put(find,"Text",toFindText);
		Dispatch.put(find,"Forward","True");
		Dispatch.put(find,"Format","True");
		Dispatch.put(find,"MatchCase","True");
		Dispatch.put(find,"MatchWholeWord","True");
		return Dispatch.call(find, "Execute").getBoolean();
	}

	/**
	 * 把选定内容设定为替换为新文本
	 * @param toFindText
	 * @param newText
	 * @return
	 */
	public boolean replaceText(String toFindText,String newText){
		if(find(toFindText)==false){
			return false;
		}
		Dispatch.put(selection, "Text",newText);
		return true;
	}
	/**
	 * 全局替换文本
	 * @param toFindText
	 * @param newText
	 */
	public void replaceAllText(String toFindText,String newText){
		while(find(toFindText)){
			Dispatch.put(selection, "Text",newText);
			Dispatch.call(selection, "MoveRight");
		}
	}
	/**
	 * 在当前插入点插入字符串
	 * @param newText
	 */
	public void insertText(String newText){
		this.moveRight(1);//注意：向右移动光标，如果不移动连续插入文本时，会出现覆盖的情况
		Dispatch.put(selection,"Text",newText);
		this.moveRight(1);//注意：向右移动光标，如果不移动连续插入文本时，会出现覆盖的情况
	}

	/**
	 * 将文本替换成图片
	 * @param toFindText　查找字符串
	 * @param imagePath 图片路径
	 */
	public boolean replaceImage(String toFindText,String imagePath){
		if(find(toFindText)==false){
			return false;
		}
		Dispatch.call(Dispatch.get(selection, "InLineShapes").toDispatch(), "AddPicture",imagePath);
	    return true;
	}
	/**
	 * 全局替换图片
	 *@param toFindText　查找字符串
	 *@param imagePath 图片路径
	 */
	public void replaceAllImage(String toFindText,String imagePath){
		while(find(toFindText)){
			Dispatch.call(Dispatch.get(selection, "InLineShapes").toDispatch(),"AddPicture",imagePath);
			Dispatch.call(selection, "MoveRight");
		}
	}

	/**
	 * 在当前插入点插入图片
	 * @param imagePath 图片路径
	 */
	public void insertImage(String imagePath){
		Dispatch.call(Dispatch.get(selection, "InLineShapes").toDispatch(),"AddPicture",imagePath);
	}

	/**
	 * 合并单元格
	 * @param tableIndex
	 * @param fstCellRowIdx
	 * @param fstCellColIdx
	 * @param secCellRowIdx
	 * @param secCellColIdx
	 */
	public void mergeCell(int tableIndex,int fstCellRowIdx,int fstCellColIdx,int secCellRowIdx,int secCellColIdx){
		//所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
		//要填充的表格
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
		Dispatch fstCell = Dispatch.call(table, "Cell",new Variant(fstCellRowIdx),new Variant(fstCellColIdx)).toDispatch();
		Dispatch secCell = Dispatch.call(table, "Cell",new Variant(secCellRowIdx),new Variant(secCellColIdx)).toDispatch();
		Dispatch.call(fstCell, "Merge",secCell);
	}
	/**
	 * 在指定的单元格里填写数据
	 * @param tableIndex
	 * @param cellRowIdx
	 * @param cellColIdx
	 * @param txt
	 */
	public void putTxtToCell(int tableIndex,int cellRowIdx,int cellColIdx,String txt){
		//所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
	    //要填充的表格
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
	    //要填充的单元格
		Dispatch cell = Dispatch.call(table, "Cell",new Variant(cellRowIdx),new Variant(cellColIdx)).toDispatch();
	    Dispatch.call(cell, "Select");
	    Dispatch.put(selection,"Text",txt);
	}

	/**
	 * 在当前文档拷贝数据
	 * @param toCopyText
	 */
	public void copy(String toCopyText){
		moveStart();
		if(this.find(toCopyText)){
			Dispatch textRange = Dispatch.get(selection, "Range").toDispatch();
			Dispatch.call(textRange, "Copy");
		}
	}
	/**
	 * 在当前文档粘贴剪贴板数据
	 * @param pos
	 */
	public void paste(String pos){
		moveStart();
		if(this.find(pos)){
			Dispatch textRange = Dispatch.get(selection, "Range").toDispatch();
			Dispatch.call(textRange, "Paste");
		}
	}
	/**
	 * 在当前文档指定的位置拷贝表格
	 * @param pos 当前文档指定的位置
	 * @param tableIndex 被拷贝的表格在word文档中所处的位置
	 */
	public void copyTable(String pos,int tableIndex){
		Dispatch  tables = Dispatch.get(doc, "Tables").toDispatch();
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
		Dispatch range = Dispatch.get(table, "Range").toDispatch();
		Dispatch.call(range,"Copy");
		if(this.find(pos)){
			Dispatch textRange = Dispatch.get(selection, "Range").toDispatch();
			Dispatch.call(textRange, "Paste");
		}
	}
	/**
	 * 在当前文档末尾拷贝来自另一个文档中的段落
	 * @param anotherDocPath 另一个文档的磁盘路径
	 * @param paragraphIndex　被拷贝的段落在另一文档中的序号(从1开始)
	 */
	public void copyParagrapFromAnotherDoc(String anotherDocPath,int paragraphIndex){
		//获取当前文档的内容
		Dispatch wordContent = Dispatch.get(doc,"Content").toDispatch();
		//插入特殊符定位插入点
		Dispatch.call(wordContent, "InsertAfter","$selection$");
		copyParagrapFromAnotherDoc(anotherDocPath,paragraphIndex,"$selection$");
	}

	/**
	 * 在当前文档指定位置拷贝来自另一个文档中的段落
	 * @param anotherDocPath 另一个文档的磁盘路径
	 * @param paragraphIndex　被拷贝的段落在另一个文档中的序号(从1开始)
	 * @param pos 当前文档指定的位置
	 */
	public void copyParagrapFromAnotherDoc(String anotherDocPath,
			int paragraphIndex, String pos) {
		Dispatch doc2 = null;
		try {
			doc2 = Dispatch.call(documents, "Open",anotherDocPath).toDispatch();
		    Dispatch paragraphs = Dispatch.get(doc2,"Paragraphs").toDispatch();
		    Dispatch paragraph = Dispatch.call(paragraphs, "Item",new Variant(paragraphIndex)).toDispatch();
		    Dispatch range = Dispatch.get(paragraph,"Range").toDispatch();
		    Dispatch.call(range, "Copy");
		    if(this.find(pos)){
		    	Dispatch textRange = Dispatch.get(selection, "Range").toDispatch();
		    	Dispatch.call(textRange, "Paste");
		    }
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(doc2!=null){
			  Dispatch.call(doc2, "Close",new Variant(saveOnExit));	
			  doc2=null;
			}
		}
	}
	/**
	 * 在当前文档指定的位置拷贝来自另一个文档中的图片
	 * @param anotherDocPath 另一个文档的磁盘路径
	 * @param shapeIndex　被拷贝的图片在另一个文档中的位置
	 * @param pos　当前文档指定的位置
	 */
	public void copyImageFromAnotherDoc(String anotherDocPath,int shapeIndex,String pos){
		Dispatch doc2 = null;
		try {
			doc2 = Dispatch.call(documents, "Open",anotherDocPath).toDispatch();
			Dispatch shapes = Dispatch.get(doc2, "InLineShapes").toDispatch();
			Dispatch shape = Dispatch.call(shapes, "Item",new Variant(shapeIndex)).toDispatch();
			Dispatch imageRanage=Dispatch.get(shape, "Range").toDispatch();
			Dispatch.call(imageRanage, "Copy");
			if(this.find(pos)){
				Dispatch textRange = Dispatch.get(selection, "Range").toDispatch();
				Dispatch.call(textRange, "Paste");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(doc2!=null){
				  Dispatch.call(doc2, "Close",new Variant(saveOnExit));	
				  doc2=null;
			}
		}
	}
	/**
	 * 创建表格
	 * @param numCols 列数
	 * @param numRows 行数
	 */
	public void createTable(int numCols,int numRows){
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
		Dispatch range = Dispatch.get(selection, "Range").toDispatch();
		Dispatch.call(tables,"Add",range,new Variant(numRows),new Variant(numCols)).toDispatch();
		Dispatch.call(selection, "MoveRight");
		moveEnd();
	}
	/**
	 * 在指定行前面增加行
	 * @param tableIndex word文件中的第N张表(从1开始)
	 * @param rowIndex 指定行的序号(从1开始)
	 */
	public void addTableRow(int tableIndex,int rowIndex){
		//所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
		//要填充的表格　
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
		//表格的所有行　
		Dispatch rows = Dispatch.get(table, "Rows").toDispatch();
		Dispatch row = Dispatch.call(rows, "Item",new Variant(rowIndex)).toDispatch();
	    Dispatch.call(rows, "Add",new Variant(row));
	}
	/**
	 * 在第一行前增加一行
	 * @param tableIndex word文件中的第N张表(从1开始)
	 */
	public void addFirstTableRow(int tableIndex){
		 //所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
	    //要填充的表格　
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
		//表格的所有行　
		Dispatch rows = Dispatch.get(table, "Rows").toDispatch();
		Dispatch row = Dispatch.get(rows, "First").toDispatch();
		Dispatch.call(rows, "Add",new Variant(row));
	}
	/**
	 * 在最后一行前增加一行
	 * @param tableIndex word文件中的第N张表(从1开始)
	 */
	public void addLastTableRow(int tableIndex){
		 //所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
	    //要填充的表格　
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
		//表格的所有行　
		Dispatch rows = Dispatch.get(table, "Rows").toDispatch();
		Dispatch row = Dispatch.get(rows, "Last").toDispatch();
		Dispatch.call(rows, "Add",new Variant(row));
	}
	/**
	 * 增加一行
	 * @param tableIndex word文件中的第N张表(从1开始)
	 */
	public void addRow(int tableIndex){
		 //所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
	    //要填充的表格　
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
		//表格的所有行　
		Dispatch rows = Dispatch.get(table, "Rows").toDispatch();
		Dispatch.call(rows, "Add");
	}
	/**
	 * 增加一列
	 * @param tableIndex word文件中的第N张表(从1开始)
	 */
	public void addCol(int tableIndex){
		 //所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
	    //要填充的表格　
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
		//表格的所有列　
		Dispatch cols = Dispatch.get(table, "Columns").toDispatch();
		Dispatch.call(cols, "Add").toDispatch();
		Dispatch.call(cols, "AutoFit");
	}
	/**
	 * 在指定列前面增加表格的列
	 * @param tableIndex word文件中的第N张表(从1开始)
	 * @param colIndex 指定的列序号(从1开始)
	 */
	public void addTableCol(int tableIndex,int colIndex){
		 //所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
	    //要填充的表格　
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
		//表格的所有列　
		Dispatch cols = Dispatch.get(table, "Columns").toDispatch();
		System.out.println(Dispatch.get(cols,"Count"));
		Dispatch col = Dispatch.call(cols, "Item",new Variant(colIndex)).toDispatch();
		//Dispatch col = Dispatch.get(cols, "First").toDispatch();
		Dispatch.call(cols, "Add",col).toDispatch();
		Dispatch.call(cols, "AutoFit");
	}
	/**
	 * 在第一列前增加一列
	 * @param tableIndex word文件中的第N张表(从1开始)
	 */
	public void addFirstTableCol(int tableIndex){
		 //所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
	    //要填充的表格　
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
		//表格的所有列　
		Dispatch cols = Dispatch.get(table, "Columns").toDispatch();
		System.out.println(Dispatch.get(cols,"Count"));
		Dispatch col = Dispatch.get(cols, "First").toDispatch();
		Dispatch.call(cols, "Add",col).toDispatch();
		Dispatch.call(cols, "AutoFit");
	}
	/**
	 * 在最后一列前增加一列
	 * @param tableIndex word文件中的第N张表(从1开始)
	 */
	public void addLastTableCol(int tableIndex){
		 //所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
	    //要填充的表格　
		Dispatch table = Dispatch.call(tables, "Item",new Variant(tableIndex)).toDispatch();
		//表格的所有列　
		Dispatch cols = Dispatch.get(table, "Columns").toDispatch();
		System.out.println(Dispatch.get(cols,"Count"));
		Dispatch col = Dispatch.get(cols, "Last").toDispatch();
		Dispatch.call(cols, "Add",col).toDispatch();
		Dispatch.call(cols, "AutoFit");
	}

	/**
	 * 自动调整表格 
	 */
	public void autoFitTable(){
		 //所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
		int count = Dispatch.get(tables, "Count").toInt();
		for(int i=0;i<count;i++){
			Dispatch table = Dispatch.call(tables, "Item",new Variant(i+1)).toDispatch();
			Dispatch cols = Dispatch.get(table, "Columns").toDispatch();
			Dispatch.call(cols, "AutoFit");
		}
	}
	/**
	 * 调整word里的宏以调整表格的宽度，其中宏保存在document下
	 */
	public void callWordMacro(){
		 //所有表格
		Dispatch tables = Dispatch.get(doc, "Tables").toDispatch();
		int count = Dispatch.get(tables, "Count").toInt();
		Variant vMacroName = new Variant("Normal.newMacros.tableFit");
		Variant vParam = new Variant("param1");
		Variant[] para = new Variant[]{vMacroName};
		for(int i=0;i<para.length;i++){
			Dispatch table = Dispatch.call(tables, "Item",new Variant(i+1)).toDispatch();
			Dispatch.call(table, "Select");
			Dispatch.call(word, "Run","tableFitContent");
		}
	}
	/**
	 * 设置当前选定内容的字体
	 * @param name
	 * @param bold
	 * @param italic
	 * @param underLine
	 * @param color
	 * @param size
	 */
	public void setFont(String name,boolean bold,boolean italic,boolean underLine,String color,String size){
		Dispatch font = Dispatch.get(selection, "Font").toDispatch();
		Dispatch.put(font,"Name",new Variant(name));
		Dispatch.put(font,"Bold",new Variant(bold));
		Dispatch.put(font,"Italic",new Variant(italic));
		Dispatch.put(font,"Underline",new Variant(underLine));
		Dispatch.put(font,"Color",new Variant(color));
		Dispatch.put(font,"Size",new Variant(size));
	}

	/**
	 * 文件保存或另存为
	 * @param savePath 保存或另存路径
	 */
	public void save(String savePath){
		Dispatch.call(Dispatch.call(word, "WordBasic").getDispatch(), "FileSaveAs",savePath);
	}

	/**
	 * 关闭当前word文档
	 */
	public void closeDocument() {
		if(doc!=null){
		  Dispatch.call(doc, "Save");
		  Dispatch.call(doc, "Close",new Variant(saveOnExit));
		  doc = null;
		}
	}

	/**
	 * 关闭全部应用 
	 */
	public void close(){
		this.closeDocument();
		if(word!=null){
			Dispatch.call(word, "Quit");
			word = null;
		}
		selection = null;
		documents = null;
	}

	/**
	 * 打印当前word文档
	 */
	public void printFile(){
		if(doc!=null){
			Dispatch.call(doc, "PrintOut");
		}
	}

	public static void main(String[] args) {
		MSWordManager manager = new MSWordManager(false);
		manager.createNewDocument();
        manager.insertText("this is word test!");
        manager.moveEnd();
        manager.save("c:/test.doc");
        manager.close();
	}

}

package com.app.common.util.db;

import com.app.common.util.json.GsonUtil;
/**
 * 表实体
 * @author Administrator
 *
 */
public class TableEntry implements java.io.Serializable {

	private static final long serialVersionUID = -8362167311636380415L;

	private String tableName;

	private String tableComment;

	public TableEntry() {

	}

	public TableEntry(String tableName, String tableComment) {
		this.tableName = tableName;
		this.tableComment = tableComment;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getTableComment() {
		return tableComment;
	}

	public void setTableComment(String tableComment) {
		this.tableComment = tableComment;
	}

	@Override
	public String toString() {
		return GsonUtil.toJson(this);
	}
}

package com.app.common.util.db;

import java.io.Serializable;

import com.app.common.util.json.GsonUtil;

/**
 * 主键实体
 * @author Administrator
 *
 */
public class PKEntry implements Serializable {

	private static final long serialVersionUID = 2059944768182893985L;

	private String tableName;

	private String columnName;

	private String pkName;

	public PKEntry() {

	}

	public PKEntry(String tableName, String columnName, String pkName) {
		this.tableName = tableName;
		this.columnName = columnName;
		this.pkName = pkName;
	}

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getColumnName() {
		return columnName;
	}

	public void setColumnName(String columnName) {
		this.columnName = columnName;
	}

	public String getPkName() {
		return pkName;
	}

	public void setPkName(String pkName) {
		this.pkName = pkName;
	}

	@Override
	public String toString() {
		return GsonUtil.toJson(this);
	}

}

package com.app.common.util.db;

import java.io.Serializable;

import com.app.common.util.json.GsonUtil;


public class FKEntity implements Serializable {


	private static final long serialVersionUID = 3791984051686152229L;

	private String fkTableName;

	private String fkColumnName;

	private String fkName;

	private String pkTableName;

	private String pkColumnName;

	public FKEntity(){

	}

	public FKEntity(String fkTableName,String fkColumnName,String fkName,String pkTableName,String pkColumnName){
		this.fkTableName = fkTableName;
		this.fkColumnName = fkColumnName;
		this.fkName = fkName;
		this.pkTableName = pkTableName;
		this.pkColumnName = pkColumnName;
	}

	public String getFkTableName() {
		return fkTableName;
	}

	public void setFkTableName(String fkTableName) {
		this.fkTableName = fkTableName;
	}

	public String getFkColumnName() {
		return fkColumnName;
	}

	public void setFkColumnName(String fkColumnName) {
		this.fkColumnName = fkColumnName;
	}

	public String getFkName() {
		return fkName;
	}

	public void setFkName(String fkName) {
		this.fkName = fkName;
	}

	public String getPkTableName() {
		return pkTableName;
	}

	public void setPkTableName(String pkTableName) {
		this.pkTableName = pkTableName;
	}

	public String getPkColumnName() {
		return pkColumnName;
	}

	public void setPkColumnName(String pkColumnName) {
		this.pkColumnName = pkColumnName;
	}

	@Override
	public String toString(){
		return GsonUtil.toJson(this);
	}
}

package com.app.common.util.db;

import com.app.common.util.json.GsonUtil;
/**
 * 列实体
 * @author Administrator
 *
 */
public class ColumnEntity implements java.io.Serializable {

	private static final long serialVersionUID = 1739908445348410736L;

	private String columnName;

	private String typeName;

	private int columnSize;

	private int decimalDigits;

	private int numPrecRadix;

	private String columnDef;

	private String isNullAble;

	private String pkDesc;

	private String fkDesc;

	private String columnComment;

	public ColumnEntity() {

	}

	public String getColumnName() {
		return columnName;
	}

	public void setColumnName(String columnName) {
		this.columnName = columnName;
	}

	public String getTypeName() {
		return typeName;
	}

	public void setTypeName(String typeName) {
		this.typeName = typeName;
	}

	public int getColumnSize() {
		return columnSize;
	}

	public void setColumnSize(int columnSize) {
		this.columnSize = columnSize;
	}

	public int getDecimalDigits() {
		return decimalDigits;
	}

	public void setDecimalDigits(int decimalDigits) {
		this.decimalDigits = decimalDigits;
	}

	public int getNumPrecRadix() {
		return numPrecRadix;
	}

	public void setNumPrecRadix(int numPrecRadix) {
		this.numPrecRadix = numPrecRadix;
	}

	public String getColumnDef() {
		return columnDef;
	}

	public void setColumnDef(String columnDef) {
		this.columnDef = columnDef;
	}

	public String getIsNullAble() {
		return isNullAble;
	}

	public void setIsNullAble(String isNullAble) {
		this.isNullAble = isNullAble;
	}

	public String getPkDesc() {
		return pkDesc;
	}

	public void setPkDesc(String pkDesc) {
		this.pkDesc = pkDesc;
	}

	public String getFkDesc() {
		return fkDesc;
	}

	public void setFkDesc(String fkDesc) {
		this.fkDesc = fkDesc;
	}

	public String getColumnComment() {
		return columnComment;
	}

	public void setColumnComment(String columnComment) {
		this.columnComment = columnComment;
	}

	public String toString(){
		return GsonUtil.toJson(this);
	}
}

package com.app.common.util.db;

import java.io.Serializable;
import java.util.List;

/**
 * 表列实体
 * @author Administrator
 *
 */
public class TableColumnEntry implements Serializable {


	private static final long serialVersionUID = -5971854272092205815L;

	private TableEntry table;

	private List<ColumnEntity> columns;

	public TableColumnEntry(){

	}

	public TableColumnEntry(TableEntry table,List<ColumnEntity> columns){
		this.table = table;
		this.columns = columns;
	}

	public TableEntry getTable() {
		return table;
	}

	public void setTable(TableEntry table) {
		this.table = table;
	}

	public List<ColumnEntity> getColumns() {
		return columns;
	}

	public void setColumns(List<ColumnEntity> columns) {
		this.columns = columns;
	}


}

package com.app.common.util.db;


public class DBConstants {

	public static final String[] TYPE_TABLE = { "TABLE" };

	public static final String COL_TABLE_NAME = "TABLE_NAME";

	public static final String COL_REMARKS = "REMARKS";

	public static final String COL_COMMENTS = "COMMENTS";

	public static final String COL_PK_NAME = "PK_NAME";

	public static final String COL_COLUMN_NAME = "COLUMN_NAME";

	public static final String COL_FK_NAME = "FK_NAME";

	public static final String COL_PKTABLE_NAME ="PKTABLE_NAME";

	public static final String COL_PKCOLUMN_NAME ="PKCOLUMN_NAME";

	public static final String COL_FKTABLE_NAME ="FKTABLE_NAME";

	public static final String COL_FKCOLUMN_NAME = "FKCOLUMN_NAME";

	public static final String COL_IS_NULLABLE = "NULLABLE";

	public static final String COL_TYPE_NAME = "TYPE_NAME";

	public static final String COL_COLUMN_SIZE = "COLUMN_SIZE";

	public static final String COL_DECIMAL_DIGITS = "DECIMAL_DIGITS";

	public static final String COL_NUM_PREC_RADIX = "NUM_PREC_RADIX";

	public static final String COL_COLUMN_DEF = "COLUMN_DEF";


}

package com.app.common.util.db;

import java.sql.*;

public class DBUtil {

	public static Connection getConnection(String driverClassName,String url,String user,String password){
		try {
			Class.forName(driverClassName);
			return DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static Statement getStatement(Connection conn){
		if(null!=conn){
			try {
				return conn.createStatement();
			} catch (SQLException e) {
			}
		}
		return null;
	}

	public static ResultSet getResultSet(Statement stmt,String sql){
		if(null!=stmt&&(null!=sql&&sql.length()>0)){
			try {
				return stmt.executeQuery(sql);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public static void close(Connection conn,Statement stmt,ResultSet rs){
		if(null!=rs){
			try {
				rs.close();
				rs=null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(null!=stmt){
			try {
				stmt.close();
				stmt=null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(null!=conn){
			try {
				conn.close();
				conn=null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

package com.app.common.util.db;

import java.sql.*;

/**
 * 连接包装器
 * @author Administrator
 *
 */
public class ConnectionWrapper {

	public  static final String PRODUCT_ORACLE = "ORACLE";

	public  static final String PRODUCT_MYSQL = "MYSQL";

	private Connection conn;

	private DatabaseMetaData dbMetaData;

	private String dbProductName;

	private String dataBaseType;


	public ConnectionWrapper(Connection conn) throws SQLException {
		initData(conn);
	}

	protected void initData(Connection conn) throws SQLException {
		this.conn = conn;
		this.dbMetaData = conn.getMetaData();
		this.dbProductName = this.dbMetaData.getDatabaseProductName();
		this.setDataBaseType(this.dbProductName);
	}

	protected void setDataBaseType(String dbProductName) {
		if(PRODUCT_ORACLE.equalsIgnoreCase(dbProductName)){
			this.dataBaseType = PRODUCT_ORACLE;
		}else if(PRODUCT_MYSQL.equalsIgnoreCase(dbProductName)){
			this.dataBaseType = PRODUCT_MYSQL;
		}else{
			throw new UnsupportedOperationException("unsupport this database:"+dbProductName);
		}
	}

	public Connection getConnection(){
		return this.conn;
	}

	public DatabaseMetaData getDatabaseMetaData(){
		return this.dbMetaData;
	}

	public String getDatabaseProductName(){
		return this.dbProductName;
	}

	public String getDataBaseType() {
		return this.dataBaseType;
	}

}

package com.app.common.util.db;

import java.sql.*;
import java.util.*;

public class DataBaseTableGenerator {

	private ConnectionWrapper connWrapper;

	public DataBaseTableGenerator(ConnectionWrapper connWrapper){
		if(null!=connWrapper){
			this.connWrapper = connWrapper;
		}else{
			throw new  NullPointerException("the connWrapper can't be null.");
		}

	}

	public List<TableColumnEntry> buildTables(){
		List<TableColumnEntry> result = new ArrayList<TableColumnEntry>();
		ResultSet talbesRS = null;
		try {
			talbesRS = this.getUserTables();
			this.handleTables(result,talbesRS);
		} catch (SQLException e) {
		}finally{
			if(null!=talbesRS){
				try {
					talbesRS.close();
				} catch (SQLException e) {
				}
				talbesRS = null;
			}
		}
		return result;
	}

	protected void handleTables(List<TableColumnEntry> result, ResultSet talbesRS) throws SQLException {
		TableEntry tableEntry =null;
		List<PKEntry> pkEntries = null;
		List<FKEntity> fkEntries = null;
		List<ColumnEntity> columnEntries =null;
		TableColumnEntry tableColumn;
		while(talbesRS.next()){
			String tableName = talbesRS.getString(DBConstants.COL_TABLE_NAME);
			String tableComment = talbesRS.getString(DBConstants.COL_REMARKS);
			if(null==tableComment||tableComment.length()==0||"null".equalsIgnoreCase(tableComment)){
				tableComment = this.getTableComment(tableName);
			}
			//获取表信息
			tableEntry = new TableEntry(tableName,tableComment);
			//获取表的主键信息
			pkEntries = this.getTablePrimaryKeys(tableName);
			//获取表的外键信息
			fkEntries = this.getTableImportedKeys(tableName);
			//获取表列信息
			columnEntries = this.getTableColumns(pkEntries, fkEntries, tableName);
			tableColumn = new TableColumnEntry(tableEntry,columnEntries);
			result.add(tableColumn);
		}
	}

	private List<PKEntry> getTablePrimaryKeys(String tableName) {
		List<PKEntry> pkEntries = new ArrayList<PKEntry>();
		DatabaseMetaData dmd = connWrapper.getDatabaseMetaData();
		ResultSet primaryKeyRS = null;
		try{
			primaryKeyRS = dmd.getPrimaryKeys(dmd.getConnection().getCatalog(), dmd.getUserName(),tableName );
		    while(primaryKeyRS.next()){
		        	String pkName = primaryKeyRS.getString(DBConstants.COL_PK_NAME);
		        	String tabName = primaryKeyRS.getString(DBConstants.COL_TABLE_NAME);
		        	String colName = primaryKeyRS.getString(DBConstants.COL_COLUMN_NAME);
					PKEntry pkEntry = new PKEntry(tabName,colName,pkName);
					pkEntries.add(pkEntry);
		     }
		}catch(SQLException e){

		}finally{
			DBUtil.close(null, null, primaryKeyRS);
		}
		return pkEntries;
	}

	protected List<FKEntity> getTableImportedKeys(String tableName) throws SQLException {
		List<FKEntity> fkEntries = new ArrayList<FKEntity>();
		DatabaseMetaData dmd = connWrapper.getDatabaseMetaData();
		// 获取导入键信息(获取由给定表的外键列（表导入的主键）引用的主键列的描述)
		ResultSet importedKeyRS = null;
		try{
			importedKeyRS = dmd.getImportedKeys(dmd.getConnection().getCatalog(), dmd.getUserName(), tableName);
			while (importedKeyRS.next()) {
				String fkName = importedKeyRS.getString(DBConstants.COL_FK_NAME);
				String pkTableName = importedKeyRS.getString(DBConstants.COL_PKTABLE_NAME);
				String pkColumnName = importedKeyRS.getString(DBConstants.COL_PKCOLUMN_NAME);
				String fkTableName = importedKeyRS.getString(DBConstants.COL_FKTABLE_NAME);
				String fkColumnName = importedKeyRS.getString(DBConstants.COL_FKCOLUMN_NAME);
				FKEntity fkEntry = new FKEntity(fkTableName,fkColumnName,fkName,pkTableName,pkColumnName);
				fkEntries.add(fkEntry);
			}
		}catch(SQLException e){

		}finally{
			DBUtil.close(null, null, importedKeyRS);
		}
		return fkEntries;
	}

	protected List<ColumnEntity>  getTableColumns(List<PKEntry> pkEntries,List<FKEntity> fkEntries ,String tableName){
		List<ColumnEntity> columnEntries = new ArrayList<ColumnEntity>();
		System.out.println("获取列信息：");

		List<String> pkColumnNames = new ArrayList<String>();
		if(null!=pkEntries&&pkEntries.size()>0){
			for(PKEntry pkEntry : pkEntries){
				pkColumnNames.add(pkEntry.getColumnName());
			}
		}

		List<String> fkColumnNames = new ArrayList<String>();
		if(null!=fkEntries&&fkEntries.size()>0){
			for(FKEntity fkEntry : fkEntries){
				fkColumnNames.add(fkEntry.getFkColumnName());
			}
		}

		DatabaseMetaData dmd = connWrapper.getDatabaseMetaData();
		ResultSet columnRS = null;
		ColumnEntity columnEntry = null;

		try{
			columnRS = dmd.getColumns(dmd.getConnection().getCatalog(),dmd.getUserName(),tableName, null);
			while (columnRS.next()) {
				String columnName = columnRS.getString(DBConstants.COL_COLUMN_NAME);
				String typeName = columnRS.getString(DBConstants.COL_TYPE_NAME);
				int columnSize = columnRS.getInt(DBConstants.COL_COLUMN_SIZE);
				int decimalDigits = columnRS.getInt(DBConstants.COL_DECIMAL_DIGITS);
				int numPrecRadix = columnRS.getInt(DBConstants.COL_NUM_PREC_RADIX);
				String columnDef = columnRS.getString(DBConstants.COL_COLUMN_DEF);
				String isNullAble = columnRS.getString(DBConstants.COL_IS_NULLABLE);
				String columnComment = columnRS.getString(DBConstants.COL_REMARKS);
				columnEntry = new ColumnEntity();
				columnEntry.setColumnName(columnName);
				columnEntry.setTypeName(typeName);
				columnEntry.setColumnSize(columnSize);
				columnEntry.setDecimalDigits(decimalDigits);
				columnEntry.setNumPrecRadix(numPrecRadix);
				columnEntry.setColumnDef(columnDef);
				columnEntry.setIsNullAble(isNullAble);
				if(pkColumnNames.size()>0&&pkColumnNames.contains(columnName)){
					//设置主键信息
					for(int i=0,size=pkColumnNames.size();i<size;i++){
						if(pkColumnNames.get(i).equals(columnName)){
							PKEntry pkEntry = pkEntries.get(i);
							columnEntry.setPkDesc(this.getPKDesc(pkEntry));
							break;
						}
					}
				}
				if(fkColumnNames.size()>0){
					//设置外键信息
					for(int i=0,size=fkColumnNames.size();i<size;i++){
						if(fkColumnNames.get(i).equals(columnName)){
							FKEntity fkEntry = fkEntries.get(i);
							columnEntry.setFkDesc(this.getFKDesc(fkEntry));
							break;
						}
					}

				}
				if(null==columnComment||columnComment.length()==0||"null".equalsIgnoreCase(columnComment)){
					columnComment = this.getColumnComment(tableName,columnName);
				}
				columnEntry.setColumnComment(columnComment);
				System.out.println(columnEntry);
				// 加入实体
				columnEntries.add(columnEntry);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, null, columnRS);
		}
		return columnEntries;
	}
	private String getColumnComment(String tableName, String columnName) {
		String result = "";
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String getColCommentSQL = "";
		try {
			conn = connWrapper.getConnection();
			stmt = conn.createStatement();
			if(ConnectionWrapper.PRODUCT_ORACLE.equals(connWrapper.getDataBaseType())){
				getColCommentSQL = "SELECT TABLE_NAME,COLUMN_NAME,COMMENTS FROM  USER_COL_COMMENTS t WHERE t.TABLE_NAME='"+tableName+"' and t.COLUMN_NAME='"+columnName+"'";;
				rs = DBUtil.getResultSet(stmt,getColCommentSQL);
				rs.next();
				result = rs.getString(DBConstants.COL_COMMENTS);
			}else if(ConnectionWrapper.PRODUCT_MYSQL.equals(connWrapper.getDataBaseType())){
				throw new UnsupportedOperationException();
			}else{
				throw new UnsupportedOperationException();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			DBUtil.close(null, stmt, rs);
		}
		return result;
	}

	protected String getPKDesc(PKEntry pkEntry) {
		return pkEntry.getPkName();
	}

	protected String getFKDesc(FKEntity fkEntry){
		return fkEntry.getFkName()+"["+fkEntry.getPkTableName()+"."+fkEntry.getPkColumnName()+"]";
	}

	protected String getTableComment(String tableName) {
		String result = "";
		Statement stmt = null;
		ResultSet rs = null;
		String getTableCommentSQL = "";
		try {
			stmt = connWrapper.getConnection().createStatement();
			if(ConnectionWrapper.PRODUCT_ORACLE.equals(connWrapper.getDataBaseType())){
				getTableCommentSQL = "SELECT TABLE_NAME,TABLE_TYPE,COMMENTS FROM  USER_TAB_COMMENTS t WHERE t.TABLE_TYPE='TABLE' AND TABLE_NAME='"+tableName+"'";
				rs = DBUtil.getResultSet(stmt,getTableCommentSQL);
				rs.next();
				result = rs.getString(DBConstants.COL_COMMENTS);
			}else if(ConnectionWrapper.PRODUCT_MYSQL.equals(connWrapper.getDataBaseType())){
				throw new UnsupportedOperationException();
			}else{
				throw new UnsupportedOperationException();
			}
		} catch (SQLException e) {
		}
		finally{
			DBUtil.close(null, stmt, rs);
		}
		return result;
	}

	protected ResultSet getUserTables() throws SQLException {
		DatabaseMetaData dmd = connWrapper.getDatabaseMetaData();
		if(ConnectionWrapper.PRODUCT_ORACLE.equals(connWrapper.getDataBaseType())){
			// Oracle 不支持Catalog,但支持Schema,Schema值为数据库用户名
		   return dmd.getTables(null, dmd.getUserName(), null, DBConstants.TYPE_TABLE);
		}
		else if(ConnectionWrapper.PRODUCT_MYSQL.equals(connWrapper.getDataBaseType())){
			// MySQL不支持Catalog,但支持Schema,Schema值为数据库名，但其JDBC驱动的实现是将Catalog作为Schema用的
		   return dmd.getTables(dmd.getConnection().getCatalog(), null, null,DBConstants.TYPE_TABLE);
		}
		throw new UnsupportedOperationException("unsupport this database:"+connWrapper.getDatabaseProductName());
	}

}

package com.app.common.util.db;

import java.util.List;

public abstract class DataBaseTableExporter {

	protected static final String[] COLUMN_HEADER_DEFAULT = { "列名", "数据类型","默认值", "允许为空", "主键", "外键", "注释" };


	public void export(List<TableColumnEntry> tableColumnEntries) {
		if (null == tableColumnEntries || tableColumnEntries.size() == 0) {
            throw new IllegalArgumentException("The input parameter tableColumnEntries can't be null or zero length.");
		}
		this.doStart(tableColumnEntries);
		for (TableColumnEntry tableColumnEntry : tableColumnEntries) {
			writeTableHeader(tableColumnEntry.getTable());
			writeColumnHeader(TableColumnEntry.class);
			writeColumns(tableColumnEntry.getColumns());
		}
		this.doEnd(tableColumnEntries);

	}

	public abstract void doEnd(List<TableColumnEntry> tableColumnEntries);

	public abstract void doStart(List<TableColumnEntry> tableColumnEntries);

	public abstract void writeColumnHeader(
			Class<TableColumnEntry> tableColumnClass);

	public abstract void writeColumns(List<ColumnEntity> columns);

	public abstract void writeTableHeader(TableEntry table);

	public int getColumnHeaderLength() {
		return COLUMN_HEADER_DEFAULT.length;
	}
}

package com.app.common.util.db;

import java.io.FileWriter;
import java.io.IOException;
import java.util.List;


import au.com.bytecode.opencsv.CSVWriter;


public class DefDataBaseTableExporter extends DataBaseTableExporter {

	private String csvFilePath ;

	private CSVWriter writer = null;

	private long start = 0l;

	private long end = 0l;


	public DefDataBaseTableExporter(String csvFilePath) throws IOException{
		this.csvFilePath = csvFilePath;
		writer = new CSVWriter(new FileWriter(csvFilePath));
	}

	@Override
	public void doStart(List<TableColumnEntry> tableColumnEntries) {
		start = System.currentTimeMillis();
		System.out.println("total tables:"+tableColumnEntries.size());
	}

	@Override
	public void writeColumnHeader(Class<TableColumnEntry> tableColumnClass) {
		writer.writeNext(COLUMN_HEADER_DEFAULT);
	}

	@Override
	public void writeColumns(List<ColumnEntity> columns) {
		if(null!=columns&&columns.size()>0){
			String[] result = new String[super.getColumnHeaderLength()];
			for (ColumnEntity columnEntry : columns) {
				result[0] = columnEntry.getColumnName();
				result[1]=columnEntry.getTypeName()+"("+columnEntry.getColumnSize()+")";
				result[2] = columnEntry.getColumnDef();
				result[3] = columnEntry.getIsNullAble();
				result[4] = columnEntry.getPkDesc();
				result[5] = columnEntry.getFkDesc();
				result[6] = columnEntry.getColumnComment();
				writer.writeNext(result);
			}
		}

	}
	@Override
	public void writeTableHeader(TableEntry table) {
		writer.writeNext(new String[]{table.getTableName()+"("+table.getTableComment()+")"});
	}
	@Override
	public void doEnd(List<TableColumnEntry> tableColumnEntries) {
		System.out.println("The file save at:"+csvFilePath+"");
		end = System.currentTimeMillis();
		System.out.println("Exporting take:"+(end-start)+" ms");
	}

}

package com.app.common.util.db;

import java.util.List;

import com.app.common.util.word.MSWordManager;


public class WordDataBaseTableExporter extends DataBaseTableExporter {

	private String wordFilePath;

	private MSWordManager wordManager;

	private int tableIndex = 1;

	private static final int ONE_ROW = 1;

    private long start = 0l;

	private long end = 0l;

	private int cellRowIdx = 1;

	private int cellColIdx = 1;

	public WordDataBaseTableExporter(String wordFilePath){
		this.wordFilePath = wordFilePath;
		this.wordManager = new MSWordManager(true);
		if(null!=this.wordManager){
			this.wordManager.createNewDocument();
		}

	}

	@Override
	public void doStart(List<TableColumnEntry> tableColumnEntries) {
		start = System.currentTimeMillis();
		System.out.println("total tables:"+tableColumnEntries.size());

	}

	@Override
	public void writeColumnHeader(Class<TableColumnEntry> tableColumnClass) {
        wordManager.createTable(super.getColumnHeaderLength(), ONE_ROW);
        for(int i=0;i<super.getColumnHeaderLength();i++){
        	wordManager.putTxtToCell(tableIndex, cellRowIdx, (i+1), COLUMN_HEADER_DEFAULT[i]);
        }
	}

	@Override
	public void writeColumns(List<ColumnEntity> columns) {
		for(ColumnEntity columnEntry : columns){
			  wordManager.addRow(tableIndex);
			  //行加1
			  cellRowIdx++;
			  wordManager.putTxtToCell(tableIndex, cellRowIdx, cellColIdx++, columnEntry.getColumnName());
			  wordManager.putTxtToCell(tableIndex, cellRowIdx, cellColIdx++, columnEntry.getTypeName()+"("+columnEntry.getColumnSize()+")");
			  wordManager.putTxtToCell(tableIndex, cellRowIdx, cellColIdx++, columnEntry.getColumnDef());
			  wordManager.putTxtToCell(tableIndex, cellRowIdx, cellColIdx++, columnEntry.getIsNullAble());
			  wordManager.putTxtToCell(tableIndex, cellRowIdx, cellColIdx++, columnEntry.getPkDesc());
			  wordManager.putTxtToCell(tableIndex, cellRowIdx, cellColIdx++, columnEntry.getFkDesc());
			  wordManager.putTxtToCell(tableIndex, cellRowIdx, cellColIdx++, columnEntry.getColumnComment());
			  //列从1开始
			  cellColIdx =1;
		}
		cellRowIdx = 1;
		cellColIdx = 1;
		tableIndex++;
		//注意：此处需要移动光标，否则下个表的内容会写入到最后一个单元格内
		wordManager.moveEnd();
	}

	@Override
	public void writeTableHeader(TableEntry table) {

		wordManager.insertText(table.getTableName()+"("+table.getTableComment()+")");
	}

	@Override
	public void doEnd(List<TableColumnEntry> tableColumnEntries) {
		wordManager.moveEnd();
		wordManager.save("c:/test.doc");
		wordManager.close();
		System.out.println("The file save at:"+wordFilePath+"");
		end = System.currentTimeMillis();
		System.out.println("Exporting take:"+(end-start)+" ms");
	}

}