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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/31536.html
標籤：Oracle
上一篇：如何用pb開發類似郵件編輯的界面并將內容保存到資料庫
下一篇：Bug 28450914 : ORA-600: [KDLRCI_GET_INLINE_DATA] SELECTING FROM CDB_FEATURE_USAGE_STATISTICS