- 浏览: 103573 次
- 性别:
- 来自: 深圳
文章分类
最新评论
-
chifanbua:
我基本上在iteye上很少留言的 不过你的这个写的确实不错 用 ...
Oracle decode函数说明 -
shaoxuexue86:
谢谢啊
RMI 开发步骤 -
huxiaojun_198213:
RMIRegistHelper只是负责注册远程对象,与客户端所 ...
RMI 开发步骤 -
shaoxuexue86:
请问客户端程序ClientTest 与 RMIRegistHe ...
RMI 开发步骤 -
huxiaojun_198213:
本章节可能有一些细节方面翻译得不是很好,有出入的地方,还请各 ...
RMI动态类加载
导出数据库表结构到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"); } }
发表评论
-
使用Apache Commons Compress进行文件压缩
2012-11-18 19:43 2367使用Apache Commons Compress进行文件压缩 ... -
使用Apache Commons Compress进行文件解压
2012-11-18 19:39 2313使用Apache Commons Compress进行文件解压 ... -
文件选择器
2012-11-03 13:31 995经常我老婆让我帮她从她的手机导出图片,为了避免下次再打搅我玩游 ... -
文件行转换器
2012-11-03 13:06 855最近公司在做数据迁移,迁移脚本是在原有的基础上修改的.迁移完成 ...
相关推荐
Dbexport 导出数据库中所有对象的模式脚本和表数据文件,在该模式脚本中包含表等的创建脚本,同时包含了导出的表数据文件的信息。Dbimport 根据模式脚本进行导入,在导入过程中首先模式脚本创建对象,导入相应数据的...
在实际操作中,标签"oracle 数据表 word"暗示了这个过程可能涉及到数据库的权限管理,确保只有授权用户能够访问和导出数据。同时,对于大型数据表,可能需要考虑性能优化,例如限制查询的行数,避免一次性加载大量...
数据库中的文件导出工具是用于将存储在数据库中的非结构化或半结构化数据,如文本文件(.TXT)、Microsoft Word文档(.doc)和Excel表格(.xls),提取到本地文件系统的一种实用程序。这样的工具对于数据分析、备份...
1. 数据源:数据可能来自数据库、CSV文件或其他结构化数据源。你需要先将数据读取并处理成适合导出的格式。 2. 格式转换:根据目标格式的要求,你可能需要对数据进行格式化,比如设置字体、颜色、对齐方式等。 3. ...
然而,有时我们需要将GridView中的数据导出到其他格式,如PDF、Word、Excel和CSV,以便于进一步处理或分享。这个项目提供了ASP.NET Gridview导出到这些不同格式的源代码,下面我们将详细讨论相关的知识点。 首先,...
在MySQL中,将查询结果导出为CSV文件以及将CSV文件导入到数据库是常见的数据迁移和备份操作。这里我们将详细探讨这两个过程。 首先,我们来看如何导出查询结果到CSV文件。MySQL提供了一个内置的`SELECT ... INTO ...
7. **工具的替代方案**:除了手动操作,还有一些工具如MySQL Workbench、Navicat等数据库管理软件提供了数据导出为多种格式的功能,包括直接导出为Word。这些专业工具可能提供更丰富的选项和更方便的操作。 8. **...
如果你需要将数据库内容导出,通常会使用SQL查询或数据库管理工具,例如SQL Server Management Studio,来导出数据成CSV或Excel,然后根据需求进一步转换成PDF或Word格式。 总之,理解并熟练掌握将数据导出为PDF、...
"Excel导入导出"这一主题涵盖了如何将数据从外部源导入到Excel工作表,以及如何将Excel数据导出到其他格式或系统中。以下是对这个主题的详细阐述: **一、Excel数据导入** 1. **CSV导入**:CSV(逗号分隔值)是一...
"农房调查申请表和房屋调查表自动导出"的主题涉及到数据库管理和自动化工作流程,这些都是信息技术在实际业务应用中的核心组成部分。这里我们将深入探讨这个主题,包括相关技术、实施步骤以及可能的挑战。 首先,...
1. **表格数据导出**:这是将存储在数据库、电子表格或者应用程序中的结构化数据转换成可共享或保存的文件格式的过程。这个过程可以帮助用户将数据保存到本地,便于备份、分析或传输到其他系统。 2. **文件格式**:...
Bootstrap Table插件允许用户将表格中的数据导出到不同的文件格式,例如Excel、Word、TXT、XML和CSV。这些格式各有特点: - Excel(.xlsx):Microsoft Office的电子表格格式,适合数据分析、公式计算和复杂的表格...
### 使用Python处理题库表格并转化为Word形式的实现 #### 前言 在实际工作中,经常遇到需要处理大量表格数据的情况,特别是当这些数据来源于题库时,往往需要进行整理以便于更好的阅读和理解。本文详细介绍了一种...
若要导出到非结构化格式如TXT,可以使用BULK INSERT命令,将数据写入文本文件。对于XML,可以使用FOR XML子句来创建XML格式的数据。 ```sql -- 导出到TXT示例 BULK INSERT [TargetTable] FROM 'C:\Data\output....
1. 将Word文档另存为逗号分隔值(CSV)或制表符分隔的文本文件,这便于Access识别数据结构。 2. 在Access中,打开或新建数据库,选择“表”选项卡。 3. 通过“文件”菜单的“获取外部数据”选择“导入”。 4. 选择...
1. **CSV/TXT文件**:导出为CSV或TXT文件,可以使得数据兼容于其他不支持Excel的程序,如编程语言(Python、R)或数据库系统。 2. **PDF或XPS文档**:导出为PDF或XPS(XML Paper Specification)格式,可以保持格式...
这通常涉及到使用数据库连接库(如ADO.NET或pyodbc),以及Word自动化库(如Microsoft.Office.Interop.Word in .NET或python-docx for Python)。 以下是实现这个功能的可能步骤: 1. **连接到数据库**:程序首先...
- **插入Access表或查询**:首先,打开Word文档,定位到需要插入数据的位置。然后,通过“视图”菜单选择“工具栏”,启用“数据库”工具栏。点击“插入数据库”按钮,选择“获取数据”,在“文件类型”中选择“MS...
- **LEFT OUTER JOIN** 保证即使没有对应的注释记录也会显示表结构信息。 - **ORDER BY** 子句确保结果按表名和列ID排序。 #### 四、导出到Excel 1. **PL/SQL Developer**:这是一个常用的Oracle数据库开发工具,...