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>