Oracle生成数据库设计文档_oracle 导出数据库设计文档

效果图：

添加相关maven配置

<dependency>
    <groupId>com.lowagie.text</groupId>
    <artifactId>com.springsource.com.lowagie.text</artifactId>
    <version>2.0.8</version>
</dependency>
1
2
3
4
5

package bigdata.msgcnt.manager.service;
 
import java.awt.Color;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
 
import com.lowagie.text.Cell;
import com.lowagie.text.Document;
import com.lowagie.text.PageSize;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Table;
import com.lowagie.text.rtf.RtfWriter2;
/**
 * 数据库文档生成器 Oracle版
 * itext-2.1.7.jar
 * itext-rtf-2.1.7.jar
 * @author Eric zhou
 *
 */
public class GenerateTableDoc {
	//键类型字典
	private static Map<String,String> keyType = new HashMap<String,String>();
	//需要导出的目标表
	private static List<String> targetTable = new ArrayList<String>();
	static{
		targetTable.add("T_MAIL_CONFIG");
		targetTable.add("T_MAIL_CONFIG2");
		targetTable.add("T_MAIL_RECORD");
		targetTable.add("T_MAIL_TEMPLATE");

		targetTable.add("T_MSG_PATH_DISABLE");
		targetTable.add("T_MSG_RECEIVER");
		targetTable.add("T_MSG_RECORD");
		targetTable.add("T_OPEN_API_CONSUME");

		targetTable.add("T_PUSH_CONFIG");
		targetTable.add("T_PUSH_RECORD");
		targetTable.add("T_PUSH_TEMPLATE");
		targetTable.add("T_RECEIVER_RESULT");

		targetTable.add("T_SMS_CONFIG");
		targetTable.add("T_SMS_RECORD");
		targetTable.add("T_SMS_TEMPLATE");
		targetTable.add("T_WX_MP_CONFIG");

		targetTable.add("T_WX_MP_RECORD");
		targetTable.add("T_WX_MP_TEMPLATE");

	}
	//初始化jdbc
	static{
		try {
			keyType.put("P", "主键");
//			keyType.put("C", "Check");
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	private static String url = "*****";//链接url
//	private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";//链接url
	private static String username = "USER_MSG"; //用户名.需要设置默认表空间哈
	private static String password = "****"; //密码
	private static String schema = "****"; //目标数据库名
	//查询所有表的sql语句
	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语句
	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 ";
	public static void main(String[] args) throws Exception {
		//初始化word文档
		Document document = new Document(PageSize.A4); 
		RtfWriter2.getInstance(document,new FileOutputStream("E:/word.doc"));  
		document.open();  
		//查询开始
		Connection conn = getConnection();
		//获取所有表
		List tables = getDataBySQL(sql_get_all_tables,conn);
		int i=1;
		for (Iterator iterator = tables.iterator(); iterator.hasNext();) {
			String [] arr = (String []) iterator.next();
			//循环获取字段信息
			String tableName = arr[0];
			if(targetTable.contains(tableName)){
				System.out.print(i+".正在处理数据表-----------"+arr[0]);
				addTableMetaData(document,arr,i);
				List columns = getDataBySQL(sql_get_all_columns.replace("{table_name}", arr[0]),conn);
				addTableDetail(document,columns);
				addBlank(document);
				System.out.println("...done");
				i++;
			}
		}
		document.close();  
		conn.close();
	}
	/**
	 * 添加一个空行
	 * @param document
	 * @throws Exception
	 */
	public static void addBlank(Document document)throws Exception{
		Paragraph ph = new Paragraph("");
		ph.setAlignment(Paragraph.ALIGN_LEFT); 
		document.add(ph);
	}
	/**
	 * 添加包含字段详细信息的表格
	 * @param document
	 * @param columns
	 * @throws Exception
	 */
	public static void addTableDetail(Document document,List columns)throws Exception{
		Table table = new Table(7);
		table.setWidth(100f);
	    table.setBorderWidth(1);  
	    table.setBorderColor(Color.BLACK);  
	    table.setPadding(0);  
	    table.setSpacing(0);  
	    Cell cell1 = new Cell("序号");// 单元格
	    cell1.setHeader(true);  
	    
	    Cell cell2 = new Cell("列名");// 单元格
	    cell2.setHeader(true); 
	    
	    Cell cell3 = new Cell("类型");// 单元格
	    cell3.setHeader(true); 
	    
	    Cell cell4 = new Cell("长度");// 单元格
	    cell4.setHeader(true); 
	    
	    Cell cell5 = new Cell("键");// 单元格
	    cell5.setHeader(true);

		Cell cell6 = new Cell("非空");// 单元格
		cell5.setHeader(true);

		Cell cell7 = new Cell("说明");// 单元格
	    cell6.setHeader(true);
	    //设置表头格式
	    table.setWidths(new float[]{8f,30f,15f,8f,10f,9f,20f});
	    cell1.setHorizontalAlignment(Cell.ALIGN_CENTER);
	    cell1.setBackgroundColor(Color.gray);
	    cell2.setHorizontalAlignment(Cell.ALIGN_CENTER);
	    cell2.setBackgroundColor(Color.gray);
	    cell3.setHorizontalAlignment(Cell.ALIGN_CENTER);
	    cell3.setBackgroundColor(Color.gray);
	    cell4.setHorizontalAlignment(Cell.ALIGN_CENTER);
	    cell4.setBackgroundColor(Color.gray);
	    cell5.setHorizontalAlignment(Cell.ALIGN_CENTER);
	    cell5.setBackgroundColor(Color.gray);
	    cell6.setHorizontalAlignment(Cell.ALIGN_CENTER);
	    cell6.setBackgroundColor(Color.gray);
		cell7.setHorizontalAlignment(Cell.ALIGN_CENTER);
		cell7.setBackgroundColor(Color.gray);
	    table.addCell(cell1);  
	    table.addCell(cell2);  
	    table.addCell(cell3);  
	    table.addCell(cell4);  
	    table.addCell(cell5);
	    table.addCell(cell6);
		table.addCell(cell7);
	    table.endHeaders();// 表头结束
	    int x = 1;
	    for (Iterator iterator = columns.iterator(); iterator.hasNext();) {
			String [] arr2 = (String []) iterator.next();
			Cell c1 = new Cell(x+"");
			Cell c2 = new Cell(arr2[0]);
			Cell c3 = new Cell(arr2[1]);
			Cell c4 = new Cell(arr2[2]);
			
			String key = keyType.get(arr2[5]);
			if(key==null)key = "";
			Cell c5 = new Cell(key);
			Cell c6 = new Cell("Y".equals(arr2[4])?"否":"是");
			Cell c7 = new Cell(arr2[3]);
			c1.setHorizontalAlignment(Cell.ALIGN_CENTER);
			c2.setHorizontalAlignment(Cell.ALIGN_CENTER);
			c3.setHorizontalAlignment(Cell.ALIGN_CENTER);
			c4.setHorizontalAlignment(Cell.ALIGN_CENTER);
			c5.setHorizontalAlignment(Cell.ALIGN_CENTER);
			c6.setHorizontalAlignment(Cell.ALIGN_CENTER);
			c7.setHorizontalAlignment(Cell.ALIGN_CENTER);
			table.addCell(c1);
			table.addCell(c2);
			table.addCell(c3);
			table.addCell(c4);
			table.addCell(c5);
			table.addCell(c6);
			table.addCell(c7);
			x++;
		}
	    document.add(table);
	}
	/**
	 * 增加表概要信息
	 * @param dcument
	 * @param arr
	 * @param i
	 * @throws Exception
	 */
	public static void addTableMetaData(Document dcument,String [] arr,int i) throws Exception{
		Paragraph ph = new Paragraph(i+". 表名: "+arr[0]+"        说明: "+(arr[1]==null?"":arr[1]));
		ph.setAlignment(Paragraph.ALIGN_LEFT); 
		dcument.add(ph);
	}
	/**
	 * 把SQL语句查询出列表
	 * @param sql
	 * @param conn
	 * @return
	 */
	public static List getDataBySQL(String sql,Connection conn){
		Statement stmt = null;
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				String [] arr = new String[rs.getMetaData().getColumnCount()];
				for(int i=0;i<arr.length;i++){
					arr[i] = rs.getString(i+1);
				}
				list.add(arr);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null)rs.close();
				if(stmt!=null)stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	/**
	 * 获取数据库连接
	 * @return
	 */
	public static Connection getConnection(){
		try {
			return DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258