长沙外汇,同涩吧,新浪微博粉丝
我要评论 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>
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
Oracle字符串函数length、upper、lower、initcap、 concat、instr、replace
网友评论