oracle数据库自动生成数据库表结构文档(亲测有效)
1 23import java.awt.Color;
4import java.io.FileOutputStream;
5import java.sql.Connection;
6import java.sql.DriverManager;
7import java.sql.ResultSet;
8import java.sql.SQLException;
9import java.sql.Statement;
10import java.util.ArrayList;
11import java.util.HashMap;
12import java.util.Iterator;
13import java.util.List;
14import java.util.Map;
15
16import com.lowagie.text.*;
17import 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*/
25publicclass GenerateTableDoc {
26//键类型字典
27privatestatic Map<String,String> keyType = new HashMap<String,String>();
28//需要导出的目标表
29privatestatic List<String> targetTable = new ArrayList<String>();
30static{
31 targetTable.add("COMMON_ADDRESS");//表名
32 targetTable.add("L_USER");
33 }
34//初始化jdbc
35static{
36try {
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
45privatestatic String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";//链接url
46privatestatic String username = "user"; //用户名.需要设置默认表空间哈
47privatestatic String password = "user"; //密码
48privatestatic String schema = "USER"; //目标数据库名
49//查询所有表的sql语句
50privatestatic 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语句
51privatestatic 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 ";
52publicstaticvoid 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);
61int i=1;
62for (Iterator iterator = tables.iterator(); iterator.hasNext();) {
63 String [] arr = (String []) iterator.next();
64//循环获取字段信息
65 String tableName = arr[0];
66if(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*/
84publicstaticvoid 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*/
96publicstaticvoid 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(newfloat[]{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();// 表头结束
141int x = 1;
142for (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]);
150if(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*/
176publicstaticvoid 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*/
187publicstatic List getDataBySQL(String sql,Connection conn){
188 Statement stmt = null;
189 ResultSet rs = null;
190 List list = new ArrayList();
191try {
192 stmt = conn.createStatement();
193 rs = stmt.executeQuery(sql);
194while(rs.next()){
195 String [] arr = new String[rs.getMetaData().getColumnCount()];
196for(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{
204try {
205if(rs!=null)rs.close();
206if(stmt!=null)stmt.close();
207 } catch (SQLException e) {
208 e.printStackTrace();
209 }
210 }
211return list;
212 }
213/**
214 * 获取数据库连接
215 * @return
216*/
217publicstatic Connection getConnection(){
218try {
219return DriverManager.getConnection(url, username, password);
220 } catch (SQLException e) {
221 e.printStackTrace();
222 }
223returnnull;
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数据库自动生成数据库表结构文档(亲测有效) 的全部内容， 来源链接： utcz.com/z/531362.html
