1 2 3 import java.awt.color; 4 import java.io.fileoutputstream; 5 import java.sql.connection; 6 import java.sql.drivermanager; 7 import java.sql.resultset; 8 import java.sql.sqlexception; 9 import java.sql.statement; 10 import java.util.arraylist; 11 import java.util.hashmap; 12 import java.util.iterator; 13 import java.util.list; 14 import java.util.map; 15 16 import com.lowagie.text.*; 17 import com.lowagie.text.rtf.rtfwriter2; 18 /** 19 * 数据库文档生成器 oracle版 20 * itext-2.1.7.jar 21 * itext-rtf-2.1.7.jar 22 * @author cuiyj 23 * 24 */ 25 public class generatetabledoc { 26 //键类型字典 27 private static map<string,string> keytype = new hashmap<string,string>(); 28 //需要导出的目标表 29 private static list<string> targettable = new arraylist<string>(); 30 static{ 31 targettable.add("common_address");//表名 32 targettable.add("l_user"); 33 } 34 //初始化jdbc 35 static{ 36 try { 37 keytype.put("id", "主键"); 38 // keytype.put("c", "check"); 39 class.forname("oracle.jdbc.oracledriver"); 40 } catch (classnotfoundexception e) { 41 e.printstacktrace(); 42 } 43 } 44 //private static string url = "";//链接url 45 private static string url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";//链接url 46 private static string username = "user"; //用户名.需要设置默认表空间哈 47 private static string password = "user"; //密码 48 private static string schema = "user"; //目标数据库名 49 //查询所有表的sql语句 50 private static string sql_get_all_tables = "select a.table_name,b.comments from user_tables a,user_tab_comments b where a.table_name=b.table_name order by table_name"; //查询所有字段的sql语句 51 private static string sql_get_all_columns = "select t1.column_name,t1.data_type,t1.data_length,t2.comments,t1.nullable,(select max(constraint_type) from user_constraints x left join user_cons_columns y on x.constraint_name=y.constraint_name where x.table_name=t1.table_name and y.column_name=t1.column_name) from user_tab_cols t1, user_col_comments t2, user_tab_comments t3 where t1.table_name=t2.table_name(+) and t1.column_name=t2.column_name(+) and t1.table_name=t3.table_name(+) and t1.table_name='{table_name}' order by t1.column_id "; 52 public static void main(string[] args) throws exception { 53 //初始化word文档 54 document document = new document(pagesize.a4); 55 rtfwriter2.getinstance(document,new fileoutputstream("e:/word.doc")); 56 document.open(); 57 //查询开始 58 connection conn = getconnection(); 59 //获取所有表 60 list tables = getdatabysql(sql_get_all_tables,conn); 61 int i=1; 62 for (iterator iterator = tables.iterator(); iterator.hasnext();) { 63 string [] arr = (string []) iterator.next(); 64 //循环获取字段信息 65 string tablename = arr[0]; 66 if(targettable.contains(tablename)){ 67 system.out.print(i+".正在处理数据表-----------"+arr[0]); 68 addtablemetadata(document,arr,i); 69 list columns = getdatabysql(sql_get_all_columns.replace("{table_name}", arr[0]),conn); 70 addtabledetail(document,columns); 71 addblank(document); 72 system.out.println("...done"); 73 i++; 74 } 75 } 76 document.close(); 77 conn.close(); 78 } 79 /** 80 * 添加一个空行 81 * @param document 82 * @throws exception 83 */ 84 public static void addblank(document document)throws exception{ 85 paragraph ph = new paragraph(""); 86 ph.setalignment(paragraph.align_left); 87 document.add(ph); 88 } 89 /** 90 * 添加包含字段详细信息的表格 91 * @param document 92 * @param arr1 93 * @param columns 94 * @throws exception 95 */ 96 public static void addtabledetail(document document,list columns)throws exception{ 97 table table = new table(6); 98 table.setwidth(100f); 99 table.setborderwidth(1); 100 table.setbordercolor(color.black); 101 table.setpadding(0); 102 table.setspacing(0); 103 cell cell1 = new cell("序号");// 单元格 104 cell1.setheader(true); 105 106 cell cell2 = new cell("列名");// 单元格 107 cell2.setheader(true); 108 109 cell cell3 = new cell("类型");// 单元格 110 cell3.setheader(true); 111 112 cell cell4 = new cell("长度");// 单元格 113 cell4.setheader(true); 114 115 cell cell5 = new cell("键");// 单元格 116 cell5.setheader(true); 117 118 cell cell6 = new cell("说明");// 单元格 119 cell6.setheader(true); 120 //设置表头格式 121 table.setwidths(new float[]{8f,30f,15f,8f,10f,29f}); 122 cell1.sethorizontalalignment(cell.align_center); 123 cell1.setbackgroundcolor(color.gray); 124 cell2.sethorizontalalignment(cell.align_center); 125 cell2.setbackgroundcolor(color.gray); 126 cell3.sethorizontalalignment(cell.align_center); 127 cell3.setbackgroundcolor(color.gray); 128 cell4.sethorizontalalignment(cell.align_center); 129 cell4.setbackgroundcolor(color.gray); 130 cell5.sethorizontalalignment(cell.align_center); 131 cell5.setbackgroundcolor(color.gray); 132 cell6.sethorizontalalignment(cell.align_center); 133 cell6.setbackgroundcolor(color.gray); 134 table.addcell(cell1); 135 table.addcell(cell2); 136 table.addcell(cell3); 137 table.addcell(cell4); 138 table.addcell(cell5); 139 table.addcell(cell6); 140 table.endheaders();// 表头结束 141 int x = 1; 142 for (iterator iterator = columns.iterator(); iterator.hasnext();) { 143 string [] arr2 = (string []) iterator.next(); 144 cell c1 = new cell(x+""); 145 cell c2 = new cell(arr2[0]); 146 cell c3 = new cell(arr2[1]); 147 cell c4 = new cell(arr2[2]); 148 149 string key = keytype.get(arr2[5]); 150 if(key==null)key = ""; 151 cell c5 = new cell(key); 152 cell c6 = new cell(arr2[3]); 153 c1.sethorizontalalignment(cell.align_center); 154 c2.sethorizontalalignment(cell.align_center); 155 c3.sethorizontalalignment(cell.align_center); 156 c4.sethorizontalalignment(cell.align_center); 157 c5.sethorizontalalignment(cell.align_center); 158 c6.sethorizontalalignment(cell.align_center); 159 table.addcell(c1); 160 table.addcell(c2); 161 table.addcell(c3); 162 table.addcell(c4); 163 table.addcell(c5); 164 table.addcell(c6); 165 x++; 166 } 167 document.add(table); 168 } 169 /** 170 * 增加表概要信息 171 * @param dcument 172 * @param arr 173 * @param i 174 * @throws exception 175 */ 176 public static void addtablemetadata(document dcument,string [] arr,int i) throws exception{ 177 paragraph ph = new paragraph(i+". 表名: "+arr[0]+" 说明: "+(arr[1]==null?"":arr[1])); 178 ph.setalignment(paragraph.align_left); 179 dcument.add(ph); 180 } 181 /** 182 * 把sql语句查询出列表 183 * @param sql 184 * @param conn 185 * @return 186 */ 187 public static list getdatabysql(string sql,connection conn){ 188 statement stmt = null; 189 resultset rs = null; 190 list list = new arraylist(); 191 try { 192 stmt = conn.createstatement(); 193 rs = stmt.executequery(sql); 194 while(rs.next()){ 195 string [] arr = new string[rs.getmetadata().getcolumncount()]; 196 for(int i=0;i<arr.length;i++){ 197 arr[i] = rs.getstring(i+1); 198 } 199 list.add(arr); 200 } 201 } catch (sqlexception e) { 202 e.printstacktrace(); 203 }finally{ 204 try { 205 if(rs!=null)rs.close(); 206 if(stmt!=null)stmt.close(); 207 } catch (sqlexception e) { 208 e.printstacktrace(); 209 } 210 } 211 return list; 212 } 213 /** 214 * 获取数据库连接 215 * @return 216 */ 217 public static connection getconnection(){ 218 try { 219 return drivermanager.getconnection(url, username, password); 220 } catch (sqlexception e) { 221 e.printstacktrace(); 222 } 223 return null; 224 } 225 }
1 ------ 这是相关依赖 2 <dependency> 3 <groupid>com.lowagie</groupid> 4 <artifactid>itext</artifactid> 5 <version>2.1.7</version> 6 </dependency> 7 <dependency> 8 <groupid>com.lowagie</groupid> 9 <artifactid>itext-rtf</artifactid> 10 <version>2.1.7</version> 11 </dependency>