oracle数据库自动生成数据库表结构文档(亲测有效)

  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>

 

 

 

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐