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
25 public class GenerateTableDoc {
26 //键类型字典
27 private static Map keyType = new HashMap();
28 //需要导出的目标表
29 private static List targetTable = new ArrayList();
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
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
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
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
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){
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
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.lowagiegroupId>
4 <artifactId>itextartifactId>
5 <version>2.1.7version>
6 dependency>
7 <dependency>
8 <groupId>com.lowagiegroupId>
9 <artifactId>itext-rtfartifactId>
10 <version>2.1.7version>
11 dependency>
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
